Oracle 数据库基础教程2007
1
第 15章 PL/SQL程序设计
Oracle 数据库基础教程2007
2
本章内容
PL/SQL概述
PL/SQL基础
控制结构
游标
异常处理
存储子程序
包
触发器
Oracle 数据库基础教程2007
3
本章要求
掌握 PL/SQL程序设计基础知识
掌握存储过程、函数、包、触发器的应用
Oracle 数据库基础教程2007
4
15.1 PL/SQL概述
PL/SQL特点
PL/SQL功能特性
PL/SQL执行过程与开发工具
Oracle 数据库基础教程2007
5
15.1.1PL/SQL特点
与 SQL语言紧密集成。
减小网络流量，提高应用程序的运行性能。
模块化的程序设计功能，提高了系统可靠性。
服务器端程序设计，可移植性好。
Oracle 数据库基础教程2007
6
15.1.2PL/SQL功能特性
语句块结构
异常处理
变量和类型
条件语句
循环结构
游标
过程、函数和触发器
包
集合
动态 SQL
对象特性
Oracle 数据库基础教程2007
7
15.1.3PL/SQL执行过程与开发工具
PL/SQL块
SQL语句客户端应用程序
PL/SQL引擎数据库服务器过程化语句执行器
SQL执行器块中 SQL语句
PL/SQL执行过程
Oracle 数据库基础教程2007
8
PL/SQL开发工具
SQL *PLUS
Procedure Builder
Oracle Form,Oracle Reports
PL/SQL Developer
Oracle 数据库基础教程2007
9
15.2 PL/SQL基础
PL/SQL程序结构
词法单元
数据类型
变量与常量
编译指示
PL/SQL中的 SQL语句
Oracle 数据库基础教程2007
10
PL/SQL程序结构
PL/SQL块的组成
PL/SQL块分类
Oracle 数据库基础教程2007
11
PL/SQL块的组成
PL/SQL语言以块为单位，块中可以嵌套子块。
一个基本的 PL/SQL块由 3部分组成：
声明（ DECLARE），
可执行部分 （ BEGIN）,
异常处理部分 EXCEPTION）。
Oracle 数据库基础教程2007
12
声明部分
声明部分以关键字 DECLARE开始,BEGIN结束。主要用于声明变量、常量、数据类型、游标、异常处理名称以及本地（局部）子程序定义等。
可执行部分
执行部分是 PL/SQL块的功能实现部分，以关键字 BEGIN开始，
EXCEPTION或 END结束（如果 PL/SQL块中没有异常处理部分，
则以 END结束）。该部分通过变量赋值、流程控制、数据查询、数据操纵、数据定义、事务控制、游标处理等实现块的功能。
异常处理部分
异常处理部分以关键字 EXCEPTION开始,END结束。该部分用于处理该块执行过程中产生的异常。
Oracle 数据库基础教程2007
13
注意：
执行部分是必需的，而声明部分和异常部分是可选的；
可以在一个块的执行部分或异常处理部分嵌套其他的 PL/SQL块；
所有的 PL/SQL块都是以? END;? 结束
Oracle 数据库基础教程2007
14
PL/SQL块分类
匿名块
命名块
函数
存储过程
包
触发器
Oracle 数据库基础教程2007
15
15.5.2词法单元
字符集
标识符
分隔符
常量值
注释
Oracle 数据库基础教程2007
16
字符集
大小写字母,A~Z,a~z
数字,0~9
空白：制表符,空格和回车
数字符号,+ - * / 〈 〉 =
标点符号,~ ! @ # $ % ^&* （） _ | { }
[ ]？ ；,,,? ‘
Oracle 数据库基础教程2007
17
标识符
标识符以字母开头,后边可以跟字母,数字,
货币符号,下划线和? #?
标识符的最大长度为 30字符,并且所有字符都是有效的 。
合法,X v_studentID TempVar
非法,X+y _temp
Oracle 数据库基础教程2007
18
+
-
*
/
=
,=
<
>
<=
>=
<>
!=
~=
^=
(
)
/*
*/
<<
>>
%;
,
,
,
,.
@
||
=>
**
-
分隔符
Oracle 数据库基础教程2007
19
常量值
字符型常量
数字型常量
布尔型常量,TURE,FALSE,NULL
日期型常量
Oracle 数据库基础教程2007
20
15.2.3数据类型
数字类型
字符类型
日期 /区间类型
行标识类型
布尔类型
原始类型
LOB类型
记录类型
集合类型
Oracle 数据库基础教程2007
21
PL/SQL中常用的基本数据类型分类 数据类型数字类型 NUMBER,BINARY_NUMBER
PLS_NUMBER
字符类型 VARCHAR2,CHAR,LONG、
NCHAR,NVARCHAR
日期 /区间类型 DATE,TIMESTAMP,INTERVAL
行标识类型 ROWID,UROWID
布尔类型 BOOLEAN（ TRUE,FALSE,NULL）
原始类型 RAW,LONG RAW
LOB类型 CLOB,BLOB,NCLOB,BFILE
记录类型 RECORD
集合类型 TABLE,VARRAY
Oracle 数据库基础教程2007
22
记录类型的定义
TYPE record_type IS RECORD(
field1 datatype1 [NOT
NULL][DEFAULT|:=expr1],
field2 datatype2 [NOT
NULL][ DEFAULT|:=expr2],
……
fieldn datatypen [NOT
NULL][ DEFAULT|:=exprn]);
Oracle 数据库基础教程2007
23
15.2.4变量与常量
变量与常量的定义
变量的作用域
Oracle 数据库基础教程2007
24
变量声明变量与常量的定义
变量定义的一般格式：
<variablename> [CONSTANT]<datatype>
[[NOT NULL] {DEFAULT|:=} <expression>];
说明
每行只能定义一个标识符。
如果加上关键字 CONSTANT,则表示所定义的标识符为一个常量，必须为它赋初值。
如果定义的标识符不能为空，则必须加上关键字 NOT
NULL,并赋初值。
为标识符赋值时，使用赋值符号 ‘,=’,默认值为空。
Oracle 数据库基础教程2007
25
DECLARE
v1 NUMBER(4);
v2 NUMBER(4) NOT NULL,=10;
v3 CONSTANT NUMBER(4) DEFAULT 100;
BEGIN
IF v1 IS NULL THEN
DBMS_OUTPUT.PUT_LINE('V1 IS NULL!');
END IF;
DBMS_OUTPUT.PUT_LINE(v2||' '||v3);
END;
Oracle 数据库基础教程2007
26
声明一个变量，使它的类型与某个变量或数据库基本表中某个列的数据类型一致，
可以使用 %TYPE。
示例
v_empno1 emp.empno%TYPE;
v_empno2 v_empno1%TYPE;
Oracle 数据库基础教程2007
27
变量的作用域
变量的作用域是指变量的有效作用范围，从变量声明开始，直到块结束。
如果 PL/SQL块相互嵌套，则在内部块中声明的变量是局部的，只能在内部块中引用，而在外部块中声明的变量是全局的，既可以在外部块中引用，
也可以在内部块中引用。
如果内部块与外部块中定义了同名变量，则在内部块中引用外部块的全局变量时需要使用外部块名进行标识。
Oracle 数据库基础教程2007
28
<<OUTER>>
DECLARE
v_ename CHAR(15);
v_outer NUMBER(5);
BEGIN
v_outer,=10;
DECLARE
v_ename CHAR(20);
v_inner DATE;
BEGIN
v_inner:=sysdate;
v_ename:='INNER V_ENAME';
OUTER.v_ename:='OUTER V_ENAME';
END;
DBMS_OUTPUT.PUT_LINE(v_ename);
END;
Oracle 数据库基础教程2007
29
15.2.5编译指示
编译指示是对编译程序发出的特殊指令，
也称伪指令。
关键字,PRAGMA
PL/SQL提供以下四种编译指示：
EXCEPTION_INIT
告诉编译程序将一个特定的错误号与程序中所声明的异常标识符关联起来。
RESTRICT_REFERENCES
告诉编译程序打包程序的纯度，即对函数中可以使用的 SQL语句和包变量进行限制。
Oracle 数据库基础教程2007
30
SERIALLY_REUSEABLE
告诉 PL/SQL运行时引擎，在数据引用之间不要保持包级数据。
AUTONOMOUS_TRANSACTION
告诉编译程序，该程序块为自治事务，即该事务的提交和回滚是独立进行的。
Oracle 数据库基础教程2007
31
15.2.6PL/SQL中 SQL语句
可以在 PL/SQL中执行的 SQL语句包括
SELECT
DML(UPDATE,DELETE,INSERT)
事务控制语句（ COMMIT,ROLLBACK、
SAVEPOINT）
注意 DDL语句不可以直接使用
Oracle 数据库基础教程2007
32
SELECT… INTO
SELECT… INTO语句只能查询一个记录的信息，如果没有查询到任何数据，则会产生
NO_DATA_FOUND异常；如果查询到多个记录，则会产生 TOO_MANY_ROW异常。
INTO句子后的变量用于接收查询的结果，变量的个数、顺序应该与查询的目标数据相匹配，
也可以是记录类型的变量。 DML语句
Oracle 数据库基础教程2007
33
DECLARE
v_emp emp%ROWTYPE;
v_ename emp.ename%type;
v_sal emp.sal%type;
BEGIN
SELECT * INTO v_emp FROM emp WHERE ename='SMITH';
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.sal);
select ename,sal INTO v_ename,v_sal FROM emp WHERE
empno=7900;
DBMS_OUTPUT.PUT_LINE(v_ename||' '||v_sal);
END;
Oracle 数据库基础教程2007
34
DML语句
PL/SQL中 DML语句对标准 SQL语句中的 DML语句进行了扩展，允许使用变量。
示例
DECLARE
v_empno emp.empno%TYPE,=7500;
BEGIN
INSERT INTO emp(empno,ename,sal,deptno)
VALUES(v_empno,'JOAN',2300,20);
UPDATE emp SET sal=sal+100 WHERE
empno=v_empno;
DELETE FROM emp WHERE empno=v_empno;
END;
Oracle 数据库基础教程2007
35
WHERE
标识符的区分
系统首先查看 WHERE子句中的标识符是否与表中的列名相同，
如果相同，则该标识符被解释为列名；如果没有同名列，系统检查该标识符是不是 PL/SQL语句块的变量。
字符串比较
填充比较：通过在短字符串后添加空格，使两个字符串达到相同长度，然后根据每个字符的 ASCII码进行比较。
非填充比较：根据每个字符的 ASCII码进行比较，最先结束的字符串为小。
PL/SQL中规定，对定长的字符串（ CHAR类型的字符串和字符串常量）采用填充比较；如果比较的字符串中有一个是变长字符串（ VARCHAR2类型的字符串），则采用非填充比较。
Oracle 数据库基础教程2007
36
RETURNING
如果要查询当前 DML语句操作的记录的信息，
可以在 DML语句末尾使用 RETURNING语句返回该记录的信息。
RETURNING语句的基本语法：
RETURNING select_list_item INTO
variable_list|record_variable;
Oracle 数据库基础教程2007
37
DECLARE
v_sal emp.sal%TYPE;
BEGIN
UPDATE emp SET sal=sal+100
WHERE empno=7844
RETURNING sal INTO v_sal;
DBMS_OUTPUT.PUT_LINE(v_sal);
END;
Oracle 数据库基础教程2007
38
15.3 控制结构
选择结构
循环结构
跳转结构
Oracle 数据库基础教程2007
39
15.3.1选择结构
IF语句
IF condition1 THEN statements1;
[ELSIF condition2 THEN statements2;]
……
[ELSE else_statements];
END IF;
注意
条件是一个布尔型变量或表达式，取值只能是 TRUE,FALSE,NULL。
Oracle 数据库基础教程2007
40
例如，输入一个员工号，修改该员工的工资，如果该员工为 10号部门，工资增加
100；若为 20号部门，工资增加 150；若为 30号部门，工资增加 200；否则增加
300。
Oracle 数据库基础教程2007
41
DECLARE
v_deptno emp.deptno%type;
v_increment NUMBER(4);
v_empno emp.empno%type;
BEGIN
v_empno:=&x;
SELECT deptno INTO v_deptno FROM emp WHERE
empno=v_empno;
IF v_deptno=10 THEN v_increment:=100;
ELSIF v_deptno=20 THEN v_increment:=150;
ELSIF v_deptno=30 THEN v_increment:=200;
ELSE v_increment:=300;
END IF;
UPDATE emp SET sal=sal+v_increment WHERE
empno=v_empno;
END;
Oracle 数据库基础教程2007
42
搜索式 CASE语句
基本语法
CASE
WHEN condition1 THEN statements1;
WHEN condition2 THEN statements2;
……
WHEN conditionn THEN statementsn;
[ELSE else_statements;]
END CASE;
Oracle 数据库基础教程2007
43
等值比较的 CASE语句
基本语法
CASE test_value
WHEN value1 THEN statements1;
WHEN value2 THEN statements2;
……
WHEN valuen THEN statementsn;
[ELSE else_ statements;]
END CASE;
Oracle 数据库基础教程2007
44
DECLARE
v_deptno emp.deptno%type;
v_increment NUMBER(4);
v_empno emp.empno%type;
BEGIN
v_empno:=&x;
SELECT deptno INTO v_deptno FROM emp WHERE
empno=v_empno;
CASE v_deptno
WHEN 10 THEN v_increment:=100;
WHEN 20 THEN v_increment:=150;
WHEN 30 THEN v_increment:=200;
ELSE v_increment:=300;
END CASE;
UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno;
END;
Oracle 数据库基础教程2007
45
根据输入的员工号，修改该员工工资。如果该员工工资低于 1000，则工资增加 200；
如果工资在 1000-2000之间，则增加 150；
如果工资在 2000-3000之间，则增加 100；
否则增加 50。
Oracle 数据库基础教程2007
46
DECLARE
v_sal emp.sal%type;
v_increment NUMBER(4);
v_empno emp.empno%type;
BEGIN
v_empno:=&x;
SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
CASE
WHEN v_sal<1000 THEN v_increment:=200;
WHEN v_sal<2000 THEN v_increment:=150;
WHEN v_sal<3000 THEN v_increment:=100;
ELSE v_increment:=50;
END CASE;
UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno;
END;
Oracle 数据库基础教程2007
47
15.3.2循环结构
简单循环
WHILE循环
FOR循环
Oracle 数据库基础教程2007
48
简单循环
语法
LOOP
sequence_of_statement;
EXIT [WHEN condition] ;
END LOOP;
注意：在循环体中一定要包含 EXIT语句，
否则程序进入死循环。
Oracle 数据库基础教程2007
49
例如，执行 CREATE TABLE temp_table(num_col
NUMBER,info_col CHAR(10)) 语句创建 temp_table表，然后利用循环向 temp_table表中插入 50条记录。程序为：
DECLARE
v_counter BINARY_INTEGER,= 1;
BEGIN
LOOP
INSERT INTO temp_table VALUES (v_Counter,'Loop
index');
v_counter,= v_counter + 1;
EXIT WHEN v_counter > 50;
END LOOP;
END;
Oracle 数据库基础教程2007
50
WHILE循环
基本语法
WHILE condition LOOP
sequence_of_statement;
END LOOP;
Oracle 数据库基础教程2007
51
例如，利用 WHILE循环向 temp_table表中插入 50条记录。程序为：
DECLARE
v_counter BINARY_INTEGER,=1;
BEGIN
WHILE v_counter <= 50 LOOP
INSERT INTO temp_table VALUES (v_counter,
'Loop index');
v_counter,= v_counter + 1;
END LOOP;
END;
Oracle 数据库基础教程2007
52
FOR循环
基本语法
FOR loop_counter IN [REVERSE]
low_bound..high_bound
LOOP
sequence_of_statement;
END LOOP;
注意：
循环变量不需要显式定义，系统隐含地将它声明为
BINARY_INTEGER变量；
系统默认时，循环变量从下界往上界递增计数，如果使用 REVERSE关键字，则表示循环变量从上界向下界递减计数；
循环变量只能在循环体中使用，不能在循环体外使用。
Oracle 数据库基础教程2007
53
例如，利用 FOR循环向 temp_table表中插入 50条记录。程序为：
BEGIN
FOR v_counter IN 1..50 LOOP
INSERT INTO temp_table VALUES
(v_counter,'Loop Index');
END LOOP;
END;
Oracle 数据库基础教程2007
54
15.3.3跳转结构
语法格式：
,标号,
…
GOTO 标号；
说明：
块内可以跳转，内层块可以跳到外层块，但外层块不能跳到内层。
IF语句不能跳入。不能从循环体外跳入循环体内。
不能从子程序外部跳到子程序中。
由于 goto语句的缺点，建议尽量少用甚至不用 goto
语句。
Oracle 数据库基础教程2007
55
15.4 游标
游标的概念及类型
显式游标
隐式游标
Oracle 数据库基础教程2007
56
DECLARE
v_counter BINARY_INTEGER,=1;
BEGIN
<<LABEL>>
INSERT INTO temp_table
VALUES (v_counter,'Loop index');
v_counter,= v_Counter + 1;
IF v_counter<=50 THEN
GOTO LABEL;
END IF;
END;
Oracle 数据库基础教程2007
57
15.4游标
游标的概念及类型
显式游标
隐式游标
Oracle 数据库基础教程2007
58
15.4.1游标的及类型
游标的 概念
游标（ CURSOR）是 Oracle系统在内存中开辟的一个工作区，在其中存放 SELECT
语句返回的查询结果。
使用游标时,SELECT语句查询的结果可以是单条记录，多条记录，也可以是零条记录。
游标工作区中，存在着一个指针
（ POINTER）,在初始状态它指向查询结果的首记录。
Oracle 数据库基础教程2007
59
游标的类型
显式游标
由用户定义、操作，用于处理返回多行数据的 SELECT查询。
隐式游标
由系统自动进行操作，用于处理 DML语句和返回单行数据的 SELECT查询。
Oracle 数据库基础教程2007
60
15.4.2显式游标
显式游标的操作
显式游标的属性
参数化显式游标
显式游标的检索
利用游标更新或删除数据
Oracle 数据库基础教程2007
61
显式游标的操作
步骤
定义游标
打开游标
检索游标
关闭游标
Oracle 数据库基础教程2007
62
定义游标
语法格式
CURSOR cursor_name IS select_statement ;
说明
游标必须在 PL/SQL块的声明部分进行定义；
游标定义时可以引用 PL/SQL变量，但变量必须在游标定义之前定义；
定义游标时并没有生成数据，只是将定义信息保存到数据字典中；
游标定义后，可以使用
cursor_name%ROWTYPE定义游标类型变量。
Oracle 数据库基础教程2007
63
打开游标
语法格式
OPEN cursor_name;
说明
检查变量的值
执行游标定义时对应的 SELECT语句，将查询结果检索到工作区中。
游标指针指向第一个元组
一旦游标打开，就无法再次打开，除非先关闭
如果游标定义中的变量值发生变化，则只能在下次打开游标时才起作用。
Oracle 数据库基础教程2007
64
检索游标
语法格式
FETCH cursor_name INTO
variable_list|record_variable;
说明
在使用 FETCH语句之前必须先打开游标
对游标第一次使用 FETCH语句时，游标指针指向第一条记录，因此操作的对象是第一条记录，
使用后，游标指针指向下一条记录。
游标指针只能向下移动，不能回退
INTO子句中的变量个数、顺序、数据类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应。
Oracle 数据库基础教程2007
65
关闭游标
语法格式
CLOSE cursor_name;
说明
游标所对应的内存工作区变为无效，释放与游标相关的系统资源。
Oracle 数据库基础教程2007
66
根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定。
Oracle 数据库基础教程2007
67
DECLARE
v_deptno emp.deptno%TYPE;
CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_deptno;
v_emp c_emp%ROWTYPE;
BEGIN
v_deptno:=&x;
OPEN c_emp;
LOOP
FETCH c_emp INTO v_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
v_emp.ename||' '||
v_emp.sal ||' '||
v_deptno);
END LOOP;
CLOSE c_emp;
END;
Oracle 数据库基础教程2007
68
显式游标的属性
%ISOPEN
布尔型。如果游标已经打开，返回 TRUE,否则为 FALSE。
%FOUND
布尔型，如果最近一次使用 FETCH语句，有返回结果则为 TRUE,否则为 FALSE;
%NOTFOUND
布尔型，如果最近一次使用 FETCH语句,没有返回结果则为 TRUE,否则为 FALSE;
%ROWCOUNT
数值型，返回到目前为止从游标缓冲区检索的元组数。
Oracle 数据库基础教程2007
69
参数化显式游标
参数化游标定义语法格式：
CURSOR cursor_name(parameter1
datatype[,parameter2 datatype… ])
IS select_statement
打开参数化游标的方法
OPEN cursor_name(
parameter1[,parameter2… ])
Oracle 数据库基础教程2007
70
注意：
定义游标时，只能指定参数的类型，而不能指定参数的长度、精度、刻度；
打开带参数的游标时，实参的个数和数据类型等必须与游标定义时形参个数和数据类型等相匹配。
Oracle 数据库基础教程2007
71
DECLARE
CURSOR c_emp(p_deptno emp.deptno%TYPE)IS
SELECT * FROM emp WHERE deptno=p_deptno;
v_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp(10);
LOOP
FETCH c_emp INTO v_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
END LOOP;
CLOSE c_emp;
OPEN c_emp(20);
LOOP
FETCH c_emp INTO v_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
END LOOP;
CLOSE c_emp;
END;
Oracle 数据库基础教程2007
72
显式游标的检索
利用简单循环检索游标
利用 WHILE循环检索游标
利用 FOR循环检索游标
Oracle 数据库基础教程2007
73
利用简单循环检索游标
DECLARE
CURSOR cursor_name IS SELECT… ;
BEGIN
OPEN cursor_name;
LOOP
FETCH… INTO… ;
EXIT WHEN cursor_name%NOTFOUND;
……
END LOOP;
CLOSE cursor_name;
END;
Oracle 数据库基础教程2007
74
利用简单循环统计并输出各个部门的平均工资。
DECLARE
CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal
FROM emp GROUP BY deptno;
v_dept c_dept_stat%ROWTYPE;
BEGIN
OPEN c_dept_stat;
LOOP
FETCH c_dept_stat INTO v_dept;
EXIT WHEN c_dept_stat%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||'
'||v_dept.avgsal);
END LOOP;
CLOSE c_dept_stat;
END;
Oracle 数据库基础教程2007
75
利用 WHILE循环检索游标
DECLARE
CURSOR cursor_name IS SELECT… ;
BEGIN
OPEN cursor_name;
FETCH… INTO… ;
WHILE cursor_name%FOUND LOOP
FETCH… INTO… ;
……
END LOOP;
CLOSE cursor;
END;
Oracle 数据库基础教程2007
76
利用 WHILE循环统计并输出各个部门的平均工资。
DECLARE
CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal
FROM emp GROUP BY deptno;
v_dept c_dept_stat%ROWTYPE;
BEGIN
OPEN c_dept_stat;
FETCH c_dept_stat INTO v_dept;
WHILE c_dept_stat%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||'
'||v_dept.avgsal);
FETCH c_dept_stat INTO v_dept;
END LOOP;
CLOSE c_dept_stat;
END;
Oracle 数据库基础教程2007
77
利用 FOR循环检索游标
DECLARE
CURSOR cursor_name IS SELECT… ;
BEGIN
FOR loop_variable IN cursor_name LOOP
……
END LOOP;
END;
Oracle 数据库基础教程2007
78
FOR循环说明
系统隐含地定义了一个数据类型为 %ROWTYPE的变量，并以此作为循环的计算器。
系统自动打开游标，不用显式地使用 OPEN语句打开；
系统重复地自动从游标工作区中提取数据并放入计数器变量中。
系统自动进行 %FOUND属性检查以确定是否有数据
当游标工作区中所有的记录都被提取完毕或循环中断时，系统自动地关闭游标。
Oracle 数据库基础教程2007
79
利用 FOR循环统计并输出各个部门的平均工资。
DECLARE
CURSOR c_dept_stat IS SELECT deptno,avg(sal)
avgsal FROM emp GROUP BY deptno;
BEGIN
FOR v_dept IN c_dept_stat LOOP
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||'
'||v_dept.avgsal);
END LOOP;
END;
Oracle 数据库基础教程2007
80
隐式 FOR游标
BEGIN
FOR v_emp IN
(select * from emp where deptno=10) LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
v_emp.ename);
END LOOP;
END;
Oracle 数据库基础教程2007
81
利用游标更新或删除数据
游标定义语法
CURSOR cursor_name IS
SELECT select_list_item FROM table FOR UPDATE
更新或修改数据的语法为：
UPDATE|DELETE…
WHERE CURRENT OF cursor_name
注意由于 COMMIT语句会释放会话拥有的任何锁，因此如果在检索游标的循环内使用 COMMIT语句会释放定义游标时对数据加的锁，从而导致利用游标修改或删除数据的操作失败。
Oracle 数据库基础教程2007
82
修改员工的工资，如果员工的部门号为 10，
工资提高 100；部门号为 20，工资提高
150；部门号为 30，工资提高 200；否则工资提高 250。
Oracle 数据库基础教程2007
83
DECLARE
CURSOR c_emp IS SELECT * FROM emp FOR UPDATE;
v_increment NUMBER;
BEGIN
FOR v_emp IN c_emp LOOP
CASE v_emp.deptno
WHEN 10 THEN v_increment:=100;
WHEN 20 THEN v_increment:=150;
WHEN 30 THEN v_increment:=200;
ELSE v_increment:=250;
END CASE;
UPDATE emp SET sal=sal+v_increment WHERE CURRENT OF c_emp;
END LOOP;
END;
Oracle 数据库基础教程2007
84
15.4.3隐式游标
用于处理 INSERT,UPDATE,DELETE和
SELECT… INTO语句
没有 OPEN,FETCH,CLOSE命令
属性
SQL%ISOPEN
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
Oracle 数据库基础教程2007
85
修改员工号为 1000的员工工资，将其工资增加 100。如果该员工不存在，
则向 emp表中插入一个员工号为 1000，工资为 1500的员工。
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=1000;
IF SQL%NOTFOUND THEN
INSERT INTO emp(empno,sal) VALUES(1000,1500);
END IF;
END;
或
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=1000;
IF SQL%ROWCOUNT=0 THEN
INSERT INTO emp(empno,sal) VALUES(1000,1500);
END IF;
END;
Oracle 数据库基础教程2007
86
15.5 异常处理
异常概述
异常处理过程
异常的传播
Oracle 数据库基础教程2007
87
15.5.1异常概述
Oracle错误处理机制
异常的类型
Oracle 数据库基础教程2007
88
Oracle错误处理机制
概念
一个错误对应一个异常，当错误产生时抛出相应的异常，并被异常处理器捕获，程序控制权传递给异常处理器，由异常处理器来处理运行时错误。
Oracle 数据库基础教程2007
89
异常的类型
预定义的 Oracle异常
非预定义的 Oracle异常
用户定义的异常
Oracle 数据库基础教程2007
90
预定义的异常异常情况名 错误代码 描述
CURSOR_ALREADY
_OPEN
ORA-06511 尝试打开已经打开的游标
INVALID_CURSOR ORA-01001 不合法的游标操作（如要打开已经关闭的游标）
NO_DATA_FOUND ORA-01403 没有发现数据
TOO_MANY_ROWS ORA-01422 一个 SELECT INTO语句匹配多个数据行
INVALID_NUMBER ORA-01722 转换成数字失败 （ ‘ X’）
VALUE_ERROR ORA-06502 截断、算法或转换错误，通常出现在赋值错误
ZERO_DIVIDE ORA-01476 除数为 0
ROWTYPE_MISMATCH ORA-06504 主机游标变量与 PL/SQL游标变量类型不匹配
Oracle 数据库基础教程2007
91
异常情况名 错误代码 描述
DUP_VAL_ON_INDEX ORA-00001 违反唯一性约束或主键约束
SYS_INVALID_ROWID ORA-01410 转换成 ROWID失败
TIMEOUT_ON_RESOUR
CE
ORA-00051 在等待资源中出现超时
LOGIN_DENIED ORA-01017 无效用户名 /密码
CASE_NOT_FOUND ORA-06592 没有匹配的 WHEN子句
NOT_LOGGED_ON ORA-01012 没有与数据库建立连接
STORAGE_ERROR ORA-06500 PL/SQL内部错误
PROGRAM_ERROR ORA-06501 PL/SQL内部错误
Oracle 数据库基础教程2007
92
异常情况名 错误代码 描述
ACCESS_INTO_NULL ORA-06530 给空对象属性赋值
COLLECTION_IS_NULL ORA-06531 对某 NULL PL/SQL表或可变数组试图应用集合方法，而不是 EXISTS
SELF_IS_NULL ORA-30625 调用空对象实例的方法
SUBSCRIPT_BEYOND_COUNT ORA-06533 对嵌套表或数组索引引用时超出集合中元素的数量
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 对嵌套表或可变数组索引的引用超出声明的范围
Oracle 数据库基础教程2007
93
非预定义异常
在语句块的声明部分声明一个异常名称
e_integrity EXCEPTION;
通过 PRAGMA EXCEPTION-INIT 将异常 与一个 Oracle错误号相关联,
PRAGMA EXCEPTION-
INIT(e_integrity.-2291)
在异常处理部分捕捉并处理异常：
WHEN e_integrity THEN,..
Oracle 数据库基础教程2007
94
用户自定义的异常
用户自定义异常必须在声明部分进行声明。
当异常发生时，系统不能自动触发，需要用户使用 RAISE语句。
在异常处理部分捕捉并处理异常。
Oracle 数据库基础教程2007
95
15.5.2异常处理过程
在声明部分为错误定义异常，包括非预定义异常和用户定义异常。
e_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(e_exceptioin,-
#####);
在执行过程中当错误产生时抛出与错误对应的异常。
RAISE user_define_exception；
在异常处理部分通过异常处理器捕获异常，并进行异常处理。
Oracle 数据库基础教程2007
96
异常的捕获与处理
异常处理器的基本形式为
EXCEPTION
WHEN exception1[OR excetpion2… ]THEN
sequence_of_statements1;
WHEN exceptioin3[OR exception4… ]THEN
sequence_of_statements2;
……
WHEN OTHERS THEN
sequence_of_statementsn;
END;
Oracle 数据库基础教程2007
97
注意,
一个异常处理器可以捕获多个异常，只需要在
WHEN子句中用 OR连接即可；
一个异常只能被一个异常处理器捕获，并进行处理。
Oracle 数据库基础教程2007
98
查询名为 SMITH的员工工资，如果该员工不存在，则输出,There is
not such an employee!”；如果存在多个同名的员工，则输出其员工号和工资。
DECLARE
v_sal emp.sal%type;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE ename='SMITH';
DBMS_OUTPUT.PUT_LINE(v_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is not such an emplyee!');
WHEN TOO_MANY_ROWS THEN
FOR v_emp IN (SELECT * FROM emp WHERE
ename='SMITH') LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.sal);
END LOOP;
END;
Oracle 数据库基础教程2007
99
删除 dept表中部门号为 10的部门信息，如果不能删除则输出,There are subrecords in emp table!”。
DECLARE
e_deptno_fk EXCEPTION;
PRAGMA EXCEPTION_INIT(e_deptno_fk,-2292);
BEGIN
DELETE FROM dept WHERE deptno=10;
EXCEPTION
WHEN e_deptno_fk THEN
DBMS_OUTPUT.PUT_LINE(' There are
subrecords in emp table!');
END;
Oracle 数据库基础教程2007
100
修改 7844员工的工资，保证修改后工资不超过 6000。
DECLARE
e_highlimit EXCEPTION;
v_sal emp.sal%TYPE;
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=7844
RETURNING sal INTO v_sal;
IF v_sal>6000 THEN
RAISE e_highlimit;
END IF;
EXCEPTION
WHEN e_highlimit THEN
DBMS_OUTPUT.PUT_LINE('The salary is too large!');
ROLLBACK;
END;
Oracle 数据库基础教程2007
101
OTHERS异常处理器
OTHERS异常处理器是一个特殊的异常处理器，可以捕获所有的异常。
通常,OTHERS异常处理器总是作为异常处理部分的最后一个异常处理器，负责处理那些没有被其他异常处理器捕获的异常。
Oracle 数据库基础教程2007
102
DECLARE
v_sal emp.sal%TYPE;
e_highlimit EXCEPTION;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE ename='JOAN';
UPDATE emp SET sal=sal+100 WHERE empno=7900;
IF v_sal>6000 THEN
RAISE e_highlimit;
END IF;
EXCEPTION
WHEN e_highlimit THEN
DBMS_OUTPUT.PUT_LINE('The salary is too large!');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('There is some wrong in selecting!');
END;
Oracle 数据库基础教程2007
103
可以通过两个函数来获取错误相关信息。
SQLCODE：返回当前错误代码。
如果是用户定义错误返回值为 1；
如果是 ORA-1403,NO DATA FOUND错误，返回值为 100；
其他 Oracle内部错误返回相应的错误号。
SQLERRM：返回当前错误的消息文本。
如果是 Oracle内部错误，返回系统内部的错误描述；
如果是用户定义错误，则返回信息文本为,User-
defined Exception”。
Oracle 数据库基础教程2007
104
DECLARE
v_sal emp.sal%TYPE;
e_highlimit EXCEPTION;
v_code NUMBER(6);
v_text VARCHAR2(200);
BEGIN
SELECT sal INTO v_sal FROM emp WHERE ename='JOAN';
UPDATE emp SET sal=sal+100 WHERE empno=7900;
IF v_sal>6000 THEN
RAISE e_highlimit;
END IF;
EXCEPTION
WHEN e_highlimit THEN
DBMS_OUTPUT.PUT_LINE('The salary is too large!');
ROLLBACK;
WHEN OTHERS THEN
v_code:=SQLCODE;
v_text:=SQLERRM;
DBMS_OUTPUT.PUT_LINE(v_code||' '||v_text);
END;
Oracle 数据库基础教程2007
105
15.5.3异常的传播
可执行部分异常的传播
如果当前语句块有该异常的处理器，则执行之，并且成功完成该语句块。然后，控制权传递到外层语句块。
如果当前语句块没有该异常的处理器，则通过在外层语句块中产生该异常来传播该异常。
然后，执行对外层语句块执行步骤 1。如果没有外层语句块，则该异常将传播到调用环境。
Oracle 数据库基础教程2007
106
DECLARE
v_sal emp.sal%TYPE;
BEGIN
BEGIN
SELECT sal INTO v_sal FROM emp WHERE ename='JOAN';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is not such an employee!');
END;
DBMS_OUTPUT.PUT_LINE('Now this is outputted by outer
block!');
END;
/
There is not such an employee!
Now this is outputted by outer block!
Oracle 数据库基础教程2007
107
DECLARE
v_sal emp.sal%TYPE;
BEGIN
BEGIN
SELECT sal INTO v_sal FROM emp WHERE deptno=10;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is not such an employee!');
END;
DBMS_OUTPUT.PUT_LINE('Now this is outputted by outer block!');
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('There are more than one employee!');
END;
/
There are more than one employee!
Oracle 数据库基础教程2007
108
声明部分异常的传播
声明部分的异常立刻传播到外层语句块，即使当前语句块有异常处理器。
异常处理部分的异常的传播
异常处理器中产生的异常，可以有 RAISE
语句显式产生，也可以通过运行时错误而隐含产生。异常立即被传播到外层语句块。
Oracle 数据库基础教程2007
109
BEGIN
DECLARE
v_number NUMBER(6),='ABC';
BEGIN
v_number:=10;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('This is outputted by inner block!');
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('This is outputted by outer block!');
END;
/
This is outputted by outer block!
Oracle 数据库基础教程2007
110
15.6 存储子程序
存储过程
函数
局部子程序
Oracle 数据库基础教程2007
111
存储子程序是指被命名的 PL/SQL块，以编译的形式存储在数据库服务器中，可以在应用程序中进行调用，是 PL/SQL程序模块化的一种体现。
存储子程序是以独立对象的形式存储在数据库服务器中，因此是一种全局结构，与之对应的是局部子程序，即嵌套在 PL/SQL
块中的局部过程和函数，其存储位置取决于其所在的父块的位置。
Oracle 数据库基础教程2007
112
存储过程
存储过程的创建
存储过程的调用
存储过程的管理
Oracle 数据库基础教程2007
113
存储过程的创建
CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter1_name [mode] datatype [DEFAULT|:=value]
[,parameter2_name [mode] datatype [DEFAULT|:=value],… ])
AS|IS
/*Declarative section is here */
BEGIN
/*Executable section is here*/
EXCEPTION
/*Exception section is here*/
END[procedure_name];
PROCEDURE
BODY
Oracle 数据库基础教程2007
114
参数模式
IN
当过程被调用时，实参值被传递给过程。在过程内，该参数起常数作用，可读不可写。调用结束，实参值不变。（默认参数类型）
OUT
当过程被调用时，实参值被忽略。在过程内，该参数起未初始化的变量作用，值为 NULL。过程内，该参数可读可写。 调用结束，形参赋给实参。
IN OUT
当过程被调用时，实参值被传递给过程。在过程内，该参数起已初始化变量作用，过程内，该参数可读可写。调用结束，形参赋给实参。
Oracle 数据库基础教程2007
115
参数限制
声明形参时不能定义形参的长度或精度、刻度
参数传递
IN参数为引用传递，即实参的指针被传递给形参；
OUT,IN OUT参数为值传递，即实参的值被复制给形参。
Oracle 数据库基础教程2007
116
创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。
CREATE OR REPLACE PROCEDURE show_emp(
p_deptno emp.deptno%TYPE)
AS
v_sal emp.sal%TYPE;
BEGIN
SELECT avg(sal) INTO v_sal FROM emp WHERE
deptno=p_deptno;
DBMS_OUTPUT.PUT_LINE(p_deptno||' '||'average salary
is:'||v_sal);
FOR v_emp IN (SELECT * FROM emp WHERE
deptno=p_deptno AND sal>v_sal) LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||'
'||v_emp.ename);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The department doesnt exists!');
END show_emp;
Oracle 数据库基础教程2007
117
通常，存储过程不需要返回值，如果需要返回一个值可以通过函数调用实现。但是，
如果希望返回多个值，可以使用 OUT或 IN
OUT模式参数来实现。
Oracle 数据库基础教程2007
118
创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。
CREATE OR REPLACE PROCEDURE return_deptinfo(
p_deptno emp.deptno%TYPE,
p_avgsal OUT emp.sal%TYPE,
p_count OUT emp.sal%TYPE)
AS
BEGIN
SELECT avg(sal),count(*) INTO p_avgsal,p_count
FROM emp
WHERE deptno=p_deptno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The department dont exists!');
END return_deptinfo;
Oracle 数据库基础教程2007
119
存储过程的调用
在 SQL*PLUS中调用
EXEC procedure_name(parameter_list)
EXECUTE show_emp(10)
在 PL/SQL块中调用
BEGIN
procedure_name(parameter_list);
END；
Oracle 数据库基础教程2007
120
DECLARE
v_avgsal emp.sal%TYPE;
v_count NUMBER;
BEGIN
show_emp(20);
return_deptinfo(10,v_avgsal,v_count);
DBMS_OUTPUT.PUT_LINE(v_avgsal||'
'||v_count);
END;
Oracle 数据库基础教程2007
121
存储过程的管理
修改存储过程
CREATE OR REPLACE PROCEDURE
重新编译存储过程
ALTER PROCEDURE procedure_name COMPILE；
删除存储过程
DROP PROCEDURE procedure_name名；
查看过程源代码
select text from user_source where
name=procedure_name ;
Oracle 数据库基础教程2007
122
15.6.2函数
函数概述
函数的创建
函数的调用
函数的管理
Oracle 数据库基础教程2007
123
函数概述
函数用于返回特定数据，可以返回一个或多个值。
在一个函数中必须包含一个或多个
RETURN 语句
函数调用是 PL/SQL表达式的一部分,而过程调用可以是一个独立的 PL/SQL语句
Oracle 数据库基础教程2007
124
函数的创建
CREATE [OR REPLACE] FUNCTION function_name
(parameter1_name [mode] datatype [DEFAULT|:=value]
[,parameter2_name [mode] datatype [DEFAULT|:=value],… ])
RETURN return_datatype
AS |IS
/*Declarative section is here */
BEGIN
/*Executable section is here*/
EXCEPTION
/*Exception section is here*/
END [function_name];
FUNCTION
BODY
Oracle 数据库基础教程2007
125
创建一个以部门号为参数，返回该部门最高工资的函数。
CREATE OR REPLACE FUNCTION return_maxsal
(p_deptno emp.deptno%TYPE)
RETURN emp.sal%TYPE
AS
v_maxsal emp.sal%TYPE;
BEGIN
SELECT max(sal) INTO v_maxsal FROM emp
WHERE deptno=p_deptno;
RETURN v_maxsal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The deptno is invalid!');
END return_maxsal;
Oracle 数据库基础教程2007
126
函数的调用
在 SQL语句中调用函数
在 PL/SQL中调用函数
Oracle 数据库基础教程2007
127
DECLARE
v_sal emp.sal%TYPE;
BEGIN
FOR v_dept IN (SELECT DISTINCT deptno FROM emp)
LOOP
v_sal:=return_maxsal(v_dept.deptno);
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_sal);
END LOOP;
END;
Oracle 数据库基础教程2007
128
函数的管理
修改函数
CREATE OR REPLACE FUNCTION function_name
重新编译存储过程
ALTER FUNCTION function_name COMPILE；
删除存储过程
DROP FUNCTION function_name ；
查看过程源代码
select text from user_source where
name= function_name;
Oracle 数据库基础教程2007
129
15.6.3局部子程序
局部子程序
嵌套在其他 PL/SQL块中的子程序。
只能在其定义的块内部被调用，而不能在其父块外被调用。
使用局部子程序时需要注意：
局部子程序只在当前语句块内有效；
局部子程序必须在 PL/SQL块声明部分的最后进行定义；
局部子程序必须在使用之前声明，如果是子程序间相互引用，则需要采用预先声明；
局部子程序可以重载。
Oracle 数据库基础教程2007
130
在一个块内部定义一个函数和一个过程。
函数以部门号为参数返回该部门的平均工资；过程以部门号为参数，输出该部门中工资低于部门平均工资的员工的员工号、
员工名。
Oracle 数据库基础教程2007
131
DECLARE
v_deptno emp.deptno%TYPE;
v_avgsal emp.sal%TYPE;
FUNCTION return_avgsal(p_deptno emp.deptno%TYPE)
RETURN emp.sal%TYPE
AS
v_sal emp.sal%TYPE;
BEGIN
SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno;
RETURN v_sal;
END return_avgsal;
PROCEDURE show_emp(p_deptno emp.deptno%TYPE)
AS
CURSOR c_emp IS
SELECT * FROM emp WHERE sal<return_avgsal(p_deptno);
BEGIN
FOR v_emp IN c_emp LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
END LOOP;
END show_emp;
BEGIN
v_deptno:=&x;
v_avgsal:=return_avgsal(v_deptno);
show_emp(v_deptno);
END;
Oracle 数据库基础教程2007
132
存储子程序与局部子程序区别在于：
存储子程序己经编译好放在数据库服务器端，
可以直接调用，而局部子程序存在于定义它的语句块中，在运行时先进行编译；
存储子程序不能重载，而局部子程序可以进行重载；
存储子程序可以被任意的 PL/SQL块调用，而局部子程序只能在定义它的块中被调用。
Oracle 数据库基础教程2007
133
在一个 PL/SQL块中重载两个过程，一个以员工号为参数，输出该员工信息；另一个以员工名为参数，输出员工信息。利用这两个过程分别查询员工号为 7902,7934，
以及员工名为 SMITH,FORD的员工信息 。
Oracle 数据库基础教程2007
134
DECLARE
PROCEDURE show_empinfo(p_empno emp.empno%TYPE)
AS
v_emp emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp FROM emp WHERE empno=p_empno;
DBMS_OUTPUT.PUT_LINE(v_emp.ename||' '||v_emp.deptno);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is not such an employee!');
END show_empinfo;
PROCEDURE show_empinfo(p_ename emp.ename%TYPE)
AS
v_emp emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp FROM emp WHERE ename=p_ename;
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.deptno);
Oracle 数据库基础教程2007
135
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is not such an employee!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('There are more than on employee!');
END show_empinfo;
BEGIN
show_empinfo(7902);
show_empinfo(7934);
show_empinfo('SMITH');
show_empinfo('FORD');
END ;
Oracle 数据库基础教程2007
136
存储子程序与局部子程序区别
存储子程序己经编译好放在数据库服务器端，可以直接调用，而局部子程序存在于定义它的语句块中，在运行时先进行编译；
存储子程序不能重载，而局部子程序可以进行重载；
存储子程序可以被任意的 PL/SQL块调用，
而局部子程序只能在定义它的块中被调用
Oracle 数据库基础教程2007
137
15.7 包
包概述
包的创建
包的调用
包的重载
包的初始化
包的管理
Oracle 数据库基础教程2007
138
包概述
包是包含一个或多个子程序单元（过程、
函数等）的容器
包是全局的
包类型
数据库内置包
用户创建的包
包由包规范和包体两部分组成，在数据库中独立存储
Oracle 数据库基础教程2007
139
包规范声明了软件包中所有内容，如过程、
函数、游标、类型、异常和变量等，其中过程和函数只包括原型信息，不包含任何子程序代码。
包体中包含了在包头中的过程和函数的实现代码。包体中还可以包括在规范中没有声明的变量、游标、类型、异常、过程和函数，
但是它们是私有元素，只能由同一包体中其他过程和函数使用。
Oracle 数据库基础教程2007
140
创建包规范
语法
CREATE OR REPLACE PACKAGE package_name
IS|AS
[PRAGMA SERIALLY_RESUABLE]
type_definition|variable_declaration|exception
_declaration|cursor_declaration| procedure_
declaration |function_ declaration
END [package_name];
Oracle 数据库基础教程2007
141
注意：
元素声明的顺序可以是任意的，但必须先声明后使用；
所有元素是可选的；
过程和函数的声明只包括原型，不包括具体实现。
Oracle 数据库基础教程2007
142
创建一个软件包，包括 2个变量,2个过程和 1个异常。
CREATE OR REPLACE PACKAGE pkg_emp
AS
minsal NUMBER;
maxsal NUMBER;
e_beyondbound EXCEPTION;
PROCEDURE update_sal(
p_empno NUMBER,p_sal NUMBER);
PROCEDURE add_employee(
p_empno NUMBER,p_sal NUMBER);
END pkg_emp;
Oracle 数据库基础教程2007
143
语法
CREATE OR REPLACE PACKAGE BODY
package_name
IS|AS
[PRAGMA SERIALLY_RESUABLE]
type_definition|variable_declaration|
exception_declaration|
cursor_declaration|
procedure_definition |
function_definition
END [package_name];
Oracle 数据库基础教程2007
144
注意：
包体中函数和过程的原型必须与包规范中的声明完全一致；
只有在包规范已经创建的条件下，才可以创建包体；
如果包规范中不包含任何函数或过程，则可以不创建包体。
Oracle 数据库基础教程2007
145
CREATE OR REPLACE PACKAGE BODY pkg_emp
AS
PROCEDURE update_sal(p_empno NUMBER,p_sal NUMBER)
AS
BEGIN
SELECT min(sal),max(sal) INTO minsal,maxsal FROM emp;
IF p_sal BETWEEN minsal AND maxsal THEN
UPDATE emp SET sal=p_sal WHERE empno=p_empno;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20000,'The employee
doesn''t exist');
END IF;
ELSE
RAISE e_beyondbound;
END IF;
EXCEPTION
WHEN e_beyondbound THEN
DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');
END update_sal;
Oracle 数据库基础教程2007
146
PROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER)
AS
BEGIN
SELECT min(sal),max(sal) INTO minsal,maxsal FROM emp;
IF p_sal BETWEEN minsal AND maxsal THEN
INSERT INTO emp(empno,sal) VALUES(p_empno,p_sal);
ELSE
RAISE e_beyondbound;
END IF;
EXCEPTION
WHEN e_beyondbound THEN
DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');
END add_employee;
END pkg_emp;
Oracle 数据库基础教程2007
147
15.7.2包的调用
概念
指软件包中特定的元素或结构的可视范围。
在软件包头部声明的任何元素是公有的，在包外都是可见的 。
包外：通过 package.element形式调用；
包内：直接通过元素名进行调用。
在包体中定义而没有在包头中声明的元素是私有的，只能在包体中引用。
Oracle 数据库基础教程2007
148
调用软件包 pkg_emp中的过程 update_sal，修改
7844员工工资为 3000。调用 add_employee添加一个员工号为 1357，工资为 4000的员工。
BEGIN
pkg_emp.update_sal(7844,3000);
pkg_emp.add_employee(1357,4000);
END;
Oracle 数据库基础教程2007
149
包的重载
重载子程序必须同，即名称相同，参数不同。
如果两个子程序参数只是名称和模式不同，则不能重载。
PROCEDURE overloadme(parameter1 IN NUMBER);
PROCEDURE overloadme(parameter2 OUT NUMBER);
不能根据两个函数返回类型不同对它们进行重载。
FUNCTION overloadme RETURN DATE;
FUNCTION overloadme RETURN NUMBER;
重载子程序参数必须在类型系列方面有所不同。
PROCEDURE overloadchar(parameter IN CHAR);
PROCEDURE overloadchar(parameter IN VARCHAR2);
Oracle 数据库基础教程2007
150
在一个包中重载两个过程，分别以部门号和部门名称为参数，查询相应部门员工名、
员工号信息。
Oracle 数据库基础教程2007
151
CREATE OR REPLACE PACKAGE pkg_overload
AS
PROCEDURE show_emp(p_deptno NUMBER);
PROCEDURE show_emp(p_dname VARCHAR2);
END pkg_overload;
CREATE OR REPLACE PACKAGE BODY pkg_overload
AS
PROCEDURE show_emp(p_deptno NUMBER)
AS
BEGIN
FOR v_emp IN (SELECT * FROM emp WHERE
deptno=p_deptno) LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
v_emp.ename);
END LOOP;
END show_emp;
Oracle 数据库基础教程2007
152
PROCEDURE show_emp(p_dname VARCHAR2)
AS
v_deptno NUMBER;
BEGIN
SELECT deptno INTO v_deptno FROM dept
WHERE dname=p_dname;
FOR v_emp IN (SELECT * FROM emp WHERE
deptno=v_deptno) LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
v_emp.ename);
END LOOP;
END show_emp;
END pkg_overload;
Oracle 数据库基础教程2007
153
15.7.4包的初始化
包在第一次被调用时从磁盘读取到共享池，
并在整个会话的持续期间保持。在此过程中，可以自动执行一个初始化过程，对软件包进行实例化。
包的初始化过程只在包第一次被调用时执行，因此也称为一次性过程，它是一个匿名的 PL/SQL块，在包体结构的最后，以
BEGIN开始。
Oracle 数据库基础教程2007
154
在 pkg_emp包中，可以在包初始化时给 minsal
和 maxsal两个变量赋值，而在子程序中直接引用这两个变量。
CREATE OR REPLACE PACKAGE pkg_emp
AS
minsal NUMBER;
maxsal NUMBER;
e_beyondbound EXCEPTION;
PROCEDURE update_sal(
p_empno NUMBER,p_sal NUMBER);
PROCEDURE add_employee(
p_empno NUMBER,p_sal NUMBER);
END pkg_emp;
Oracle 数据库基础教程2007
155
CREATE OR REPLACE PACKAGE BODY pkg_emp
AS
PROCEDURE update_sal(p_empno NUMBER,p_sal NUMBER)
AS
BEGIN
IF p_sal BETWEEN minsal AND maxsal THEN
UPDATE emp SET sal=p_sal WHERE empno=p_empno;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20000,'The employee
doesn''t exist');
END IF;
ELSE
RAISE e_beyondbound;
END IF;
EXCEPTION
WHEN e_beyondbound THEN
DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');
END update_sal;
Oracle 数据库基础教程2007
156
PROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER)
AS
BEGIN
IF p_sal BETWEEN minsal AND maxsal THEN
INSERT INTO emp(empno,sal) VALUES(p_empno,p_sal);
ELSE
RAISE e_beyondbound;
END IF;
EXCEPTION
WHEN e_beyondbound THEN
DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');
END add_employee;
BEGIN
SELECT min(sal),max(sal) INTO minsal,maxsal FROM emp;
END pkg_emp;
Oracle 数据库基础教程2007
157
15.7.5包的管理
包的修改
CREATE OR REPLACE PACKAGE 包名
重新编译包
ALTER PACKAGE package_name COMPILE;
ALTER PACKAGE package_name COMPILE
SPECIFICATION;
ALTER PACKAGE package_name COMPILE
BODY;
删除包
DROP PACKAGE package_name ;
DROP PACKAGE BODY package_name ;
Oracle 数据库基础教程2007
158
查看包源代码
select text from user_source
where name=‘ EMP_PACKAGE’
and type=‘ PACKAGE’ ;
Oracle 数据库基础教程2007
159
15.8 触发器
触发器概述
DML触发器
INSTEAD-OF触发器
系统触发器
触发器的管理
Oracle 数据库基础教程2007
160
触发器概述
触发器的概念
触发器是命名块的一种。
触发器的执行是自动进行的，当相应事件发生时就会激发触发器的执行。
触发器不接受任何参数
Oracle 数据库基础教程2007
161
触发器（ TRIGGER）作用
维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束
通过记录已进行的改变及是谁进行了该项改变来检查一个表中的信息。
当一个表发生改变时，自动向其他程序发送需要采取行动的信号
在一个发布 -预定环境中发布关于各种事件的信息。
Oracle 数据库基础教程2007
162
触发器类型
DML触发器
INSERT,DELETE,UPDATE
INSTEAD-OF 触发器
只可以定义为视图的触发器
系统触发器
数据库启动或关闭之类的系统事件发生时触发
在执行诸如创建表之类的 DDL操作时触发
Oracle 数据库基础教程2007
163
触发器组成
触发器由触发器头部和触发器体两个部分组成，
主要包括以下参数：
作用对象：表、视图、数据库、模式
触发事件,DML,DDL、数据库系统事件
触发时间,BEFORE,AFTER
触发级别：语句级、行级
触发条件,WHEN条件
触发操作,SQL语句,PL/SQL块
Oracle 数据库基础教程2007
164
15.8.2 DML触发器
DML触发器的种类以及执行顺序
语句级前触发器
行级前触发器
DML操作（触发事件）
行级后触发器
语句级后触发器
Oracle 数据库基础教程2007
165
创建 DML触发器
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE|AFTER triggering_event [OF column_name]
ON table_name
[FOR EACH ROW]
[WHEN trigger_condition]
DECLARE
/*Declarative section is here */
BEGIN
/*Exccutable section si here*/
EXCEPTION
/*Exception section is here*/
END [trigger_name];
Trigger_body
Oracle 数据库基础教程2007
166
创建一个触发器，禁止在休息日改变雇员信息，
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate,'DY') in ('星期六 ','星期日 ') then
raise_application_error(-20001,'不能在休息日修改员工信息 ');
end if;
end;
Oracle 数据库基础教程2007
167
判断当前执行的触发器到底是有那个 DML操作激发的。
谓词 行为
INSERTING 如果触发语句是 INSERT，则为 TRUE；
否则为 FALSE
UPDATING 如果触发语句是 UPDATE，则为 TRUE；
否则为 FALSE
DELETING 如果触发语句是 DELETE，则为 TRUE；
否则为 FALSE
Oracle 数据库基础教程2007
168
为 emp表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；
当执行删除操作时，统计删除后各个部门的人数。
Oracle 数据库基础教程2007
169
CREATE OR REPLACE TRIGGER trg_emp_dml
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
v_count NUMBER;
v_sal NUMBER(6,2);
BEGIN
IF INSERTING THEN
SELECT count(*) INTO v_count FROM emp;
DBMS_OUTPUT.PUT_LINE(v_count);
ELSIF UPDATING THEN
SELECT avg(sal) INTO v_sal FROM emp;
DBMS_OUTPUT.PUT_LINE(v_sal);
ELSE
FOR v_dept IN (SELECT deptno,count(*) num FROM emp
GROUP BY deptno) LOOP
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.num);
END LOOP;
END IF;
END trg_emp_dml;
Oracle 数据库基础教程2007
170
行级触发器
是指执行 DML操作时，每操作一记录，触发器就执行一次，一个 DML操作涉及到多少个记录，
触发器就执行多少次。
在行级触发器中可以使用 WHEN条件，进一步控制触发器的执行。
在触发器体中，可以对当前操作的记录进行访问和操作。
Oracle 数据库基础教程2007
171
标识符
:OLD,,NEW
引用方式：
:old.field和,new.field （执行部分）
old.field 和 new.field (WHEN条件中 )
在不同操作中的意义触发语句,old,new
INSERT 未定义，所有字段都为
NULL
当语句完成时，将要被插入的值
UPDATE 更新前行的原始值 当语句完成时，将要被更新的值
DELETE 行被删除前的原始值 未定义，所有字段都为 NULL
Oracle 数据库基础教程2007
172
注意事项：
是伪记录，不能作为整个记录进行赋值或引用
不能传递给带
triggering_table%ROWTYPE参数的过程和函数
如果触发器是建立在嵌套表上,;old和 ;new都执行嵌套表的行,:parent指向父表中的当前行。
Oracle 数据库基础教程2007
173
为 emp表创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。
Oracle 数据库基础教程2007
174
CREATE OR REPLACE TRIGGER trg_emp_dml_row
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE(:new.empno||' '||
,new.ename);
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE(:old.sal||' '||:new.sal);
ELSE
DBMS_OUTPUT.PUT_LINE(:old.empno||' '||
,old.ename);
END IF;
END trg_emp_dml_row;
Oracle 数据库基础教程2007
175
在行级触发器中，可以使用 WHEN子句进一步控制触发器的执行。
修改员工工资时，保证修改后的工资高于修改前的工资。
CREATE OR REPLACE TRIGGER trg_emp_update_row
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN(new.sal<=old.sal)
BEGIN
RAISE_APPLICATION_ERROR(
-20001,'The salary is lower!');
END trg_emp_update_row;
Oracle 数据库基础教程2007
176
创建一个触发器，在修改 dept表的部门号时，同时更新 emp表中相应的员工的部门号。
create or replace trigger tr_update_dept
after update of deptno on dept
for each row
begin
update emp set deptno=:new.deptno
where deptno=:old.deptno;
end;
Oracle 数据库基础教程2007
177
15.8.3INSTEAD OF触发器
特点
只能定义在视图上
Instead-of触发器是行级触发器
Instead-of 触发器由 DML操作激发，而
DML操作本身并不执行
作用
修改一个本来不可以修改的视图
修改视图中某嵌套表列的列
Oracle 数据库基础教程2007
178
如果视图中包含下列任何一项,则该视图不可修改
集合操作符
聚集函数
GROUP BY,CONNECT BY 或 START WITH子句
DISTINCT
连接 （ 部分包含连接的视图 ）
Oracle 数据库基础教程2007
179
创建 INSTEAD OF触发器的基本语法
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF triggering_event [OF column_name]
ON view_name
FOR EACH ROW
[WHEN trigger_condition]
DECLARE
/*Declarative section is here */
BEGIN
/*Exccutable section si here*/
EXCEPTION
/*Exception section is here*/
END [trigger_name];
Oracle 数据库基础教程2007
180
INSTEAD OF 示例
创建一个包括员工及其所在部门信息的视图
empdept，然后向视图中插入一条记录
（ 2345,?TOM?,3000,?SALES?）。
创建视图
CREATE OR REPLACE VIEW empdept
AS
SELECT empno,ename,sal,dname FROM
emp,dept WHERE emp.deptno=dept.deptno
WITH CHECK OPTION;
创建触发器
Oracle 数据库基础教程2007
181
CREATE OR REPLACE TRIGGER trig_view
INSTEAD OF INSERT ON empdept
FOR EACH ROW
DECLARE
v_deptno dept.deptno%type;
BEGIN
SELECT deptno INTO v_deptno FROM dept
WHERE dname=:new.dname;
INSERT INTO emp(empno,ename,sal,deptno)
VALUES(:new.empno,:new.ename,v_deptno,:ne
w.sal);
END trig_view;
Oracle 数据库基础教程2007
182
15.8.4系统触发器
触发器事件
创建系统触发器
Oracle 数据库基础教程2007
183
系统触发器事件
系统触发器事件
DDL事件
CREATE,ALTER,DROP）
数据库事件触发
服务器启动 /关闭、用户登陆 /注销以及服务器错误
Oracle 数据库基础教程2007
184
事件 允许计时 描述
STARTUP AFTER 当实例开始时激发
SHUTDOWN BEFORE 当实例关闭时激发
SERVERERROR AFTER 只要错误发生就激发
LOGON AFTER 在一个用户成功连接数据库时触发
LOGOFF BEFORE 在用户注销时开始激发
CREATE BEFORE,AFTER 创建一个模式对象之前或之后激发
DROP BEFORE,AFTER 在删除一个模式对象之前或之后激发
ALTER BEFROE,AFTER 在更改一个模式对象之前或之后激发
Oracle 数据库基础教程2007
185
创建系统触发器的语法
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE|AFTER
ddl_event_list|database_event_list
ON DATABASE|SCHEMA
[WHEN trigger_condition]
DECLARE
/*Declarative section is here */
BEGIN
/*Executable section is here*/
EXCEPTION
/*Exception section is here*/
END [trigger_name];
Oracle 数据库基础教程2007
186
创建系统触发器示例
基于 DDL事件触发器
基数数据库事件的触发器
Oracle 数据库基础教程2007
187
系统触发器模式 （ DATABASE,SCHEMA）
只要触发事件发生，数据库级别的触发器就将激发。
只有当触发事件以指定模式发生时，模式级别的触发器才会激发。
如果没有用 SCHEMA关键词指定，却省为拥有该触发器的模式
事件属性函数
是 SYS所拥有的独立的 PL/SQL函数，返回触发器事件信息
要引用事件属性函数，必须用 SYS作为其前缀
Oracle 数据库基础教程2007
188
将每个用户的登录信息写入到 temp_table表中。
CREATE OR REPLACE TRIGGER log_user_connection
AFTER LOGON
ON DATABASE
BEGIN
INSERT INTO scott.temp_table
VALUES (user,sysdate);
END log_user_connection;
Oracle 数据库基础教程2007
189
当数据库中执行 CREATE操作时，将创建的对象信息记录到 ddl_creations表中。
CREATE TABLE ddl_creations (
user_id VARCHAR2(30),
object_type VARCHAR2(20),
object_name VARCHAR2(30),
object_owner VARCHAR2(30),
creation_date DATE);
CREATE OR REPLACE TRIGGER log_creations
AFTER CREATE ON DATABASE
BEGIN
INSERT INTO ddl_creations
VALUES(USER,SYS.DICTIONARY_OBJ_TYPE,
SYS.DICTIONARY_OBJ_NAME,
SYS.DICTIONARY_OBJ_OWNER,SYSDATE);
END log_creations;
Oracle 数据库基础教程2007
190
15.8.5触发器的管理
触发器名称
触发器存在于单独的名字空间中，在一个模式中可以与其他对象同名
触发器的限制
不能出现任何事务控制语句。因为触发器作为触发语句执行的一部分，处于同一个事务中。
触发器体所调用的过程或函数都不能发出任何事务控制语句（自治事务子程序除外）。
触发器体中不能声明 LONG 或 LONG RAW变量，而且,new
和,old不能引用 LONG或 LONG RAW类型的列。
触发器体中可以引用 LOB和 OBJECT列，但不能修改该列的值。
触发器的大小不能超过 32K。
Oracle 数据库基础教程2007
191
修改触发器
CREATE OR REPLACE TRIGGER trigger_name
重新编译触发器
ALTER TRIGGER trigger_name COMPILE;
禁用、启用触发器
ALTER TRIGGER trigger_name DISALBLE|ENABLE
禁用、启用某个表相关的所有触发器：
ALTER TABLE table_name
DISABLE|ENABLE ALL TRIGGERS；
删除触发器
DROP TRIGGER trigger_name;
Oracle 数据库基础教程2007
192
小结
PL/SQL概述
PL/SQL基础
控制结构
游标
异常处理
存储子程序
包
触发器
1
第 15章 PL/SQL程序设计
Oracle 数据库基础教程2007
2
本章内容
PL/SQL概述
PL/SQL基础
控制结构
游标
异常处理
存储子程序
包
触发器
Oracle 数据库基础教程2007
3
本章要求
掌握 PL/SQL程序设计基础知识
掌握存储过程、函数、包、触发器的应用
Oracle 数据库基础教程2007
4
15.1 PL/SQL概述
PL/SQL特点
PL/SQL功能特性
PL/SQL执行过程与开发工具
Oracle 数据库基础教程2007
5
15.1.1PL/SQL特点
与 SQL语言紧密集成。
减小网络流量，提高应用程序的运行性能。
模块化的程序设计功能，提高了系统可靠性。
服务器端程序设计，可移植性好。
Oracle 数据库基础教程2007
6
15.1.2PL/SQL功能特性
语句块结构
异常处理
变量和类型
条件语句
循环结构
游标
过程、函数和触发器
包
集合
动态 SQL
对象特性
Oracle 数据库基础教程2007
7
15.1.3PL/SQL执行过程与开发工具
PL/SQL块
SQL语句客户端应用程序
PL/SQL引擎数据库服务器过程化语句执行器
SQL执行器块中 SQL语句
PL/SQL执行过程
Oracle 数据库基础教程2007
8
PL/SQL开发工具
SQL *PLUS
Procedure Builder
Oracle Form,Oracle Reports
PL/SQL Developer
Oracle 数据库基础教程2007
9
15.2 PL/SQL基础
PL/SQL程序结构
词法单元
数据类型
变量与常量
编译指示
PL/SQL中的 SQL语句
Oracle 数据库基础教程2007
10
PL/SQL程序结构
PL/SQL块的组成
PL/SQL块分类
Oracle 数据库基础教程2007
11
PL/SQL块的组成
PL/SQL语言以块为单位，块中可以嵌套子块。
一个基本的 PL/SQL块由 3部分组成：
声明（ DECLARE），
可执行部分 （ BEGIN）,
异常处理部分 EXCEPTION）。
Oracle 数据库基础教程2007
12
声明部分
声明部分以关键字 DECLARE开始,BEGIN结束。主要用于声明变量、常量、数据类型、游标、异常处理名称以及本地（局部）子程序定义等。
可执行部分
执行部分是 PL/SQL块的功能实现部分，以关键字 BEGIN开始，
EXCEPTION或 END结束（如果 PL/SQL块中没有异常处理部分，
则以 END结束）。该部分通过变量赋值、流程控制、数据查询、数据操纵、数据定义、事务控制、游标处理等实现块的功能。
异常处理部分
异常处理部分以关键字 EXCEPTION开始,END结束。该部分用于处理该块执行过程中产生的异常。
Oracle 数据库基础教程2007
13
注意：
执行部分是必需的，而声明部分和异常部分是可选的；
可以在一个块的执行部分或异常处理部分嵌套其他的 PL/SQL块；
所有的 PL/SQL块都是以? END;? 结束
Oracle 数据库基础教程2007
14
PL/SQL块分类
匿名块
命名块
函数
存储过程
包
触发器
Oracle 数据库基础教程2007
15
15.5.2词法单元
字符集
标识符
分隔符
常量值
注释
Oracle 数据库基础教程2007
16
字符集
大小写字母,A~Z,a~z
数字,0~9
空白：制表符,空格和回车
数字符号,+ - * / 〈 〉 =
标点符号,~ ! @ # $ % ^&* （） _ | { }
[ ]？ ；,,,? ‘
Oracle 数据库基础教程2007
17
标识符
标识符以字母开头,后边可以跟字母,数字,
货币符号,下划线和? #?
标识符的最大长度为 30字符,并且所有字符都是有效的 。
合法,X v_studentID TempVar
非法,X+y _temp
Oracle 数据库基础教程2007
18
+
-
*
/
=
,=
<
>
<=
>=
<>
!=
~=
^=
(
)
/*
*/
<<
>>
%;
,
,
,
,.
@
||
=>
**
-
分隔符
Oracle 数据库基础教程2007
19
常量值
字符型常量
数字型常量
布尔型常量,TURE,FALSE,NULL
日期型常量
Oracle 数据库基础教程2007
20
15.2.3数据类型
数字类型
字符类型
日期 /区间类型
行标识类型
布尔类型
原始类型
LOB类型
记录类型
集合类型
Oracle 数据库基础教程2007
21
PL/SQL中常用的基本数据类型分类 数据类型数字类型 NUMBER,BINARY_NUMBER
PLS_NUMBER
字符类型 VARCHAR2,CHAR,LONG、
NCHAR,NVARCHAR
日期 /区间类型 DATE,TIMESTAMP,INTERVAL
行标识类型 ROWID,UROWID
布尔类型 BOOLEAN（ TRUE,FALSE,NULL）
原始类型 RAW,LONG RAW
LOB类型 CLOB,BLOB,NCLOB,BFILE
记录类型 RECORD
集合类型 TABLE,VARRAY
Oracle 数据库基础教程2007
22
记录类型的定义
TYPE record_type IS RECORD(
field1 datatype1 [NOT
NULL][DEFAULT|:=expr1],
field2 datatype2 [NOT
NULL][ DEFAULT|:=expr2],
……
fieldn datatypen [NOT
NULL][ DEFAULT|:=exprn]);
Oracle 数据库基础教程2007
23
15.2.4变量与常量
变量与常量的定义
变量的作用域
Oracle 数据库基础教程2007
24
变量声明变量与常量的定义
变量定义的一般格式：
<variablename> [CONSTANT]<datatype>
[[NOT NULL] {DEFAULT|:=} <expression>];
说明
每行只能定义一个标识符。
如果加上关键字 CONSTANT,则表示所定义的标识符为一个常量，必须为它赋初值。
如果定义的标识符不能为空，则必须加上关键字 NOT
NULL,并赋初值。
为标识符赋值时，使用赋值符号 ‘,=’,默认值为空。
Oracle 数据库基础教程2007
25
DECLARE
v1 NUMBER(4);
v2 NUMBER(4) NOT NULL,=10;
v3 CONSTANT NUMBER(4) DEFAULT 100;
BEGIN
IF v1 IS NULL THEN
DBMS_OUTPUT.PUT_LINE('V1 IS NULL!');
END IF;
DBMS_OUTPUT.PUT_LINE(v2||' '||v3);
END;
Oracle 数据库基础教程2007
26
声明一个变量，使它的类型与某个变量或数据库基本表中某个列的数据类型一致，
可以使用 %TYPE。
示例
v_empno1 emp.empno%TYPE;
v_empno2 v_empno1%TYPE;
Oracle 数据库基础教程2007
27
变量的作用域
变量的作用域是指变量的有效作用范围，从变量声明开始，直到块结束。
如果 PL/SQL块相互嵌套，则在内部块中声明的变量是局部的，只能在内部块中引用，而在外部块中声明的变量是全局的，既可以在外部块中引用，
也可以在内部块中引用。
如果内部块与外部块中定义了同名变量，则在内部块中引用外部块的全局变量时需要使用外部块名进行标识。
Oracle 数据库基础教程2007
28
<<OUTER>>
DECLARE
v_ename CHAR(15);
v_outer NUMBER(5);
BEGIN
v_outer,=10;
DECLARE
v_ename CHAR(20);
v_inner DATE;
BEGIN
v_inner:=sysdate;
v_ename:='INNER V_ENAME';
OUTER.v_ename:='OUTER V_ENAME';
END;
DBMS_OUTPUT.PUT_LINE(v_ename);
END;
Oracle 数据库基础教程2007
29
15.2.5编译指示
编译指示是对编译程序发出的特殊指令，
也称伪指令。
关键字,PRAGMA
PL/SQL提供以下四种编译指示：
EXCEPTION_INIT
告诉编译程序将一个特定的错误号与程序中所声明的异常标识符关联起来。
RESTRICT_REFERENCES
告诉编译程序打包程序的纯度，即对函数中可以使用的 SQL语句和包变量进行限制。
Oracle 数据库基础教程2007
30
SERIALLY_REUSEABLE
告诉 PL/SQL运行时引擎，在数据引用之间不要保持包级数据。
AUTONOMOUS_TRANSACTION
告诉编译程序，该程序块为自治事务，即该事务的提交和回滚是独立进行的。
Oracle 数据库基础教程2007
31
15.2.6PL/SQL中 SQL语句
可以在 PL/SQL中执行的 SQL语句包括
SELECT
DML(UPDATE,DELETE,INSERT)
事务控制语句（ COMMIT,ROLLBACK、
SAVEPOINT）
注意 DDL语句不可以直接使用
Oracle 数据库基础教程2007
32
SELECT… INTO
SELECT… INTO语句只能查询一个记录的信息，如果没有查询到任何数据，则会产生
NO_DATA_FOUND异常；如果查询到多个记录，则会产生 TOO_MANY_ROW异常。
INTO句子后的变量用于接收查询的结果，变量的个数、顺序应该与查询的目标数据相匹配，
也可以是记录类型的变量。 DML语句
Oracle 数据库基础教程2007
33
DECLARE
v_emp emp%ROWTYPE;
v_ename emp.ename%type;
v_sal emp.sal%type;
BEGIN
SELECT * INTO v_emp FROM emp WHERE ename='SMITH';
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.sal);
select ename,sal INTO v_ename,v_sal FROM emp WHERE
empno=7900;
DBMS_OUTPUT.PUT_LINE(v_ename||' '||v_sal);
END;
Oracle 数据库基础教程2007
34
DML语句
PL/SQL中 DML语句对标准 SQL语句中的 DML语句进行了扩展，允许使用变量。
示例
DECLARE
v_empno emp.empno%TYPE,=7500;
BEGIN
INSERT INTO emp(empno,ename,sal,deptno)
VALUES(v_empno,'JOAN',2300,20);
UPDATE emp SET sal=sal+100 WHERE
empno=v_empno;
DELETE FROM emp WHERE empno=v_empno;
END;
Oracle 数据库基础教程2007
35
WHERE
标识符的区分
系统首先查看 WHERE子句中的标识符是否与表中的列名相同，
如果相同，则该标识符被解释为列名；如果没有同名列，系统检查该标识符是不是 PL/SQL语句块的变量。
字符串比较
填充比较：通过在短字符串后添加空格，使两个字符串达到相同长度，然后根据每个字符的 ASCII码进行比较。
非填充比较：根据每个字符的 ASCII码进行比较，最先结束的字符串为小。
PL/SQL中规定，对定长的字符串（ CHAR类型的字符串和字符串常量）采用填充比较；如果比较的字符串中有一个是变长字符串（ VARCHAR2类型的字符串），则采用非填充比较。
Oracle 数据库基础教程2007
36
RETURNING
如果要查询当前 DML语句操作的记录的信息，
可以在 DML语句末尾使用 RETURNING语句返回该记录的信息。
RETURNING语句的基本语法：
RETURNING select_list_item INTO
variable_list|record_variable;
Oracle 数据库基础教程2007
37
DECLARE
v_sal emp.sal%TYPE;
BEGIN
UPDATE emp SET sal=sal+100
WHERE empno=7844
RETURNING sal INTO v_sal;
DBMS_OUTPUT.PUT_LINE(v_sal);
END;
Oracle 数据库基础教程2007
38
15.3 控制结构
选择结构
循环结构
跳转结构
Oracle 数据库基础教程2007
39
15.3.1选择结构
IF语句
IF condition1 THEN statements1;
[ELSIF condition2 THEN statements2;]
……
[ELSE else_statements];
END IF;
注意
条件是一个布尔型变量或表达式，取值只能是 TRUE,FALSE,NULL。
Oracle 数据库基础教程2007
40
例如，输入一个员工号，修改该员工的工资，如果该员工为 10号部门，工资增加
100；若为 20号部门，工资增加 150；若为 30号部门，工资增加 200；否则增加
300。
Oracle 数据库基础教程2007
41
DECLARE
v_deptno emp.deptno%type;
v_increment NUMBER(4);
v_empno emp.empno%type;
BEGIN
v_empno:=&x;
SELECT deptno INTO v_deptno FROM emp WHERE
empno=v_empno;
IF v_deptno=10 THEN v_increment:=100;
ELSIF v_deptno=20 THEN v_increment:=150;
ELSIF v_deptno=30 THEN v_increment:=200;
ELSE v_increment:=300;
END IF;
UPDATE emp SET sal=sal+v_increment WHERE
empno=v_empno;
END;
Oracle 数据库基础教程2007
42
搜索式 CASE语句
基本语法
CASE
WHEN condition1 THEN statements1;
WHEN condition2 THEN statements2;
……
WHEN conditionn THEN statementsn;
[ELSE else_statements;]
END CASE;
Oracle 数据库基础教程2007
43
等值比较的 CASE语句
基本语法
CASE test_value
WHEN value1 THEN statements1;
WHEN value2 THEN statements2;
……
WHEN valuen THEN statementsn;
[ELSE else_ statements;]
END CASE;
Oracle 数据库基础教程2007
44
DECLARE
v_deptno emp.deptno%type;
v_increment NUMBER(4);
v_empno emp.empno%type;
BEGIN
v_empno:=&x;
SELECT deptno INTO v_deptno FROM emp WHERE
empno=v_empno;
CASE v_deptno
WHEN 10 THEN v_increment:=100;
WHEN 20 THEN v_increment:=150;
WHEN 30 THEN v_increment:=200;
ELSE v_increment:=300;
END CASE;
UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno;
END;
Oracle 数据库基础教程2007
45
根据输入的员工号，修改该员工工资。如果该员工工资低于 1000，则工资增加 200；
如果工资在 1000-2000之间，则增加 150；
如果工资在 2000-3000之间，则增加 100；
否则增加 50。
Oracle 数据库基础教程2007
46
DECLARE
v_sal emp.sal%type;
v_increment NUMBER(4);
v_empno emp.empno%type;
BEGIN
v_empno:=&x;
SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
CASE
WHEN v_sal<1000 THEN v_increment:=200;
WHEN v_sal<2000 THEN v_increment:=150;
WHEN v_sal<3000 THEN v_increment:=100;
ELSE v_increment:=50;
END CASE;
UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno;
END;
Oracle 数据库基础教程2007
47
15.3.2循环结构
简单循环
WHILE循环
FOR循环
Oracle 数据库基础教程2007
48
简单循环
语法
LOOP
sequence_of_statement;
EXIT [WHEN condition] ;
END LOOP;
注意：在循环体中一定要包含 EXIT语句，
否则程序进入死循环。
Oracle 数据库基础教程2007
49
例如，执行 CREATE TABLE temp_table(num_col
NUMBER,info_col CHAR(10)) 语句创建 temp_table表，然后利用循环向 temp_table表中插入 50条记录。程序为：
DECLARE
v_counter BINARY_INTEGER,= 1;
BEGIN
LOOP
INSERT INTO temp_table VALUES (v_Counter,'Loop
index');
v_counter,= v_counter + 1;
EXIT WHEN v_counter > 50;
END LOOP;
END;
Oracle 数据库基础教程2007
50
WHILE循环
基本语法
WHILE condition LOOP
sequence_of_statement;
END LOOP;
Oracle 数据库基础教程2007
51
例如，利用 WHILE循环向 temp_table表中插入 50条记录。程序为：
DECLARE
v_counter BINARY_INTEGER,=1;
BEGIN
WHILE v_counter <= 50 LOOP
INSERT INTO temp_table VALUES (v_counter,
'Loop index');
v_counter,= v_counter + 1;
END LOOP;
END;
Oracle 数据库基础教程2007
52
FOR循环
基本语法
FOR loop_counter IN [REVERSE]
low_bound..high_bound
LOOP
sequence_of_statement;
END LOOP;
注意：
循环变量不需要显式定义，系统隐含地将它声明为
BINARY_INTEGER变量；
系统默认时，循环变量从下界往上界递增计数，如果使用 REVERSE关键字，则表示循环变量从上界向下界递减计数；
循环变量只能在循环体中使用，不能在循环体外使用。
Oracle 数据库基础教程2007
53
例如，利用 FOR循环向 temp_table表中插入 50条记录。程序为：
BEGIN
FOR v_counter IN 1..50 LOOP
INSERT INTO temp_table VALUES
(v_counter,'Loop Index');
END LOOP;
END;
Oracle 数据库基础教程2007
54
15.3.3跳转结构
语法格式：
,标号,
…
GOTO 标号；
说明：
块内可以跳转，内层块可以跳到外层块，但外层块不能跳到内层。
IF语句不能跳入。不能从循环体外跳入循环体内。
不能从子程序外部跳到子程序中。
由于 goto语句的缺点，建议尽量少用甚至不用 goto
语句。
Oracle 数据库基础教程2007
55
15.4 游标
游标的概念及类型
显式游标
隐式游标
Oracle 数据库基础教程2007
56
DECLARE
v_counter BINARY_INTEGER,=1;
BEGIN
<<LABEL>>
INSERT INTO temp_table
VALUES (v_counter,'Loop index');
v_counter,= v_Counter + 1;
IF v_counter<=50 THEN
GOTO LABEL;
END IF;
END;
Oracle 数据库基础教程2007
57
15.4游标
游标的概念及类型
显式游标
隐式游标
Oracle 数据库基础教程2007
58
15.4.1游标的及类型
游标的 概念
游标（ CURSOR）是 Oracle系统在内存中开辟的一个工作区，在其中存放 SELECT
语句返回的查询结果。
使用游标时,SELECT语句查询的结果可以是单条记录，多条记录，也可以是零条记录。
游标工作区中，存在着一个指针
（ POINTER）,在初始状态它指向查询结果的首记录。
Oracle 数据库基础教程2007
59
游标的类型
显式游标
由用户定义、操作，用于处理返回多行数据的 SELECT查询。
隐式游标
由系统自动进行操作，用于处理 DML语句和返回单行数据的 SELECT查询。
Oracle 数据库基础教程2007
60
15.4.2显式游标
显式游标的操作
显式游标的属性
参数化显式游标
显式游标的检索
利用游标更新或删除数据
Oracle 数据库基础教程2007
61
显式游标的操作
步骤
定义游标
打开游标
检索游标
关闭游标
Oracle 数据库基础教程2007
62
定义游标
语法格式
CURSOR cursor_name IS select_statement ;
说明
游标必须在 PL/SQL块的声明部分进行定义；
游标定义时可以引用 PL/SQL变量，但变量必须在游标定义之前定义；
定义游标时并没有生成数据，只是将定义信息保存到数据字典中；
游标定义后，可以使用
cursor_name%ROWTYPE定义游标类型变量。
Oracle 数据库基础教程2007
63
打开游标
语法格式
OPEN cursor_name;
说明
检查变量的值
执行游标定义时对应的 SELECT语句，将查询结果检索到工作区中。
游标指针指向第一个元组
一旦游标打开，就无法再次打开，除非先关闭
如果游标定义中的变量值发生变化，则只能在下次打开游标时才起作用。
Oracle 数据库基础教程2007
64
检索游标
语法格式
FETCH cursor_name INTO
variable_list|record_variable;
说明
在使用 FETCH语句之前必须先打开游标
对游标第一次使用 FETCH语句时，游标指针指向第一条记录，因此操作的对象是第一条记录，
使用后，游标指针指向下一条记录。
游标指针只能向下移动，不能回退
INTO子句中的变量个数、顺序、数据类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应。
Oracle 数据库基础教程2007
65
关闭游标
语法格式
CLOSE cursor_name;
说明
游标所对应的内存工作区变为无效，释放与游标相关的系统资源。
Oracle 数据库基础教程2007
66
根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定。
Oracle 数据库基础教程2007
67
DECLARE
v_deptno emp.deptno%TYPE;
CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_deptno;
v_emp c_emp%ROWTYPE;
BEGIN
v_deptno:=&x;
OPEN c_emp;
LOOP
FETCH c_emp INTO v_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
v_emp.ename||' '||
v_emp.sal ||' '||
v_deptno);
END LOOP;
CLOSE c_emp;
END;
Oracle 数据库基础教程2007
68
显式游标的属性
%ISOPEN
布尔型。如果游标已经打开，返回 TRUE,否则为 FALSE。
%FOUND
布尔型，如果最近一次使用 FETCH语句，有返回结果则为 TRUE,否则为 FALSE;
%NOTFOUND
布尔型，如果最近一次使用 FETCH语句,没有返回结果则为 TRUE,否则为 FALSE;
%ROWCOUNT
数值型，返回到目前为止从游标缓冲区检索的元组数。
Oracle 数据库基础教程2007
69
参数化显式游标
参数化游标定义语法格式：
CURSOR cursor_name(parameter1
datatype[,parameter2 datatype… ])
IS select_statement
打开参数化游标的方法
OPEN cursor_name(
parameter1[,parameter2… ])
Oracle 数据库基础教程2007
70
注意：
定义游标时，只能指定参数的类型，而不能指定参数的长度、精度、刻度；
打开带参数的游标时，实参的个数和数据类型等必须与游标定义时形参个数和数据类型等相匹配。
Oracle 数据库基础教程2007
71
DECLARE
CURSOR c_emp(p_deptno emp.deptno%TYPE)IS
SELECT * FROM emp WHERE deptno=p_deptno;
v_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp(10);
LOOP
FETCH c_emp INTO v_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
END LOOP;
CLOSE c_emp;
OPEN c_emp(20);
LOOP
FETCH c_emp INTO v_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
END LOOP;
CLOSE c_emp;
END;
Oracle 数据库基础教程2007
72
显式游标的检索
利用简单循环检索游标
利用 WHILE循环检索游标
利用 FOR循环检索游标
Oracle 数据库基础教程2007
73
利用简单循环检索游标
DECLARE
CURSOR cursor_name IS SELECT… ;
BEGIN
OPEN cursor_name;
LOOP
FETCH… INTO… ;
EXIT WHEN cursor_name%NOTFOUND;
……
END LOOP;
CLOSE cursor_name;
END;
Oracle 数据库基础教程2007
74
利用简单循环统计并输出各个部门的平均工资。
DECLARE
CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal
FROM emp GROUP BY deptno;
v_dept c_dept_stat%ROWTYPE;
BEGIN
OPEN c_dept_stat;
LOOP
FETCH c_dept_stat INTO v_dept;
EXIT WHEN c_dept_stat%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||'
'||v_dept.avgsal);
END LOOP;
CLOSE c_dept_stat;
END;
Oracle 数据库基础教程2007
75
利用 WHILE循环检索游标
DECLARE
CURSOR cursor_name IS SELECT… ;
BEGIN
OPEN cursor_name;
FETCH… INTO… ;
WHILE cursor_name%FOUND LOOP
FETCH… INTO… ;
……
END LOOP;
CLOSE cursor;
END;
Oracle 数据库基础教程2007
76
利用 WHILE循环统计并输出各个部门的平均工资。
DECLARE
CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal
FROM emp GROUP BY deptno;
v_dept c_dept_stat%ROWTYPE;
BEGIN
OPEN c_dept_stat;
FETCH c_dept_stat INTO v_dept;
WHILE c_dept_stat%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||'
'||v_dept.avgsal);
FETCH c_dept_stat INTO v_dept;
END LOOP;
CLOSE c_dept_stat;
END;
Oracle 数据库基础教程2007
77
利用 FOR循环检索游标
DECLARE
CURSOR cursor_name IS SELECT… ;
BEGIN
FOR loop_variable IN cursor_name LOOP
……
END LOOP;
END;
Oracle 数据库基础教程2007
78
FOR循环说明
系统隐含地定义了一个数据类型为 %ROWTYPE的变量，并以此作为循环的计算器。
系统自动打开游标，不用显式地使用 OPEN语句打开；
系统重复地自动从游标工作区中提取数据并放入计数器变量中。
系统自动进行 %FOUND属性检查以确定是否有数据
当游标工作区中所有的记录都被提取完毕或循环中断时，系统自动地关闭游标。
Oracle 数据库基础教程2007
79
利用 FOR循环统计并输出各个部门的平均工资。
DECLARE
CURSOR c_dept_stat IS SELECT deptno,avg(sal)
avgsal FROM emp GROUP BY deptno;
BEGIN
FOR v_dept IN c_dept_stat LOOP
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||'
'||v_dept.avgsal);
END LOOP;
END;
Oracle 数据库基础教程2007
80
隐式 FOR游标
BEGIN
FOR v_emp IN
(select * from emp where deptno=10) LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
v_emp.ename);
END LOOP;
END;
Oracle 数据库基础教程2007
81
利用游标更新或删除数据
游标定义语法
CURSOR cursor_name IS
SELECT select_list_item FROM table FOR UPDATE
更新或修改数据的语法为：
UPDATE|DELETE…
WHERE CURRENT OF cursor_name
注意由于 COMMIT语句会释放会话拥有的任何锁，因此如果在检索游标的循环内使用 COMMIT语句会释放定义游标时对数据加的锁，从而导致利用游标修改或删除数据的操作失败。
Oracle 数据库基础教程2007
82
修改员工的工资，如果员工的部门号为 10，
工资提高 100；部门号为 20，工资提高
150；部门号为 30，工资提高 200；否则工资提高 250。
Oracle 数据库基础教程2007
83
DECLARE
CURSOR c_emp IS SELECT * FROM emp FOR UPDATE;
v_increment NUMBER;
BEGIN
FOR v_emp IN c_emp LOOP
CASE v_emp.deptno
WHEN 10 THEN v_increment:=100;
WHEN 20 THEN v_increment:=150;
WHEN 30 THEN v_increment:=200;
ELSE v_increment:=250;
END CASE;
UPDATE emp SET sal=sal+v_increment WHERE CURRENT OF c_emp;
END LOOP;
END;
Oracle 数据库基础教程2007
84
15.4.3隐式游标
用于处理 INSERT,UPDATE,DELETE和
SELECT… INTO语句
没有 OPEN,FETCH,CLOSE命令
属性
SQL%ISOPEN
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
Oracle 数据库基础教程2007
85
修改员工号为 1000的员工工资，将其工资增加 100。如果该员工不存在，
则向 emp表中插入一个员工号为 1000，工资为 1500的员工。
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=1000;
IF SQL%NOTFOUND THEN
INSERT INTO emp(empno,sal) VALUES(1000,1500);
END IF;
END;
或
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=1000;
IF SQL%ROWCOUNT=0 THEN
INSERT INTO emp(empno,sal) VALUES(1000,1500);
END IF;
END;
Oracle 数据库基础教程2007
86
15.5 异常处理
异常概述
异常处理过程
异常的传播
Oracle 数据库基础教程2007
87
15.5.1异常概述
Oracle错误处理机制
异常的类型
Oracle 数据库基础教程2007
88
Oracle错误处理机制
概念
一个错误对应一个异常，当错误产生时抛出相应的异常，并被异常处理器捕获，程序控制权传递给异常处理器，由异常处理器来处理运行时错误。
Oracle 数据库基础教程2007
89
异常的类型
预定义的 Oracle异常
非预定义的 Oracle异常
用户定义的异常
Oracle 数据库基础教程2007
90
预定义的异常异常情况名 错误代码 描述
CURSOR_ALREADY
_OPEN
ORA-06511 尝试打开已经打开的游标
INVALID_CURSOR ORA-01001 不合法的游标操作（如要打开已经关闭的游标）
NO_DATA_FOUND ORA-01403 没有发现数据
TOO_MANY_ROWS ORA-01422 一个 SELECT INTO语句匹配多个数据行
INVALID_NUMBER ORA-01722 转换成数字失败 （ ‘ X’）
VALUE_ERROR ORA-06502 截断、算法或转换错误，通常出现在赋值错误
ZERO_DIVIDE ORA-01476 除数为 0
ROWTYPE_MISMATCH ORA-06504 主机游标变量与 PL/SQL游标变量类型不匹配
Oracle 数据库基础教程2007
91
异常情况名 错误代码 描述
DUP_VAL_ON_INDEX ORA-00001 违反唯一性约束或主键约束
SYS_INVALID_ROWID ORA-01410 转换成 ROWID失败
TIMEOUT_ON_RESOUR
CE
ORA-00051 在等待资源中出现超时
LOGIN_DENIED ORA-01017 无效用户名 /密码
CASE_NOT_FOUND ORA-06592 没有匹配的 WHEN子句
NOT_LOGGED_ON ORA-01012 没有与数据库建立连接
STORAGE_ERROR ORA-06500 PL/SQL内部错误
PROGRAM_ERROR ORA-06501 PL/SQL内部错误
Oracle 数据库基础教程2007
92
异常情况名 错误代码 描述
ACCESS_INTO_NULL ORA-06530 给空对象属性赋值
COLLECTION_IS_NULL ORA-06531 对某 NULL PL/SQL表或可变数组试图应用集合方法，而不是 EXISTS
SELF_IS_NULL ORA-30625 调用空对象实例的方法
SUBSCRIPT_BEYOND_COUNT ORA-06533 对嵌套表或数组索引引用时超出集合中元素的数量
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 对嵌套表或可变数组索引的引用超出声明的范围
Oracle 数据库基础教程2007
93
非预定义异常
在语句块的声明部分声明一个异常名称
e_integrity EXCEPTION;
通过 PRAGMA EXCEPTION-INIT 将异常 与一个 Oracle错误号相关联,
PRAGMA EXCEPTION-
INIT(e_integrity.-2291)
在异常处理部分捕捉并处理异常：
WHEN e_integrity THEN,..
Oracle 数据库基础教程2007
94
用户自定义的异常
用户自定义异常必须在声明部分进行声明。
当异常发生时，系统不能自动触发，需要用户使用 RAISE语句。
在异常处理部分捕捉并处理异常。
Oracle 数据库基础教程2007
95
15.5.2异常处理过程
在声明部分为错误定义异常，包括非预定义异常和用户定义异常。
e_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(e_exceptioin,-
#####);
在执行过程中当错误产生时抛出与错误对应的异常。
RAISE user_define_exception；
在异常处理部分通过异常处理器捕获异常，并进行异常处理。
Oracle 数据库基础教程2007
96
异常的捕获与处理
异常处理器的基本形式为
EXCEPTION
WHEN exception1[OR excetpion2… ]THEN
sequence_of_statements1;
WHEN exceptioin3[OR exception4… ]THEN
sequence_of_statements2;
……
WHEN OTHERS THEN
sequence_of_statementsn;
END;
Oracle 数据库基础教程2007
97
注意,
一个异常处理器可以捕获多个异常，只需要在
WHEN子句中用 OR连接即可；
一个异常只能被一个异常处理器捕获，并进行处理。
Oracle 数据库基础教程2007
98
查询名为 SMITH的员工工资，如果该员工不存在，则输出,There is
not such an employee!”；如果存在多个同名的员工，则输出其员工号和工资。
DECLARE
v_sal emp.sal%type;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE ename='SMITH';
DBMS_OUTPUT.PUT_LINE(v_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is not such an emplyee!');
WHEN TOO_MANY_ROWS THEN
FOR v_emp IN (SELECT * FROM emp WHERE
ename='SMITH') LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.sal);
END LOOP;
END;
Oracle 数据库基础教程2007
99
删除 dept表中部门号为 10的部门信息，如果不能删除则输出,There are subrecords in emp table!”。
DECLARE
e_deptno_fk EXCEPTION;
PRAGMA EXCEPTION_INIT(e_deptno_fk,-2292);
BEGIN
DELETE FROM dept WHERE deptno=10;
EXCEPTION
WHEN e_deptno_fk THEN
DBMS_OUTPUT.PUT_LINE(' There are
subrecords in emp table!');
END;
Oracle 数据库基础教程2007
100
修改 7844员工的工资，保证修改后工资不超过 6000。
DECLARE
e_highlimit EXCEPTION;
v_sal emp.sal%TYPE;
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=7844
RETURNING sal INTO v_sal;
IF v_sal>6000 THEN
RAISE e_highlimit;
END IF;
EXCEPTION
WHEN e_highlimit THEN
DBMS_OUTPUT.PUT_LINE('The salary is too large!');
ROLLBACK;
END;
Oracle 数据库基础教程2007
101
OTHERS异常处理器
OTHERS异常处理器是一个特殊的异常处理器，可以捕获所有的异常。
通常,OTHERS异常处理器总是作为异常处理部分的最后一个异常处理器，负责处理那些没有被其他异常处理器捕获的异常。
Oracle 数据库基础教程2007
102
DECLARE
v_sal emp.sal%TYPE;
e_highlimit EXCEPTION;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE ename='JOAN';
UPDATE emp SET sal=sal+100 WHERE empno=7900;
IF v_sal>6000 THEN
RAISE e_highlimit;
END IF;
EXCEPTION
WHEN e_highlimit THEN
DBMS_OUTPUT.PUT_LINE('The salary is too large!');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('There is some wrong in selecting!');
END;
Oracle 数据库基础教程2007
103
可以通过两个函数来获取错误相关信息。
SQLCODE：返回当前错误代码。
如果是用户定义错误返回值为 1；
如果是 ORA-1403,NO DATA FOUND错误，返回值为 100；
其他 Oracle内部错误返回相应的错误号。
SQLERRM：返回当前错误的消息文本。
如果是 Oracle内部错误，返回系统内部的错误描述；
如果是用户定义错误，则返回信息文本为,User-
defined Exception”。
Oracle 数据库基础教程2007
104
DECLARE
v_sal emp.sal%TYPE;
e_highlimit EXCEPTION;
v_code NUMBER(6);
v_text VARCHAR2(200);
BEGIN
SELECT sal INTO v_sal FROM emp WHERE ename='JOAN';
UPDATE emp SET sal=sal+100 WHERE empno=7900;
IF v_sal>6000 THEN
RAISE e_highlimit;
END IF;
EXCEPTION
WHEN e_highlimit THEN
DBMS_OUTPUT.PUT_LINE('The salary is too large!');
ROLLBACK;
WHEN OTHERS THEN
v_code:=SQLCODE;
v_text:=SQLERRM;
DBMS_OUTPUT.PUT_LINE(v_code||' '||v_text);
END;
Oracle 数据库基础教程2007
105
15.5.3异常的传播
可执行部分异常的传播
如果当前语句块有该异常的处理器，则执行之，并且成功完成该语句块。然后，控制权传递到外层语句块。
如果当前语句块没有该异常的处理器，则通过在外层语句块中产生该异常来传播该异常。
然后，执行对外层语句块执行步骤 1。如果没有外层语句块，则该异常将传播到调用环境。
Oracle 数据库基础教程2007
106
DECLARE
v_sal emp.sal%TYPE;
BEGIN
BEGIN
SELECT sal INTO v_sal FROM emp WHERE ename='JOAN';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is not such an employee!');
END;
DBMS_OUTPUT.PUT_LINE('Now this is outputted by outer
block!');
END;
/
There is not such an employee!
Now this is outputted by outer block!
Oracle 数据库基础教程2007
107
DECLARE
v_sal emp.sal%TYPE;
BEGIN
BEGIN
SELECT sal INTO v_sal FROM emp WHERE deptno=10;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is not such an employee!');
END;
DBMS_OUTPUT.PUT_LINE('Now this is outputted by outer block!');
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('There are more than one employee!');
END;
/
There are more than one employee!
Oracle 数据库基础教程2007
108
声明部分异常的传播
声明部分的异常立刻传播到外层语句块，即使当前语句块有异常处理器。
异常处理部分的异常的传播
异常处理器中产生的异常，可以有 RAISE
语句显式产生，也可以通过运行时错误而隐含产生。异常立即被传播到外层语句块。
Oracle 数据库基础教程2007
109
BEGIN
DECLARE
v_number NUMBER(6),='ABC';
BEGIN
v_number:=10;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('This is outputted by inner block!');
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('This is outputted by outer block!');
END;
/
This is outputted by outer block!
Oracle 数据库基础教程2007
110
15.6 存储子程序
存储过程
函数
局部子程序
Oracle 数据库基础教程2007
111
存储子程序是指被命名的 PL/SQL块，以编译的形式存储在数据库服务器中，可以在应用程序中进行调用，是 PL/SQL程序模块化的一种体现。
存储子程序是以独立对象的形式存储在数据库服务器中，因此是一种全局结构，与之对应的是局部子程序，即嵌套在 PL/SQL
块中的局部过程和函数，其存储位置取决于其所在的父块的位置。
Oracle 数据库基础教程2007
112
存储过程
存储过程的创建
存储过程的调用
存储过程的管理
Oracle 数据库基础教程2007
113
存储过程的创建
CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter1_name [mode] datatype [DEFAULT|:=value]
[,parameter2_name [mode] datatype [DEFAULT|:=value],… ])
AS|IS
/*Declarative section is here */
BEGIN
/*Executable section is here*/
EXCEPTION
/*Exception section is here*/
END[procedure_name];
PROCEDURE
BODY
Oracle 数据库基础教程2007
114
参数模式
IN
当过程被调用时，实参值被传递给过程。在过程内，该参数起常数作用，可读不可写。调用结束，实参值不变。（默认参数类型）
OUT
当过程被调用时，实参值被忽略。在过程内，该参数起未初始化的变量作用，值为 NULL。过程内，该参数可读可写。 调用结束，形参赋给实参。
IN OUT
当过程被调用时，实参值被传递给过程。在过程内，该参数起已初始化变量作用，过程内，该参数可读可写。调用结束，形参赋给实参。
Oracle 数据库基础教程2007
115
参数限制
声明形参时不能定义形参的长度或精度、刻度
参数传递
IN参数为引用传递，即实参的指针被传递给形参；
OUT,IN OUT参数为值传递，即实参的值被复制给形参。
Oracle 数据库基础教程2007
116
创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。
CREATE OR REPLACE PROCEDURE show_emp(
p_deptno emp.deptno%TYPE)
AS
v_sal emp.sal%TYPE;
BEGIN
SELECT avg(sal) INTO v_sal FROM emp WHERE
deptno=p_deptno;
DBMS_OUTPUT.PUT_LINE(p_deptno||' '||'average salary
is:'||v_sal);
FOR v_emp IN (SELECT * FROM emp WHERE
deptno=p_deptno AND sal>v_sal) LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||'
'||v_emp.ename);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The department doesnt exists!');
END show_emp;
Oracle 数据库基础教程2007
117
通常，存储过程不需要返回值，如果需要返回一个值可以通过函数调用实现。但是，
如果希望返回多个值，可以使用 OUT或 IN
OUT模式参数来实现。
Oracle 数据库基础教程2007
118
创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。
CREATE OR REPLACE PROCEDURE return_deptinfo(
p_deptno emp.deptno%TYPE,
p_avgsal OUT emp.sal%TYPE,
p_count OUT emp.sal%TYPE)
AS
BEGIN
SELECT avg(sal),count(*) INTO p_avgsal,p_count
FROM emp
WHERE deptno=p_deptno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The department dont exists!');
END return_deptinfo;
Oracle 数据库基础教程2007
119
存储过程的调用
在 SQL*PLUS中调用
EXEC procedure_name(parameter_list)
EXECUTE show_emp(10)
在 PL/SQL块中调用
BEGIN
procedure_name(parameter_list);
END；
Oracle 数据库基础教程2007
120
DECLARE
v_avgsal emp.sal%TYPE;
v_count NUMBER;
BEGIN
show_emp(20);
return_deptinfo(10,v_avgsal,v_count);
DBMS_OUTPUT.PUT_LINE(v_avgsal||'
'||v_count);
END;
Oracle 数据库基础教程2007
121
存储过程的管理
修改存储过程
CREATE OR REPLACE PROCEDURE
重新编译存储过程
ALTER PROCEDURE procedure_name COMPILE；
删除存储过程
DROP PROCEDURE procedure_name名；
查看过程源代码
select text from user_source where
name=procedure_name ;
Oracle 数据库基础教程2007
122
15.6.2函数
函数概述
函数的创建
函数的调用
函数的管理
Oracle 数据库基础教程2007
123
函数概述
函数用于返回特定数据，可以返回一个或多个值。
在一个函数中必须包含一个或多个
RETURN 语句
函数调用是 PL/SQL表达式的一部分,而过程调用可以是一个独立的 PL/SQL语句
Oracle 数据库基础教程2007
124
函数的创建
CREATE [OR REPLACE] FUNCTION function_name
(parameter1_name [mode] datatype [DEFAULT|:=value]
[,parameter2_name [mode] datatype [DEFAULT|:=value],… ])
RETURN return_datatype
AS |IS
/*Declarative section is here */
BEGIN
/*Executable section is here*/
EXCEPTION
/*Exception section is here*/
END [function_name];
FUNCTION
BODY
Oracle 数据库基础教程2007
125
创建一个以部门号为参数，返回该部门最高工资的函数。
CREATE OR REPLACE FUNCTION return_maxsal
(p_deptno emp.deptno%TYPE)
RETURN emp.sal%TYPE
AS
v_maxsal emp.sal%TYPE;
BEGIN
SELECT max(sal) INTO v_maxsal FROM emp
WHERE deptno=p_deptno;
RETURN v_maxsal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The deptno is invalid!');
END return_maxsal;
Oracle 数据库基础教程2007
126
函数的调用
在 SQL语句中调用函数
在 PL/SQL中调用函数
Oracle 数据库基础教程2007
127
DECLARE
v_sal emp.sal%TYPE;
BEGIN
FOR v_dept IN (SELECT DISTINCT deptno FROM emp)
LOOP
v_sal:=return_maxsal(v_dept.deptno);
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_sal);
END LOOP;
END;
Oracle 数据库基础教程2007
128
函数的管理
修改函数
CREATE OR REPLACE FUNCTION function_name
重新编译存储过程
ALTER FUNCTION function_name COMPILE；
删除存储过程
DROP FUNCTION function_name ；
查看过程源代码
select text from user_source where
name= function_name;
Oracle 数据库基础教程2007
129
15.6.3局部子程序
局部子程序
嵌套在其他 PL/SQL块中的子程序。
只能在其定义的块内部被调用，而不能在其父块外被调用。
使用局部子程序时需要注意：
局部子程序只在当前语句块内有效；
局部子程序必须在 PL/SQL块声明部分的最后进行定义；
局部子程序必须在使用之前声明，如果是子程序间相互引用，则需要采用预先声明；
局部子程序可以重载。
Oracle 数据库基础教程2007
130
在一个块内部定义一个函数和一个过程。
函数以部门号为参数返回该部门的平均工资；过程以部门号为参数，输出该部门中工资低于部门平均工资的员工的员工号、
员工名。
Oracle 数据库基础教程2007
131
DECLARE
v_deptno emp.deptno%TYPE;
v_avgsal emp.sal%TYPE;
FUNCTION return_avgsal(p_deptno emp.deptno%TYPE)
RETURN emp.sal%TYPE
AS
v_sal emp.sal%TYPE;
BEGIN
SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno;
RETURN v_sal;
END return_avgsal;
PROCEDURE show_emp(p_deptno emp.deptno%TYPE)
AS
CURSOR c_emp IS
SELECT * FROM emp WHERE sal<return_avgsal(p_deptno);
BEGIN
FOR v_emp IN c_emp LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
END LOOP;
END show_emp;
BEGIN
v_deptno:=&x;
v_avgsal:=return_avgsal(v_deptno);
show_emp(v_deptno);
END;
Oracle 数据库基础教程2007
132
存储子程序与局部子程序区别在于：
存储子程序己经编译好放在数据库服务器端，
可以直接调用，而局部子程序存在于定义它的语句块中，在运行时先进行编译；
存储子程序不能重载，而局部子程序可以进行重载；
存储子程序可以被任意的 PL/SQL块调用，而局部子程序只能在定义它的块中被调用。
Oracle 数据库基础教程2007
133
在一个 PL/SQL块中重载两个过程，一个以员工号为参数，输出该员工信息；另一个以员工名为参数，输出员工信息。利用这两个过程分别查询员工号为 7902,7934，
以及员工名为 SMITH,FORD的员工信息 。
Oracle 数据库基础教程2007
134
DECLARE
PROCEDURE show_empinfo(p_empno emp.empno%TYPE)
AS
v_emp emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp FROM emp WHERE empno=p_empno;
DBMS_OUTPUT.PUT_LINE(v_emp.ename||' '||v_emp.deptno);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is not such an employee!');
END show_empinfo;
PROCEDURE show_empinfo(p_ename emp.ename%TYPE)
AS
v_emp emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp FROM emp WHERE ename=p_ename;
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.deptno);
Oracle 数据库基础教程2007
135
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is not such an employee!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('There are more than on employee!');
END show_empinfo;
BEGIN
show_empinfo(7902);
show_empinfo(7934);
show_empinfo('SMITH');
show_empinfo('FORD');
END ;
Oracle 数据库基础教程2007
136
存储子程序与局部子程序区别
存储子程序己经编译好放在数据库服务器端，可以直接调用，而局部子程序存在于定义它的语句块中，在运行时先进行编译；
存储子程序不能重载，而局部子程序可以进行重载；
存储子程序可以被任意的 PL/SQL块调用，
而局部子程序只能在定义它的块中被调用
Oracle 数据库基础教程2007
137
15.7 包
包概述
包的创建
包的调用
包的重载
包的初始化
包的管理
Oracle 数据库基础教程2007
138
包概述
包是包含一个或多个子程序单元（过程、
函数等）的容器
包是全局的
包类型
数据库内置包
用户创建的包
包由包规范和包体两部分组成，在数据库中独立存储
Oracle 数据库基础教程2007
139
包规范声明了软件包中所有内容，如过程、
函数、游标、类型、异常和变量等，其中过程和函数只包括原型信息，不包含任何子程序代码。
包体中包含了在包头中的过程和函数的实现代码。包体中还可以包括在规范中没有声明的变量、游标、类型、异常、过程和函数，
但是它们是私有元素，只能由同一包体中其他过程和函数使用。
Oracle 数据库基础教程2007
140
创建包规范
语法
CREATE OR REPLACE PACKAGE package_name
IS|AS
[PRAGMA SERIALLY_RESUABLE]
type_definition|variable_declaration|exception
_declaration|cursor_declaration| procedure_
declaration |function_ declaration
END [package_name];
Oracle 数据库基础教程2007
141
注意：
元素声明的顺序可以是任意的，但必须先声明后使用；
所有元素是可选的；
过程和函数的声明只包括原型，不包括具体实现。
Oracle 数据库基础教程2007
142
创建一个软件包，包括 2个变量,2个过程和 1个异常。
CREATE OR REPLACE PACKAGE pkg_emp
AS
minsal NUMBER;
maxsal NUMBER;
e_beyondbound EXCEPTION;
PROCEDURE update_sal(
p_empno NUMBER,p_sal NUMBER);
PROCEDURE add_employee(
p_empno NUMBER,p_sal NUMBER);
END pkg_emp;
Oracle 数据库基础教程2007
143
语法
CREATE OR REPLACE PACKAGE BODY
package_name
IS|AS
[PRAGMA SERIALLY_RESUABLE]
type_definition|variable_declaration|
exception_declaration|
cursor_declaration|
procedure_definition |
function_definition
END [package_name];
Oracle 数据库基础教程2007
144
注意：
包体中函数和过程的原型必须与包规范中的声明完全一致；
只有在包规范已经创建的条件下，才可以创建包体；
如果包规范中不包含任何函数或过程，则可以不创建包体。
Oracle 数据库基础教程2007
145
CREATE OR REPLACE PACKAGE BODY pkg_emp
AS
PROCEDURE update_sal(p_empno NUMBER,p_sal NUMBER)
AS
BEGIN
SELECT min(sal),max(sal) INTO minsal,maxsal FROM emp;
IF p_sal BETWEEN minsal AND maxsal THEN
UPDATE emp SET sal=p_sal WHERE empno=p_empno;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20000,'The employee
doesn''t exist');
END IF;
ELSE
RAISE e_beyondbound;
END IF;
EXCEPTION
WHEN e_beyondbound THEN
DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');
END update_sal;
Oracle 数据库基础教程2007
146
PROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER)
AS
BEGIN
SELECT min(sal),max(sal) INTO minsal,maxsal FROM emp;
IF p_sal BETWEEN minsal AND maxsal THEN
INSERT INTO emp(empno,sal) VALUES(p_empno,p_sal);
ELSE
RAISE e_beyondbound;
END IF;
EXCEPTION
WHEN e_beyondbound THEN
DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');
END add_employee;
END pkg_emp;
Oracle 数据库基础教程2007
147
15.7.2包的调用
概念
指软件包中特定的元素或结构的可视范围。
在软件包头部声明的任何元素是公有的，在包外都是可见的 。
包外：通过 package.element形式调用；
包内：直接通过元素名进行调用。
在包体中定义而没有在包头中声明的元素是私有的，只能在包体中引用。
Oracle 数据库基础教程2007
148
调用软件包 pkg_emp中的过程 update_sal，修改
7844员工工资为 3000。调用 add_employee添加一个员工号为 1357，工资为 4000的员工。
BEGIN
pkg_emp.update_sal(7844,3000);
pkg_emp.add_employee(1357,4000);
END;
Oracle 数据库基础教程2007
149
包的重载
重载子程序必须同，即名称相同，参数不同。
如果两个子程序参数只是名称和模式不同，则不能重载。
PROCEDURE overloadme(parameter1 IN NUMBER);
PROCEDURE overloadme(parameter2 OUT NUMBER);
不能根据两个函数返回类型不同对它们进行重载。
FUNCTION overloadme RETURN DATE;
FUNCTION overloadme RETURN NUMBER;
重载子程序参数必须在类型系列方面有所不同。
PROCEDURE overloadchar(parameter IN CHAR);
PROCEDURE overloadchar(parameter IN VARCHAR2);
Oracle 数据库基础教程2007
150
在一个包中重载两个过程，分别以部门号和部门名称为参数，查询相应部门员工名、
员工号信息。
Oracle 数据库基础教程2007
151
CREATE OR REPLACE PACKAGE pkg_overload
AS
PROCEDURE show_emp(p_deptno NUMBER);
PROCEDURE show_emp(p_dname VARCHAR2);
END pkg_overload;
CREATE OR REPLACE PACKAGE BODY pkg_overload
AS
PROCEDURE show_emp(p_deptno NUMBER)
AS
BEGIN
FOR v_emp IN (SELECT * FROM emp WHERE
deptno=p_deptno) LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
v_emp.ename);
END LOOP;
END show_emp;
Oracle 数据库基础教程2007
152
PROCEDURE show_emp(p_dname VARCHAR2)
AS
v_deptno NUMBER;
BEGIN
SELECT deptno INTO v_deptno FROM dept
WHERE dname=p_dname;
FOR v_emp IN (SELECT * FROM emp WHERE
deptno=v_deptno) LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
v_emp.ename);
END LOOP;
END show_emp;
END pkg_overload;
Oracle 数据库基础教程2007
153
15.7.4包的初始化
包在第一次被调用时从磁盘读取到共享池，
并在整个会话的持续期间保持。在此过程中，可以自动执行一个初始化过程，对软件包进行实例化。
包的初始化过程只在包第一次被调用时执行，因此也称为一次性过程，它是一个匿名的 PL/SQL块，在包体结构的最后，以
BEGIN开始。
Oracle 数据库基础教程2007
154
在 pkg_emp包中，可以在包初始化时给 minsal
和 maxsal两个变量赋值，而在子程序中直接引用这两个变量。
CREATE OR REPLACE PACKAGE pkg_emp
AS
minsal NUMBER;
maxsal NUMBER;
e_beyondbound EXCEPTION;
PROCEDURE update_sal(
p_empno NUMBER,p_sal NUMBER);
PROCEDURE add_employee(
p_empno NUMBER,p_sal NUMBER);
END pkg_emp;
Oracle 数据库基础教程2007
155
CREATE OR REPLACE PACKAGE BODY pkg_emp
AS
PROCEDURE update_sal(p_empno NUMBER,p_sal NUMBER)
AS
BEGIN
IF p_sal BETWEEN minsal AND maxsal THEN
UPDATE emp SET sal=p_sal WHERE empno=p_empno;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20000,'The employee
doesn''t exist');
END IF;
ELSE
RAISE e_beyondbound;
END IF;
EXCEPTION
WHEN e_beyondbound THEN
DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');
END update_sal;
Oracle 数据库基础教程2007
156
PROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER)
AS
BEGIN
IF p_sal BETWEEN minsal AND maxsal THEN
INSERT INTO emp(empno,sal) VALUES(p_empno,p_sal);
ELSE
RAISE e_beyondbound;
END IF;
EXCEPTION
WHEN e_beyondbound THEN
DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');
END add_employee;
BEGIN
SELECT min(sal),max(sal) INTO minsal,maxsal FROM emp;
END pkg_emp;
Oracle 数据库基础教程2007
157
15.7.5包的管理
包的修改
CREATE OR REPLACE PACKAGE 包名
重新编译包
ALTER PACKAGE package_name COMPILE;
ALTER PACKAGE package_name COMPILE
SPECIFICATION;
ALTER PACKAGE package_name COMPILE
BODY;
删除包
DROP PACKAGE package_name ;
DROP PACKAGE BODY package_name ;
Oracle 数据库基础教程2007
158
查看包源代码
select text from user_source
where name=‘ EMP_PACKAGE’
and type=‘ PACKAGE’ ;
Oracle 数据库基础教程2007
159
15.8 触发器
触发器概述
DML触发器
INSTEAD-OF触发器
系统触发器
触发器的管理
Oracle 数据库基础教程2007
160
触发器概述
触发器的概念
触发器是命名块的一种。
触发器的执行是自动进行的，当相应事件发生时就会激发触发器的执行。
触发器不接受任何参数
Oracle 数据库基础教程2007
161
触发器（ TRIGGER）作用
维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束
通过记录已进行的改变及是谁进行了该项改变来检查一个表中的信息。
当一个表发生改变时，自动向其他程序发送需要采取行动的信号
在一个发布 -预定环境中发布关于各种事件的信息。
Oracle 数据库基础教程2007
162
触发器类型
DML触发器
INSERT,DELETE,UPDATE
INSTEAD-OF 触发器
只可以定义为视图的触发器
系统触发器
数据库启动或关闭之类的系统事件发生时触发
在执行诸如创建表之类的 DDL操作时触发
Oracle 数据库基础教程2007
163
触发器组成
触发器由触发器头部和触发器体两个部分组成，
主要包括以下参数：
作用对象：表、视图、数据库、模式
触发事件,DML,DDL、数据库系统事件
触发时间,BEFORE,AFTER
触发级别：语句级、行级
触发条件,WHEN条件
触发操作,SQL语句,PL/SQL块
Oracle 数据库基础教程2007
164
15.8.2 DML触发器
DML触发器的种类以及执行顺序
语句级前触发器
行级前触发器
DML操作（触发事件）
行级后触发器
语句级后触发器
Oracle 数据库基础教程2007
165
创建 DML触发器
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE|AFTER triggering_event [OF column_name]
ON table_name
[FOR EACH ROW]
[WHEN trigger_condition]
DECLARE
/*Declarative section is here */
BEGIN
/*Exccutable section si here*/
EXCEPTION
/*Exception section is here*/
END [trigger_name];
Trigger_body
Oracle 数据库基础教程2007
166
创建一个触发器，禁止在休息日改变雇员信息，
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate,'DY') in ('星期六 ','星期日 ') then
raise_application_error(-20001,'不能在休息日修改员工信息 ');
end if;
end;
Oracle 数据库基础教程2007
167
判断当前执行的触发器到底是有那个 DML操作激发的。
谓词 行为
INSERTING 如果触发语句是 INSERT，则为 TRUE；
否则为 FALSE
UPDATING 如果触发语句是 UPDATE，则为 TRUE；
否则为 FALSE
DELETING 如果触发语句是 DELETE，则为 TRUE；
否则为 FALSE
Oracle 数据库基础教程2007
168
为 emp表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；
当执行删除操作时，统计删除后各个部门的人数。
Oracle 数据库基础教程2007
169
CREATE OR REPLACE TRIGGER trg_emp_dml
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
v_count NUMBER;
v_sal NUMBER(6,2);
BEGIN
IF INSERTING THEN
SELECT count(*) INTO v_count FROM emp;
DBMS_OUTPUT.PUT_LINE(v_count);
ELSIF UPDATING THEN
SELECT avg(sal) INTO v_sal FROM emp;
DBMS_OUTPUT.PUT_LINE(v_sal);
ELSE
FOR v_dept IN (SELECT deptno,count(*) num FROM emp
GROUP BY deptno) LOOP
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.num);
END LOOP;
END IF;
END trg_emp_dml;
Oracle 数据库基础教程2007
170
行级触发器
是指执行 DML操作时，每操作一记录，触发器就执行一次，一个 DML操作涉及到多少个记录，
触发器就执行多少次。
在行级触发器中可以使用 WHEN条件，进一步控制触发器的执行。
在触发器体中，可以对当前操作的记录进行访问和操作。
Oracle 数据库基础教程2007
171
标识符
:OLD,,NEW
引用方式：
:old.field和,new.field （执行部分）
old.field 和 new.field (WHEN条件中 )
在不同操作中的意义触发语句,old,new
INSERT 未定义，所有字段都为
NULL
当语句完成时，将要被插入的值
UPDATE 更新前行的原始值 当语句完成时，将要被更新的值
DELETE 行被删除前的原始值 未定义，所有字段都为 NULL
Oracle 数据库基础教程2007
172
注意事项：
是伪记录，不能作为整个记录进行赋值或引用
不能传递给带
triggering_table%ROWTYPE参数的过程和函数
如果触发器是建立在嵌套表上,;old和 ;new都执行嵌套表的行,:parent指向父表中的当前行。
Oracle 数据库基础教程2007
173
为 emp表创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。
Oracle 数据库基础教程2007
174
CREATE OR REPLACE TRIGGER trg_emp_dml_row
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE(:new.empno||' '||
,new.ename);
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE(:old.sal||' '||:new.sal);
ELSE
DBMS_OUTPUT.PUT_LINE(:old.empno||' '||
,old.ename);
END IF;
END trg_emp_dml_row;
Oracle 数据库基础教程2007
175
在行级触发器中，可以使用 WHEN子句进一步控制触发器的执行。
修改员工工资时，保证修改后的工资高于修改前的工资。
CREATE OR REPLACE TRIGGER trg_emp_update_row
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN(new.sal<=old.sal)
BEGIN
RAISE_APPLICATION_ERROR(
-20001,'The salary is lower!');
END trg_emp_update_row;
Oracle 数据库基础教程2007
176
创建一个触发器，在修改 dept表的部门号时，同时更新 emp表中相应的员工的部门号。
create or replace trigger tr_update_dept
after update of deptno on dept
for each row
begin
update emp set deptno=:new.deptno
where deptno=:old.deptno;
end;
Oracle 数据库基础教程2007
177
15.8.3INSTEAD OF触发器
特点
只能定义在视图上
Instead-of触发器是行级触发器
Instead-of 触发器由 DML操作激发，而
DML操作本身并不执行
作用
修改一个本来不可以修改的视图
修改视图中某嵌套表列的列
Oracle 数据库基础教程2007
178
如果视图中包含下列任何一项,则该视图不可修改
集合操作符
聚集函数
GROUP BY,CONNECT BY 或 START WITH子句
DISTINCT
连接 （ 部分包含连接的视图 ）
Oracle 数据库基础教程2007
179
创建 INSTEAD OF触发器的基本语法
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF triggering_event [OF column_name]
ON view_name
FOR EACH ROW
[WHEN trigger_condition]
DECLARE
/*Declarative section is here */
BEGIN
/*Exccutable section si here*/
EXCEPTION
/*Exception section is here*/
END [trigger_name];
Oracle 数据库基础教程2007
180
INSTEAD OF 示例
创建一个包括员工及其所在部门信息的视图
empdept，然后向视图中插入一条记录
（ 2345,?TOM?,3000,?SALES?）。
创建视图
CREATE OR REPLACE VIEW empdept
AS
SELECT empno,ename,sal,dname FROM
emp,dept WHERE emp.deptno=dept.deptno
WITH CHECK OPTION;
创建触发器
Oracle 数据库基础教程2007
181
CREATE OR REPLACE TRIGGER trig_view
INSTEAD OF INSERT ON empdept
FOR EACH ROW
DECLARE
v_deptno dept.deptno%type;
BEGIN
SELECT deptno INTO v_deptno FROM dept
WHERE dname=:new.dname;
INSERT INTO emp(empno,ename,sal,deptno)
VALUES(:new.empno,:new.ename,v_deptno,:ne
w.sal);
END trig_view;
Oracle 数据库基础教程2007
182
15.8.4系统触发器
触发器事件
创建系统触发器
Oracle 数据库基础教程2007
183
系统触发器事件
系统触发器事件
DDL事件
CREATE,ALTER,DROP）
数据库事件触发
服务器启动 /关闭、用户登陆 /注销以及服务器错误
Oracle 数据库基础教程2007
184
事件 允许计时 描述
STARTUP AFTER 当实例开始时激发
SHUTDOWN BEFORE 当实例关闭时激发
SERVERERROR AFTER 只要错误发生就激发
LOGON AFTER 在一个用户成功连接数据库时触发
LOGOFF BEFORE 在用户注销时开始激发
CREATE BEFORE,AFTER 创建一个模式对象之前或之后激发
DROP BEFORE,AFTER 在删除一个模式对象之前或之后激发
ALTER BEFROE,AFTER 在更改一个模式对象之前或之后激发
Oracle 数据库基础教程2007
185
创建系统触发器的语法
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE|AFTER
ddl_event_list|database_event_list
ON DATABASE|SCHEMA
[WHEN trigger_condition]
DECLARE
/*Declarative section is here */
BEGIN
/*Executable section is here*/
EXCEPTION
/*Exception section is here*/
END [trigger_name];
Oracle 数据库基础教程2007
186
创建系统触发器示例
基于 DDL事件触发器
基数数据库事件的触发器
Oracle 数据库基础教程2007
187
系统触发器模式 （ DATABASE,SCHEMA）
只要触发事件发生，数据库级别的触发器就将激发。
只有当触发事件以指定模式发生时，模式级别的触发器才会激发。
如果没有用 SCHEMA关键词指定，却省为拥有该触发器的模式
事件属性函数
是 SYS所拥有的独立的 PL/SQL函数，返回触发器事件信息
要引用事件属性函数，必须用 SYS作为其前缀
Oracle 数据库基础教程2007
188
将每个用户的登录信息写入到 temp_table表中。
CREATE OR REPLACE TRIGGER log_user_connection
AFTER LOGON
ON DATABASE
BEGIN
INSERT INTO scott.temp_table
VALUES (user,sysdate);
END log_user_connection;
Oracle 数据库基础教程2007
189
当数据库中执行 CREATE操作时，将创建的对象信息记录到 ddl_creations表中。
CREATE TABLE ddl_creations (
user_id VARCHAR2(30),
object_type VARCHAR2(20),
object_name VARCHAR2(30),
object_owner VARCHAR2(30),
creation_date DATE);
CREATE OR REPLACE TRIGGER log_creations
AFTER CREATE ON DATABASE
BEGIN
INSERT INTO ddl_creations
VALUES(USER,SYS.DICTIONARY_OBJ_TYPE,
SYS.DICTIONARY_OBJ_NAME,
SYS.DICTIONARY_OBJ_OWNER,SYSDATE);
END log_creations;
Oracle 数据库基础教程2007
190
15.8.5触发器的管理
触发器名称
触发器存在于单独的名字空间中，在一个模式中可以与其他对象同名
触发器的限制
不能出现任何事务控制语句。因为触发器作为触发语句执行的一部分，处于同一个事务中。
触发器体所调用的过程或函数都不能发出任何事务控制语句（自治事务子程序除外）。
触发器体中不能声明 LONG 或 LONG RAW变量，而且,new
和,old不能引用 LONG或 LONG RAW类型的列。
触发器体中可以引用 LOB和 OBJECT列，但不能修改该列的值。
触发器的大小不能超过 32K。
Oracle 数据库基础教程2007
191
修改触发器
CREATE OR REPLACE TRIGGER trigger_name
重新编译触发器
ALTER TRIGGER trigger_name COMPILE;
禁用、启用触发器
ALTER TRIGGER trigger_name DISALBLE|ENABLE
禁用、启用某个表相关的所有触发器：
ALTER TABLE table_name
DISABLE|ENABLE ALL TRIGGERS；
删除触发器
DROP TRIGGER trigger_name;
Oracle 数据库基础教程2007
192
小结
PL/SQL概述
PL/SQL基础
控制结构
游标
异常处理
存储子程序
包
触发器
