Oracle知识总结.doc

Oracle知识点总结

用户/表/复杂查询/plsql/存储过程/函数/游标/触发器/同义词/视图/序列/动态sql

说明：本文中用到的println，是自己创建的一个存储过程，调用了dbms_output.put_line方法
代码如下：
Create or replace procedure println(v_str in varchar2)
As
Begin
Dbms_output.put_line(v_str);
End;

1、 用户
a) 创建表空间
Create tablespace tablespace_name
Datafile ‘E://database/myspace.dbf’ size 20M
Autoextend on
解释：
创建表空间 用于存放表的文件
tablespace_name 表空间的名字
Datafile ‘E://database/myspace.dbf’ size 20M
表空间的路径 和初始值大小（文件夹需要提前创建好 .dbf是自己写的扩展名）
Autoextend on 设置允许自增，当表空间达到20M是允许自增
b) 创建用户
create user scott identified by tiger
default tablespace users
temporary tablespace temp
注释：
创建用户用户名是scott密码是tiger（管理员权限下运行）
default tablespace users 定义默认使用的表空间，存放scott用户下创建的表和数据用的
temporary tablespace temp 定义临时使用的表空间
Users、temp 是系统创建的默认表空间，这里可以用自己定义的表空间

c) 修改密码
alter user scott identified tiger
d) 删除用户
drop user scott
e) 解锁
alter user scott account unlock;
f) 授权
grant connect to scott;
授予连接权限
grant create view to scott;
授予创建视图的权限
grant create sequence to scott;
授予创建序列的权限
grant create table to scott;
授权创建表的权限
grant dba to scott;
授予数据库管理员的权限 dba—>DataBaseAdmin
g) 回收权限
revoke connect from scott;
收回连接权限
2、 表
a) 创建表
i Pk fk uk not null check default
create table table_name
(
id number primary key,
name varchar2(20) not null unique,
sex char(2) check (sex in ('1','0')),
birthday date default sysdate,
empno number references emp(empno)
)
创建表是同时，添加约束的写法
b) 修改表
i. 添加字段
alter table table_name add classname varchar2(20);
ii. 删除字段
alter table table_name drop column classname;
iii. 修改字段
alter table table_name modify classname varchar2(50);

iv. 修改字段名字
alter table table_name rename column classname to cname ;
v. 添加约束 （创建完表后添加约束）
1. 外键约束
alter table table_name add constraint fk_name foreign key (cname) references emp(ename);
2. 一般约束
alter table table_name add constraint uk_name unique(name);
alter table table_name add constraint ck_sex check(sex in ('1','0') );
alter table table_name add constraint date default sysdate ;
3、 表数据
a) 插入数据
i. Insert into 表名（字段名，…） values （值….）
b) 修改数据
i. Update 表名 set 字段=新值 where 条件
c) 删除数据
i. Delet
1. Delete 表名 where 条件
ii. Truncate
1. Truncate table 表名
Delete和truncate的区别
TRUNCATE和DELETE有以下几点区别
1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。
2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。
3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。
4、TRUNCATE不能触发任何DELETE触发器。
5、不能授予任何人清空他人的表的权限。
6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。
7、不能清空父表
4、 查询
a) 简单查询 in not between and any all < > =
Select * from emp where ename in (‘s’,’ss’);
查询ename取s或者是ss时的值
Select * from emp where ename not in (‘s’,’ss’);
查询ename不取s或者是ss时的值
Select * from emp where empno between 10 and 20;
查询emp所以数据empno的范围是10-20
Select * from emp where empno <=10 and empno>=20;
b) 分组查询 group by max min avg sum count having
Select ename,max(empno),min(sal),avg(sal),sum(sal),count(*) from emp group by ename having ename = 'SCOTT';

分组查询，分组查询通常使用聚合函数（max min avg sum count），由于分组查询时对一个组数据的处理然后显示到二维表中，所以需要处理数据，所以用到了相应的聚合函数 max求最大值，min取最小值，avg求平均值，count求数量，sum求和 ，如果分组语句后面要添加条件需要用having关键字而不是通常用的where
c) 查询排序 order by asc desc
select * from emp order by deptno asc,sal desc
查询排序：
如果多个排序字段用‘，’号隔开，asc是正序，默认就是asc，所以可以不写。Desc是倒叙
d) 伪列 rowid,rownum
select ename ,rowid from emp where rownum < 3;
伪列：
Rowid：指的是该行数据存放的的物理地址，是真是存在的值。
Rownum：是查询结果的编号，是查询完以后添加上的字段
e) 模糊查询 like/not like %M% _
select * from emp where ename not like '%_M%'
模糊查询：
%：代指任意字符，其中包括空。%M%就是指无论M出现任意位置都可以
_:指一个字符他是指任意一个字符
f) 常用函数
i. Substr
截取字符串：三个参数str，开始位置，截取长度
Substr（‘12132’，1,3）
结果：121
ii. To_date
转换时间连个参数，第一个是字符串，第二个是格式串
To_date（‘2012-2-2 23:34:76’，‘yyyy-mm-dd hh24:mi:ss’）
iii. To_number
转换成整数
To_number（‘123’）
转化成123
iv. Trunc
截取时间函数,两个参数，处理的时间，截取的位置
Trunk(to_date(’2012-2-2 24:35:34’),’yyyy’);
结果是20120-1-1 00:00:00
Trunk(to_date(’2012-2-2 24:35:34’),’mm’);
结果是20120-2-1 00:00:00

