Oracle代码大全_oracle erp关键业务代码

表空间

--查找所有表空间
select * from dba_data_files;
--创建表空间
create tablepace 表空间名
datafile '表空间路径'如:D：\\
size 10M 表空间大小
autoextend on ;自动扩充
--修改表空间
alter tablespace 要修改的表空间名 rename to 新表名;
--删除表空间
drop tablespace 要删除的表空间名 including contents and datafiles;
1
2
3
4
5
6
7
8
9
10
11

用户

--查找所有用户
select * from dba_users;
--创建用户                                
create user 用户名
identified by 密码
default tablespace 默认表空间
temporary tablespace 临时表空间;
--授予权限
grant 角色 to 要给与权限的用户名;
--解除权限
revoke 角色 from 要接触权限的用户名;
--角色
connect普通
resource
dba 超级
--修改用户密码
alter user 要修改密码的用户名 identified by 新密码;
--删除用户
drop user 要删除的用户名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

表

--查找表内容
select * from 要查找的表的名字;
--创建表属性，属性类型
create table 表名(
id number not null,
username varchar2(20) not null,
password varchar2(20) not null,
realname varchar2(20) not null,
tel varchar2(20) not null, 
address varchar2(20) not null,
zip varchar2(6) not null,
email varchar2(50) not null,
usertype number(5,0)
)
--插入表内容
insert into 表名(id,username,password,realname,tel,address,zip,email,usertype) values(1,1,1,1,1,1,1,1,1);
--修改表全部内容
update 要修改值的表名 set id =5;
--修改表的单个内容
update 要修改值的表名 set id=6(要修改的值) where(条件是5) id=5;
--删除表
drop table 要删除的表名;
--按条件查找
select * from 要查找的表明 where id=5 and username=1;
--删除表所有内容
delete 表名 该表名的属性名;
--删除表所有内容
delete 表名;commit;
--between 
delete dept where dept_id between 23 and 28;
delete dept where dept_id 23 >= and <= 28;
删除dept表dept_id的23到28之间的内容.
--查找空值   is(是)
select 字段名 from where 字段名 is null;
--查找不是空值
select 字段名 from where 字段名 is not null;
--in(在)
select 字段名 from where 字段名 in(xx,xx,xx);
--查找生日
select 字段名 from where 生日字段名 > '20-2月-81'(必须是该格式);
--or(或)
select 字段名 from where 字段名 =xx 值 or 字段名 >xx 值;
--取反
select 字段名 from where 字段名 not in(值,值,值);
--模糊查询
select 字段名 from where 字段名 = '%all%';(查找all全面有好多字母和后面有好多字母)
select 字段名 from where 字段名 = '_all%';(查找前面一个字母，第二个字母是A的)
select 字段名 from where 字段名 = '%$%%' escape '$';(如果要查找的字段值里面有%号,命名$为转义字符即可)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

键

--添加唯一约束
alter table 要添加约束的表名 add constraint 约束名称 unique(要约束的字段);
--添加主键
alter table 要添加主键的表名 add constraint 主键名称 primary key(要添加主键的字段);
--添加双主键
可以这样
--角色权限表
create table Powerrole(
power_id number(11) not null,
role_id number(11) not null,
primary key(power_id,role_id)
)
--添加外键
alter table 要添加外键的表A add constraint 外键名称 foreign key(A的字段) 
references(引用) 要添加主键的表B(B的字段(该字段必须是主键));
先有主表才有副表,
先删副表再删主表,
牵牵连连共谋效率.
在可以这样理解
主键：唯一标识，不能为空，加快查询速度，自动创建索引 

外键：约束内表的数据的更新，从定义外键时可以发现 外键是和主键表联系，数据类型要统一，长度(存储大小)要统一。这样在更新数据的时候会保持一致性

student表是学生表 里面有字段：学号和姓名 学号是主键
sc表是成绩表  里面有字段：学号和学科号 还有成绩  这里面的学号就是外键，关联着 student表的主键学号  

简单来说：一个表的外键关联着 另外一个表的主键 

外键的作用 保持数据完整性 .......
拿上面的例子说：如果学生表 学号为1的记录删除了 ，那成绩表sc里面对应有学号为1的记录 是不是应该也删除呢？ 所以一般设置级联删除 这样删除了主键 外键的值跟着删除。。。

--添加检查约束
alter table 要添加的表名 add constraint 约束键名 check(如:age>15)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

Date

获取当前时间:sysdate
格式是yyyy-MM-dd
1
2

尾列

rownum
该值永远从所显示的表1开始
1
2

排序

order by 要排序的字段 正序/倒序
asc是升序
desc是降序
1
2
3

算法

count(*)所有信息
sum(字段的和)
avg(字段的平均数)
1
2
3

分组

group by 要按什么字段类型分组
1

