本文目录：
1. PL/SQL 程序设计简介
2. PL/SQL块结构和组成元素
3. PL/SQL流程控制语句
4. 把游标说透
5. 异常错误处理
6. 把过程与函数说透
7. 程序包的创建与应用
8. 把触发器说透
1. PL/SQL 程序设计简介
SQL语言只是访问、操作数据库的语言，并不是一种具有流程控制的程序设计语言，而只有程序设计语言才能用于应用软件的开发。PL /SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。除此之外，可以在ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。本章的主要内容是讨论引入PL/SQL语言的必要性和该语言的主要特点，以及了解PL/SQL语言的重要性和数据库版本问题。还要介绍一些贯穿全书的更详细的高级概念，并在本章的最后就我们在本书案例中使用的数据库表的若干约定做一说明。
PL/SQL是 Procedure Language & Structured Query Language 的缩写。ORACLE的SQL是支持ANSI(American national Standards Institute)和ISO92 (International Standards Organization)标准的产品。PL/SQL是对SQL语言存储过程语言的扩展。从ORACLE6以后，ORACLE的RDBMS附带了PL/SQL。它现在已经成为一种过程处理语言，简称PL/SQL。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如C语言，JAVA语言等）工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具（如ORACLE Forms）的句法，如：在窗体上设置按钮等。本章主要介绍数据库PL/SQL内容。
对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数据交换。应用运行的效率自然就回受到影响。如果使用PL/SQL进行编程，将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。
PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。
PL/SQL是Oracle在标准SQL上的过程性扩展，不仅允许在PL/SQL程序内嵌入SQL语句，而且允许使用各种类型的条件分支语句和循环语句，可以多个应用程序之间共享其解决方案。
PL/SQL程序结构是一种描述性很强、界限分明的块结构、嵌套块结构，被分成单独的过程、函数、触发器，且可以把它们组合为程序包，提高程序的模块化能力。
使用PL/SQL提供的异常处理（EXCEPTION），开发人员可集中处理各种ORACLE错误和PL/SQL错误，或处理系统错误与自定义错误，以增强应用程序的健壮性。
ORACLE提供了大量的内置程序包。通过这些程序包能够实现DBS的一些低层操作、高级功能，不论对DBA还是应用开发人员都具有重要作用。
当然还有其它的一些优点如：更好的性能、可移植性和兼容性、可维护性、易用性与快速性等。
PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：
INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。
提示：在 PL/SQL中只能用 SQL语句中的 DML 部分，不能用 DDL 部分，如果要在PL/SQL中使用DDL(如CREATE table 等)的话，只能以动态的方式来使用。
ORACLE 的 PL/SQL 组件在对 PL/SQL 程序进行解释时，同时对在其所使用的表名、列名及数据类型进行检查。
PL/SQL 可以在SQL*PLUS 中使用。
PL/SQL 可以在高级语言中使用。
PL/SQL可以在ORACLE的开发工具中使用(如：SQL Developer或Procedure Builder等)。
其它开发工具也可以调用PL/SQL编写的过程和函数，如Power Builder 等都可以调用服务器端的PL/SQL过程。
PL/SQL程序的运行是通过ORACLE中的一个引擎来进行的。这个引擎可能在ORACLE的服务器端，也可能在 ORACLE 应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行。再将结果返回给执行端。
2. PL/SQL块结构和组成元素
2.1 PL/SQL块
PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。
PL/SQL块的结构如下：
1 DECLARE 2 --声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数 3 BEGIN 4 -- 执行部分: 过程及SQL 语句 , 即程序的主要部分 5 EXCEPTION 6 -- 执行异常部分: 错误处理 7 END;
其中：执行部分不能省略。
PL/SQL块可以分为以下几类：
1. 无名块或匿名块（anonymous）：动态构造，只能执行一次，可调用其它程序，但不能被其它程序调用。
2. 命名块（named）：是带有名称的匿名块，这个名称就是标签。
3. 子程序（subprogram）：存储在数据库中的存储过程、函数等。当在数据库上建立好后可以在其它程序中调用它们。
4. 触发器（Trigger）：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。
5. 程序包/包（package）：存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其它程序包或子程序调用。但如果声明的是局部子程序，则只能在定义该局部子程序的块中调用该局部子程序。
2.2 PL/SQL结构
1.PL/SQL块中可以包含子块；
2.子块可以位于 PL/SQL中的任何部分；
3.子块也即PL/SQL中的一条命令；
2.3 标识符
PL/SQL程序设计中的标识符定义与SQL 的标识符定义的要求相同。要求和限制有：
1.标识符名不能超过30字符；
2.第一个字符必须为字母；
3.不分大小写；
4.不能用’-‘(减号);
5.不能是SQL保留字。
提示: 一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.
例如：下面的例子将会删除所有的纪录，而不是’EricHu’的记录；
1 DECLARE 2 ename varchar2(20) :='EricHu'; 3 BEGIN 4 DELETE FROM scott.emp WHERE ename=ename; 5 END;
变量命名在PL/SQL中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。下面是建议的命名方法：
2.4 PL/SQL 变量类型
在前面的介绍中，有系统的数据类型，也可以自定义数据类型。下表给出ORACLE类型和PL/SQL中的变量类型的合法使用列表：
2.4.1 变量类型
在ORACLE8i中可以使用的变量类型有：
例1. 插入一条记录并显示；
1 DECLARE 2 Row_id ROWID; 3 info VARCHAR2(40); 4 BEGIN 5 INSERT INTO scott.dept VALUES (90, '财务室', '海口') 6 RETURNING rowid, dname||':'||to_char(deptno)||':'||loc 7 INTO row_id, info; 8 DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id); 9 DBMS_OUTPUT.PUT_LINE(info); 10 END;
其中：
RETURNING子句用于检索INSERT语句中所影响的数据行数，当INSERT语句使用VALUES 子句插入数据时，RETURNING 字句还可将列表达式、ROWID和REF值返回到输出变量中。在使用RETURNING 子句是应注意以下几点限制：
1．不能与DML语句和远程对象一起使用；
2．不能检索LONG 类型信息；
3．当通过视图向基表中插入数据时，只能与单基表视图一起使用。
例2. 修改一条记录并显示
1 DECLARE 2 Row_id ROWID; 3 info VARCHAR2(40); 4 BEGIN 5 UPDATE dept SET deptno=100 WHERE DNAME='财务室' 6 RETURNING rowid, dname||':'||to_char(deptno)||':'||loc 7 INTO row_id, info; 8 DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id); 9 DBMS_OUTPUT.PUT_LINE(info); 10 END;
其中：
RETURNING子句用于检索被修改行的信息。当UPDATE语句修改单行数据时，RETURNING 子句可以检索被修改行的ROWID和REF值，以及行中被修改列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当UPDATE语句修改多行数据时，RETURNING 子句可以将被修改行的ROWID和REF值，以及列表达式值返回到复合变量数组中。在UPDATE中使用RETURNING 子句的限制与INSERT语句中对RETURNING子句的限制相同。
例3. 删除一条记录并显示
1 DECLARE 2 Row_id ROWID; 3 info VARCHAR2(40); 4 BEGIN 5 DELETE dept WHERE DNAME='办公室' 6 RETURNING rowid, dname||':'||to_char(deptno)||':'||loc 7 INTO row_id, info; 8 DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id); 9 DBMS_OUTPUT.PUT_LINE(info); 10 END;
其中：
RETURNING子句用于检索被删除行的信息：当DELETE语句删除单行数据时，RETURNING 子句可以检索被删除行的ROWID和REF值，以及被删除列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当DELETE语句删除多行数据时，RETURNING 子句可以将被删除行的ROWID和REF值，以及列表达式值返回到复合变量数组中。在DELETE中使用RETURNING 子句的限制与INSERT语句中对RETURNING子句的限制相同。
2.4.2 复合类型
ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表.
2.4.2.1 记录类型
记录类型类似于C语言中的结构数据类型，它把逻辑相关的、分离的、基本数据类型的变量组成一个整体存储起来，它必须包括至少一个标量型或RECORD数据类型的成员，称作PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。在使用记录数据类型变量时，需要先在声明部分先定义记录的组成、记录的变量，然后在执行部分引用该记录变量本身或其中的成员。
定义记录类型语法如下:
TYPE record_name IS RECORD(
v1 data_type1 [NOT NULL] [:= default_value ],
v2 data_type2 [NOT NULL] [:= default_value ],
......
vn data_typen [NOT NULL] [:= default_value ] );
例4 ：
1 DECLARE 2 TYPE test_rec IS RECORD( 3 Name VARCHAR2(30) NOT NULL := '胡勇', 4 Info VARCHAR2(100)); 5 rec_book test_rec; 6 BEGIN 7 rec_book.Name :='胡勇'; 8 rec_book.Info :='谈PL/SQL编程;'; 9 DBMS_OUTPUT.PUT_LINE(rec_book.Name||' ' ||rec_book.Info); 10 END;
可以用 SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。
例5 ：
1 DECLARE 2 --定义与hr.employees表中的这几个列相同的记录数据类型 3 TYPE RECORD_TYPE_EMPLOYEES IS RECORD( 4 f_name hr.employees.first_name%TYPE, 5 h_date hr.employees.hire_date%TYPE, 6 j_id hr.employees.job_id%TYPE); 7 --声明一个该记录数据类型的记录变量 8 v_emp_record RECORD_TYPE_EMPLOYEES; 9 BEGIN 10 SELECT first_name, hire_date, job_id INTO v_emp_record 11 FROM employees 12 WHERE employee_id = &emp_id; 13 DBMS_OUTPUT.PUT_LINE('雇员名称：'||v_emp_record.f_name 14 ||' 雇佣日期：'||v_emp_record.h_date 15 ||' 岗位：'||v_emp_record.j_id); 16 END;
一个记录类型的变量只能保存从数据库中查询出的一行记录，若查询出了多行记录，就会出现错误。
2.4.2.2 数组类型
数据是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，它取决于成员在数组中的位置。在PL/SQL中，数组数据类型是VARRAY。
定义VARRY数据类型语法如下：
TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL];
varray_name是VARRAY数据类型的名称，size是下整数，表示可容纳的成员的最大数量，每个成员的数据类型是element_type。默认成员可以取空值，否则需要使用NOT NULL加以限制。对于VARRAY数据类型来说，必须经过三个步骤，分别是：定义、声明、初始化。
例6 ：
1 DECLARE 2 --定义一个最多保存5个VARCHAR(25)数据类型成员的VARRAY数据类型 3 TYPE reg_varray_type IS VARRAY(5) OF VARCHAR(25); 4 --声明一个该VARRAY数据类型的变量 5 v_reg_varray REG_VARRAY_TYPE; 6 BEGIN 7 --用构造函数语法赋予初值 8 v_reg_varray := reg_varray_type 9 ('中国', '美国', '英国', '日本', '法国'); 10 DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_varray(1)||'、' 11 ||v_reg_varray(2)||'、' 12 ||v_reg_varray(3)||'、' 13 ||v_reg_varray(4)); 14 DBMS_OUTPUT.PUT_LINE('赋予初值NULL的第5个成员的值：'||v_reg_varray(5)); 15 --用构造函数语法赋予初值后就可以这样对成员赋值 16 v_reg_varray(5) := '法国'; 17 DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_varray(5)); 18 END;
2.4.2.3 使用%TYPE
定义一个变量，其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致，这时可以使用%TYPE。
使用%TYPE特性的优点在于：
1.所引用的数据库列的数据类型可以不必知道；
2.所引用的数据库列的数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。
例7：
1 DECLARE 2 -- 用%TYPE 类型定义与表相配的字段 3 TYPE T_Record IS RECORD( 4 T_no emp.empno%TYPE, 5 T_name emp.ename%TYPE, 6 T_sal emp.sal%TYPE ); 7 -- 声明接收数据的变量 8 v_emp T_Record; 9 BEGIN 10 SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788; 11 DBMS_OUTPUT.PUT_LINE 12 (TO_CHAR(v_emp.t_no)||' '||v_emp.t_name||' ' || TO_CHAR(v_emp.t_sal)); 13 END;
例8：
1 DECLARE 2 v_empno emp.empno%TYPE :=&no; 3 Type t_record is record ( 4 v_name emp.ename%TYPE, 5 v_sal emp.sal%TYPE, 6 v_date emp.hiredate%TYPE); 7 Rec t_record; 8 BEGIN 9 SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno; 10 DBMS_OUTPUT.PUT_LINE(Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date); 11 END;
PL/SQL 提供%ROWTYPE操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。
使用%ROWTYPE特性的优点在于：
l 所引用的数据库中列的个数和数据类型可以不必知道；
l 所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。
例9：
1 DECLARE 2 v_empno emp.empno%TYPE :=&no; 3 rec emp%ROWTYPE; 4 BEGIN 5 SELECT * INTO rec FROM emp WHERE empno=v_empno; 6 DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate); 7 END;
ORACLE提供了LOB (Large OBject)类型，用于存储大的数据对象的类型。ORACLE目前主要支持BFILE, BLOB, CLOB 及 NCLOB 类型。
BFILE (Movie)
存放大的二进制数据对象，这些数据文件不放在数据库里，而是放在操作系统的某个目录里，数据库的表里只存放文件的目录。
BLOB(Photo)
存储大的二进制数据类型。变量存储大的二进制对象的位置。大二进制对象的大小<=4GB。
CLOB(Book)
存储大的字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。
NCLOB
存储大的NCHAR字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。
2.4.5 BIND 变量
绑定变量是在主机环境中定义的变量。在PL/SQL 程序中可以使用绑定变量作为他们将要使用的其它变量。为了在PL/SQL 环境中声明绑定变量，使用命令VARIABLE。例如：
VARIABLE return_code NUMBER
VARIABLE return_msg VARCHAR2(20)
可以通过SQL*Plus命令中的PRINT 显示绑定变量的值。例如：
PRINT return_code
PRINT return_msg
例10：
1 VARIABLE result NUMBER; 2 BEGIN 3 SELECT (sal*10)+nvl(comm, 0) INTO :result FROM emp 4 WHERE empno=7844; 5 END; 6 --然后再执行 7 PRINT result
定义记录表（或索引表）数据类型。它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于高级中的二维数组，使得可以在PL/SQL中模仿数据库中的表。
定义记录表类型的语法如下：
TYPE table_name IS TABLE OF element_type [NOT NULL]
INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARRAY2];
关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。
例11：
1 DECLARE 2 TYPE dept_table_type IS TABLE OF 3 dept%ROWTYPE INDEX BY BINARY_INTEGER; 4 my_dname_table dept_table_type; 5 v_count number(2) :=4; 6 BEGIN 7 FOR int IN 1 .. v_count LOOP 8 SELECT * INTO my_dname_table(int) FROM dept WHERE deptno=int*10; 9 END LOOP; 10 FOR int IN my_dname_table.FIRST .. my_dname_table.LAST LOOP 11 DBMS_OUTPUT.PUT_LINE('Department number: '||my_dname_table(int).deptno); 12 DBMS_OUTPUT.PUT_LINE('Department name: '|| my_dname_table(int).dname); 13 END LOOP; 14 END;
例12：按一维数组使用记录表
1 DECLARE 2 --定义记录表数据类型 3 TYPE reg_table_type IS TABLE OF varchar2(25) 4 INDEX BY BINARY_INTEGER; 5 --声明记录表数据类型的变量 6 v_reg_table REG_TABLE_TYPE; 7 BEGIN 8 v_reg_table(1) := 'Europe'; 9 v_reg_table(2) := 'Americas'; 10 v_reg_table(3) := 'Asia'; 11 v_reg_table(4) := 'Middle East and Africa'; 12 v_reg_table(5) := 'NULL'; 13 DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_table (1)||'、' 14 ||v_reg_table (2)||'、' 15 ||v_reg_table (3)||'、' 16 ||v_reg_table (4)); 17 DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_table(5)); 18 END;
例13：按二维数组使用记录表
1 DECLARE 2 --定义记录表数据类型 3 TYPE emp_table_type IS TABLE OF employees%ROWTYPE 4 INDEX BY BINARY_INTEGER; 5 --声明记录表数据类型的变量 6 v_emp_table EMP_TABLE_TYPE; 7 BEGIN 8 SELECT first_name, hire_date, job_id INTO 9 v_emp_table(1).first_name,v_emp_table(1).hire_date, v_emp_table(1).job_id 10 FROM employees WHERE employee_id = 177; 11 SELECT first_name, hire_date, job_id INTO 12 v_emp_table(2).first_name,v_emp_table(2).hire_date, v_emp_table(2).job_id 13 FROM employees WHERE employee_id = 178; 14 DBMS_OUTPUT.PUT_LINE('177雇员名称：'||v_emp_table(1).first_name 15 ||' 雇佣日期：'||v_emp_table(1).hire_date 16 ||' 岗位：'||v_emp_table(1).job_id); 17 DBMS_OUTPUT.PUT_LINE('178雇员名称：'||v_emp_table(2).first_name 18 ||' 雇佣日期：'||v_emp_table(2).hire_date 19 ||' 岗位：'||v_emp_table(2).job_id); 20 END;
2.5.1 关系运算符
2.5.2 一般运算符
2.5.3 逻辑运算符
2.6 变量赋值
在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：
variable := expression ;
variable 是一个PL/SQL变量, expression 是一个PL/SQL 表达式.
2.6.1 字符及数字运算特点
空值加数字仍是空值：NULL + < 数字> = NULL
空值加（连接）字符，结果为字符：NULL || <字符串> = < 字符串>
2.6.2 BOOLEAN 赋值
布尔值只有TRUE, FALSE及 NULL 三个值。如：
1 DECLARE 2 bDone BOOLEAN; 3 BEGIN 4 bDone := FALSE; 5 WHILE NOT bDone LOOP 6 Null; 7 END LOOP; 8 END;
2.6.3 数据库赋值
数据库赋值是通过 SELECT语句来完成的，每次执行 SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。如：
例14：
1 DECLARE 2 emp_id emp.empno%TYPE :=7788; 3 emp_name emp.ename%TYPE; 4 wages emp.sal%TYPE; 5 BEGIN 6 SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages 7 FROM emp WHERE empno = emp_id; 8 DBMS_OUTPUT.PUT_LINE(emp_name||'----'||to_char(wages)); 9 END;
提示：不能将SELECT语句中的列赋值给布尔变量。
2.6.4 可转换的类型赋值
1.CHAR 转换为 NUMBER：
使用 TO_NUMBER 函数来完成字符到数字的转换，如：
v_total := TO_NUMBER('100.0') + sal;
2.NUMBER 转换为CHAR：
使用 TO_CHAR函数可以实现数字到字符的转换，如：
v_comm := TO_CHAR('123.45') || '元' ;
3.字符转换为日期：
使用 TO_DATE函数可以实现 字符到日期的转换，如：
v_date := TO_DATE('2001.07.03','yyyy.mm.dd');
4.日期转换为字符
使用 TO_CHAR函数可以实现日期到字符的转换，如：
v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh24:mi:ss') ;
2.7 变量作用范围及可见性
在PL/SQL编程中，如果在变量的定义上没有做到统一的话，可能会隐藏一些危险的错误，这样的原因主要是变量的作用范围所致。变量的作用域是指变量的有效作用范围，与其它高级语言类似，PL/SQL的变量作用范围特点是：
1.变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。
2.一个变量（标识）只能在你所引用的块内是可见的。
3.当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。
4.在子块中重新定义该变量后，它的作用仅在该块内。
例15：
1 DECLARE 2 Emess char(80); 3 BEGIN 4 DECLARE 5 V1 NUMBER(4); 6 BEGIN 7 SELECT empno INTO v1 FROM emp WHERE LOWER(job)='president'; 8 DBMS_OUTPUT.PUT_LINE(V1); 9 EXCEPTION 10 When TOO_MANY_ROWS THEN 11 DBMS_OUTPUT.PUT_LINE ('More than one president'); 12 END; 13 DECLARE 14 V1 NUMBER(4); 15 BEGIN 16 SELECT empno INTO v1 FROM emp WHERE LOWER(job)='manager'; 17 EXCEPTION 18 When TOO_MANY_ROWS THEN 19 DBMS_OUTPUT.PUT_LINE ('More than one manager'); 20 END; 21 EXCEPTION 22 When others THEN 23 Emess:=substr(SQLERRM,1,80); 24 DBMS_OUTPUT.PUT_LINE(emess); 25 END;
2.8 注释
在PL/SQL里，可以使用两种符号来写注释，即：使用双 ‘-‘ ( 减号) 加注释
PL/SQL允许用 – 来写注释，它的作用范围是只能在一行有效。如：
V_Sal NUMBER(12,2); -- 人员的工资变量。
使用 /* */ 来加一行或多行注释，如：
/***********************************************/
/* 文件名： department_salary.sql */
/* 作 者： EricHu */
/* 时 间： 2011-5-9 */
/***********************************************/
提示：被解释后存放在数据库中的 PL/SQL 程序，一般系统自动将程序头部的注释去掉。只有在 PROCEDURE 之后的注释才被保留；另外程序中的空行也自动被去掉。
2.9 简单例子
2.9.1 简单数据插入例子
例16：
1 /***********************************************/ 2 /* 文件名： test.sql */ 3 /* 说 明: 4 一个简单的插入测试，无实际应用。*/ 5 /* 作 者： EricHu */ 6 /* 时 间： 2011-5-9 */ 7 /***********************************************/ 8 DECLARE 9 v_ename VARCHAR2(20) := 'Bill'; 10 v_sal NUMBER(7,2) :=1234.56; 11 v_deptno NUMBER(2) := 10; 12 v_empno NUMBER(4) := 8888; 13 BEGIN 14 INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate ) 15 VALUES (v_empno, v_ename, 'Manager', v_sal, v_deptno, 16 TO_DATE('1954.06.09','yyyy.mm.dd') ); 17 COMMIT; 18 END;
例17：
1 /***********************************************/ 2 /* 文件名： test_deletedata.sql */ 3 /* 说 明: 4 简单的删除例子，不是实际应用。 */ 5 /* 作 者： EricHu */ 6 /* 时 间： 2011-5-9 */ 7 /***********************************************/ 8 DECLARE 9 v_ename VARCHAR2(20) := 'Bill'; 10 v_sal NUMBER(7,2) :=1234.56; 11 v_deptno NUMBER(2) := 10; 12 v_empno NUMBER(4) := 8888; 13 BEGIN 14 INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate ) 15 VALUES ( v_empno, v_ename, ‘Manager’, v_sal, v_deptno, 16 TO_DATE(’1954.06.09’,’yyyy.mm.dd’) ); 17 COMMIT; 18 END; 19 DECLARE 20 v_empno number(4) := 8888; 21 BEGIN 22 DELETE FROM emp WHERE empno=v_empno; 23 COMMIT; 24 END;
3. PL/SQL流程控制语句
介绍PL/SQL的流程控制语句, 包括如下三类:
控制语句: IF 语句
循环语句: LOOP语句, EXIT语句
顺序语句: GOTO语句, NULL语句
IF <布尔表达式> THEN
PL/SQL 和 SQL语句
END IF;
-----------------------
IF <布尔表达式> THEN
PL/SQL 和 SQL语句
ELSE
其它语句
END IF;
-----------------------
IF <布尔表达式> THEN
PL/SQL 和 SQL语句
ELSIF < 其它布尔表达式> THEN
其它语句
ELSIF < 其它布尔表达式> THEN
其它语句
ELSE
其它语句
END IF;
提示: ELSIF 不能写成 ELSEIF
例1:
1 DECLARE 2 v_empno employees.employee_id%TYPE :=&empno; 3 V_salary employees.salary%TYPE; 4 V_comment VARCHAR2(35); 5 BEGIN 6 SELECT salary INTO v_salary FROM employees 7 WHERE employee_id = v_empno; 8 IF v_salary < 1500 THEN 9 V_comment:= '太少了,加点吧~!'; 10 ELSIF v_salary <3000 THEN 11 V_comment:= '多了点,少点吧~!'; 12 ELSE 13 V_comment:= '没有薪水~!'; 14 END IF; 15 DBMS_OUTPUT.PUT_LINE(V_comment); 16 exception 17 when no_data_found then 18 DBMS_OUTPUT.PUT_LINE('没有数据~!'); 19 when others then 20 DBMS_OUTPUT.PUT_LINE(sqlcode || '---' || sqlerrm); 21 END;
例2:
1 DECLARE 2 v_first_name VARCHAR2(20); 3 v_salary NUMBER(7,2); 4 BEGIN 5 SELECT first_name, salary INTO v_first_name, v_salary FROM employees 6 WHERE employee_id = &emp_id; 7 DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的工资是'||v_salary); 8 IF v_salary < 10000 THEN 9 DBMS_OUTPUT.PUT_LINE('工资低于10000'); 10 ELSE 11 IF 10000 <= v_salary AND v_salary < 20000 THEN 12 DBMS_OUTPUT.PUT_LINE('工资在10000到20000之间'); 13 ELSE 14 DBMS_OUTPUT.PUT_LINE('工资高于20000'); 15 END IF; 16 END IF; 17 END;
例3:
1 DECLARE 2 v_first_name VARCHAR2(20); 3 v_hire_date DATE; 4 v_bonus NUMBER(6,2); 5 BEGIN 6 SELECT first_name, hire_date INTO v_first_name, v_hire_date FROM employees 7 WHERE employee_id = &emp_id; 8 IF v_hire_date > TO_DATE('01-1月-90') THEN 9 v_bonus := 800; 10 ELSIF v_hire_date > TO_DATE('01-1月-88') THEN 11 v_bonus := 1600; 12 ELSE 13 v_bonus := 2400; 14 END IF; 15 DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的雇佣日期是'||v_hire_date 16 ||'、奖金是'||v_bonus); 17 END;
---------格式一---------
CASE 条件表达式
WHEN 条件表达式结果1 THEN
语句段1
WHEN 条件表达式结果2 THEN
语句段2
......
WHEN 条件表达式结果n THEN
语句段n
[ELSE 条件表达式结果]
END;
---------格式二---------
CASE
WHEN 条件表达式1 THEN
语句段1
WHEN 条件表达式2 THEN
语句段2
......
WHEN 条件表达式n THEN
语句段n
[ELSE 语句段]
END;
例4:
1 DECLARE 2 V_grade char(1) := UPPER('&p_grade'); 3 V_appraisal VARCHAR2(20); 4 BEGIN 5 V_appraisal := 6 CASE v_grade 7 WHEN 'A' THEN 'Excellent' 8 WHEN 'B' THEN 'Very Good' 9 WHEN 'C' THEN 'Good' 10 ELSE 'No such grade' 11 END; 12 DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||' Appraisal: '|| v_appraisal); 13 END;
例5:
1 DECLARE 2 v_first_name employees.first_name%TYPE; 3 v_job_id employees.job_id%TYPE; 4 v_salary employees.salary%TYPE; 5 v_sal_raise NUMBER(3,2); 6 BEGIN 7 SELECT first_name, job_id, salary INTO 8 v_first_name, v_job_id, v_salary 9 FROM employees WHERE employee_id = &emp_id; 10 CASE 11 WHEN v_job_id = 'PU_CLERK' THEN 12 IF v_salary < 3000 THEN v_sal_raise := .08; 13 ELSE v_sal_raise := .07; 14 END IF; 15 WHEN v_job_id = 'SH_CLERK' THEN 16 IF v_salary < 4000 THEN v_sal_raise := .06; 17 ELSE v_sal_raise := .05; 18 END IF; 19 WHEN v_job_id = 'ST_CLERK' THEN 20 IF v_salary < 3500 THEN v_sal_raise := .04; 21 ELSE v_sal_raise := .03; 22 END IF; 23 ELSE 24 DBMS_OUTPUT.PUT_LINE('该岗位不涨工资: '||v_job_id); 25 END CASE; 26 DBMS_OUTPUT.PUT_LINE(v_first_name||'的岗位是'||v_job_id 27 ||'、的工资是'||v_salary 28 ||'、工资涨幅是'||v_sal_raise); 29 END;
1. 简单循环
LOOP
要执行的语句;
EXIT WHEN <条件语句> --条件满足，退出循环语句
END LOOP;
例 6:
1 DECLARE 2 int NUMBER(2) :=0; 3 BEGIN 4 LOOP 5 int := int + 1; 6 DBMS_OUTPUT.PUT_LINE('int 的当前值为:'||int); 7 EXIT WHEN int =10; 8 END LOOP; 9 END;
2. WHILE 循环
WHILE <布尔表达式> LOOP
要执行的语句;
END LOOP;
例7.
1 DECLARE 2 x NUMBER :=1; 3 BEGIN 4 WHILE x<=10 LOOP 5 DBMS_OUTPUT.PUT_LINE('X的当前值为:'||x); 6 x:= x+1; 7 END LOOP; 8 END;
3. 数字式循环
[<<循环标签>>]
FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
要执行的语句;
END LOOP [循环标签];
每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT 退出循环。
例8.
BEGIN
FOR int in 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int);
END LOOP;
END;
例 9.
1 CREATE TABLE temp_table(num_col NUMBER); 2 DECLARE 3 V_counter NUMBER := 10; 4 BEGIN 5 INSERT INTO temp_table(num_col) VALUES (v_counter ); 6 FOR v_counter IN 20 .. 25 LOOP 7 INSERT INTO temp_table (num_col ) VALUES ( v_counter ); 8 END LOOP; 9 INSERT INTO temp_table(num_col) VALUES (v_counter ); 10 FOR v_counter IN REVERSE 20 .. 25 LOOP 11 INSERT INTO temp_table (num_col ) VALUES ( v_counter ); 12 END LOOP; 13 END ; 14 DROP TABLE temp_table;
例10:
1 DECLARE 2 TYPE jobids_varray IS VARRAY(12) OF VARCHAR2(10); --定义一个VARRAY数据类型 3 v_jobids JOBIDS_VARRAY; --声明一个具有JOBIDS_VARRAY数据类型的变量 4 v_howmany NUMBER; --声明一个变量来保存雇员的数量 5 BEGIN 6 --用某些job_id值初始化数组 7 v_jobids := jobids_varray('FI_ACCOUNT', 'FI_MGR', 'ST_CLERK', 'ST_MAN'); 8 --用FOR...LOOP...END LOOP循环使用每个数组成员的值 9 FOR i IN v_jobids.FIRST..v_jobids.LAST LOOP 10 --针对数组中的每个岗位，决定该岗位的雇员的数量 11 SELECT count(*) INTO v_howmany FROM employees WHERE job_id = v_jobids(i); 12 DBMS_OUTPUT.PUT_LINE ( '岗位'||v_jobids(i)|| 13 '总共有'|| TO_CHAR(v_howmany) || '个雇员'); 14 END LOOP; 15 END;
例11 在While循环中嵌套loop循环:
1 /*求100至110之间的素数*/ 2 DECLARE 3 v_m NUMBER := 101; 4 v_i NUMBER; 5 v_n NUMBER := 0; 6 BEGIN 7 WHILE v_m < 110 LOOP 8 v_i := 2; 9 LOOP 10 IF mod(v_m, v_i) = 0 THEN 11 v_i := 0; 12 EXIT; 13 END IF; 14 v_i := v_i + 1; 15 EXIT WHEN v_i > v_m - 1; 16 END LOOP; 17 IF v_i > 0 THEN 18 v_n := v_n + 1; 19 DBMS_OUTPUT.PUT_LINE('第'|| v_n || '个素数是' || v_m); 20 END IF; 21 v_m := v_m + 2; 22 END LOOP; 23 END;
PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思。语法如下：
GOTO label;
......
<<label>> /*标号是用<< >>括起来的标识符 */
注意，在以下地方使用是不合法的，编译时会出错误。
u 跳转到非执行语句前面。
u 跳转到子块中。
u 跳转到循环语句中。
u 跳转到条件语句中。
u 从异常处理部分跳转到执行。
u 从条件语句的一部分跳转到另一部分。
例12:
1 DECLARE 2 V_counter NUMBER := 1; 3 BEGIN 4 LOOP 5 DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter); 6 V_counter := v_counter + 1; 7 IF v_counter > 10 THEN 8 GOTO labelOffLOOP; 9 END IF; 10 END LOOP; 11 <<labelOffLOOP>> 12 DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter); 13 END;
例13:
1 DECLARE 2 v_i NUMBER := 0; 3 v_s NUMBER := 0; 4 BEGIN 5 <<label_1>> 6 v_i := v_i + 1; 7 IF v_i <= 1000 THEN 8 v_s := v_s + v_i; 9 GOTO label_1; 10 END IF; 11 DBMS_OUTPUT.PUT_LINE(v_s); 12 END;
在PL/SQL 程序中，NULL语句是一个可执行语句，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符或不执行任何操作的空语句，可以使某些语句变得有意义，提高程序的可读性，保证其他语句结构的完整性和正确性。如：
例14:
1 DECLARE 2 ... 3 BEGIN 4 ... 5 IF v_num IS NULL THEN 6 GOTO labelPrint; 7 END IF; 8 … 9 <<labelPrint>> 10 NULL; --不需要处理任何数据。 11 END;
例15:
1 DECLARE 2 v_emp_id employees.employee_id%TYPE; 3 v_first_name employees.first_name%TYPE; 4 v_salary employees.salary%TYPE; 5 v_sal_raise NUMBER(3,2); 6 BEGIN 7 v_emp_id := &emp_id; 8 SELECT first_name, salary INTO v_first_name, v_salary 9 FROM employees WHERE employee_id = v_emp_id; 10 IF v_salary <= 3000 THEN 11 v_sal_raise := .10; 12 DBMS_OUTPUT.PUT_LINE(v_first_name||'的工资是'||v_salary 13 ||'、工资涨幅是'||v_sal_raise); 14 ELSE 15 NULL; 16 END IF; 17 END;
4. 把游标说透
4.1.3 关于 NO_DATA_FOUND 和 %NOTFOUND的区别
游标的使用
在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。
4.1 游标概念
在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。它为应用等量齐观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。
在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。
对于不同的SQL语句，游标的使用情况不同：
4.1.1 处理显式游标
1. 显式游标处理
显式游标处理需四个 PL/SQL步骤:
1）定义/声明游标：就是定义一个游标名，以及与其相对应的SELECT 语句。
格式：
CURSOR cursor_name[(parameter[, parameter]…)]
[RETURN datatype]
IS
select_statement;
游标参数只能为输入参数，其格式为：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定数据类型时，不能使用长度约束。如NUMBER(4),CHAR(10) 等都是错误的。
[RETURN datatype]是可选的，表示游标返回数据的数据。如果选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。一般是记录数据类型或带“%ROWTYPE”的数据。
2）打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。
格式：
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。PL/SQL 程序不能用OPEN 语句重复打开一个游标。
3）提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。
格式：
FETCH cursor_name INTO {variable_list | record_variable };
执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。
对该记录进行处理；
继续处理，直到活动集合中没有记录；
4）关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。关闭后的游标可以使用OPEN 语句重新打开。
格式：
CLOSE cursor_name;
注：定义的游标不能有INTO 子句。
例1. 查询前10名员工的信息。
1 DECLARE 2 CURSOR c_cursor 3 IS SELECT first_name || last_name, Salary 4 FROM EMPLOYEES 5 WHERE rownum<11; 6 v_ename EMPLOYEES.first_name%TYPE; 7 v_sal EMPLOYEES.Salary%TYPE; 8 BEGIN 9 OPEN c_cursor; 10 FETCH c_cursor INTO v_ename, v_sal; 11 WHILE c_cursor%FOUND LOOP 12 DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) ); 13 FETCH c_cursor INTO v_ename, v_sal; 14 END LOOP; 15 CLOSE c_cursor; 16 END;
例2. 游标参数的传递方法。
1 DECLARE 2 DeptRec DEPARTMENTS%ROWTYPE; 3 Dept_name DEPARTMENTS.DEPARTMENT_NAME%TYPE; 4 Dept_loc DEPARTMENTS.LOCATION_ID%TYPE; 5 CURSOR c1 IS 6 SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS 7 WHERE DEPARTMENT_ID <= 30; 8 CURSOR c2(dept_no NUMBER DEFAULT 10) IS 9 SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS 10 WHERE DEPARTMENT_ID <= dept_no; 11 CURSOR c3(dept_no NUMBER DEFAULT 10) IS 12 SELECT * FROM DEPARTMENTS 13 WHERE DEPARTMENTS.DEPARTMENT_ID <=dept_no; 14 BEGIN 15 OPEN c1; 16 LOOP 17 FETCH c1 INTO dept_name, dept_loc; 18 EXIT WHEN c1%NOTFOUND; 19 DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc); 20 END LOOP; 21 CLOSE c1; 22 OPEN c2; 23 LOOP 24 FETCH c2 INTO dept_name, dept_loc; 25 EXIT WHEN c2%NOTFOUND; 26 DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc); 27 END LOOP; 28 CLOSE c2; 29 OPEN c3(dept_no =>20); 30 LOOP 31 FETCH c3 INTO deptrec; 32 EXIT WHEN c3%NOTFOUND; 33 DBMS_OUTPUT.PUT_LINE(deptrec.DEPARTMENT_ID||'---'||deptrec.DEPARTMENT_NAME||'---'||deptrec.LOCATION_ID); 34 END LOOP; 35 CLOSE c3; 36 END;
2.游标属性
Cursor_name%FOUND 布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；
Cursor_name%NOTFOUND 布尔型属性，与%FOUND相反；
Cursor_name%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；
Cursor_name%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。
例3：给工资低于1200 的员工增加工资50。
1 DECLARE 2 v_empno EMPLOYEES.EMPLOYEE_ID%TYPE; 3 v_sal EMPLOYEES.Salary%TYPE; 4 CURSOR c_cursor IS SELECT EMPLOYEE_ID, Salary FROM EMPLOYEES; 5 BEGIN 6 OPEN c_cursor; 7 LOOP 8 FETCH c_cursor INTO v_empno, v_sal; 9 EXIT WHEN c_cursor%NOTFOUND; 10 IF v_sal<=1200 THEN 11 UPDATE EMPLOYEES SET Salary=Salary+50 WHERE EMPLOYEE_ID=v_empno; 12 DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!'); 13 END IF; 14 DBMS_OUTPUT.PUT_LINE('记录数:'|| c_cursor %ROWCOUNT); 15 END LOOP; 16 CLOSE c_cursor; 17 END;
例4：没有参数且没有返回值的游标。
1 DECLARE 2 v_f_name employees.first_name%TYPE; 3 v_j_id employees.job_id%TYPE; 4 CURSOR c1 --声明游标,没有参数没有返回值 5 IS 6 SELECT first_name, job_id FROM employees 7 WHERE department_id = 20; 8 BEGIN 9 OPEN c1; --打开游标 10 LOOP 11 FETCH c1 INTO v_f_name, v_j_id; --提取游标 12 IF c1%FOUND THEN 13 DBMS_OUTPUT.PUT_LINE(v_f_name||'的岗位是'||v_j_id); 14 ELSE 15 DBMS_OUTPUT.PUT_LINE('已经处理完结果集了'); 16 EXIT; 17 END IF; 18 END LOOP; 19 CLOSE c1; --关闭游标 20 END;
例5：有参数且没有返回值的游标。
1 DECLARE 2 v_f_name employees.first_name%TYPE; 3 v_h_date employees.hire_date%TYPE; 4 CURSOR c2(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值 5 IS 6 SELECT first_name, hire_date FROM employees 7 WHERE department_id = dept_id AND job_id = j_id; 8 BEGIN 9 OPEN c2(90, 'AD_VP'); --打开游标,传递参数值 10 LOOP 11 FETCH c2 INTO v_f_name, v_h_date; --提取游标 12 IF c2%FOUND THEN 13 DBMS_OUTPUT.PUT_LINE(v_f_name||'的雇佣日期是'||v_h_date); 14 ELSE 15 DBMS_OUTPUT.PUT_LINE('已经处理完结果集了'); 16 EXIT; 17 END IF; 18 END LOOP; 19 CLOSE c2; --关闭游标 20 END;
例6：有参数且有返回值的游标。
1 DECLARE 2 TYPE emp_record_type IS RECORD( 3 f_name employees.first_name%TYPE, 4 h_date employees.hire_date%TYPE); 5 v_emp_record EMP_RECORD_TYPE; 6 CURSOR c3(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数有返回值 7 RETURN EMP_RECORD_TYPE 8 IS 9 SELECT first_name, hire_date FROM employees 10 WHERE department_id = dept_id AND job_id = j_id; 11 BEGIN 12 OPEN c3(j_id => 'AD_VP', dept_id => 90); --打开游标,传递参数值 13 LOOP 14 FETCH c3 INTO v_emp_record; --提取游标 15 IF c3%FOUND THEN 16 DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是' 17 ||v_emp_record.h_date); 18 ELSE 19 DBMS_OUTPUT.PUT_LINE('已经处理完结果集了'); 20 EXIT; 21 END IF; 22 END LOOP; 23 CLOSE c3; --关闭游标 24 END;
例7：基于游标定义记录变量。
1 DECLARE 2 CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值 3 IS 4 SELECT first_name f_name, hire_date FROM employees 5 WHERE department_id = dept_id AND job_id = j_id; 6 --基于游标定义记录变量，比声明记录类型变量要方便，不容易出错 7 v_emp_record c4%ROWTYPE; 8 BEGIN 9 OPEN c4(90, 'AD_VP'); --打开游标,传递参数值 10 LOOP 11 FETCH c4 INTO v_emp_record; --提取游标 12 IF c4%FOUND THEN 13 DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是' 14 ||v_emp_record.hire_date); 15 ELSE 16 DBMS_OUTPUT.PUT_LINE('已经处理完结果集了'); 17 EXIT; 18 END IF; 19 END LOOP; 20 CLOSE c4; --关闭游标 21 END;
3. 游标的FOR循环
PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。
格式：
FOR index_variable IN cursor_name[(value[, value]…)] LOOP
-- 游标数据处理代码
END LOOP;
其中：
index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据。
注：不要在程序中对游标进行人工操作；不要在程序中定义用于控制FOR循环的记录。
例8：
1 DECLARE 2 CURSOR c_sal IS SELECT employee_id, first_name || last_name ename, salary 3 FROM employees ; 4 BEGIN 5 --隐含打开游标 6 FOR v_sal IN c_sal LOOP 7 --隐含执行一个FETCH语句 8 DBMS_OUTPUT.PUT_LINE(to_char(v_sal.employee_id)||'---'|| v_sal.ename||'---'||to_char(v_sal.salary)) ; 9 --隐含监测c_sal%NOTFOUND 10 END LOOP; 11 --隐含关闭游标 12 END;
例9：当所声明的游标带有参数时，通过游标FOR 循环语句为游标传递参数。
1 DECLARE 2 CURSOR c_cursor(dept_no NUMBER DEFAULT 10) 3 IS 4 SELECT department_name, location_id FROM departments WHERE department_id <= dept_no; 5 BEGIN 6 DBMS_OUTPUT.PUT_LINE('当dept_no参数值为30：'); 7 FOR c1_rec IN c_cursor(30) LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id); 8 END LOOP; 9 DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默认的dept_no参数值10：'); 10 FOR c1_rec IN c_cursor LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id); 11 END LOOP; 12 END;
例10：PL/SQL还允许在游标FOR循环语句中使用子查询来实现游标的功能。
1 BEGIN 2 FOR c1_rec IN(SELECT department_name, location_id FROM departments) LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id); 3 END LOOP; 4 END;
显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。
格式调用为： SQL%
注：INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标。
隐式游标属性
例11: 删除EMPLOYEES表中某部门的所有员工，如果该部门中已没有员工，则在DEPARTMENT表中删除该部门。
1 DECLARE 2 V_deptno department_id%TYPE :=&p_deptno; 3 BEGIN 4 DELETE FROM employees WHERE department_id=v_deptno; 5 IF SQL%NOTFOUND THEN 6 DELETE FROM departments WHERE department_id=v_deptno; 7 END IF; 8 END;
例12: 通过隐式游标SQL的%ROWCOUNT属性来了解修改了多少行。
1 DECLARE 2 v_rows NUMBER; 3 BEGIN 4 --更新数据 5 UPDATE employees SET salary = 30000 6 WHERE department_id = 90 AND job_id = 'AD_VP'; 7 --获取默认游标的属性值 8 v_rows := SQL%ROWCOUNT; 9 DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资'); 10 --回退更新，以便使数据库的数据保持原样 11 ROLLBACK; 12 END;
4.1.3 关于 NO_DATA_FOUND 和 %NOTFOUND的区别
SELECT … INTO 语句触发 NO_DATA_FOUND；
当一个显式游标的WHERE子句未找到时触发%NOTFOUND；当UPDATE或DELETE 语句的WHERE 子句未找到时触发 SQL%NOTFOUND；在提取循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND。
游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。
为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。该需求迫使ORACLE锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。
语法：
SELECT column_list FROM table_list FOR UPDATE [OF column[, column]…] [NOWAIT]
如果另一个会话已对活动集中的行加了锁，那么SELECT FOR UPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：
ORA-0054 ：resource busy and acquire with nowait specified.
如果使用 FOR UPDATE 声明游标，则可在DELETE和UPDATE 语句中使用
WHERE CURRENT OF cursor_name子句，修改或删除游标结果集合当前行对应的数据库表中的数据行。
例13：从EMPLOYEES表中查询某部门的员工情况，将其工资最低定为 1500；
1 DECLARE 2 V_deptno employees.department_id%TYPE :=&p_deptno; 3 CURSOR emp_cursor 4 IS 5 SELECT employees.employee_id, employees.salary 6 FROM employees WHERE employees.department_id=v_deptno 7 FOR UPDATE NOWAIT; 8 BEGIN 9 FOR emp_record IN emp_cursor LOOP 10 IF emp_record.salary < 1500 THEN 11 UPDATE employees SET salary=1500 12 WHERE CURRENT OF emp_cursor; 13 END IF; 14 END LOOP; 15 -- COMMIT; 16 END;
例14：将EMPLOYEES表中部门编码为90、岗位为AD_VP的雇员的工资都更新为2000元；
1 DECLARE 2 v_emp_record employees%ROWTYPE; 3 CURSOR c1 4 IS 5 SELECT * FROM employees FOR UPDATE; 6 BEGIN 7 OPEN c1; 8 LOOP 9 FETCH c1 INTO v_emp_record; 10 EXIT WHEN c1%NOTFOUND; 11 IF v_emp_record.department_id = 90 AND 12 v_emp_record.job_id = 'AD_VP' 13 THEN 14 UPDATE employees SET salary = 20000 15 WHERE CURRENT OF c1; --更新当前游标行对应的数据行 16 END IF; 17 END LOOP; 18 COMMIT; --提交已经修改的数据 19 CLOSE c1; 20 END;
4.2 游标变量
与游标一样，游标变量也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。
4.2.1 声明游标变量
游标变量为一个指针，它属于参照类型，所以在声明游标变量类型之前必须先定义游标变量类型。在PL/SQL中，可以在块、子程序和包的声明区域内定义游标变量类型。
语法格式为：
TYPE ref_type_name IS REF CURSOR
[ RETURN return_type];
其中：ref_type_name为新定义的游标变量类型名称；
return_type 为游标变量的返回值类型，它必须为记录变量。
在定义游标变量类型时，可以采用强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，而弱类型定义则不说明返回值类型。
声明一个游标变量的两个步骤：
步骤一：定义一个REF CURSOU数据类型，如：TYPE ref_cursor_type IS REF CURSOR;
步骤二：声明一个该数据类型的游标变量，如： cv_ref REF_CURSOR_TYPE;
例：创建两个强类型定义游标变量和一个弱类型游标变量：
1 DECLARE 2 TYPE deptrecord IS RECORD( 3 Deptno departments.department_id%TYPE, 4 Dname departments.department_name%TYPE, 5 Loc departments.location_id%TYPE 6 ); 7 TYPE deptcurtype IS REF CURSOR RETURN departments%ROWTYPE; 8 TYPE deptcurtyp1 IS REF CURSOR RETURN deptrecord; 9 TYPE curtype IS REF CURSOR; 10 Dept_c1 deptcurtype; 11 Dept_c2 deptcurtyp1; 12 Cv curtype;
与游标一样，游标变量操作也包括打开、提取和关闭三个步骤。
1． 打开游标变量
打开游标变量时使用的是OPEN…FOR 语句。格式为：
OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;
其中：cursor_variable_name为游标变量，host_cursor_variable_name为PL/SQL主机环境（如OCI: ORACLE Call Interface，Pro*c 程序等）中声明的游标变量。
OPEN…FOR 语句可以在关闭当前的游标变量之前重新打开游标变量，而不会导致CURSOR_ALREAD_OPEN异常错误。新打开游标变量时，前一个查询的内存处理区将被释放。
2． 提取游标变量数据
使用FETCH语句提取游标变量结果集合中的数据。格式为：
FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]…| record_variable};
其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称；variable和record_variable分别为普通变量和记录变量名称。
3． 关闭游标变量
CLOSE语句关闭游标变量，格式为：
CLOSE {cursor_variable_name | :host_cursor_variable_name}
其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称，如果应用程序试图关闭一个未打开的游标变量，则将导致INVALID_CURSOR异常错误。
例15：强类型参照游标变量类型
1 DECLARE 2 TYPE emp_job_rec IS RECORD( 3 Employee_id employees.employee_id%TYPE, 4 Employee_name employees.first_name%TYPE, 5 Job_title employees.job_id%TYPE 6 ); 7 TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec; 8 Emp_refcur emp_job_refcur_type ; 9 Emp_job emp_job_rec; 10 BEGIN 11 OPEN emp_refcur FOR 12 SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id 13 FROM employees 14 ORDER BY employees.department_id; 15 FETCH emp_refcur INTO emp_job; 16 WHILE emp_refcur%FOUND LOOP 17 DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_title); 18 FETCH emp_refcur INTO emp_job; 19 END LOOP; 20 END;
例16：弱类型参照游标变量类型
1 PROMPT 2 PROMPT 'What table would you like to see?' 3 ACCEPT tab PROMPT '(D)epartment, or (E)mployees:' 4 DECLARE 5 Type refcur_t IS REF CURSOR; 6 Refcur refcur_t; 7 TYPE sample_rec_type IS RECORD ( 8 Id number, 9 Description VARCHAR2 (30) 10 ); 11 sample sample_rec_type; 12 selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1)); 13 BEGIN 14 IF selection='D' THEN 15 OPEN refcur FOR 16 SELECT departments.department_id, departments.department_name FROM departments; 17 DBMS_OUTPUT.PUT_LINE('Department data'); 18 ELSIF selection='E' THEN 19 OPEN refcur FOR 20 SELECT employees.employee_id, employees.first_name||' is a '||employees.job_id FROM employees; 21 DBMS_OUTPUT.PUT_LINE('Employee data'); 22 ELSE 23 DBMS_OUTPUT.PUT_LINE('Please enter ''D'' or ''E'''); 24 RETURN; 25 END IF; 26 DBMS_OUTPUT.PUT_LINE('----------------------'); 27 FETCH refcur INTO sample; 28 WHILE refcur%FOUND LOOP 29 DBMS_OUTPUT.PUT_LINE(sample.id||': '||sample.description); 30 FETCH refcur INTO sample; 31 END LOOP; 32 CLOSE refcur; 33 END;
例17：使用游标变量（没有RETURN子句）
1 DECLARE 2 --定义一个游标数据类型 3 TYPE emp_cursor_type IS REF CURSOR; 4 --声明一个游标变量 5 c1 EMP_CURSOR_TYPE; 6 --声明两个记录变量 7 v_emp_record employees%ROWTYPE; 8 v_reg_record regions%ROWTYPE; 9 BEGIN 10 OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20; 11 LOOP 12 FETCH c1 INTO v_emp_record; 13 EXIT WHEN c1%NOTFOUND; 14 DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇佣日期是' 15 ||v_emp_record.hire_date); 16 END LOOP; 17 --将同一个游标变量对应到另一个SELECT语句 18 OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）; 19 LOOP 20 FETCH c1 INTO v_reg_record; 21 EXIT WHEN c1%NOTFOUND; 22 DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示' 23 ||v_reg_record.region_name); 24 END LOOP; 25 CLOSE c1; 26 END;
例18：使用游标变量（有RETURN子句）
1 DECLARE 2 --定义一个与employees表中的这几个列相同的记录数据类型 3 TYPE emp_record_type IS RECORD( 4 f_name employees.first_name%TYPE, 5 h_date employees.hire_date%TYPE, 6 j_id employees.job_id%TYPE); 7 --声明一个该记录数据类型的记录变量 8 v_emp_record EMP_RECORD_TYPE; 9 --定义一个游标数据类型 10 TYPE emp_cursor_type IS REF CURSOR 11 RETURN EMP_RECORD_TYPE; 12 --声明一个游标变量 13 c1 EMP_CURSOR_TYPE; 14 BEGIN 15 OPEN c1 FOR SELECT first_name, hire_date, job_id 16 FROM employees WHERE department_id = 20; 17 LOOP 18 FETCH c1 INTO v_emp_record; 19 EXIT WHEN c1%NOTFOUND; 20 DBMS_OUTPUT.PUT_LINE('雇员名称：'||v_emp_record.f_name 21 ||' 雇佣日期：'||v_emp_record.h_date 22 ||' 岗位：'||v_emp_record.j_id); 23 END LOOP; 24 CLOSE c1; 25 END;
5. 异常错误处理
5.4 在 PL/SQL 中使用 SQLCODE, SQLERRM异常处理函数
即使是写得最好的PL/SQL程序也会遇到错误或未预料到的事件。一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。任何ORACLE错误（报告为ORA-xxxxx形式的Oracle错误号）、PL/SQL运行错误或用户定义条件（不一写是错误），都可以。当然了，PL/SQL编译错误不能通过PL/SQL异常处理来处理，因为这些错误发生在PL/SQL程序执行之前。
ORACLE 提供异常情况(EXCEPTION)和异常处理(EXCEPTION HANDLER)来实现错误处理。
异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行.
有三种类型的异常错误：
1． 预定义 ( Predefined )错误
ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。
2． 非预定义 ( Predefined )错误
即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。
3． 用户定义(User_define) 错误
程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。
异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:
1 EXCEPTION 2 WHEN first_exception THEN <code to handle first exception > 3 WHEN second_exception THEN <code to handle second exception > 4 WHEN OTHERS THEN <code to handle others exception > 5 END;
异常处理可以按任意次序排列,但 OTHERS 必须放在最后.
5.1.1 预定义的异常处理
预定义说明的部分 ORACLE 异常错误
对这种异常情况的处理，只需在PL/SQL块的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可。
例1：更新指定员工工资，如工资小于1500，则加100；
1 DECLARE 2 v_empno employees.employee_id%TYPE := &empno; 3 v_sal employees.salary%TYPE; 4 BEGIN 5 SELECT salary INTO v_sal FROM employees WHERE employee_id = v_empno; 6 IF v_sal<=1500 THEN 7 UPDATE employees SET salary = salary + 100 WHERE employee_id=v_empno; 8 DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!'); 9 ELSE 10 DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!'); 11 END IF; 12 EXCEPTION 13 WHEN NO_DATA_FOUND THEN 14 DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工'); 15 WHEN TOO_MANY_ROWS THEN 16 DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标'); 17 WHEN OTHERS THEN 18 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 19 END;
5.1.2 非预定义的异常处理
对于这类异常情况的处理，首先必须对非定义的ORACLE错误进行定义。步骤如下：
1. 在PL/SQL 块的定义部分定义异常情况：
<异常情况> EXCEPTION;
2. 将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句：
PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>);
3. 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。
例2：删除指定部门的记录信息，以确保该部门没有员工。
1 INSERT INTO departments VALUES(50, 'FINANCE', 'CHICAGO'); 2 DECLARE 3 v_deptno departments.department_id%TYPE := &deptno; 4 deptno_remaining EXCEPTION; 5 PRAGMA EXCEPTION_INIT(deptno_remaining, -2292); 6 /* -2292 是违反一致性约束的错误代码 */ 7 BEGIN 8 DELETE FROM departments WHERE department_id = v_deptno; 9 EXCEPTION 10 WHEN deptno_remaining THEN 11 DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!'); 12 WHEN OTHERS THEN 13 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 14 END;
当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。
对于这类异常情况的处理，步骤如下：
1． 在PL/SQL 块的定义部分定义异常情况：
<异常情况> EXCEPTION;
2． RAISE <异常情况>；
3． 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。
例3：更新指定员工工资，增加100；
1 DECLARE 2 v_empno employees.employee_id%TYPE :=&empno; 3 no_result EXCEPTION; 4 BEGIN 5 UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno; 6 IF SQL%NOTFOUND THEN 7 RAISE no_result; 8 END IF; 9 EXCEPTION 10 WHEN no_result THEN 11 DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!'); 12 WHEN OTHERS THEN 13 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 14 END;
调用DBMS_STANDARD(ORACLE提供的包)包所定义的RAISE_APPLICATION_ERROR过程，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。
RAISE_APPLICATION_ERROR 的语法如下：
RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] );
这里的error_number 是从 –20,000 到 –20,999 之间的参数，
error_message 是相应的提示信息(< 2048 字节)，
keep_errors 为可选，如果keep_errors =TRUE ,则新错误将被添加到已经引发的错误列表中。如果keep_errors=FALSE(缺省),则新错误将替换当前的错误列表。
例4：创建一个函数get_salary, 该函数检索指定部门的工资总和，其中定义了-20991和-20992号错误，分别处理参数为空和非法部门代码两种错误：
1 CREATE TABLE errlog( 2 Errcode NUMBER, 3 Errtext CHAR(40)); 4 CREATE OR REPLACE FUNCTION get_salary(p_deptno NUMBER) 5 RETURN NUMBER 6 AS 7 v_sal NUMBER; 8 BEGIN 9 IF p_deptno IS NULL THEN 10 RAISE_APPLICATION_ERROR(-20991, ’部门代码为空’); 11 ELSIF p_deptno<0 THEN 12 RAISE_APPLICATION_ERROR(-20992, ’无效的部门代码’); 13 ELSE 14 SELECT SUM(employees.salary) INTO v_sal FROM employees 15 WHERE employees.department_id=p_deptno; 16 RETURN v_sal; 17 END IF; 18 END; 19 DECLARE 20 V_salary NUMBER(7,2); 21 V_sqlcode NUMBER; 22 V_sqlerr VARCHAR2(512); 23 Null_deptno EXCEPTION; 24 Invalid_deptno EXCEPTION; 25 PRAGMA EXCEPTION_INIT(null_deptno,-20991); 26 PRAGMA EXCEPTION_INIT(invalid_deptno, -20992); 27 BEGIN 28 V_salary :=get_salary(10); 29 DBMS_OUTPUT.PUT_LINE('10号部门工资：' || TO_CHAR(V_salary)); 30 BEGIN 31 V_salary :=get_salary(-10); 32 EXCEPTION 33 WHEN invalid_deptno THEN 34 V_sqlcode :=SQLCODE; 35 V_sqlerr :=SQLERRM; 36 INSERT INTO errlog(errcode, errtext) 37 VALUES(v_sqlcode, v_sqlerr); 38 COMMIT; 39 END inner1; 40 V_salary :=get_salary(20); 41 DBMS_OUTPUT.PUT_LINE('部门号为20的工资为：'||TO_CHAR(V_salary)); 42 BEGIN 43 V_salary :=get_salary(NULL); 44 END inner2; 45 V_salary := get_salary(30); 46 DBMS_OUTPUT.PUT_LINE('部门号为30的工资为：'||TO_CHAR(V_salary)); 47 EXCEPTION 48 WHEN null_deptno THEN 49 V_sqlcode :=SQLCODE; 50 V_sqlerr :=SQLERRM; 51 INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr); 52 COMMIT; 53 WHEN OTHERS THEN 54 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 55 END outer;
例5：定义触发器，使用RAISE_APPLICATION_ERROR阻止没有员工姓名的新员式记录插入：
1 CREATE OR REPLACE TRIGGER tr_insert_emp 2 BEFORE INSERT ON employees 3 FOR EACH ROW 4 BEGIN 5 IF :new.first_name IS NULL OR :new.last_name is null THEN 6 RAISE_APPLICATION_ERROR(-20000,'Employee must have a name.'); 7 END IF; 8 END;
由于异常错误可以在声明部分和执行部分以及异常错误部分出现，因而在不同部分引发的异常错误也不一样。
5.2.1 在执行部分引发异常错误
当一个异常错误在执行部分引发时，有下列情况：
l 如果当前块对该异常错误设置了处理，则执行它并成功完成该块的执行，然后控制转给包含块。
l 如果没有对当前块异常错误设置定义处理器，则通过在包含块中引发它来传播异常错误。然后对该包含块执行步骤1)。
5.2.2 在声明部分引发异常错误
如果在声明部分引起异常情况，即在声明部分出现错误，那么该错误就能影响到其它的块。比如在有如下的PL/SQL程序：
1 DECLARE 2 name varchar2(12):='EricHu'; 3 其它语句 4 BEGIN 5 其它语句 6 EXCEPTION 7 WHEN OTHERS THEN 8 其它语句 9 END;
例子中，由于Abc number(3)=’abc’; 出错，尽管在EXCEPTION中说明了WHEN OTHERS THEN语句，但WHEN OTHERS THEN也不会被执行。 但是如果在该错误语句块的外部有一个异常错误，则该错误能被抓住，如：
1 BEGIN 2 DECLARE 3 name varchar2(12):='EricHu'; 4 其它语句 5 BEGIN 6 其它语句 7 EXCEPTION 8 WHEN OTHERS THEN 9 其它语句 10 END; 11 EXCEPTION 12 WHEN OTHERS THEN 13 其它语句 14 END;
在一般的应用处理中，建议程序人员要用异常处理，因为如果程序中不声明任何异常处理，则在程序运行出错时，程序就被终止，并且也不提示任何信息。下面是使用系统提供的异常来编程的例子。
5.4 在 PL/SQL 中使用 SQLCODE, SQLERRM异常处理函数
由于ORACLE 的错信息最大长度是512字节，为了得到完整的错误提示信息，我们可用 SQLERRM和 SUBSTR 函数一起得到错误提示信息，方便进行错误，特别是如果WHEN OTHERS异常处理器时更为方便。
SQLCODE 返回遇到的Oracle错误号,
SQLERRM 返回遇到的Oracle错误信息.
如: SQLCODE=-100 è SQLERRM=’no_data_found ‘
SQLCODE=0 è SQLERRM=’normal, successfual completion’
例6. 将ORACLE错误代码及其信息存入错误代码表
1 CREATE TABLE errors (errnum NUMBER(4), errmsg VARCHAR2(100)); 2 DECLARE 3 err_msg VARCHAR2(100); 4 BEGIN 5 /* 得到所有 ORACLE 错误信息 */ 6 FOR err_num IN -100 .. 0 LOOP 7 err_msg := SQLERRM(err_num); 8 INSERT INTO errors VALUES(err_num, err_msg); 9 END LOOP; 10 END; 11 DROP TABLE errors;
例7. 查询ORACLE错误代码；
1 BEGIN 2 INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id) 3 VALUES(2222, 'Eric','Hu', SYSDATE, 20); 4 DBMS_OUTPUT.PUT_LINE('插入数据记录成功!'); 5 INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id) 6 VALUES(2222, '胡','勇', SYSDATE, 20); 7 DBMS_OUTPUT.PUT_LINE('插入数据记录成功!'); 8 EXCEPTION 9 WHEN OTHERS THEN 10 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 11 END;
例8. 利用ORACLE错误代码，编写异常错误处理代码；
1 DECLARE 2 empno_remaining EXCEPTION; 3 PRAGMA EXCEPTION_INIT(empno_remaining, -1); 4 /* -1 是违反唯一约束条件的错误代码 */ 5 BEGIN 6 INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id) 7 VALUES(3333, 'Eric','Hu', SYSDATE, 20); 8 DBMS_OUTPUT.PUT_LINE('插入数据记录成功!'); 9 INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id) 10 VALUES(3333, '胡','勇',SYSDATE, 20); 11 DBMS_OUTPUT.PUT_LINE('插入数据记录成功!'); 12 EXCEPTION 13 WHEN empno_remaining THEN 14 DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!'); 15 WHEN OTHERS THEN 16 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 17 END;
6. 把过程与函数说透
6.3.4 PRAGMA AUTONOMOUS_TRANSACTION
6.1 引言
过程与函数（另外还有包与触发器）是命名的PL/SQL块（也是用户的方案对象），被编译后存储在数据库中，以备执行。因此，其它PL/SQL块可以按名称来使用他们。所以，可以将商业逻辑、企业规则写成函数或过程保存到数据库中，以便共享。
过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。在本节中，主要介绍：
1． 创建存储过程和函数。
2． 正确使用系统级的异常处理和用户定义的异常处理。
3． 建立和管理存储过程和函数。
6.2 创建函数
1. 创建函数
语法如下：
1 CREATE [OR REPLACE] FUNCTION function_name 2 (arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1], 3 [arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]], 4 ...... 5 [argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]]) 6 [ AUTHID DEFINER | CURRENT_USER ] 7 RETURN return_type 8 IS | AS 9 <类型.变量的声明部分> 10 BEGIN 11 执行部分 12 RETURN expression 13 EXCEPTION 14 异常处理部分 15 END function_name;
IN,OUT,IN OUT是形参的模式。若省略，则为IN模式。IN模式的形参只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回时实参的值不变。OUT模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL），但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。IN OUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量。
一般，只有在确认function_name函数是新函数或是要更新的函数时，才使用OR REPALCE关键字，否则容易删除有用的函数。
例1. 获取某部门的工资总和：
1 --获取某部门的工资总和 2 CREATE OR REPLACE 3 FUNCTION get_salary( 4 Dept_no NUMBER, 5 Emp_count OUT NUMBER) 6 RETURN NUMBER 7 IS 8 V_sum NUMBER; 9 BEGIN 10 SELECT SUM(SALARY), count(*) INTO V_sum, emp_count 11 FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no; 12 RETURN v_sum; 13 EXCEPTION 14 WHEN NO_DATA_FOUND THEN 15 DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); 16 WHEN OTHERS THEN 17 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 18 END get_salary;
2. 函数的调用
函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数：
第一种参数传递格式：位置表示法。
即在调用时按形参的排列顺序，依次写出实参的名称，而将形参与实参关联起来进行传递。用这种方法进行调用，形参与实参的名称是相互独立，没有关系，强调次序才是重要的。
格式为：
argument_value1[,argument_value2 …]
例2：计算某部门的工资总和：
1 DECLARE 2 V_num NUMBER; 3 V_sum NUMBER; 4 BEGIN 5 V_sum :=get_salary(10, v_num); 6 DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num); 7 END;
第二种参数传递格式：名称表示法。
即在调用时按形参的名称与实参的名称，写出实参对应的形参，而将形参与实参关联起来进行传递。这种方法，形参与实参的名称是相互独立的，没有关系，名称的对应关系才是最重要的，次序并不重要。
格式为：
argument => parameter [,…]
其中：argument 为形式参数，它必须与函数定义时所声明的形式参数名称相同parameter 为实际参数。
在这种格式中，形势参数与实际参数成对出现，相互间关系唯一确定，所以参数的顺序可以任意排列。
例3：计算某部门的工资总和：
1 DECLARE 2 V_num NUMBER; 3 V_sum NUMBER; 4 BEGIN 5 V_sum :=get_salary(emp_count => v_num, dept_no => 10); 6 DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num); 7 END;
第三种参数传递格式：组合传递。
即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。
例4：
1 CREATE OR REPLACE FUNCTION demo_fun( 2 Name VARCHAR2,--注意VARCHAR2不能给精度，如：VARCHAR2(10)，其它类似 3 Age INTEGER, 4 Sex VARCHAR2) 5 RETURN VARCHAR2 6 AS 7 V_var VARCHAR2(32); 8 BEGIN 9 V_var := name||'：'||TO_CHAR(age)||'岁.'||sex; 10 RETURN v_var; 11 END; 12 DECLARE 13 Var VARCHAR(32); 14 BEGIN 15 Var := demo_fun('user1', 30, sex => '男'); 16 DBMS_OUTPUT.PUT_LINE(var); 17 Var := demo_fun('user2', age => 40, sex => '男'); 18 DBMS_OUTPUT.PUT_LINE(var); 19 Var := demo_fun('user3', sex => '女', age => 20); 20 DBMS_OUTPUT.PUT_LINE(var); 21 END;
无论采用哪一种参数传递方法，实际参数和形式参数之间的数据传递只有两种方法：传址法和传值法。所谓传址法是指在调用函数时，将实际参数的地址指针传递给形式参数，使形式参数和实际参数指向内存中的同一区域，从而实现参数数据的传递。这种方法又称作参照法，即形式参数参照实际参数数据。输入参数均采用传址法传递数据。
传值法是指将实际参数的数据拷贝到形式参数，而不是传递实际参数的地址。默认时，输出参数和输入/输出参数均采用传值法。在函数调用时，ORACLE将实际参数数据拷贝到输入/输出参数，而当函数正常运行退出时，又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。
3. 参数默认值
在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。
例5：
1 CREATE OR REPLACE FUNCTION demo_fun( 2 Name VARCHAR2, 3 Age INTEGER, 4 Sex VARCHAR2 DEFAULT '男') 5 RETURN VARCHAR2 6 AS 7 V_var VARCHAR2(32); 8 BEGIN 9 V_var := name||'：'||TO_CHAR(age)||'岁.'||sex; 10 RETURN v_var; 11 END;
具有默认值的函数创建后，在函数调用时，如果没有为具有默认值的参数提供实际参数值，函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时，函数将使用实际参数值。在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。
1 DECLARE 2 3 var VARCHAR(32); 4 5 BEGIN 6 7 Var := demo_fun('user1', 30); 8 9 DBMS_OUTPUT.PUT_LINE(var); 10 11 Var := demo_fun('user2', age => 40); 12 13 DBMS_OUTPUT.PUT_LINE(var); 14 15 Var := demo_fun('user3', sex => '女', age => 20); 16 17 DBMS_OUTPUT.PUT_LINE(var); 18 19 END;
6.3 存储过程
6.3.1 创建过程
建立存储过程
在 ORACLE SERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数.
创建过程语法:
1 CREATE [OR REPLACE] PROCEDURE procedure_name 2 ([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1], 3 [arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]], 4 ...... 5 [argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen]) 6 [ AUTHID DEFINER | CURRENT_USER ] 7 { IS | AS } 8 <声明部分> 9 BEGIN 10 <执行部分> 11 EXCEPTION 12 <可选的异常错误处理程序> 13 END procedure_name;
说明：相关参数说明参见函数的语法说明。
例6．用户连接登记记录；
1 CREATE TABLE logtable (userid VARCHAR2(10), logdate date); 2 CREATE OR REPLACE PROCEDURE logexecution 3 IS 4 BEGIN 5 INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE); 6 END;
例7．删除指定员工记录；
1 CREATE OR REPLACE 2 PROCEDURE DelEmp 3 (v_empno IN employees.employee_id%TYPE) 4 AS 5 No_result EXCEPTION; 6 BEGIN 7 DELETE FROM employees WHERE employee_id = v_empno; 8 IF SQL%NOTFOUND THEN 9 RAISE no_result; 10 END IF; 11 DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被删除!'); 12 EXCEPTION 13 WHEN no_result THEN 14 DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 15 WHEN OTHERS THEN 16 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 17 END DelEmp;
例8．插入员工记录:
1 CREATE OR REPLACE 2 PROCEDURE InsertEmp( 3 v_empno in employees.employee_id%TYPE, 4 v_firstname in employees.first_name%TYPE, 5 v_lastname in employees.last_name%TYPE, 6 v_deptno in employees.department_id%TYPE 7 ) 8 AS 9 empno_remaining EXCEPTION; 10 PRAGMA EXCEPTION_INIT(empno_remaining, -1); 11 /* -1 是违反唯一约束条件的错误代码 */ 12 BEGIN 13 INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID) 14 VALUES(v_empno, v_firstname,v_lastname, sysdate, v_deptno); 15 DBMS_OUTPUT.PUT_LINE('温馨提示:插入数据记录成功!'); 16 EXCEPTION 17 WHEN empno_remaining THEN 18 DBMS_OUTPUT.PUT_LINE('温馨提示:违反数据完整性约束!'); 19 WHEN OTHERS THEN 20 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 21 END InsertEmp;
例9．使用存储过程向departments表中插入数据。
1 CREATE OR REPLACE 2 PROCEDURE insert_dept 3 (v_dept_id IN departments.department_id%TYPE, 4 v_dept_name IN departments.department_name%TYPE, 5 v_mgr_id IN departments.manager_id%TYPE, 6 v_loc_id IN departments.location_id%TYPE) 7 IS 8 ept_null_error EXCEPTION; 9 PRAGMA EXCEPTION_INIT(ept_null_error, -1400); 10 ept_no_loc_id EXCEPTION; 11 PRAGMA EXCEPTION_INIT(ept_no_loc_id, -2291); 12 BEGIN 13 INSERT INTO departments 14 (department_id, department_name, manager_id, location_id) 15 VALUES 16 (v_dept_id, v_dept_name, v_mgr_id, v_loc_id); 17 DBMS_OUTPUT.PUT_LINE('插入部门'||v_dept_id||'成功'); 18 EXCEPTION 19 WHEN DUP_VAL_ON_INDEX THEN 20 RAISE_APPLICATION_ERROR(-20000, '部门编码不能重复'); 21 WHEN ept_null_error THEN 22 RAISE_APPLICATION_ERROR(-20001, '部门编码、部门名称不能为空'); 23 WHEN ept_no_loc_id THEN 24 RAISE_APPLICATION_ERROR(-20002, '没有该地点'); 25 END insert_dept; 26 /*调用实例一: 27 DECLARE 28 ept_20000 EXCEPTION; 29 PRAGMA EXCEPTION_INIT(ept_20000, -20000); 30 ept_20001 EXCEPTION; 31 PRAGMA EXCEPTION_INIT(ept_20001, -20001); 32 ept_20002 EXCEPTION; 33 PRAGMA EXCEPTION_INIT(ept_20002, -20002); 34 BEGIN 35 insert_dept(300, '部门300', 100, 2400); 36 insert_dept(310, NULL, 100, 2400); 37 insert_dept(310, '部门310', 100, 900); 38 EXCEPTION 39 WHEN ept_20000 THEN 40 DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复'); 41 WHEN ept_20001 THEN 42 DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空'); 43 WHEN ept_20002 THEN 44 DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点'); 45 WHEN OTHERS THEN 46 DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误'); 47 END; 48 调用实例二： 49 DECLARE 50 ept_20000 EXCEPTION; 51 PRAGMA EXCEPTION_INIT(ept_20000, -20000); 52 ept_20001 EXCEPTION; 53 PRAGMA EXCEPTION_INIT(ept_20001, -20001); 54 ept_20002 EXCEPTION; 55 PRAGMA EXCEPTION_INIT(ept_20002, -20002); 56 BEGIN 57 insert_dept(v_dept_name => '部门310', v_dept_id => 310, 58 v_mgr_id => 100, v_loc_id => 2400); 59 insert_dept(320, '部门320', v_mgr_id => 100, v_loc_id => 900); 60 EXCEPTION 61 WHEN ept_20000 THEN 62 DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复'); 63 WHEN ept_20001 THEN 64 DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空'); 65 WHEN ept_20002 THEN 66 DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点'); 67 WHEN OTHERS THEN 68 DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误'); 69 END; 70 */
6.3.2 调用存储过程
存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS 、ORACLE开发工具或第三方开发工具中来调用运行。对于参数的传递也有三种：按位置传递、按名称传递和组合传递，传递方法与函数的一样。ORACLE 使用EXECUTE 语句来实现对存储过程的调用：
EXEC[UTE] procedure_name( parameter1, parameter2…);
例10：
EXECUTE logexecution;
例11：查询指定员工记录；
1 CREATE OR REPLACE 2 PROCEDURE QueryEmp 3 (v_empno IN employees.employee_id%TYPE, 4 v_ename OUT employees.first_name%TYPE, 5 v_sal OUT employees.salary%TYPE) 6 AS 7 BEGIN 8 SELECT last_name || last_name, salary INTO v_ename, v_sal 9 FROM employees 10 WHERE employee_id = v_empno; 11 DBMS_OUTPUT.PUT_LINE('温馨提示:编码为'||v_empno||'的员工已经查到!'); 12 EXCEPTION 13 WHEN NO_DATA_FOUND THEN 14 DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 15 WHEN OTHERS THEN 16 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 17 END QueryEmp; 18 --调用 19 DECLARE 20 v1 employees.first_name%TYPE; 21 v2 employees.salary%TYPE; 22 BEGIN 23 QueryEmp(100, v1, v2); 24 DBMS_OUTPUT.PUT_LINE('姓名:'||v1); 25 DBMS_OUTPUT.PUT_LINE('工资:'||v2); 26 QueryEmp(103, v1, v2); 27 DBMS_OUTPUT.PUT_LINE('姓名:'||v1); 28 DBMS_OUTPUT.PUT_LINE('工资:'||v2); 29 QueryEmp(104, v1, v2); 30 DBMS_OUTPUT.PUT_LINE('姓名:'||v1); 31 DBMS_OUTPUT.PUT_LINE('工资:'||v2); 32 END;
例12．计算指定部门的工资总和，并统计其中的职工数量。
1 CREATE OR REPLACE 2 PROCEDURE proc_demo 3 ( 4 dept_no NUMBER DEFAULT 10, 5 sal_sum OUT NUMBER, 6 emp_count OUT NUMBER 7 ) 8 IS 9 BEGIN 10 SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count 11 FROM employees WHERE department_id = dept_no; 12 EXCEPTION 13 WHEN NO_DATA_FOUND THEN 14 DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 15 WHEN OTHERS THEN 16 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 17 END proc_demo; 18 DECLARE 19 V_num NUMBER; 20 V_sum NUMBER(8, 2); 21 BEGIN 22 Proc_demo(30, v_sum, v_num); 23 DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和：'||v_sum||',人数：'||v_num); 24 Proc_demo(sal_sum => v_sum, emp_count => v_num); 25 DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和：'||v_sum||',人数：'||v_num); 26 END;
在PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。
例13：建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量；
1 DECLARE 2 V_num NUMBER; 3 V_sum NUMBER(8, 2); 4 PROCEDURE proc_demo 5 ( 6 Dept_no NUMBER DEFAULT 10, 7 Sal_sum OUT NUMBER, 8 Emp_count OUT NUMBER 9 ) 10 IS 11 BEGIN 12 SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count 13 FROM employees WHERE department_id=dept_no; 14 EXCEPTION 15 WHEN NO_DATA_FOUND THEN 16 DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); 17 WHEN OTHERS THEN 18 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 19 END proc_demo; 20 --调用方法： 21 BEGIN 22 Proc_demo(30, v_sum, v_num); 23 DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num); 24 Proc_demo(sal_sum => v_sum, emp_count => v_num); 25 DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||'，人数：'||v_num); 26 END;
6.3.3 AUTHID
过程中的AUTHID 指令可以告诉ORACLE ，这个过程使用谁的权限运行．默任情况下，存储过程会作为调用者的过程运行，但是具有设计者的特权．这称为设计者权利运行．
例14：建立过程，使用AUTOID DEFINER；
1 Connect HR/qaz 2 DROP TABLE logtable; 3 CREATE table logtable (userid VARCHAR2(10), logdate date); 4 CREATE OR REPLACE PROCEDURE logexecution 5 AUTHID DEFINER 6 IS 7 BEGIN 8 INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE); 9 END; 10 GRANT EXECUTE ON logexecution TO PUBLIC; 11 CONNECT / AS SYSDBA 12 GRANT CONNECT TO testuser1 IDENTIFIED BY userpwd1; 13 CONNECT testuser1/userpwd1 14 INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE); 15 EXECUTE HR.logexecution 16 CONNECT HR/qaz 17 SELECT * FROM HR.logtable;
例15：建立过程，使用AUTOID CURRENT_USER；
1 CONNECT HR/qaz 2 CREATE OR REPLACE PROCEDURE logexecution 3 AUTHID CURRENT_USER 4 IS 5 BEGIN 6 INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE); 7 END; 8 GRANT EXECUTE ON logexecution TO PUBLIC; 9 CONNECT testuser1/userpwd1 10 INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE); 11 EXECUTE HR.logexecution
6.3.4 PRAGMA AUTONOMOUS_TRANSACTION
ORACLE8i 可以支持事务处理中的事务处理的概念．这种子事务处理可以完成它自己的工作，独立于父事务处理进行提交或者回滚．通过使用这种方法，开发者就能够这样的过程，无论父事务处理是提交还是回滚，它都可以成功执行．
例16：建立过程，使用自动事务处理进行日志记录；
1 DROP TABLE logtable; 2 CREATE TABLE logtable( 3 Username varchar2(20), 4 Dassate_time date, 5 Mege varchar2(60) 6 ); 7 CREATE TABLE temp_table( N number ); 8 CREATE OR REPLACE PROCEDURE log_message(p_message varchar2) 9 AS 10 PRAGMA AUTONOMOUS_TRANSACTION; 11 BEGIN 12 INSERT INTO logtable VALUES ( user, sysdate, p_message ); 13 COMMIT; 14 END log_message; 15 BEGIN 16 Log_message (‘About to insert into temp_table‘); 17 INSERT INTO temp_table VALUES (1); 18 Log_message (‘Rollback to insert into temp_table‘); 19 ROLLBACK; 20 END; 21 SELECT * FROM logtable; 22 SELECT * FROM temp_table;
例17：建立过程，没有使用自动事务处理进行日志记录；
1 CREATE OR REPLACE PROCEDURE log_message(p_message varchar2) 2 AS 3 BEGIN 4 INSERT INTO logtable VALUES ( user, sysdate, p_message ); 5 COMMIT; 6 END log_message; 7 BEGIN 8 Log_message ('About to insert into temp_table'); 9 INSERT INTO temp_table VALUES (1); 10 Log_message ('Rollback to insert into temp_table'); 11 ROLLBACK; 12 END; 13 SELECT * FROM logtable; 14 SELECT * FROM temp_table;
6.3.5 开发存储过程步骤
开发存储过程、函数、包及触发器的步骤如下：
6.3.5.1 使用文字编辑处理软件编辑存储过程源码
使用文字编辑处理软件编辑存储过程源码，要用类似WORD 文字处理软件进行编辑时，要将源码存为文本格式。
6.3.5.2 在SQLPLUS或用调试工具将存储过程程序进行解释
在SQLPLUS或用调试工具将存储过程程序进行解释；
在SQL>下调试，可用START 或GET 等ORACLE命令来启动解释。如：
SQL>START c:stat1.sql
如果使用调式工具，可直接编辑和点击相应的按钮即可生成存储过程。
6.3.5.3 调试源码直到正确
我们不能保证所写的存储过程达到一次就正确。所以这里的调式是每个程序员必须进行的工作之一。在SQLPLUS下来调式主要用的方法是：
使用 SHOW ERROR命令来提示源码的错误位置；
使用 user_errors 数据字典来查看各存储过程的错误位置。
6.3.5.4 授权执行权给相关的用户或角色
如果调式正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。在SQL*PLUS下可以用GRANT命令来进行存储过程的运行授权。
GRANT语法：
1 GRANT system_privilege | role 2 TO user | role | PUBLIC [WITH ADMIN OPTION] 3 GRANT object_privilege | ALL ON schema.object 4 TO user | role | PUBLIC [WITH GRANT OPTION] 5 --例子： 6 CREATE OR REPLACE PUBLIC SYNONYM dbms_job FOR dbms_job 7 GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION
6.3.5.5 与过程相关数据字典
USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,
ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS
相关的权限:
CREATE ANY PROCEDURE
DROP ANY PROCEDURE
在SQL*PLUS 中，可以用DESCRIBE 命令查看过程的名字及其参数表。
DESC[RIBE] Procedure_name;
6.3.6 删除过程和函数
1．删除过程
可以使用DROP PROCEDURE命令对不需要的过程进行删除，语法如下：
DROP PROCEDURE [user.]Procudure_name;
2．删除函数
可以使用DROP FUNCTION 命令对不需要的函数进行删除，语法如下：
1 DROP FUNCTION [user.]Function_name; 2 --删除上面实例创建的存储过程与函数 3 DROP PROCEDURE logexecution; 4 DROP PROCEDURE delemp; 5 DROP PROCEDURE insertemp; 6 DROP PROCEDURE fireemp; 7 DROP PROCEDURE queryemp; 8 DROP PROCEDURE proc_demo; 9 DROP PROCEDURE log_message; 10 DROP FUNCTION demo_fun; 11 DROP FUNCTION get_salary;
6.3.7 过程与函数的比较
使用过程与函数具有如下优点：
1、共同使用的代码可以只需要被编写和测试一次，而被需要该代码的任何应用程序（如：.NET、C++、JAVA、VB程序，也可以是DLL库）调用。
2、这种集中编写、集中维护更新、大家共享（或重用）的方法，简化了应用程序的开发和维护，提高了效率与性能。
3、这种模块化的方法，使得可以将一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分，进行分别编写、调试。因此使程序的结构清晰、简单，也容易实现。
4、可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。
5、节省内存空间。它们以一种压缩的形式被存储在外存中，当被调用时才被放入内存进行处理。并且，如果多个用户要执行相同的过程或函数时，就只需要在内存中加载一个该过程或函数。
6、提高数据的安全性与完整性。通过把一些对数据的操作放到过程或函数中，就可以通过是否授予用户有执行该过程或的权限，来限制某些用户对数据进行这些操作。
过程与函数的相同功能有：
1、 都使用IN模式的参数传入数据、OUT模式的参数返回数据。
2、 输入参数都可以接受默认值，都可以传值或传引导。
3、 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
4、 都有声明部分、执行部分和异常处理部分。
5、 其管理过程都有创建、编译、授权、删除、显示依赖关系等。
使用过程与函数的原则：
1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。
2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。
3、可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。
7. 程序包的创建与应用
程序包(PACKAGE，简称包)是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，作为一个完整的单元存储在数据库中，用名称来标识包。它具有面向对象程序设计语言的特点，是对这些PL/SQL 程序设计元素的封装。包类似于c#和JAVA语言中的类，其中变量相当于类中的成员变量，过程和函数相当于类方法。把相关的模块归类成为包，可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能。
与高级语言中的类相同，包中的程序元素也分为公用元素和私用元素两种，这两种元素的区别是他们允许访问的程序范围不同，即它们的作用域不同。公用元素不仅可以被包中的函数、过程所调用，也可以被包外的PL/SQL程序访问，而私有元素只能被包内的函数和过程序所访问。
当然，对于不包含在程序包中的过程、函数是独立存在的。一般是先编写独立的过程与函数，待其较为完善或经过充分验证无误后，再按逻辑相关性组织为程序包。
程序包的优点
简化应用程序设计：程序包的说明部分和包体部分可以分别创建各编译。主要体现在以下三个方面：
1) 可以在设计一个应用程序时，只创建各编译程序包的说明部分，然后再编写引用该 程序包的PL/SQL块。
2) 当完成整个应用程序的整体框架后，再回头来定义包体部分。只要不改变包的说明部分，就可以单独调试、增加或替换包体的内容，这不会影响其他的应用程序。
3) 更新包的说明后必须重新编译引用包的应用程序，但更新包体，则不需重新编译引用包的应用程序，以快速进行进行应用程序的原形开发。
模块化：可将逻辑相关的PL/SQL块或元素等组织在一起，用名称来唯一标识程序 包。把一个大的功能模块划分人适当个数小的功能模块，分别完成各自的功能。这样组织的程序包都易于编写，易于理解更易于管理。
信息隐藏：因为包中的元素可以分为公有元素和私有元素。公有元素可被程序包内的过程、函数等的访问，还可以被包外的PL/SQL访问。但对于私有元素只能被包内的过程、函数等访问。对于用户，只需知道包的说明，不用了解包休的具体细节。
效率高：程序包在应用程序第一次调用程序包中的某个元素时，ORACLE将把整个程序包加载到内存中，当第二次访问程序包中的元素时，ORACLE将直接从内在中读取，而不需要进行磁盘I/O操作而影响速度，同时位于内在中的程序包可被同一会话期间的其它应用程序共享。因此，程序包增加了重用性并改善了多用户、多应用程序环境的效率。
对程序包的优点可总结如下：在PL/SQL程序设计中，使用包不仅可以使程序设计模块化，对外隐藏包内所使用的信息（通过使用私用变量），而写可以提高程序的执行效率。因为，当程序首次调用包内函数或过程时，ORACLE将整个包调入内存，当再次访问包内元素时，ORACLE直接从内存中读取，而不需要进行磁盘I/O操作，从而使程序执行效率得到提高。
一个包由两个分开的部分组成：
包说明（PACKAGE）：包说明部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素，这些元素为包的公有元素。
包主体（PACKAGE BODY）：包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子程序，在包主体中还可以声明包的私有元素。
包说明和包主体分开编译，并作为两部分分开的对象存放在数据库字典中，可查看数据字典user_source, all_source, dba_source，分别了解包说明与包主体的详细信息。
7.2 程序包的定义
程序包的定义分为程序包说明定义和程序包主体定义两部分组成。
程序包说明用于声明包的公用组件，如变量、常量、自定义数据类型、异常、过程、函数、游标等。包说明中定义的公有组件不仅可以在包内使用，还可以由包外其他过程、函数。但需要说明与注意的是，我们为了实现信息的隐藏，建议不要将所有组件都放在包说明处声明，只应把公共组件放在包声明部分。包的名称是唯一的，但对于两个包中的公有组件的名称可以相同，这种用“包名.公有组件名“加以区分。
包体是包的具体实现细节，其实现在包说明中声明的所有公有过程、函数、游标等。当然也可以在包体中声明仅属于自己的私有过程、函数、游标等。创建包体时，有以下几点需要注意：
包体只能在包说明被创建或编译后才能进行创建或编译。
在包体中实现的过程、函数、游标的名称必须与包说明中的过程、函数、游标一致，包括名称、参数的名称以及参数的模式（IN、OUT、IN OUT）。并建设按包说明中的次序定义包体中具体的实现。
在包体中声明的数据类型、变量、常量都是私有的，只能在包体中使用而不能被印刷体外的应用程序访问与使用。
在包体执行部分，可对包说明，包体中声明的公有或私有变量进行初始化或其它设置。
创建程序包说明语法格式:
1 CREATE [OR REPLACE] PACKAGE package_name 2 [AUTHID {CURRENT_USER | DEFINER}] 3 {IS | AS} 4 [公有数据类型定义[公有数据类型定义]…] 5 [公有游标声明[公有游标声明]…] 6 [公有变量、常量声明[公有变量、常量声明]…] 7 [公有函数声明[公有函数声明]…] 8 [公有过程声明[公有过程声明]…] 9 END [package_name];
其中：AUTHID CURRENT_USER和AUTHID DEFINER选项说明应用程序在调用函数时所使用的权限模式，它们与CREATE FUNCTION语句中invoker_right_clause子句的作用相同。
创建程序包主体语法格式:
1 CREATE [OR REPLACE] PACKAGE BODY package_name 2 {IS | AS} 3 [私有数据类型定义[私有数据类型定义]…] 4 [私有变量、常量声明[私有变量、常量声明]…] 5 [私有异常错误声明[私有异常错误声明]…] 6 [私有函数声明和定义[私有函数声明和定义]…] 7 [私有函过程声明和定义[私有函过程声明和定义]…] 8 [公有游标定义[公有游标定义]…] 9 [公有函数定义[公有函数定义]…] 10 [公有过程定义[公有过程定义]…] 11 BEGIN 12 执行部分(初始化部分) 13 END package_name;
其中：在包主体定义公有程序时，它们必须与包定义中所声明子程序的格式完全一致。
7.3 包的开发步骤
与开发存储过程类似，包的开发需要几个步骤：
1．将每个存储过程调式正确；
2．用文本编辑软件将各个存储过程和函数集成在一起；
3．按照包的定义要求将集成的文本的前面加上包定义；
4．按照包的定义要求将集成的文本的前面加上包主体；
5．使用SQLPLUS或开发工具进行调式。
7.4 包定义的说明
例1:创建的包为DEMO_PKG, 该包中包含一个记录变量DEPTREC、两个函数和一个过程。实现对dept表的增加、删除与查询。
1 CREATE OR REPLACE PACKAGE DEMO_PKG 2 IS 3 DEPTREC DEPT%ROWTYPE; 4 --Add dept... 5 FUNCTION add_dept( 6 dept_no NUMBER, 7 dept_name VARCHAR2, 8 location VARCHAR2) 9 RETURN NUMBER; 10 --delete dept... 11 FUNCTION delete_dept(dept_no NUMBER) 12 RETURN NUMBER; 13 --query dept... 14 PROCEDURE query_dept(dept_no IN NUMBER); 15 END DEMO_PKG;
包主体的创建方法，它实现上面所声明的包定义，并在包主体中声明一个私有变量flag和一个私有函数check_dept，由于在add_dept和remove_dept等函数中需要调用check_dpet函数，所以，在定义check_dept 函数之前首先对该函数进行声明，这种声明方法称作前向声明。
1 CREATE OR REPLACE PACKAGE BODY DEMO_PKG 2 IS 3 FUNCTION add_dept 4 ( 5 dept_no NUMBER, 6 dept_name VARCHAR2, 7 location VARCHAR2 8 ) 9 RETURN NUMBER 10 IS 11 empno_remaining EXCEPTION; --自定义异常 12 PRAGMA EXCEPTION_INIT(empno_remaining, -1); 13 /* -1 是违反唯一约束条件的错误代码 */ 14 BEGIN 15 INSERT INTO dept VALUES(dept_no, dept_name, location); 16 IF SQL%FOUND THEN 17 RETURN 1; 18 END IF; 19 EXCEPTION 20 WHEN empno_remaining THEN 21 RETURN 0; 22 WHEN OTHERS THEN 23 RETURN -1; 24 END add_dept; 25 FUNCTION delete_dept(dept_no NUMBER) 26 RETURN NUMBER 27 IS 28 BEGIN 29 DELETE FROM dept WHERE deptno = dept_no; 30 IF SQL%FOUND THEN 31 RETURN 1; 32 ELSE 33 RETURN 0; 34 END IF; 35 EXCEPTION 36 WHEN OTHERS THEN 37 RETURN -1; 38 END delete_dept; 39 PROCEDURE query_dept 40 (dept_no IN NUMBER) 41 IS 42 BEGIN 43 SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no; 44 EXCEPTION 45 WHEN NO_DATA_FOUND THEN 46 DBMS_OUTPUT.PUT_LINE('温馨提示:数据库中没有编码为'||dept_no||'的部门'); 47 WHEN TOO_MANY_ROWS THEN 48 DBMS_OUTPUT.PUT_LINE('程序运行错误,请使用游标进行操作!'); 49 WHEN OTHERS THEN 50 DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM); 51 END query_dept; 52 BEGIN 53 Null; 54 END DEMO_PKG;
对包内共有元素的调用格式为：包名.元素名称
调用DEMO_PKG包内函数对dept表进行插入、查询和删除操作，并通过DEMO_PKG包中的记录变量DEPTREC显示所查询到的数据库信息：
1 DECLARE 2 Var NUMBER; 3 BEGIN 4 Var := DEMO_PKG.add_dept(90,'HKLORB', 'HAIKOU'); 5 IF var =-1 THEN 6 DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM); 7 ELSIF var =0 THEN 8 DBMS_OUTPUT.PUT_LINE('温馨提示:该部门记录已经存在！'); 9 ELSE 10 DBMS_OUTPUT.PUT_LINE('温馨提示:添加记录成功！'); 11 DEMO_PKG.query_dept(90); 12 DBMS_OUTPUT.PUT_LINE(DEMO_PKG.DeptRec.deptno||'---'|| 13 DEMO_PKG.DeptRec.dname||'---'||DEMO_PKG.DeptRec.loc); 14 var := DEMO_PKG.delete_dept(90); 15 IF var =-1 THEN 16 DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM); 17 ELSIF var=0 THEN 18 DBMS_OUTPUT.PUT_LINE('温馨提示:该部门记录不存在！'); 19 ELSE 20 DBMS_OUTPUT.PUT_LINE('温馨提示:删除记录成功！'); 21 END IF; 22 END IF; 23 END;
例2: 创建包EMP_PKG，读取emp表中的数据
1 --创建包说明 2 CREATE OR REPLACE PACKAGE EMP_PKG 3 IS 4 TYPE emp_table_type IS TABLE OF emp%ROWTYPE 5 INDEX BY BINARY_INTEGER; 6 PROCEDURE read_emp_table (p_emp_table OUT emp_table_type); 7 END EMP_PKG; 8 --创建包体 9 CREATE OR REPLACE PACKAGE BODY EMP_PKG 10 IS 11 PROCEDURE read_emp_table (p_emp_table OUT emp_table_type) 12 IS 13 I BINARY_INTEGER := 0; 14 BEGIN 15 FOR emp_record IN ( SELECT * FROM emp ) LOOP 16 P_emp_table(i) := emp_record; 17 I := I + 1; 18 END LOOP; 19 END read_emp_table; 20 END EMP_PKG; 21 --执行 22 DECLARE 23 E_table EMP_PKG.emp_table_type; 24 BEGIN 25 EMP_PKG.read_emp_table(e_table); 26 FOR I IN e_table.FIRST ..e_table.LAST LOOP 27 DBMS_OUTPUT.PUT_LINE(e_table(i).empno||' '||e_table(i).ename); 28 END LOOP; 29 END;
例3: 创建包MANAGE_EMP_PKG，对员工进行管理（新增员工、新增部门、删除指定员工、删除指定部门、增加指定员工的工资与奖金）：
1 --创建序列从100开始,依次增加1 2 CREATE SEQUENCE empseq 3 START WITH 100 4 INCREMENT BY 1 5 ORDER NOCYCLE; 6 --创建序列从100开始,依次增加10 7 CREATE SEQUENCE deptseq 8 START WITH 100 9 INCREMENT BY 10 10 ORDER NOCYCLE; 11 -- ******************************************* 12 -- 创建包说明 13 -- 包 名：MANAGE_EMP_PKG 14 -- 功能描述：对员工进行管理(新增员工,新增部门 15 -- ,删除员工,删除部门,增加工资与奖金等) 16 -- 创建人员：胡勇 17 -- 创建日期：2010-05-19 18 -- Q Q: 80368704 19 -- E-mail : 80368704@yahoo.com.cn 20 -- WebSite: http://www.cnblogs.com/huyong 21 -- ****************************************** 22 CREATE OR REPLACE PACKAGE MANAGE_EMP_PKG 23 AS 24 --增加一名员工 25 FUNCTION hire_emp 26 (ename VARCHAR2, job VARCHAR2 27 , mgr NUMBER, sal NUMBER 28 , comm NUMBER, deptno NUMBER) 29 RETURN NUMBER; 30 --新增一个部门 31 FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2) 32 RETURN NUMBER; 33 --删除指定员工 34 PROCEDURE remove_emp(empno NUMBER); 35 --删除指定部门 36 PROCEDURE remove_dept(deptno NUMBER); 37 --增加指定员工的工资 38 PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER); 39 --增加指定员工的奖金 40 PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER); 41 END MANAGE_EMP_PKG;--创建包说明结束 42 -- ******************************************* 43 -- 创建包体 44 -- 包 名：MANAGE_EMP_PKG 45 -- 功能描述：对员工进行管理(新增员工,新增部门 46 -- ,删除员工,删除部门,增加工资与奖金等) 47 -- 创建人员：胡勇 48 -- 创建日期：2010-05-19 49 -- Q Q: 80368704 50 -- E-mail : 80368704@yahoo.com.cn 51 -- WebSite: http://www.cnblogs.com/huyong 52 -- ****************************************** 53 CREATE OR REPLACE PACKAGE BODY MANAGE_EMP_PKG 54 AS 55 total_emps NUMBER; --员工数 56 total_depts NUMBER; --部门数 57 no_sal EXCEPTION; 58 no_comm EXCEPTION; 59 --增加一名员工 60 FUNCTION hire_emp(ename VARCHAR2, job VARCHAR2, mgr NUMBER, 61 sal NUMBER, comm NUMBER, deptno NUMBER) 62 RETURN NUMBER --返回新增加的员工编号 63 IS 64 new_empno NUMBER(4); 65 BEGIN 66 SELECT empseq.NEXTVAL INTO new_empno FROM dual; 67 SELECT COUNT(*) INTO total_emps FROM emp;--当前记录总数 68 INSERT INTO emp 69 VALUES (new_empno, ename, job, mgr, sysdate, sal, comm, deptno); 70 total_emps:=total_emps+1; 71 RETURN(new_empno); 72 EXCEPTION 73 WHEN OTHERS THEN 74 DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 75 END hire_emp; 76 --新增一个部门 77 FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2) 78 RETURN NUMBER 79 IS 80 new_deptno NUMBER(4); --部门编号 81 BEGIN 82 --得到一个新的自增的员工编号 83 SELECT deptseq.NEXTVAL INTO new_deptno FROM dual; 84 SELECT COUNT(*) INTO total_depts FROM dept;--当前部门总数 85 INSERT INTO dept VALUES (new_deptno, dname, loc); 86 total_depts:=total_depts; 87 RETURN(new_deptno); 88 EXCEPTION 89 WHEN OTHERS THEN 90 DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 91 END add_dept; 92 --删除指定员工 93 PROCEDURE remove_emp(empno NUMBER) 94 IS 95 no_result EXCEPTION; --自定义异常 96 BEGIN 97 DELETE FROM emp WHERE emp.empno=remove_emp.empno; 98 IF SQL%NOTFOUND THEN 99 RAISE no_result; 100 END IF; 101 total_emps:=total_emps - 1; --总的员工数减1 102 EXCEPTION 103 WHEN no_result THEN 104 DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 105 WHEN OTHERS THEN 106 DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 107 END remove_emp; 108 --删除指定部门 109 PROCEDURE remove_dept(deptno NUMBER) 110 IS 111 no_result EXCEPTION; --自定义异常 112 exception_deptno_remaining EXCEPTION; --自定义异常 113 /*-2292 是违反一致性约束的错误代码*/ 114 PRAGMA EXCEPTION_INIT(exception_deptno_remaining, -2292); 115 BEGIN 116 DELETE FROM dept WHERE dept.deptno=remove_dept.deptno; 117 IF SQL%NOTFOUND THEN 118 RAISE no_result; 119 END IF; 120 total_depts:=total_depts-1; --总的部门数减1 121 EXCEPTION 122 WHEN no_result THEN 123 DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 124 WHEN exception_deptno_remaining THEN 125 DBMS_OUTPUT.PUT_LINE('温馨提示:违反数据完整性约束!'); 126 WHEN OTHERS THEN 127 DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 128 END remove_dept; 129 --给指定员工增加指定数量的工资 130 PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER) 131 IS 132 curr_sal NUMBER(7, 2); --当前工资 133 BEGIN 134 --得到当前工资 135 SELECT sal INTO curr_sal FROM emp WHERE emp.empno=increase_sal.empno; 136 IF curr_sal IS NULL THEN 137 RAISE no_sal; 138 ELSE 139 UPDATE emp SET sal = sal + increase_sal.sal_incr --当前工资加新增的工资 140 WHERE emp.empno = increase_sal.empno; 141 END IF; 142 EXCEPTION 143 WHEN NO_DATA_FOUND THEN 144 DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 145 WHEN no_sal THEN 146 DBMS_OUTPUT.PUT_LINE('温馨提示:此员工的工资不存在!'); 147 WHEN OTHERS THEN 148 DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 149 END increase_sal; 150 --给指定员工增加指定数量的奖金 151 PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER) 152 IS 153 curr_comm NUMBER(7,2); 154 BEGIN 155 --得到指定员工的当前资金 156 SELECT comm INTO curr_comm 157 FROM emp WHERE emp.empno = increase_comm.empno; 158 IF curr_comm IS NULL THEN 159 RAISE no_comm; 160 ELSE 161 UPDATE emp SET comm = comm + increase_comm.comm_incr 162 WHERE emp.empno=increase_comm.empno; 163 END IF; 164 EXCEPTION 165 WHEN NO_DATA_FOUND THEN 166 DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 167 WHEN no_comm THEN 168 DBMS_OUTPUT.PUT_LINE('温馨提示:此员工的奖金不存在!'); 169 WHEN OTHERS THEN 170 DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 171 END increase_comm; 172 END MANAGE_EMP_PKG;--创建包体结束
--调用
SQL> variable empno number
SQL>execute :empno:= manage_emp_pkg.hire_emp('HUYONG',PM,1455,5500,14,10)
PL/SQL procedure successfully completed
empno
---------
105
例4：利用游标变量创建包 CURROR_VARIBAL_PKG。由于游标变量指是一个指针，其状态是不确定的，因此它不能随同包存储在数据库中，既不能在PL/SQL包中声明游标变量。但在包中可以创建游标变量参照类型，并可向包中的子程序传递游标变量参数。
1 -- ******************************************* 2 -- 创建包体 3 -- 包 名：CURROR_VARIBAL_PKG 4 -- 功能描述：在包中引用游标变量 5 -- 创建人员：胡勇 6 -- 创建日期：2010-05-19 7 -- Q Q: 80368704 8 -- E-mail : 80368704@yahoo.com.cn 9 -- WebSite: http://www.cnblogs.com/huyong 10 -- ****************************************** 11 CREATE OR REPLACE PACKAGE CURROR_VARIBAL_PKG AS 12 TYPE DeptCurType IS REF CURSOR 13 RETURN dept%ROWTYPE; --强类型定义 14 TYPE CurType IS REF CURSOR;-- 弱类型定义 15 PROCEDURE OpenDeptVar( 16 Cv IN OUT DeptCurType, 17 Choice INTEGER DEFAULT 0, 18 Dept_no NUMBER DEFAULT 50, 19 Dept_name VARCHAR DEFAULT '%'); 20 END; 21 -- ******************************************* 22 -- 创建包体 23 -- 包 名：CURROR_VARIBAL_PKG 24 -- 功能描述：在包中引用游标变量 25 -- 创建人员：胡勇 26 -- 创建日期：2010-05-19 27 -- Q Q: 80368704 28 -- E-mail : 80368704@yahoo.com.cn 29 -- WebSite: http://www.cnblogs.com/huyong 30 -- ****************************************** 31 CREATE OR REPLACE PACKAGE BODY CURROR_VARIBAL_PKG 32 AS 33 PROCEDURE OpenDeptvar( 34 Cv IN OUT DeptCurType, 35 Choice INTEGER DEFAULT 0, 36 Dept_no NUMBER DEFAULT 50, 37 Dept_name VARCHAR DEFAULT ‘%’) 38 IS 39 BEGIN 40 IF choice =1 THEN 41 OPEN cv FOR SELECT * FROM dept WHERE deptno <= dept_no; 42 ELSIF choice = 2 THEN 43 OPEN cv FOR SELECT * FROM dept WHERE dname LIKE dept_name; 44 ELSE 45 OPEN cv FOR SELECT * FROM dept; 46 END IF; 47 END OpenDeptvar; 48 END CURROR_VARIBAL_PKG; 49 --定义一个过程 50 CREATE OR REPLACE PROCEDURE UP_OpenCurType( 51 Cv IN OUT CURROR_VARIBAL_PKG.CurType, 52 FirstCapInTableName CHAR) 53 AS 54 BEGIN 55 --CURROR_VARIBAL_PKG.CurType采用弱类型定义 56 --所以可以使用它定义的游标变量打开不同类型的查询语句 57 IF FirstCapInTableName = 'D' THEN 58 OPEN cv FOR SELECT * FROM dept; 59 ELSE 60 OPEN cv FOR SELECT * FROM emp; 61 END IF; 62 END UP_OpenCurType; 63 --定义一个应用 64 DECLARE 65 DeptRec Dept%ROWTYPE; 66 EmpRec Emp%ROWTYPE; 67 Cv1 CURROR_VARIBAL_PKG.deptcurtype; 68 Cv2 CURROR_VARIBAL_PKG.curtype; 69 BEGIN 70 DBMS_OUTPUT.PUT_LINE('游标变量强类型定义应用'); 71 CURROR_VARIBAL_PKG.OpenDeptVar(cv1, 1, 30); 72 FETCH cv1 INTO DeptRec; 73 WHILE cv1%FOUND LOOP 74 DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname); 75 FETCH cv1 INTO DeptRec; 76 END LOOP; 77 CLOSE cv1; 78 DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用'); 79 CURROR_VARIBAL_PKG.OpenDeptvar(cv2, 2, dept_name => 'A%'); 80 FETCH cv2 INTO DeptRec; 81 WHILE cv2%FOUND LOOP 82 DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname); 83 FETCH cv2 INTO DeptRec; 84 END LOOP; 85 DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用—dept表'); 86 UP_OpenCurType(cv2, 'D'); 87 FETCH cv2 INTO DeptRec; 88 WHILE cv2%FOUND LOOP 89 DBMS_OUTPUT.PUT_LINE(deptrec.deptno||':'||deptrec.dname); 90 FETCH cv2 INTO deptrec; 91 END LOOP; 92 DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用—emp表'); 93 UP_OpenCurType(cv2, 'E'); 94 FETCH cv2 INTO EmpRec; 95 WHILE cv2%FOUND LOOP 96 DBMS_OUTPUT.PUT_LINE(emprec.empno||':'||emprec.ename); 97 FETCH cv2 INTO emprec; 98 END LOOP; 99 CLOSE cv2; 100 END;
----------运行结果-------------------
游标变量强类型定义应用
10:ACCOUNTING
20:RESEARCH
30:SALES
游标变量弱类型定义应用
10:ACCOUNTING
游标变量弱类型定义应用—dept表
10:ACCOUNTING
20:RESEARCH
30:SALES
40:OPERATIONS
50:50abc
60:Developer
游标变量弱类型定义应用—emp表
7369:SMITH
7499:ALLEN
7521:WARD
7566:JONES
7654:MARTIN
7698:BLAKE
7782:CLARK
7788:SCOTT
7839:KING
7844:TURNER
7876:ADAMS
7900:JAMES
7902:FORD
7934:MILLER
PL/SQL procedure successfully completed
7.5 子程序重载
PL/SQL 允许对包内子程序和本地子程序进行重载。所谓重载时指两个或多个子程序有相同的名称，但拥有不同的参数变量、参数顺序或参数数据类型。
例5：
1 -- ******************************************* 2 -- 创建包说明 3 -- 包 名：DEMO_PKG1 4 -- 功能描述：创建包对子程序重载进行测试 5 -- 创建人员：胡勇 6 -- 创建日期：2010-05-22 7 -- Q Q: 80368704 8 -- E-mail : 80368704@yahoo.com.cn 9 -- WebSite: http://www.cnblogs.com/huyong 10 -- ****************************************** 11 CREATE OR REPLACE PACKAGE DEMO_PKG1 12 IS 13 DeptRec dept%ROWTYPE; 14 V_sqlcode NUMBER; 15 V_sqlerr VARCHAR2(2048); 16 --两个子程序名字相同,但参数类型不同 17 FUNCTION query_dept(dept_no IN NUMBER) 18 RETURN INTEGER; 19 FUNCTION query_dept(dept_no IN VARCHAR2) 20 RETURN INTEGER; 21 END DEMO_PKG1; 22 -- ******************************************* 23 -- 创建包体 24 -- 包 名：DEMO_PKG1 25 -- 功能描述：创建包对子程序重载进行测试 26 -- 创建人员：胡勇 27 -- 创建日期：2010-05-22 28 -- Q Q: 80368704 29 -- E-mail : 80368704@yahoo.com.cn 30 -- WebSite: http://www.cnblogs.com/huyong 31 -- ****************************************** 32 CREATE OR REPLACE PACKAGE BODY DEMO_PKG1 33 IS 34 FUNCTION check_dept(dept_no NUMBER) 35 RETURN INTEGER 36 IS 37 deptCnt INTEGER; --指定部门号的部门数量 38 BEGIN 39 SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno = dept_no; 40 IF deptCnt > 0 THEN 41 RETURN 1; 42 ELSE 43 RETURN 0; 44 END IF; 45 END check_dept; 46 FUNCTION check_dept(dept_no VARCHAR2) 47 RETURN INTEGER 48 IS 49 deptCnt INTEGER; 50 BEGIN 51 SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno=dept_no; 52 IF deptCnt > 0 THEN 53 RETURN 1; 54 ELSE 55 RETURN 0; 56 END IF; 57 END check_dept; 58 FUNCTION query_dept(dept_no IN NUMBER) 59 RETURN INTEGER 60 IS 61 BEGIN 62 IF check_dept(dept_no) =1 THEN 63 SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no; 64 RETURN 1; 65 ELSE 66 RETURN 0; 67 END IF; 68 END query_dept; 69 FUNCTION query_dept(dept_no IN VARCHAR2) 70 RETURN INTEGER 71 IS 72 BEGIN 73 IF check_dept(dept_no) =1 THEN 74 SELECT * INTO DeptRec FROM dept WHERE deptno = dept_no; 75 RETURN 1; 76 ELSE 77 RETURN 0; 78 END IF; 79 END query_dept; 80 END DEMO_PKG1;
7.6 加密实用程序
ORACLE 提供了一个实用工具来加密或者包装用户的PL/SQL，它会将用户的PL/SQL改变为只有ORACLE能够解释的代码版本．
WRAP 实用工具位于$ORACLE_HOME/BIN.
格式为：
WRAP INAME=<input_file_name> [ONAME=<output_file_name>]
wrap iname=e:sample.txt
注意：在加密前，请将PL/SQL程序先保存一份，以备后用。
7.7 删除包
可以使用 DROP PACKAGE 命令对不需要的包进行删除，语法如下：
1 DROP PACKAGE [BODY] [user.]package_name; 2 DROP PROCEDURE OpenCurType; --删除存储过程 3 --删除我们实例中创建的各个包 4 DROP PACKAGE demo_pack; 5 DROP PACKAGE demo_pack1; 6 DROP PACKAGE emp_mgmt; 7 DROP PACKAGE emp_package;
7.8 包的管理
包与过程、函数一样，也是存储在数据库中的，可以随时查看其源码。若有需要，在创建包时可以随时查看更详细的编译错误。不需要的包也可以删除。
同样，为了避免调用的失败，在更新表的结构后，一定要记得重新编译依赖于它的程序包。在更新了包说明或包体后，也应该重新编译包说明与包体。
语法如下：
ALTER PACKAGE package_name COMPILE [PACKAGE|BODY|SPECIFICATION];
也可以通过以下数据字典视图查看包的相关。
DBA_SOURCE, USER_SOURCE, USER_ERRORS, DBA-OBJECTS
如，我们可以用：select text from user_source where name = 'DEMO_PKG1';来查看我们创建的包的源码。
8. 把触发器说透
触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。
8.1 触发器类型
触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。
8.1.1 DML触发器
ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。
8.1.2 替代触发器
由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE 8专门为进行视图操作的一种处理方法。
8.1.3 系统触发器
ORACLE 8i 提供了第三种类型的触发器叫系统触发器。它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。
触发器组成:
触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。
触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。
触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。
触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。
触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。
触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。
语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；
行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。
编写触发器时，需要注意以下几点：
触发器不接受参数。
一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。
在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。
触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。
在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。
触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。
在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。
在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。
不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。
8.2 创建触发器
创建触发器的一般语法是:
1 CREATE [OR REPLACE] TRIGGER trigger_name 2 {BEFORE | AFTER } 3 {INSERT | DELETE | UPDATE [OF column [, column …]]} 4 [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] 5 ON [schema.]table_name | [schema.]view_name 6 [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] 7 [FOR EACH ROW ] 8 [WHEN condition] 9 PL/SQL_BLOCK | CALL procedure_name;
其中：
BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。
FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。
REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。
WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。
当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。
每张表最多可建立12 种类型的触发器，它们是:
BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW
BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW
BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW
8.2.1 触发器触发次序
1. 执行 BEFORE语句级触发器;
2. 对与受语句影响的每一行：
执行 BEFORE行级触发器
执行 DML语句
执行 AFTER行级触发器
3. 执行 AFTER语句级触发器
8.2.2 创建DML触发器
触发器名与过程名和包的名字不一样，它是单独的名字空间，因而触发器名可以和表或过程有相同的名字，但在一个模式中触发器名不能相同。
DML触发器的限制
CREATE TRIGGER语句文本的字符长度不能超过32KB；
触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT 语句。
触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句；
由触发器所调用的过程或函数也不能使用数据库事务控制语句；
触发器中不能使用LONG, LONG RAW 类型；
触发器内可以参照LOB 类型列的列值，但不能通过 :NEW 修改LOB列中的数据；
DML触发器基本要点
触发时机：指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。
触发事件：引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。
条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的如下条件谓词。
1）INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。
2）UPDATING [（column_1,column_2,…,column_x）]：当触发事件是UPDATE 时，如果修改了column_x列，则取值为TRUE，否则为FALSE。其中column_x是可选的。
3）DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。
解发对象：指定触发器是创建在哪个表、视图上。
触发类型：是语句级还是行级触发器。
触发条件：由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定UPDATING后面的列的列表。
问题：当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、 后列的值.
实现: :NEW 修饰符访问操作完成后列的值
:OLD 修饰符访问操作完成前列的值
例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。
1 CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; 2 CREATE OR REPLACE TRIGGER tr_del_emp 3 BEFORE DELETE --指定触发时机为删除操作前触发 4 ON scott.emp 5 FOR EACH ROW --说明创建的是行级触发器 6 BEGIN 7 --将修改前数据插入到日志记录表 del_emp ,以供监督使用。 8 INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate ) 9 VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate ); 10 END; 11 DELETE emp WHERE empno=7788; 12 DROP TABLE emp_his; 13 DROP TRIGGER del_emp;
例2：限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表。
1 CREATE OR REPLACE TRIGGER tr_dept_time 2 BEFORE INSERT OR DELETE OR UPDATE 3 ON departments 4 BEGIN 5 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN 6 RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表'); 7 END IF; 8 END;
例3：限定只对部门号为80的记录进行行触发器操作。
1 CREATE OR REPLACE TRIGGER tr_emp_sal_comm 2 BEFORE UPDATE OF salary, commission_pct 3 OR DELETE 4 ON HR.employees 5 FOR EACH ROW 6 WHEN (old.department_id = 80) 7 BEGIN 8 CASE 9 WHEN UPDATING ('salary') THEN 10 IF :NEW.salary < :old.salary THEN 11 RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降'); 12 END IF; 13 WHEN UPDATING ('commission_pct') THEN 14 IF :NEW.commission_pct < :old.commission_pct THEN 15 RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降'); 16 END IF; 17 WHEN DELETING THEN 18 RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录'); 19 END CASE; 20 END; 21 /* 22 实例： 23 UPDATE employees SET salary = 8000 WHERE employee_id = 177; 24 DELETE FROM employees WHERE employee_id in (177,170); 25 */
例4：利用行触发器实现级联更新。在修改了主表regions中的region_id之后（AFTER），级联的、自动的更新子表countries表中原来在该地区的国家的region_id。
1 CREATE OR REPLACE TRIGGER tr_reg_cou 2 AFTER update OF region_id 3 ON regions 4 FOR EACH ROW 5 BEGIN 6 DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id 7 ||'、新的region_id值是'||:new.region_id); 8 UPDATE countries SET region_id = :new.region_id 9 WHERE region_id = :old.region_id; 10 END;
例5：在触发器中调用过程。
1 CREATE OR REPLACE PROCEDURE add_job_history 2 ( p_emp_id job_history.employee_id%type 3 , p_start_date job_history.start_date%type 4 , p_end_date job_history.end_date%type 5 , p_job_id job_history.job_id%type 6 , p_department_id job_history.department_id%type 7 ) 8 IS 9 BEGIN 10 INSERT INTO job_history (employee_id, start_date, end_date, 11 job_id, department_id) 12 VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id); 13 END add_job_history; 14 --创建触发器调用存储过程... 15 CREATE OR REPLACE TRIGGER update_job_history 16 AFTER UPDATE OF job_id, department_id ON employees 17 FOR EACH ROW 18 BEGIN 19 add_job_history(:old.employee_id, :old.hire_date, sysdate, 20 :old.job_id, :old.department_id); 21 END;
8.2.3 创建替代(INSTEAD OF)触发器
创建触发器的一般语法是:
1 CREATE [OR REPLACE] TRIGGER trigger_name 2 INSTEAD OF 3 {INSERT | DELETE | UPDATE [OF column [, column …]]} 4 [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] 5 ON [schema.] view_name --只能定义在视图上 6 [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] 7 [FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定 8 [WHEN condition] 9 PL/SQL_block | CALL procedure_name;
其中：
INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF 触发器。
FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则为行触发器。
REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。
WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。
INSTEAD_OF 用于对视图的DML触发，由于视图有可能是由多个表进行联结(join)而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新，例如下面情况：
例1：
1 CREATE OR REPLACE VIEW emp_view AS 2 SELECT deptno, count(*) total_employeer, sum(sal) total_salary 3 FROM emp GROUP BY deptno;
在此视图中直接删除是非法：
SQL>DELETE FROM emp_view WHERE deptno=10;
DELETE FROM emp_view WHERE deptno=10
ERROR 位于第 1 行:
ORA-01732: 此视图的数据操纵操作非法
但是我们可以创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理，即删除EMP表中所有基准行：
1 CREATE OR REPLACE TRIGGER emp_view_delete 2 INSTEAD OF DELETE ON emp_view FOR EACH ROW 3 BEGIN 4 DELETE FROM emp WHERE deptno= :old.deptno; 5 END emp_view_delete; 6 DELETE FROM emp_view WHERE deptno=10; 7 DROP TRIGGER emp_view_delete; 8 DROP VIEW emp_view;
例2：创建复杂视图，针对INSERT操作创建INSTEAD OF触发器，向复杂视图插入数据。
创建视图：
1 CREATE OR REPLACE FORCE VIEW "HR"."V_REG_COU" ("R_ID", "R_NAME", "C_ID", "C_NAME") 2 AS 3 SELECT r.region_id, 4 r.region_name, 5 c.country_id, 6 c.country_name 7 FROM regions r, 8 countries c 9 WHERE r.region_id = c.region_id;
创建触发器：
1 CREATE OR REPLACE TRIGGER "HR"."TR_I_O_REG_COU" INSTEAD OF 2 INSERT ON v_reg_cou FOR EACH ROW DECLARE v_count NUMBER; 3 BEGIN 4 SELECT COUNT(*) INTO v_count FROM regions WHERE region_id = :new.r_id; 5 IF v_count = 0 THEN 6 INSERT INTO regions 7 (region_id, region_name 8 ) VALUES 9 (:new.r_id, :new.r_name 10 ); 11 END IF; 12 SELECT COUNT(*) INTO v_count FROM countries WHERE country_id = :new.c_id; 13 IF v_count = 0 THEN 14 INSERT 15 INTO countries 16 ( 17 country_id, 18 country_name, 19 region_id 20 ) 21 VALUES 22 ( 23 :new.c_id, 24 :new.c_name, 25 :new.r_id 26 ); 27 END IF; 28 END;
创建INSTEAD OF触发器需要注意以下几点：
只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。
不能指定BEFORE 或 AFTER选项。
FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。
没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。
8.2.3 创建系统事件触发器
ORACLE10G提供的系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE 、ALTER及DROP 等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。创建系统触发器的语法如下：
创建触发器的一般语法是:
1 CREATE OR REPLACE TRIGGER [sachema.]trigger_name 2 {BEFORE|AFTER} 3 {ddl_event_list | database_event_list} 4 ON { DATABASE | [schema.]SCHEMA } 5 [WHEN condition] 6 PL/SQL_block | CALL procedure_name;
其中: ddl_event_list：一个或多个DDL 事件，事件间用 OR 分开；
database_event_list：一个或多个数据库事件，事件间用 OR 分开；
系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTER DATABASE TRIGGER权限。
下面给出系统触发器的种类和事件出现的时机（前或后）：
8.2.4 系统触发器事件属性
除DML语句的列属性外，其余事件属性值可通过调用ORACLE定义的事件属性函数来读取。
例1：创建触发器，存放有关事件信息。
1 DESC ora_sysevent 2 DESC ora_login_user 3 --创建用于记录事件用的表 4 CREATE TABLE ddl_event 5 (crt_date timestamp PRIMARY KEY, 6 event_name VARCHAR2(20), 7 user_name VARCHAR2(10), 8 obj_type VARCHAR2(20), 9 obj_name VARCHAR2(20)); 10 --创建触犯发器 11 CREATE OR REPLACE TRIGGER tr_ddl 12 AFTER DDL ON SCHEMA 13 BEGIN 14 INSERT INTO ddl_event VALUES 15 (systimestamp,ora_sysevent, ora_login_user, 16 ora_dict_obj_type, ora_dict_obj_name); 17 END tr_ddl;
例2：创建登录、退出触发器。
1 CREATE TABLE log_event 2 (user_name VARCHAR2(10), 3 address VARCHAR2(20), 4 logon_date timestamp, 5 logoff_date timestamp); 6 --创建登录触发器 7 CREATE OR REPLACE TRIGGER tr_logon 8 AFTER LOGON ON DATABASE 9 BEGIN 10 INSERT INTO log_event (user_name, address, logon_date) 11 VALUES (ora_login_user, ora_client_ip_address, systimestamp); 12 END tr_logon; 13 --创建退出触发器 14 CREATE OR REPLACE TRIGGER tr_logoff 15 BEFORE LOGOFF ON DATABASE 16 BEGIN 17 INSERT INTO log_event (user_name, address, logoff_date) 18 VALUES (ora_login_user, ora_client_ip_address, systimestamp); 19 END tr_logoff;
8.2.5 使用触发器谓词
ORACLE 提供三个参数INSERTING, UPDATING, DELETING 用于判断触发了哪些操作。
8.2.6 重新编译触发器
如果在触发器内调用其它函数或过程，当这些函数或过程被删除或修改后，触发器的状态将被标识为无效。当DML语句激活一个无效触发器时，ORACLE将重新编译触发器代码，如果编译时发现错误，这将导致DML语句执行失败。
在PL/SQL程序中可以调用ALTER TRIGGER语句重新编译已经创建的触发器，格式为：
ALTER TRIGGER [schema.] trigger_name COMPILE [ DEBUG]
其中：DEBUG 选项要器编译器生成PL/SQL 程序条使其所使用的调试代码。
8.3 删除和使能触发器
删除触发器：
DROP TRIGGER trigger_name;
当删除其他用户模式中的触发器名称，需要具有DROP ANY TRIGGER系统权限，当删除建立在数据库上的触发器时，用户需要具有ADMINISTER DATABASE TRIGGER系统权限。
此外，当删除表或视图时，建立在这些对象上的触发器也随之删除。
禁用或启用触发器
数据库TRIGGER 的状态：
有效状态(ENABLE)：当触发事件发生时，处于有效状态的数据库触发器TRIGGER 将被触发。
无效状态(DISABLE)：当触发事件发生时，处于无效状态的数据库触发器TRIGGER 将不会被触发，此时就跟没有这个数据库触发器(TRIGGER) 一样。
数据库TRIGGER的这两种状态可以互相转换。格式为：
ALTER TIGGER trigger_name [DISABLE | ENABLE ];
--例：ALTER TRIGGER emp_view_delete DISABLE;
ALTER TRIGGER语句一次只能改变一个触发器的状态，而ALTER TABLE语句则一次能够改变与指定表相关的所有触发器的使用状态。格式为：
ALTER TABLE [schema.]table_name {ENABLE|DISABLE} ALL TRIGGERS;
--例：使表EMP 上的所有TRIGGER 失效：
ALTER TABLE emp DISABLE ALL TRIGGERS;
8.4 触发器和数据字典
相关数据字典：USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS、SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT、TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES, STATUS, ACTION_TYPE FROM user_triggers;
8.5 数据库触发器的应用举例
例1：创建一个DML语句级触发器，当对emp表执行INSERT, UPDATE, DELETE 操作时，它自动更新dept_summary 表中的数据。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。
1 CREATE TABLE dept_summary( 2 Deptno NUMBER(2), 3 Sal_sum NUMBER(9, 2), 4 Emp_count NUMBER); 5 INSERT INTO dept_summary(deptno, sal_sum, emp_count) 6 SELECT deptno, SUM(sal), COUNT(*) 7 FROM emp 8 GROUP BY deptno; 9 --创建一个PL/SQL过程disp_dept_summary 10 --在触发器中调用该过程显示dept_summary标中的数据。 11 CREATE OR REPLACE PROCEDURE disp_dept_summary 12 IS 13 Rec dept_summary%ROWTYPE; 14 CURSOR c1 IS SELECT * FROM dept_summary; 15 BEGIN 16 OPEN c1; 17 FETCH c1 INTO REC; 18 DBMS_OUTPUT.PUT_LINE('deptno sal_sum emp_count'); 19 DBMS_OUTPUT.PUT_LINE('-------------------------------------'); 20 WHILE c1%FOUND LOOP 21 DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, 6)|| 22 To_char(rec.sal_sum, '$999,999.99')|| 23 LPAD(rec.emp_count, 13)); 24 FETCH c1 INTO rec; 25 END LOOP; 26 CLOSE c1; 27 END; 28 BEGIN 29 DBMS_OUTPUT.PUT_LINE('插入前'); 30 Disp_dept_summary(); 31 DBMS_UTILITY.EXEC_DDL_STATEMENT(' 32 CREATE OR REPLACE TRIGGER trig1 33 AFTER INSERT OR DELETE OR UPDATE OF sal ON emp 34 BEGIN 35 DBMS_OUTPUT.PUT_LINE(''正在执行trig1 触发器…''); 36 DELETE FROM dept_summary; 37 INSERT INTO dept_summary(deptno, sal_sum, emp_count) 38 SELECT deptno, SUM(sal), COUNT(*) 39 FROM emp GROUP BY deptno; 40 END; 41 '); 42 INSERT INTO dept(deptno, dname, loc) 43 VALUES(90, ‘demo_dept’, ‘none_loc’); 44 INSERT INTO emp(ename, deptno, empno, sal) 45 VALUES(USER, 90, 9999, 3000); 46 DBMS_OUTPUT.PUT_LINE('插入后'); 47 Disp_dept_summary(); 48 UPDATE emp SET sal=1000 WHERE empno=9999; 49 DBMS_OUTPUT.PUT_LINE('修改后'); 50 Disp_dept_summary(); 51 DELETE FROM emp WHERE empno=9999; 52 DELETE FROM dept WHERE deptno=90; 53 DBMS_OUTPUT.PUT_LINE('删除后'); 54 Disp_dept_summary(); 55 DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig1’); 56 EXCEPTION 57 WHEN OTHERS THEN 58 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 59 END;
例2：创建DML语句行级触发器。当对emp表执行INSERT, UPDATE, DELETE 操作时，它自动更新dept_summary 表中的数据。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。
1 BEGIN 2 DBMS_OUTPUT.PUT_LINE('插入前'); 3 Disp_dept_summary(); 4 DBMS_UTILITY.EXEC_DDL_STATEMENT( 5 'CREATE OR REPLACE TRIGGER trig2_update 6 AFTER UPDATE OF sal ON emp 7 REFERENCING OLD AS old_emp NEW AS new_emp 8 FOR EACH ROW 9 WHEN (old_emp.sal != new_emp.sal) 10 BEGIN 11 DBMS_OUTPUT.PUT_LINE(''正在执行trig2_update 触发器…''); 12 DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal); 13 DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal); 14 UPDATE dept_summary 15 SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal 16 WHERE deptno = :new_emp.deptno; 17 END;' 18 ); 19 DBMS_UTILITY.EXEC_DDL_STATEMENT( 20 'CREATE OR REPLACE TRIGGER trig2_insert 21 AFTER INSERT ON emp 22 REFERENCING NEW AS new_emp 23 FOR EACH ROW 24 DECLARE 25 I NUMBER; 26 BEGIN 27 DBMS_OUTPUT.PUT_LINE(''正在执行trig2_insert 触发器…''); 28 SELECT COUNT(*) INTO I 29 FROM dept_summary WHERE deptno = :new_emp.deptno; 30 IF I > 0 THEN 31 UPDATE dept_summary 32 SET sal_sum=sal_sum+:new_emp.sal, 33 Emp_count=emp_count+1 34 WHERE deptno = :new_emp.deptno; 35 ELSE 36 INSERT INTO dept_summary 37 VALUES (:new_emp.deptno, :new_emp.sal, 1); 38 END IF; 39 END;' 40 ); 41 DBMS_UTILITY.EXEC_DDL_STATEMENT( 42 'CREATE OR REPLACE TRIGGER trig2_delete 43 AFTER DELETE ON emp 44 REFERENCING OLD AS old_emp 45 FOR EACH ROW 46 DECLARE 47 I NUMBER; 48 BEGIN 49 DBMS_OUTPUT.PUT_LINE(''正在执行trig2_delete 触发器…''); 50 SELECT emp_count INTO I 51 FROM dept_summary WHERE deptno = :old_emp.deptno; 52 IF I >1 THEN 53 UPDATE dept_summary 54 SET sal_sum=sal_sum - :old_emp.sal, 55 Emp_count=emp_count - 1 56 WHERE deptno = :old_emp.deptno; 57 ELSE 58 DELETE FROM dept_summary WHERE deptno = :old_emp.deptno; 59 END IF; 60 END;' 61 ); 62 INSERT INTO dept(deptno, dname, loc) 63 VALUES(90, 'demo_dept', 'none_loc'); 64 INSERT INTO emp(ename, deptno, empno, sal) 65 VALUES(USER, 90, 9999, 3000); 66 INSERT INTO emp(ename, deptno, empno, sal) 67 VALUES(USER, 90, 9998, 2000); 68 DBMS_OUTPUT.PUT_LINE('插入后'); 69 Disp_dept_summary(); 70 UPDATE emp SET sal = sal*1.1 WHERE deptno=90; 71 DBMS_OUTPUT.PUT_LINE('修改后'); 72 Disp_dept_summary(); 73 DELETE FROM emp WHERE deptno=90; 74 DELETE FROM dept WHERE deptno=90; 75 DBMS_OUTPUT.PUT_LINE('删除后'); 76 Disp_dept_summary(); 77 DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_update'); 78 DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_insert'); 79 DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_delete'); 80 EXCEPTION 81 WHEN OTHERS THEN 82 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 83 END;
例3：利用ORACLE提供的条件谓词INSERTING、UPDATING和DELETING创建与例2具有相同功能的触发器。
1 BEGIN 2 DBMS_OUTPUT.PUT_LINE('插入前'); 3 Disp_dept_summary(); 4 DBMS_UTILITY.EXEC_DDL_STATEMENT( 5 'CREATE OR REPLACE TRIGGER trig2 6 AFTER INSERT OR DELETE OR UPDATE OF sal 7 ON emp 8 REFERENCING OLD AS old_emp NEW AS new_emp 9 FOR EACH ROW 10 DECLARE 11 I NUMBER; 12 BEGIN 13 IF UPDATING AND :old_emp.sal != :new_emp.sal THEN 14 DBMS_OUTPUT.PUT_LINE(''正在执行trig2 触发器…''); 15 DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal); 16 DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal); 17 UPDATE dept_summary 18 SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal 19 WHERE deptno = :new_emp.deptno; 20 ELSIF INSERTING THEN 21 DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…''); 22 SELECT COUNT(*) INTO I 23 FROM dept_summary 24 WHERE deptno = :new_emp.deptno; 25 IF I > 0 THEN 26 UPDATE dept_summary 27 SET sal_sum=sal_sum+:new_emp.sal, 28 Emp_count=emp_count+1 29 WHERE deptno = :new_emp.deptno; 30 ELSE 31 INSERT INTO dept_summary 32 VALUES (:new_emp.deptno, :new_emp.sal, 1); 33 END IF; 34 ELSE 35 DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…''); 36 SELECT emp_count INTO I 37 FROM dept_summary WHERE deptno = :old_emp.deptno; 38 IF I > 1 THEN 39 UPDATE dept_summary 40 SET sal_sum=sal_sum - :old_emp.sal, 41 Emp_count=emp_count - 1 42 WHERE deptno = :old_emp.deptno; 43 ELSE 44 DELETE FROM dept_summary 45 WHERE deptno = :old_emp.deptno; 46 END IF; 47 END IF; 48 END;' 49 ); 50 INSERT INTO dept(deptno, dname, loc) 51 VALUES(90, 'demo_dept', 'none_loc'); 52 INSERT INTO emp(ename, deptno, empno, sal) 53 VALUES(USER, 90, 9999, 3000); 54 INSERT INTO emp(ename, deptno, empno, sal) 55 VALUES(USER, 90, 9998, 2000); 56 DBMS_OUTPUT.PUT_LINE('插入后'); 57 Disp_dept_summary(); 58 UPDATE emp SET sal = sal*1.1 WHERE deptno=90; 59 DBMS_OUTPUT.PUT_LINE('修改后'); 60 Disp_dept_summary(); 61 DELETE FROM emp WHERE deptno=90; 62 DELETE FROM dept WHERE deptno=90; 63 DBMS_OUTPUT.PUT_LINE('删除后'); 64 Disp_dept_summary(); 65 DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2'); 66 EXCEPTION 67 WHEN OTHERS THEN 68 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 69 END;
例4：创建INSTEAD OF 触发器。首先创建一个视图myview, 由于该视图是复合查询所产生的视图，所以不能执行DML语句。根据用户对视图所插入的数据判断需要将数据插入到哪个视图基表中，然后对该基表执行插入操作。]
1 DECLARE 2 No NUMBER; 3 Name VARCHAR2(20); 4 BEGIN 5 DBMS_UTILITY.EXEC_DDL_STATEMENT(' 6 CREATE OR REPLACE VIEW myview AS 7 SELECT empno, ename, ''E'' type FROM emp 8 UNION 9 SELECT dept.deptno, dname, ''D'' FROM dept 10 '); 11 -- 创建INSTEAD OF 触发器trigger3; 12 DBMS_UTILITY.EXEC_DDL_STATEMENT(' 13 CREATE OR REPLACE TRIGGER trig3 14 INSTEAD OF INSERT ON myview 15 REFERENCING NEW n 16 FOR EACH ROW 17 DECLARE 18 Rows INTEGER; 19 BEGIN 20 DBMS_OUTPUT.PUT_LINE(''正在执行trig3触发器…''); 21 IF :n.type = ''D'' THEN 22 SELECT COUNT(*) INTO rows 23 FROM dept WHERE deptno = :n.empno; 24 IF rows = 0 THEN 25 DBMS_OUTPUT.PUT_LINE(''向dept表中插入数据…''); 26 INSERT INTO dept(deptno, dname, loc) 27 VALUES (:n.empno, :n.ename, ''none’’); 28 ELSE 29 DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno|| 30 ''的部门已存在，插入操作失败！''); 31 END IF; 32 ELSE 33 SELECT COUNT(*) INTO rows 34 FROM emp WHERE empno = :n.empno; 35 IF rows = 0 THEN 36 DBMS_OUTPUT.PUT_LINE('’向emp表中插入数据…’’); 37 INSERT INTO emp(empno, ename) 38 VALUES(:n.empno, :n.ename); 39 ELSE 40 DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno|| 41 ''的人员已存在，插入操作失败!''); 42 END IF; 43 END IF; 44 END; 45 '); 46 INSERT INTO myview VALUES (70, 'demo', 'D'); 47 INSERT INTO myview VALUES (9999, USER, 'E'); 48 SELECT deptno, dname INTO no, name FROM dept WHERE deptno=70; 49 DBMS_OUTPUT.PUT_LINE('员工编号：'||TO_CHAR(no)||'姓名：'||name); 50 SELECT empno, ename INTO no, name FROM emp WHERE empno=9999; 51 DBMS_OUTPUT.PUT_LINE('部门编号：'||TO_CHAR(no)||'姓名：'||name); 52 DELETE FROM emp WHERE empno=9999; 53 DELETE FROM dept WHERE deptno=70; 54 DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig3'); 55 END;
例5：利用ORACLE事件属性函数，创建一个系统事件触发器。首先创建一个事件日志表eventlog，由它存储用户在当前数据库中所创建的数据库对象，以及用户的登陆和注销、数据库的启动和关闭等事件，之后创建trig4_ddl、trig4_before和trig4_after触发器，它们调用事件属性函数将各个事件记录到eventlog数据表中。
1 BEGIN 2 -- 创建用于记录事件日志的数据表 3 DBMS_UTILITY.EXEC_DDL_STATEMENT(' 4 CREATE TABLE eventlog( 5 Eventname VARCHAR2(20) NOT NULL, 6 Eventdate date default sysdate, 7 Inst_num NUMBER NULL, 8 Db_name VARCHAR2(50) NULL, 9 Srv_error NUMBER NULL, 10 Username VARCHAR2(30) NULL, 11 Obj_type VARCHAR2(20) NULL, 12 Obj_name VARCHAR2(30) NULL, 13 Obj_owner VARCHAR2(30) NULL 14 ) 15 '); 16 -- 创建DDL触发器trig4_ddl 17 DBMS_UTILITY.EXEC_DDL_STATEMENT(' 18 CREATE OR REPLACE TRIGGER trig4_ddl 19 AFTER CREATE OR ALTER OR DROP 20 ON DATABASE 21 DECLARE 22 Event VARCHAR2(20); 23 Typ VARCHAR2(20); 24 Name VARCHAR2(30); 25 Owner VARCHAR2(30); 26 BEGIN 27 -- 读取DDL事件属性 28 Event := SYSEVENT; 29 Typ := DICTIONARY_OBJ_TYPE; 30 Name := DICTIONARY_OBJ_NAME; 31 Owner := DICTIONARY_OBJ_OWNER; 32 --将事件属性插入到事件日志表中 33 INSERT INTO scott.eventlog(eventname, obj_type, obj_name, obj_owner) 34 VALUES(event, typ, name, owner); 35 END; 36 '); 37 -- 创建LOGON、STARTUP和SERVERERROR 事件触发器 38 DBMS_UTILITY.EXEC_DDL_STATEMENT(' 39 CREATE OR REPLACE TRIGGER trig4_after 40 AFTER LOGON OR STARTUP OR SERVERERROR 41 ON DATABASE 42 DECLARE 43 Event VARCHAR2(20); 44 Instance NUMBER; 45 Err_num NUMBER; 46 Dbname VARCHAR2(50); 47 User VARCHAR2(30); 48 BEGIN 49 Event := SYSEVENT; 50 IF event = ''LOGON'' THEN 51 User := LOGIN_USER; 52 INSERT INTO eventlog(eventname, username) 53 VALUES(event, user); 54 ELSIF event = ''SERVERERROR'' THEN 55 Err_num := SERVER_ERROR(1); 56 INSERT INTO eventlog(eventname, srv_error) 57 VALUES(event, err_num); 58 ELSE 59 Instance := INSTANCE_NUM; 60 Dbname := DATABASE_NAME; 61 INSERT INTO eventlog(eventname, inst_num, db_name) 62 VALUES(event, instance, dbname); 63 END IF; 64 END; 65 '); 66 -- 创建LOGOFF和SHUTDOWN 事件触发器 67 DBMS_UTILITY.EXEC_DDL_STATEMENT(' 68 CREATE OR REPLACE TRIGGER trig4_before 69 BEFORE LOGOFF OR SHUTDOWN 70 ON DATABASE 71 DECLARE 72 Event VARCHAR2(20); 73 Instance NUMBER; 74 Dbname VARCHAR2(50); 75 User VARCHAR2(30); 76 BEGIN 77 Event := SYSEVENT; 78 IF event = ''LOGOFF'' THEN 79 User := LOGIN_USER; 80 INSERT INTO eventlog(eventname, username) 81 VALUES(event, user); 82 ELSE 83 Instance := INSTANCE_NUM; 84 Dbname := DATABASE_NAME; 85 INSERT INTO eventlog(eventname, inst_num, db_name) 86 VALUES(event, instance, dbname); 87 END IF; 88 END; 89 '); 90 END; 91 CREATE TABLE mydata(mydate NUMBER); 92 CONNECT SCOTT/TIGER 93 COL eventname FORMAT A10 94 COL eventdate FORMAT A12 95 COL username FORMAT A10 96 COL obj_type FORMAT A15 97 COL obj_name FORMAT A15 98 COL obj_owner FORMAT A10 99 SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error 100 FROM eventlog; 101 DROP TRIGGER trig4_ddl; 102 DROP TRIGGER trig4_before; 103 DROP TRIGGER trig4_after; 104 DROP TABLE eventlog; 105 DROP TABLE mydata;
8.6 数据库触发器的应用实例
用户可以使用数据库触发器实现各种功能：
复杂的审计功能；
例：将EMP 表的变化情况记录到AUDIT_TABLE和AUDIT_TABLE_VALUES中。
1 CREATE TABLE audit_table( 2 Audit_id NUMBER, 3 User_name VARCHAR2(20), 4 Now_time DATE, 5 Terminal_name VARCHAR2(10), 6 Table_name VARCHAR2(10), 7 Action_name VARCHAR2(10), 8 Emp_id NUMBER(4)); 9 CREATE TABLE audit_table_val( 10 Audit_id NUMBER, 11 Column_name VARCHAR2(10), 12 Old_val NUMBER(7,2), 13 New_val NUMBER(7,2)); 14 CREATE SEQUENCE audit_seq 15 START WITH 1000 16 INCREMENT BY 1 17 NOMAXVALUE 18 NOCYCLE NOCACHE; 19 CREATE OR REPLACE TRIGGER audit_emp 20 AFTER INSERT OR UPDATE OR DELETE ON emp 21 FOR EACH ROW 22 DECLARE 23 Time_now DATE; 24 Terminal CHAR(10); 25 BEGIN 26 Time_now:=sysdate; 27 Terminal:=USERENV('TERMINAL'); 28 IF INSERTING THEN 29 INSERT INTO audit_table 30 VALUES(audit_seq.NEXTVAL, user, time_now, 31 terminal, 'EMP', 'INSERT', :new.empno); 32 ELSIF DELETING THEN 33 INSERT INTO audit_table 34 VALUES(audit_seq.NEXTVAL, user, time_now, 35 terminal, 'EMP', 'DELETE', :old.empno); 36 ELSE 37 INSERT INTO audit_table 38 VALUES(audit_seq.NEXTVAL, user, time_now, 39 terminal, 'EMP', 'UPDATE', :old.empno); 40 IF UPDATING('SAL') THEN 41 INSERT INTO audit_table_val 42 VALUES(audit_seq.CURRVAL, 'SAL', :old.sal, :new.sal); 43 ELSE UPDATING('DEPTNO') 44 INSERT INTO audit_table_val 45 VALUES(audit_seq.CURRVAL, 'DEPTNO', :old.deptno, :new.deptno); 46 END IF; 47 END IF; 48 END;
增强数据的完整性管理；
例：修改DEPT表的DEPTNO列时，同时把EMP表中相应的DEPTNO也作相应的修改；
1 CREATE SEQUENCE update_sequence 2 INCREMENT BY 1 3 START WITH 1000 4 MAXVALUE 5000 CYCLE; 5 ALTER TABLE emp 6 ADD update_id NUMBER; 7 CREATE OR REPLACE PACKAGE integritypackage AS 8 Updateseq NUMBER; 9 END integritypackage; 10 CREATE OR REPLACE PACKAGE BODY integritypackage AS 11 END integritypackage; 12 CREATE OR REPLACE TRIGGER dept_cascade1 13 BEFORE UPDATE OF deptno ON dept 14 DECLARE 15 Dummy NUMBER; 16 BEGIN 17 SELECT update_sequence.NEXTVAL INTO dummy FROM dual; 18 Integritypackage.updateseq:=dummy; 19 END; 20 CREATE OR REPLACE TRIGGER dept_cascade2 21 AFTER DELETE OR UPDATE OF deptno ON dept 22 FOR EACH ROW 23 BEGIN 24 IF UPDATING THEN 25 UPDATE emp SET deptno=:new.deptno, 26 update_id=integritypackage.updateseq 27 WHERE emp.deptno=:old.deptno AND update_id IS NULL; 28 END IF; 29 IF DELETING THEN 30 DELETE FROM emp 31 WHERE emp.deptno=:old.deptno; 32 END IF; 33 END; 34 CREATE OR REPLACE TRIGGER dept_cascade3 35 AFTER UPDATE OF deptno ON dept 36 BEGIN 37 UPDATE emp SET update_id=NULL 38 WHERE update_id=integritypackage.updateseq; 39 END; 40 SELECT * FROM EMP ORDER BY DEPTNO; 41 UPDATE dept SET deptno=25 WHERE deptno=20;
l帮助实现安全控制；
例：保证对EMP表的修改仅在工作日的工作时间；
1 CREATE TABLE company_holidays(day DATE); 2 INSERT INTO company_holidays 3 VALUES(sysdate); 4 INSERT INTO company_holidays 5 VALUES(TO_DATE('21-10月-01', 'DD-MON-YY')); 6 CREATE OR REPLACE TRIGGER emp_permit_change 7 BEFORE INSERT OR DELETE OR UPDATE ON emp 8 DECLARE 9 Dummy NUMBER; 10 Not_on_weekends EXCEPTION; 11 Not_on_holidays EXCEPTION; 12 Not_working_hours EXCEPTION; 13 BEGIN 14 /* check for weekends */ 15 IF TO_CHAR(SYSDATE, 'DAY') IN ('星期六', '星期日') THEN 16 RAISE not_on_weekends; 17 END IF; 18 /* check for company holidays */ 19 SELECT COUNT(*) INTO dummy FROM company_holidays 20 WHERE TRUNC(day)=TRUNC(SYSDATE); 21 IF dummy >0 THEN 22 RAISE not_on_holidays; 23 END IF; 24 /* check for work hours(8:00 AM to 18:00 PM */ 25 IF (TO_CHAR(SYSDATE,'HH24')<8 OR TO_CHAR(SYSDATE, 'HH24')>18) THEN 26 RAISE not_working_hours; 27 END IF; 28 EXCEPTION 29 WHEN not_on_weekends THEN 30 RAISE_APPLICATION_ERROR(-20324, 31 'May not change employee table during the weekends'); 32 WHEN not_on_holidays THEN 33 RAISE_APPLICATION_ERROR(-20325, 34 'May not change employee table during a holiday'); 35 WHEN not_working_hours THEN 36 RAISE_APPLICATION_ERROR(-20326, 37 'May not change employee table during no_working hours'); 38 END;
管理复杂的表复制；
防止非法的事务发生；
自动生成派生的列值；
帮助式显复杂的商业管理。