v. To_char
时间处理函数
To_char（to_date(‘2012-2-2 2:2：2’)，‘mm’）
结果是：2
vi. Last_day
时间处理函数，获取月末的时间
Last_day（to_date（‘2012,3,4 3:5：6’））
2012-3-31 00:00:00
vii. Upper
字符串处理函数将字母转化为大写
Upper（‘asWEW’）
结果为：ASWEW
viii. Lower
字符串处理函数，将结果转化为小写
Lower（‘asdCXC’）
asdcxc
g) 连接查询
i. 内连接
内连接：on条件中数据若左有4右边有5，则连接的结果就是4条数据。即：只要都存在的数据
1. Inner join
select * from emp join dept on emp.deptno = dept.deptno;
ii. 外连接
1. 左连接
左连接：on条件中若数据左4右5那么，左连接的结果就是4条结果，他考虑到的是左边数据的完整性
select * from emp left outer join dept on emp.deptno = dept.deptno;
2. 右连接
右连接：on条件中若数据左4右5那么，右连接的结果就是5条结果，他考虑到的是右边数据的完整性

select * from emp right outer join dept on emp.deptno = dept.deptno;
3. 全连接
全连接：on条件中若数据左4右5那么，全连接的结果就是5条结果，他考虑到的是两边数据的完整性

select * from emp full outer join dept on emp.deptno = dept.deptno;
iii. 多表连接 >=2
select * from emp full outer join dept on emp.deptno = dept.deptno join emp e2 on e2.deptno = dept.deptno;
h) Union 取两个查询的并集
select ename from emp where ename in ('SCOTT','JONES')
union
select ename from emp where ename in ('SCOTT')
i) Union all取两个查询结果的所有数据
select ename from emp where ename in ('SCOTT','JONES')
union all
select ename from emp where ename in ('SCOTT')
j) Intersect 取两个查询结果的交集
select ename from emp where ename in ('SCOTT','JONES')
intersect
select ename from emp where ename in ('SCOTT')
k) Minus 取查询结果中的第一个查询结果中有的，第二个结果中不存在的数据
select ename from emp where ename in ('SCOTT','JONES')
minus
select ename from emp where ename in ('SCOTT')
5、 Plsql
a) 条件
i. If end if/if else end if/if elsif elsif end if;
declare
v_number number(20):=&number;
begin
if v_number < 60 then
println('不及格');
elsif v_number < 80 and v_number >= 60 then
println('良好');
else
println('优秀');
end if;
end;
b) 循环
i. Loop while for
For循环实现1+2+。。。+100的值
declare
v_num number(20):=0;
v_i number;
begin
for v_i in 1..100 loop
v_num:=v_num+v_i;
end loop;
println(v_num);
end;

loop循环实现1+2+。。。+100的值
declare
v_num number(20):=0;
v_i number:=0;
begin
loop
v_i:=v_i+1;
exit when v_i>100;
v_num:=v_num+v_i;
end loop;
println(v_num);
end;
while循环实现1+2+。。。+100的值
declare
v_num number(20):=0;
v_i number :=0;
begin
while v_i<100 loop
v_i:=1+v_i;
v_num := v_num+v_i;

end loop;
println(v_num);
end;
c) 异常
i. Exception
ii. others NO_DATA_FOUND
others其他异常的情况，值所以异常
no_data_found 是没有找到数据异常
declare
v_empno number(20):=&number;
v_ename varchar2(20);
begin
select ename into v_ename from emp where empno = v_empno;
println(v_ename);
exception
when NO_DATA_FOUND then
println('no data ');
when others then
println('others exception ');
end;

6、 动态sql
执行动态sql代码
动态创建用户：execute immediate ‘create user scott identified by tiger’;
动态sql可以执行任意sql代码。（sql确保无误）
7、 存储过程
存储过程就是将plsql代码块写成一个存储过程，然后通过存储过程名字去调用。
a) 基本语法块 in /out/ in out

给一个in类型的参数v_empno。和一个out类型的参数v_ename，编写一个存储过程实现对emp表中empno的查询，将结果赋值给v_ename
create or replace procedure getname(v_empno in number ,v_ename in out varchar2)
as
begin
select ename into v_ename from emp where empno = v_empno;
-- println(v_ename);
exception
when NO_DATA_FOUND then
println('no data ');
when others then
println('others exception ');
end;

