本章学习要点：
存储过程如何给别的用户调用
存储过程和游标的用法
函数
触发器
索引
表分区例：在部门表中添加一条数据
create or replace procedure proc_dept(dno number,name varchar2,dloc varchar2) is 声明异常 ex exception; begin if(dno=0 or name is null)hen raise ex; –添加 insert into dept values(dno,name,dloc); dbms_output.put_line(dno||’部门添加成功’)； exception –处理异常 exception when DUP_VAL_ON_INDEX then dbms_output.put_line(‘违反主键约束!’); when ex then dbms_output.put_line(‘部门编号与部门名称不能为空!’); when others then dbms_output.put_line(sqlcode||’–>’||sqlerrm); end;
储存过程创建好后，如若其他用户需要调用此过程，需要拥有此存储过程的用户对其进行授权 如：grant execute on proc_dept to scott; 若需要收回权限 如：revoke execute on proc_dept from scott; 删除存储过程 drop procedure proc_dept; 若需要其他用户都可以查看(所有的用户) SQL> GRANT execute on proc_dept TO PUBLIC; 进入scott用户 SQL> conn scott/aaa@ORCL; SQL> set serveroutput on; SQL> execute sqb.proc_dept(60,’AA’,’COT’);
普通cursor与REF cursor还有一些大家应该都熟悉的区别:
1）PL/SQL静态光标不能返回到客户端，只有PL/SQL才能利用它。ref光标能够被返回到客户端，这就是从Oracle的存储过程返回结果集的方式。
2）PL/SQL静态光标可以是全局的，而ref光标则不是。 也就是说，不能在包说明或包体中的过程或函数之外定义ref光标。 只能在定义ref光标的过程中处理它，或返回到客户端应用程序。
3）ref光标可以从子例程传递到子例程，而光标则不能。 为了共享静态光标，必须在包说明或包体中把它定义为全局光标。 因为使用全局变量通常不是一种很好的编码习惯，因此可以用ref光标来共享PL/SQL中的光标，无需混合使用全局变量。
sys_refcursor是oracle9i以后系统定义的一个refcursor,主要用在过程中返回结果集。
函数语法 CREATE [OR REPLACE] FUNCTION [(param1,param2)] –输入输出参数 RETURN IS|AS –必须返回类型 [local declarations] –声明变量 BEGIN Executable Statements; –执行语句 RETURN result; –返回值 EXCEPTION –处理异常 Exception handlers; END;
例1.获取某部门的工资总和： select sum(sal) from emp where deptno=20;
create or replace function fun_sal(dno number) return number is v_sum emp.sal%type; ex exception; –声明异常 begin select sum(sal) into v_sum from emp where deptno=dno; if(v_sum is null) then raise ex; –抛出异常 end if; –返回值 return v_sum;
–处理异常 exception when ex then raise_application_error(-20001,dno||’部门编号不存在!’); end;
–如何调用 declare v_sum number; begin v_sum:=fun_sal(&no); DBMS_OUTPUT.PUT_LINE(‘工资总和：’||v_sum); end;
1 触发器类型 触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。
1.1 DML触发器 ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。
1.2 替代触发器 由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE 8专门为进行视图操作的一种处理方法。
1.3 系统触发器 ORACLE 8i 提供了第三种类型的触发器叫系统触发器。它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。
触发器组成: l 触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。 l 触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。 l 触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。 l 触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。 l 触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。 l 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。 语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次； 行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。 编写触发器时，需要注意以下几点： l 触发器不接受参数。 l 一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。 l 在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。 l 触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。 l 在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。 l 触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。 l 在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。 l 在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能是表中的任何long和blob列。 l 不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。
–触发器的语法 CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER } –之前/之后 {INSERT | DELETE | UPDATE [OF column [, column …]]} –DML触发器 [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}…] ON [schema.]table_name | [schema.]view_name –表,视图 [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] [FOR EACH ROW ] –行级触发 [WHEN condition] –条件 PL/SQL_BLOCK | CALL procedure_name; –pl/sql块 或者是 调用存储过程
2.1 触发器触发次序 1. 执行 BEFORE语句级触发器; 2. 对与受语句影响的每一行： l 执行 BEFORE行级触发器 l 执行 DML语句 l 执行 AFTER行级触发器 3. 执行 AFTER语句级触发器
DML触发器的限制 l CREATE TRIGGER语句文本的字符长度不能超过32KB； l 触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT 语句。 l 触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句； l 由触发器所调用的过程或函数也不能使用数据库事务控制语句； l 触发器中不能使用LONG, LONG RAW 类型； l 触发器内可以参照LOB 类型列的列值，但不能通过 :NEW 修改LOB列中的数据；
问题：当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、 后列的值. 实现: :NEW 修饰符访问操作完成后列的值 :OLD 修饰符访问操作完成前列的值
例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。 select * from emp where ename=’SCOTT’ 删除时 –>备份这个删除的记录 delete from emp where ename=’SCOTT’ –备份表的表的结构必须存在 –CREATE TABLE h_emp AS SELECT * FROM EMP WHERE 1=2; select * from h_emp;
create or replace trigger trig_emp1 before delete –指定触发时机为删除操作前触发 on emp for each row –行级触发 begin –将修改前数据插入到日志记录表 h_emp ,以供监督使用。 insert into h_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno); end;
根据需求将数据分成一块块便于查找方便，加快查找速度 (1)B 树索引结构 –CREATE INDEX 索引名称 ON 表名（字段名）; (2)反向键索引 –CREATE INDEX 索引名称 ON 表名（字段名）reverse; (3)位图索引 –>重复出现的次数很多(如 :性别,部门编号,民族…) -CREATE bitmap INDEX 索引名称 ON 表名（字段名）; 索引使用原则： 表中导入数据后再创建索引。否则每次表中插入数据时都必须更新索引 在适当的表和字段上创建索引。如果经常检索的数据少于表中的15%则需要创建索引 限制表中索引的数目。索引越多，在修改表时对索引做出修改的工作量越大
允许用户将一个表分成多个分区 用户可以执行查询，只访问表中的特定分区 将不同的分区存储在不同的磁盘，提高访问性能和安全性 可以独立地
Oracle表分区分为四种：范围分区，散列分区，列表分区和复合分区(范围+散列,范围+列表)。
–1)范围分区: 以表中的一个列或一组列的值的范围分区 PARTITION BY RANGE (column_name) ( PARTITION part1 VALUES LESS THAN (range1), PARTITION part2 VALUES LESS THAN (range2), … [PARTITION partN VALUE S LESS THAN (MAXVALUE)] ); –年级记录表 create table graderecord ( sno varchar2(10), –学号 sname varchar2(20), –姓名 dormitory varchar2(3), –宿舍编号 grade int –分数 ) partition by range(grade) ( partition p1 values less than(60), –不及格 grade<60 partition p2 values less than(85), –及格 grade>=60 && grade<85 partition p3 values less than(maxvalue) –优秀 (剩余部分) ) –添加数据 insert into graderecord values(‘511601’,’魁’,’229’,92); insert into graderecord values(‘511602’,’凯’,’229’,62); insert into graderecord values(‘511603’,’东’,’229’,26); insert into graderecord values(‘511604’,’亮’,’228’,77); insert into graderecord values(‘511605’,’敬’,’228’,47); insert into graderecord(sno,sname,dormitory) values(‘511606’,’峰’,’228’); insert into graderecord values(‘511607’,’明’,’240’,90); insert into graderecord values(‘511608’,’楠’,’240’,100); insert into graderecord values(‘511609’,’涛’,’240’,67); insert into graderecord values(‘511610’,’博’,’240’,75); insert into graderecord values(‘511611’,’铮’,’240’,60);
select * from graderecord; select * from graderecord partition(p1); select * from graderecord partition(p2); select * from graderecord partition(p3);
–2)散列分区 允许用户对不具有逻辑范围的数据进行分区 –通过在分区键上执行HASH函数决定存储的分区 将数据分布到不同的分区（并不是平均而是随机） PARTITION BY HASH (column_name) PARTITIONS number_of_partitions; 或 PARTITION BY HASH (column_name) ( PARTITION part1 [TABLESPACE tbs1], PARTITION part2 [TABLESPACE tbs2], … PARTITION partN [TABLESPACE tbsN] );
create table graderecord ( sno varchar2(10), –学号 sname varchar2(20), –姓名 dormitory varchar2(3), –宿舍编号 grade int –分数 ) partition by hash(sno) ( partition p1, partition p2, partition p3 )
–添加数据 insert into graderecord values(‘511601’,’魁’,’229’,92); insert into graderecord values(‘511602’,’凯’,’229’,62); insert into graderecord values(‘511603’,’东’,’229’,26); insert into graderecord values(‘511604’,’亮’,’228’,77); insert into graderecord values(‘511605’,’敬’,’228’,47); insert into graderecord(sno,sname,dormitory) values(‘511606’,’峰’,’228’); insert into graderecord values(‘511607’,’明’,’240’,90); insert into graderecord values(‘511608’,’楠’,’240’,100); insert into graderecord values(‘511609’,’涛’,’240’,67); insert into graderecord values(‘511610’,’博’,’240’,75); insert into graderecord values(‘511611’,’铮’,’240’,60);
select * from graderecord; select * from graderecord partition(p1); select * from graderecord partition(p2); select * from graderecord partition(p3);
–3)列表分区 允许用户将不相关的数据组织在一起 PARTITION BY LIST (column_name) ( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), … PARTITION partN VALUES (DEFAULT) );
drop table graderecord;
create table graderecord ( sno varchar2(10), –学号 sname varchar2(20), –姓名 dormitory varchar2(3), –宿舍编号 grade int –分数 ) partition by list(dormitory) ( partition p1 values(‘228’), partition p2 values(‘229’), partition p3 values(default) )
–添加数据 insert into graderecord values(‘511601’,’魁’,’229’,92); insert into graderecord values(‘511602’,’凯’,’229’,62); insert into graderecord values(‘511603’,’东’,’229’,26); insert into graderecord values(‘511604’,’亮’,’228’,77); insert into graderecord values(‘511605’,’敬’,’228’,47); insert into graderecord(sno,sname,dormitory) values(‘511606’,’峰’,’228’); insert into graderecord values(‘511607’,’明’,’240’,90); insert into graderecord values(‘511608’,’楠’,’240’,100); insert into graderecord values(‘511609’,’涛’,’240’,67); insert into graderecord values(‘511610’,’博’,’240’,75); insert into graderecord values(‘511611’,’铮’,’240’,60);
select * from graderecord; select * from graderecord partition(p1); select * from graderecord partition(p2); select * from graderecord partition(p3);
–4)：复合分区 （范围-散列分区，范围-列表分区）
PARTITION BY RANGE (column_name1) SUBPARTITION BY HASH (column_name2) –SUBPARTITIONS number_of_partitions ( PARTITION part1 VALUES LESS THAN (range1), PARTITION part2 VALUES LESS THAN (range2), … PARTITION partN VALUES LESS THAN (MAXVALUE) ); –根分区只能是RANGE分区，子分区可以是HASH 分区或LIST分区 a)范围-散列分区 :(范围分区和哈希分区支持多列)。
drop table graderecord;
create table graderecord ( sno varchar2(10), –学号 sname varchar2(20), –姓名 dormitory varchar2(3), –宿舍编号 grade int –分数 ) partition by range(grade) subpartition by hash(sno,sname) ( partition p1 values less than(75) ( subpartition sp1,subpartition sp2 ), partition p2 values less than(maxvalue) ( subpartition sp3,subpartition sp4 ) )
–添加数据 insert into graderecord values(‘511601’,’魁’,’229’,92); insert into graderecord values(‘511602’,’凯’,’229’,62); insert into graderecord values(‘511603’,’东’,’229’,26); insert into graderecord values(‘511604’,’亮’,’228’,77); insert into graderecord values(‘511605’,’敬’,’228’,47); insert into graderecord(sno,sname,dormitory) values(‘511606’,’峰’,’228’); insert into graderecord values(‘511607’,’明’,’240’,90); insert into graderecord values(‘511608’,’楠’,’240’,100); insert into graderecord values(‘511609’,’涛’,’240’,67); insert into graderecord values(‘511610’,’博’,’240’,75); insert into graderecord values(‘511611’,’铮’,’240’,60);
select * from graderecord; select * from graderecord partition(p1); –5 select * from graderecord subpartition(sp1); –1 select * from graderecord subpartition(sp2); select * from graderecord partition(p2); –6 select * from graderecord subpartition(sp3); –2 select * from graderecord subpartition(sp4);
b)范围-列表分区 : (列表分区不支持多列)
create table MobileMessage ( ACCT_MONTH VARCHAR2(6), – 帐期 格式：年月 YYYYMM AREA_NO VARCHAR2(10), – 地域号码 DAY_ID VARCHAR2(2), – 本月中的第几天 格式 DD SUBSCRBID VARCHAR2(20), – 用户标识 SVCNUM VARCHAR2(30) – 手机号码 ) partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID) ( partition p1 values less than(‘200705’,’012’) ( subpartition shangxun1 values(‘01’,’02’,’03’,’04’,’05’,’06’,’07’,’08’,’09’,’10’), subpartition zhongxun1 values(‘11’,’12’,’13’,’14’,’15’,’16’,’17’,’18’,’19’,’20’), subpartition xiaxun1 values(‘21’,’22’,’23’,’24’,’25’,’26’,’27’,’28’,’29’,’30’,’31’) ), partition p2 values less than(‘200709’,’014’) ( subpartition shangxun2 values(‘01’,’02’,’03’,’04’,’05’,’06’,’07’,’08’,’09’,’10’), subpartition zhongxun2 values(‘11’,’12’,’13’,’14’,’15’,’16’,’17’,’18’,’19’,’20’), subpartition xiaxun2 values(‘21’,’22’,’23’,’24’,’25’,’26’,’27’,’28’,’29’,’30’,’31’) ), partition p3 values less than(‘200801’,’016’) ( subpartition shangxun3 values(‘01’,’02’,’03’,’04’,’05’,’06’,’07’,’08’,’09’,’10’), subpartition zhongxun3 values(‘11’,’12’,’13’,’14’,’15’,’16’,’17’,’18’,’19’,’20’), subpartition xiaxun3 values(‘21’,’22’,’23’,’24’,’25’,’26’,’27’,’28’,’29’,’30’,’31’) ) )
insert into MobileMessage values(‘200701’,’010’,’04’,’ghk001’,’13800000000’); insert into MobileMessage values(‘200702’,’015’,’12’,’myx001’,’13633330000’); insert into MobileMessage values(‘200703’,’015’,’24’,’hjd001’,’13300000000’); insert into MobileMessage values(‘200704’,’010’,’04’,’ghk001’,’13800000000’); insert into MobileMessage values(‘200705’,’010’,’04’,’ghk001’,’13800000000’); insert into MobileMessage values(‘200705’,’011’,’18’,’sxl001’,’13222000000’); insert into MobileMessage values(‘200706’,’011’,’21’,’sxl001’,’13222000000’); insert into MobileMessage values(‘200706’,’012’,’11’,’tgg001’,’13800044400’); insert into MobileMessage values(‘200707’,’010’,’04’,’ghk001’,’13800000000’); insert into MobileMessage values(‘200708’,’012’,’24’,’tgg001’,’13800044400’); insert into MobileMessage values(‘200709’,’014’,’29’,’zjj001’,’13100000000’); insert into MobileMessage values(‘200710’,’014’,’29’,’zjj001’,’13100000000’); insert into MobileMessage values(‘200711’,’014’,’29’,’zjj001’,’13100000000’); insert into MobileMessage values(‘200711’,’013’,’30’,’wgc001’,’13444000000’); insert into MobileMessage values(‘200712’,’013’,’30’,’wgc001’,’13444000000’); insert into MobileMessage values(‘200712’,’010’,’30’,’ghk001’,’13800000000’); insert into MobileMessage values(‘200801’,’015’,’22’,’myx001’,’13633330000’);
select * from mobileMessage; –‘200705’,’012’ select * from mobileMessage partition(p1); select * from mobileMessage subpartition(shangxun1); –3 select * from mobileMessage subpartition(zhongxun1); –2 select * from mobileMessage subpartition(xiaxun1);
–‘200709’,’014’ select * from mobileMessage partition(p2); select * from mobileMessage subpartition(shangxun2); select * from mobileMessage subpartition(zhongxun2); select * from mobileMessage subpartition(xiaxun2);
