Oracle知识总结.doc

Oracle知识点总结用户/表/复杂查询/plsql/存储过程/函数/游标/触发器/同义词/视图/序列/动态sql说明：本文中用到的println，是自己创建的一个存储过程，调用了dbms_output.put_line方法代码如下：Create or replace procedure println(v_str in varchar2)AsBeginDbms_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 userstemporary tablespace temp注释：创建用户用户名是scott密码是tiger（管理员权限下运行）default tablespace users 定义默认使用的表空间，存放scott用户下创建的表和数据用的temporary tablespace temp 定义临时使用的表空间Users、temp 是系统创建的默认表空间，这里可以用自己定义的表空间c) 修改密码alter user scott identified tigerd) 删除用户drop user scotte) 解锁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;授予数据库管理员的权限 dbaDataBaseAdming) 回收权限revoke connect from scott;收回连接权限2、 表a) 创建表i. Pk fk uk not null check defaultcreate 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. Delet1. Delete 表名 where 条件ii. Truncate1. 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-20Select * from emp where empno =20;b) 分组查询 group by max min avg sum count havingSelect 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关键字而不是通常用的wherec) 查询排序 order by asc descselect * from emp order by deptno asc,sal desc查询排序：如果多个排序字段用，号隔开，asc是正序，默认就是asc，所以可以不写。Desc是倒叙d) 伪列 rowid,rownumselect ename ,rowid from emp where rownum =2select * 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)unionselect ename from emp where ename in (SCOTT)i) Union all取两个查询结果的所有数据select ename from emp where ename in (SCOTT,JONES)union allselect ename from emp where ename in (SCOTT)j) Intersect 取两个查询结果的交集select ename from emp where ename in (SCOTT,JONES)intersectselect ename from emp where ename in (SCOTT)k) Minus 取查询结果中的第一个查询结果中有的，第二个结果中不存在的数据select ename from emp where ename in (SCOTT,JONES)minusselect ename from emp where ename in (SCOTT)5、 Plsqla) 条件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 = 60 then println(良好); else println(优秀); end if;end;b) 循环i. Loop while forFor循环实现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_i100; 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_i100 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_enamecreate or replace procedure getname(v_empno in number ,v_ename in out varchar2)asbegin 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(7369,v_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) Triggerb) After/before insert or update or delete on tablename for each row触发的条件可以是摸个操作的之前或知识之后before、after触发器监听是可以对整个表的一个监听，也可以是对每行数据的一个监听for each rowc) 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_actionstart with 1increment 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