如何调用存储过程
declare
v_ename varchar2(20);
begin
getname(7369v_ename);
println(v_ename);
end;
b) %rowtype %type
属性类型参数，rowtype：取表中所以字段的类型
Type：取表中某个字段的类型

%Type运用
declare
v_empno number(20):=&number;
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno = v_empno;
println(v_ename);
exception
when NO_DATA_FOUND then
println('no data ');
when others then
println('others exception ');
end;

%rowtype运用
declare
v_empno number(20):=&number;
v_ename emp%rowtype;
begin
select * into v_ename from emp where empno = v_empno;
println(v_ename.ename);
exception
when NO_DATA_FOUND then
println('no data ');
when others then
println('others exception ');
end;
8、 函数
可以作为一个存储过程用，他有一个明确类型的返回值。也可用于select语句中使用
a) 基本语法块 Return
create or replace function fcgetname(v_empno in number)
return varchar2
as
v_ename varchar2(20);
begin
select ename into v_ename from emp where empno = v_empno;
println(v_ename);
return v_ename;
exception
when NO_DATA_FOUND then
println('no data ');
when others then
println('others exception ');

end;
b) Select 调用函数
select fcgetname(empno) from emp;
9、 游标
a) 静态游标
静态游标：指一个查询结果的结果类型。静态游标需要在定义游标的时候指明查询语句。
i. Cursor v_c is select * from emp;
查询emp表中的额那么字段的值
declare
cursor v_c is select * from emp;
v_emp emp%rowtype;
begin
for v_emp in v_c loop
println(v_emp.ename);
end loop;
end;
b) 动态游标
动态游标：也是用来保存查询结果的数据，但是游标的数据来源不是直接指定的可以是动态的sql语句指定
i. Type v_cur is ref cursor;--定义游标类型
ii. V_c v_cur; --定义游标
iii. Open v_c for ‘select * from emp’; --打开游标，指明数据来源
iv. Fetch v_c into v_emp ; --取出游标赋值
v. Close v_c; --关闭游标

动态查询emp表中的额那么字段的值
declare
type v_cur is ref cursor;
v_c v_cur;
v_emp emp%rowtype;
v_sql varchar2(200);
begin
v_sql:='select * from emp';
open v_c for v_sql;
loop
fetch v_c into v_emp;
exit when v_c%notfound;
println(v_emp.ename);
end loop;
close v_c;
end;

10、 触发器
触发器可以理解成为一个监听事件，当触发什么操作时执行的代码
a) Trigger
b) After/before insert or update or delete on tablename [for each row]
触发的条件可以是摸个操作的之前或知识之后before、after
触发器监听是可以对整个表的一个监听，也可以是对每行数据的一个监听[for each row]

c) Inserting updating deleting
事件：插入操作、修改操作、删除操作

示例代码：创建一个emp2表写一个对emp2数据的触发器监听。
当对emp2数据操作时，将记录记录到action表中。USER是关键字，指登陆用户，就像是scott一样。
create table emp2
(
empno number primary key,
ename varchar2(20) ,
sal number(6,2),
comm number(6,4),
hiredate date,
deptno number,
job varchar(20)
)

create table action
(
id number primary key,
username varchar2(20) ,
action varchar2(50),
atime date
)

create sequence seq_action
start with 1
increment by 1;

create or replace trigger trig_emp2
after update or delete or insert on emp2 for each row
begin
if inserting then
insert into action values (seq_action.nextval,USER,'insert',sysdate);
elsif updating then
insert into action values (seq_action.nextval,USER,'update',sysdate);
elsif deleting then
insert into action values (seq_action.nextval,USER,'delete',sysdate);
end if;
end;

insert into emp2 values(1,'wl',1050.00,60.00,sysdate,1,'MANAGER');

ALTER TRIGGER triggername ENABLE;--启动触发器
ALTER TRIGGER triggername DISABLE;--挂起触发器

trop trigger triggername;--删除触发器
11、 序列
Create sequence seq_name
Start with 1
Increment by 1

创建序列：
序列就如同是sqlserver里的自增长identity一样。实现数据的递加、或者是递减
Start with 1 –数据起始位置为1
Increment by 1 –数据自增量是1
seq_action.nextval 获取的序列打下一个值
seq_action.currval 获取序列的当前值
12、 视图
a) Create or replace view viewname
b) As
c) Select …..
创建视图：
视图是将一个select查询语句保存到一起的一个方式，他就是指as下面的那个查询语句，不能对视图里的数据进行更新操作，因为他是一个查询结果，要相对数据操作，需要找到他的对应表离去操作。
13、 同义词 synonym
同义词就像是给表起一个别名
建议两个情况下用
1、 表名特别长
2、 表名是其他国语言时
a) 私有同义词 synonym
b) CREATE SYNONYM e FOR SCOTT.emp;
给emp表创建一个性的名字叫做e
以后的操作可以对e进行操作，就和对emp操作一样。
c) 共有同义词
d) CREATE PUBLIC SYNONYM syn_emp FOR SCOTT.emp;