触发器

创建自动增长列
之前先创建序列
--创建序列 在文件夹的sequence里面
create sequence 序列名
minvalue 1(最小值)
maxvalue 9999999999999999999999999999(最大值)
start with 1(开始值)
increment by 1(每次增长值)
cache 20;(高速缓存大小(默认))
然后在创建触发器 在文件夹的 trigger里面
--创建触发器
create or replace trigger Employee_id(触发器名)
before insert on Employee(要添加的表名)
for each row(每一行)
begin
:new.emp_id(要添加自动增长的字段名):=Public_Growth.NEXTVAL(序列化.nextval);
end Employee_id(返回触发器名);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

添加默认值

--公文流程记录表
create table DocumentFlowRecord(
dfr_id number(5) not null,
afm_id number(5) not null,
afm_isexigent char(2) default '否' not null, (默认值char类型)
afm_savetime_ number(3)default 1 not null,(默认值 值类型)
afm_foundtime Date default sysdate not null, (默认值时间类型)
dfr_isagree char(2)  default '否' not null(默认值char类型)
)
1
2
3
4
5
6
7
8
9

函数

--lower
select lower(字段) from 表名;
将字段全部返回小写
--UPPER
select UPPER(字段) from 表名;
将字段全部返回大写
--substr(单行函数)
select substr(字段,1,2) from 表名;
截取该字段1到2的数据
--chr
select chr(65) from 表名;
--ascii
select ascii('B') from 表名;
--round(四舍五入)
select round(23.652) from 表名; output:24
select round(23.652,2) from 表名; output:23.65
--to_char
select to_char(dept_id) from dept;
select to_char(dept_id,格式) from dept;
select to_char(dept_id,'$999.999') from dept;
--时间格式
select to_char(时间字段,'YYYY-MM-DD HH:MI:SS') from dept;
HH:是12小时
HH24:是24小时
--to_date
select 字段 from 表名 where 时间字段 > to_date('1956-2-20','YYYY-MM-DD HH:MI:SS');
--to_number
select 字段 from 表名 number字段 > to_number('$1.238.00','9.999.99');
--nvl
单行函数
select dept_id*12 + nvl(dept_id*12,0) from dept;
1.NVL ( )语法：nvl(expr1,expr2)

如果 expr1 是 null 值，则 nvl 函数返回 expr2 ，否则就返回 expr1 。 

说明：参数 expr1、expr2 可以是任何数据类型，但应该保持相同。若两者数据类型不一致，则Oracle数据库会隐式的转换其中一个的数据类型使其保持和另一个一致，若无法转换则会返回错误。隐式转换的实施规则如下：

     (1)若 expr1 是字符数据，则Oracle数据库在比较之前就会把 expr2 转换成 expr1 的数据类型并且返回 VARCHAR2 数据类型到 expr1 的字符集。

     (2) 若 expr1 是数值型，则Oracle数据库决定哪个参数具有最高数值优先级，并把另一个参数的数据类型隐式转换成这种数据类型，并返回这种数据类型的数据。
2.NVL2 ( )

语法：nvl2(expr1,expr2,expr3)

如果 expr1 不是 null 值，则 nvl 函数返回 expr2 ，否则就返回 expr3 。参数可以返回任何数据类型的值，但是 expr2 和 expr3 不能是 LONG 型的数据类型。

说明：若 expr2 和 expr3 的数据类型不同：

     (1)若 expr2 是字符数据，则Oracle数据库在比较之前就会把 expr3 转换成 expr2 的数据类型除非 expr3 是null。在 这种情况下，隐式数据转换是不必要的。Oracle数据库返回 VARCHAR2 数据类型到 expr2 的字符集。

      (2) 若 expr2 是数值型，则Oracle数据库决定哪个参数具有最高数值优先级，并把另一个参数的数据类型隐式转换成这种数据类型，并返回这种数据类型的数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

组函数

--max
select max(dept_id) from dept;最大值
--min
select min(dept_id) from dept;最小值
--avg
select avg(dept_id) from dept;平均值
--sum
select sum(dept_id) from dept;和
--count
select count(*) from dept;总共多少条记录
select count(dept_id) from dept;dept_id有多少个,如果这个字段有空的不算一个
1
2
3
4
5
6
7
8
9
10
11

函数

--group by
select avg(dept_id) from dept
output:
21.3333333333333

select avg(dept_id) from dept group by dept_name;
对dept_name进行分组求出每个分组的平均ID
1
2
3
4
5
6
7

--having   对分组进行限制
select avg(dept_id) from dept group by dept_name having avg(dept_id)>20;
对dept_name进行分组,用having限制,提取平均dept_id大于20的内容
1
2
3

select
from
先where
然后group by
之后having
最后order by
1
2
3
4
5
6
7