第14章PLSQL程序设计

1、大型数据库系统大型数据库系统1第第1414章章 PL/SQLPL/SQL程序设计程序设计大型数据库系统大型数据库系统2本章内容本章内容nPL/SQL概述概述nPL/SQL基础基础n控制结构控制结构n游标游标n异常处理异常处理n存储子程序存储子程序n包包n触发器触发器大型数据库系统大型数据库系统3本章要求本章要求n掌握掌握PL/SQL程序设计基础知识程序设计基础知识n掌握存储过程、函数、包、触发器的应用掌握存储过程、函数、包、触发器的应用大型数据库系统大型数据库系统414.1 PL/SQL14.1 PL/SQL概述概述nPL/SQL特点特点nPL/SQL功能特性功能特性nPL/SQL执行过程与开

2、发工具执行过程与开发工具大型数据库系统大型数据库系统5为什么需要为什么需要PL/SQLnPL/SQL：针对：针对ORACLE的编程语言的编程语言nSQL语言的缺点：仅限于数据库的操作，语言的缺点：仅限于数据库的操作，没有逻辑控制结构没有逻辑控制结构n无法实现复杂的无法实现复杂的数据结构、算法数据结构、算法，无法进行复，无法进行复杂的杂的业务逻辑业务逻辑。n编程语言编程语言+SQL？n高级语言：高级语言：C+（Java）+SQLn脚本语言：脚本语言：PHP（ASP）+SQL大型数据库系统大型数据库系统6“编程语言编程语言+SQL”的缺点的缺点exec(select * from tbMath);

3、for (每条记录每条记录) if(女女) exec(update set xx= where id=) ; else if(男男) exec(update set xx= where id=) ; 修改学生数学成绩：女生加修改学生数学成绩：女生加10分，男生加分，男生加15分。分。大型数据库系统大型数据库系统7客户端客户端Oracleselect查询结果查询结果更新成功？更新成功？update 1update更新成功？更新成功？update n大型数据库系统大型数据库系统8普通程序执行过程普通程序执行过程n普通程序执行过程普通程序执行过程n客户端通过网络发送客户端通过网络发送select语句

4、到语句到Oracle，Oracle编编译、执行该语句译、执行该语句nOracle将查询结果网络发送到客户端将查询结果网络发送到客户端n客户端逐条判断查询结果，逐条发送客户端逐条判断查询结果，逐条发送update语句语句nOracle通过网络接收通过网络接收update语句，编译、执行语句，编译、执行n客户端客户端Oraclen1条条Select+n条条update：编译、执行编译、执行nOracle客户端客户端：n1条查询结果条查询结果+n条更新结果：其中，条更新结果：其中，查询结果数据量查询结果数据量可能很大，且通常需要可能很大，且通常需要网络传输网络传输n普通数据库程序：普通数据库程序：程

5、序执行效率差程序执行效率差大型数据库系统大型数据库系统9客户端客户端Oracle成功？成功？PL/SQL大型数据库系统大型数据库系统10PL/SQL执行过程执行过程nPL/SQL程序：程序：n只进行一次网络传输只进行一次网络传输n在在ORACLE端编译；端编译；n执行过程在服务器端进行。执行过程在服务器端进行。程序执行过程中：程序执行过程中：n不需要网络传输不需要网络传输n不占用客户端资源：不占用客户端资源：CPU、内存、内存n编译：编译：PL/SQL程序分两类程序分两类n普通程序：普通程序：一次编译一次编译n存储过程存储过程：预编译预编译，执行时不需要编译，执行时不需要编译大型数据库系统大型

6、数据库系统1114.1.1 PL/SQL14.1.1 PL/SQL特点特点n服务器端程序（存储过程）：服务器端程序（存储过程）：永久驻留在永久驻留在服务器端服务器端n减小网络流量，提高应用程序的运行性能。减小网络流量，提高应用程序的运行性能。n可移植性好：可移植性好：只限于只限于Oraclen专门针对专门针对ORACLE的程序设计语言的程序设计语言n结构化的程序设计风格结构化的程序设计风格n与与SQL语言紧密集成。语言紧密集成。大型数据库系统大型数据库系统1214.1.2 PL/SQL14.1.2 PL/SQL功能特性功能特性n语法：语法：n语句块结构语句块结构n异常处理异常处理n变量和类型变

7、量和类型n条件语句条件语句n循环结构循环结构n游标游标n过程过程/函数函数包包n触发器触发器大型数据库系统大型数据库系统1314.1.3 PL/SQL14.1.3 PL/SQL执行过程与开发工具执行过程与开发工具PL/SQL块块SQL语句语句客户端应用程序客户端应用程序PL/SQL引擎引擎数据库服务器数据库服务器过程化语句执行器过程化语句执行器SQL执行器执行器块中块中SQL语句语句nPL/SQL执行过程执行过程 大型数据库系统大型数据库系统14nPL/SQL开发工具开发工具nSQL *PLUSnProcedure BuildernOracle Form、Oracle ReportsnPL/S

8、QL Developer大型数据库系统大型数据库系统1514.2.1 PL/SQL14.2.1 PL/SQL程序结构程序结构nPL/SQL块的组成块的组成nPL/SQL块分类块分类大型数据库系统大型数据库系统16nPL/SQL语言以程序块为单位。语言以程序块为单位。 n块可以是一个块可以是一个独立程序，独立程序，也可以也可以嵌入其他块嵌入其他块nPL/SQL块的组成块的组成n一个基本的一个基本的PL/SQL块由块由3部分组成：部分组成：n声明（声明（DECLARE）n可执行部分可执行部分（BEGIN）n异常处理部分异常处理部分EXCEPTION）大型数据库系统大型数据库系统17示例示例1：完整

9、的：完整的PL/SQL块块DECLARE v_ename VARCHAR2(10);BEGIN SELECT ename INTO v_ename FROM emp WHERE empno=7844; DBMS_OUTPUT.PUT_LINE(v_ename);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(There is not such an employee);END;大型数据库系统大型数据库系统18n声明部分：声明部分：关键字关键字DECLARE开始，开始，BEGIN结束结束n声明声明变量变量、常量、数据、常量、数据类型类

10、型、游标、游标n异常处理异常处理名称、名称、局部子程序局部子程序定义。定义。 n可执行部分：可执行部分：关键字关键字BEGIN开始，开始，EXCEPTION或或END结束结束n实现块的功能：变量赋值、流程控制、数据查询、数据实现块的功能：变量赋值、流程控制、数据查询、数据操纵、数据定义、事务控制、游标处理等。操纵、数据定义、事务控制、游标处理等。n异常处理部分：异常处理部分：关键字关键字EXCEPTION开始，开始，END结束。结束。n处理块执行过程中产生的处理块执行过程中产生的异常异常。 大型数据库系统大型数据库系统19n注意：注意：n执行部分是必需的，而执行部分是必需的，而声明部分和异常部

11、分是声明部分和异常部分是可选的可选的；n所有的所有的PL/SQL块都是以块都是以“END;”结束结束 n在一个块的在一个块的执行部分或异常处理部分执行部分或异常处理部分，可以，可以嵌嵌套套其他其他PL/SQL块；块；大型数据库系统大型数据库系统20示例示例2：只包含执行部分的：只包含执行部分的PL/SQL块块BEGIN DBMS_OUTPUT.PUT_LINE(hello world);END;n在在SQL Plus*环境下环境下SQLset SERVEROUTPUT ONn可以输出可以输出汉字汉字大型数据库系统大型数据库系统21示例示例1：包含子块的：包含子块的PL/SQL块块DECLARE

12、 v_sal NUMBER(6,2); v_deptno NUMBER(2);BEGIN BEGIN SELECT deptno INTO v_deptno FROM emp WHERE empno=7844; END; SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=v_deptno; DBMS_OUTPUT.PUT_LINE(v_sal);END;子块也可以子块也可以包含包含完整的完整的三部分三部分大型数据库系统大型数据库系统22nPL/SQL程序分类程序分类n匿名块：匿名块：只执行一次只执行一次的程序的程序n命名块：命名块：存储在服务器端

13、的存储在服务器端的程序程序n函数函数n过程过程n包包n触发器：触发器：自动执行（不可控制）自动执行（不可控制）大型数据库系统大型数据库系统23例：命名块例：命名块CREATE OR REPLACE PROCEDURE showavgsal(p_deptno NUMBER)IS v_sal NUMBER(6,2);BEGIN SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno; DBMS_OUTPUT.PUT_LINE(v_sal);END showavgsal; 大型数据库系统大型数据库系统24可以用可以用CREATE OR RE

14、PLACE的对象的对象n可以：可以：nFUNCTION、PROCEDURE、PACKAGEnTYPEnSYNONYMnTRIGGERnVIEWn不能：不能：nTABLEnSEQUENCE大型数据库系统大型数据库系统25.2词法单元词法单元n字符集字符集n标识符标识符n分隔符分隔符n常量值常量值n注释注释大型数据库系统大型数据库系统26n字符集字符集n大小写字母：大小写字母：AZ，azn数字：数字：09n空白：制表符、空格和回车空白：制表符、空格和回车n数字符号：数字符号：+ - * / =n标点符号：标点符号： ! # $ % &* ( ) _ | ? ； ：, .

15、 “ 大型数据库系统大型数据库系统27n标识符标识符n标识符必须标识符必须以字母开头以字母开头，后边可以跟字母、数，后边可以跟字母、数字、字、货币符号、下划线和货币符号、下划线和”#” n标识符的标识符的最大长度为最大长度为30字符字符，并且所有字符，并且所有字符都是有效的。都是有效的。n合法：合法：X v_studentID TempVarn非法：非法：X+y _tempn标示符可以是中文：标示符可以是中文：不推荐不推荐大型数据库系统大型数据库系统28n+n-n*n/n=n:=nn=nn!=n=n=n(n)n/*n*/nn%n;n:n.nn“n.nn|n=n*n-p分隔符分隔符大型数据库系统

16、大型数据库系统29n常量值常量值n字符型常量字符型常量n数字型常量数字型常量 n布尔型常量：布尔型常量：TURE、FALSE、NULLn日期型常量日期型常量大型数据库系统大型数据库系统3014.2.314.2.3数据类型：变量、常量数据类型：变量、常量n数字类型数字类型n字符类型字符类型n日期日期/区间类型区间类型n行标识类型行标识类型n布尔类型布尔类型n原始类型原始类型nLOB类型类型n记录类型记录类型n集合类型集合类型大型数据库系统大型数据库系统PL/SQL程序注意事项程序注意事项n程序中既可以出现程序中既可以出现表名、字段名表名、字段名，也可以，也可以出现出现变量名变量名；n表名、字段名

17、只能出现在表名、字段名只能出现在SQL语句中语句中；n变量名变量名既可以出现在既可以出现在SQL中，也可以出现中，也可以出现在在程序的控制语句程序的控制语句中；中；n程序不能直接处理表或字段数据程序不能直接处理表或字段数据n如果程序的语句需要使用表中的数据或查如果程序的语句需要使用表中的数据或查询的结果，则需要询的结果，则需要先将数据放入变量中先将数据放入变量中，再进行程序的处理过程。再进行程序的处理过程。大型数据库系统大型数据库系统32基本数据类型基本数据类型分类分类数据类型数据类型（P122，P266）数字类型数字类型NUMBER、BINARY_NUMBER（32位有符位有符号二进制）、号

18、二进制）、 PLS_NUMBER字符类型字符类型VARCHAR2、CHAR、LONG、NCHAR、NVARCHAR（P267：最大长度变化了）：最大长度变化了）日期日期/区间类型区间类型 DATE（秒）（秒）、TIMESTAMP（秒带小数）、（秒带小数）、INTERVAL（时间间隔）（时间间隔）行标识类型行标识类型ROWID、UROWID布尔类型布尔类型BOOLEAN（TRUE、FALSE、NULL）原始类型原始类型RAW、LONG RAWLOB类型类型CLOB、BLOB、NCLOB、BFILE记录记录(结构结构)类型类型RECORD集合集合(数组数组)类型类型TABLE、VARRAY大型数据

19、库系统大型数据库系统33n记录类型的定义记录类型的定义TYPE record_type IS RECORD(field1 datatype1 NOT NULLDEFAULT|:=expr1,field2 datatype2 NOT NULL DEFAULT|:=expr2,fieldn datatypen NOT NULL DEFAULT|:=exprn); 记录类型记录类型大型数据库系统大型数据库系统34记录类型结构类型记录类型结构类型TYPE t_emp IS RECORD( empno NUMBER(4), ename CHAR(10), sal NUMBER(6,2);v_emp t_

20、emp;v1_emp t_emp;1)相同类型的记录可以直接赋值：相同类型的记录可以直接赋值：v1_emp:=v_emp;2)记录类型是局部的：记录类型是局部的：只在定义的块中有效只在定义的块中有效大型数据库系统大型数据库系统C语言语言struct t_emp short empno; char ename10; int NUMBER;struct t_emp v_emp;struct t_emp v1_emp;v1_emp=v_emp;大型数据库系统大型数据库系统36n索引表类型（局部：索引表类型（局部：块中有效块中有效）nTYPE emp_table IS TABLE OF CHAR(10

21、) INDEX BY BINARY_INTEGER;n嵌套表类型（全局：嵌套表类型（全局：全部数据库全部数据库）nTYPE nested_table IS TABLE OF CHAR(10);n可变数组类型（全局）可变数组类型（全局）nTYPE varry_table IS VARRY (2) OF CHAR(10);集合类型（数组）集合类型（数组）大型数据库系统大型数据库系统37n索引表（索引表（hash表，散列数组）表，散列数组）emp_table vemps;vemps(100)=a;vemps(1)=b; n嵌套表（变长数组）嵌套表（变长数组）nested_table vemps;ve

22、mps(1)=a;vemps(2)=b; vemps(4)=c;n可变数组（定长数组）可变数组（定长数组）varry_table vemps;vemps(1)=a;vemps(2)=b;vemps(3)=c;报错！报错！报错！报错！大型数据库系统大型数据库系统38PL/SQL vs. 编程语言的数组编程语言的数组n集合类型：集合类型：n无需无需事先分配空间，直接使用事先分配空间，直接使用n可变表和嵌套表必须可变表和嵌套表必须依序使用各下标依序使用各下标；n编译型语言的编译型语言的静态数组：静态数组：n事先分配事先分配；n无序无序使用各下标使用各下标大型数据库系统大型数据库系统399、%TYPE

23、与与%ROWTYPEDECLARE v_sal emp.sal%TYPE; v_emp emp%ROWTYPE;BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=7844; SELECT * INTO v_emp FROM emp WHERE empno=7900; DBMS_OUTPUT.PUT_LINE(v_sal); DBMS_OUTPUT.PUT_LINE(v_emp.ename |v_emp.sal);END;利用利用已有表已有表或或已有字段已有字段定义数据类型定义数据类型大型数据库系统大型数据库系统40记录类型的索引表（结构数组）记录

24、类型的索引表（结构数组）DECLARE TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; emp_table emp_table_type;BEGIN SELECT * INTO emp_table(1) FROM emp WHERE empno = &no; dbms_output.putline(雇员姓名雇员姓名| emp_table(1).ename); dbms_output.putline(雇员工资雇员工资|emp_table(1).sal);END;大型数据库系统大型数据库系统4114.

25、2.4 14.2.4 语法：变量与常量语法：变量与常量n变量与常量的定义变量与常量的定义n变量的作用域变量的作用域大型数据库系统大型数据库系统42变量声明变量、常量与非空变量、常量与非空n变量定义的一般格式：变量定义的一般格式：n CONSTANT NOT NULL DEFAULT|:= ;n说明说明n每行只能定义一个每行只能定义一个标识符。标识符。n如果加上关键字如果加上关键字CONSTANT，则表示所定义的标识符，则表示所定义的标识符为一个常量，必须为它赋初值。为一个常量，必须为它赋初值。n如果定义的标识符不能为空，则必须加上关键字如果定义的标识符不能为空，则必须加上关键字NOT NULL

26、，并赋初值。，并赋初值。n为标识符赋值时，使用为标识符赋值时，使用赋值符号赋值符号“:=”，默认值为空。，默认值为空。n“=” ？大型数据库系统大型数据库系统43DECLARE 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; 大型数据库系统大型数据库系统44变量的作用域变量的作用域n变量有

27、效范围：变量有效范围：从声明开始，直到块结束从声明开始，直到块结束n嵌套块：嵌套块：n内部块内部块中声明的变量是局部的，只能在内部块中声明的变量是局部的，只能在内部块中引用中引用n外部块外部块中声明的变量中声明的变量既可以既可以在外部块中引用，在外部块中引用，也可以也可以在内部块中引用。在内部块中引用。n允许内部块与外部块允许内部块与外部块定义同名变量定义同名变量n在内部块中引用外部块的全局变量时，需要在内部块中引用外部块的全局变量时，需要使使用外部块名进行标识用外部块名进行标识。 大型数据库系统大型数据库系统45DECLARE v_ename CHAR(15); v_outer NUMBER

28、(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; 变量作用域变量作用域是否在作是否在作用域？用域？大型数据库系统大型数据库系统4614.2.514.2.5编译指示编译指示n编译指示编译指示（编译开关）是（编译开关）是对编译器发出的对编译器发出的命令，与程序流程没有直接关系命令，与程序流

29、程没有直接关系。n关键字：关键字：PRAGMAnPL/SQL提供以下四种编译指示：提供以下四种编译指示：nEXCEPTION_INITn告诉编译程序将一个特定的错误号与程序中所声明告诉编译程序将一个特定的错误号与程序中所声明的异常标识符关联起来。的异常标识符关联起来。 nRESTRICT_REFERENCESn告诉编译程序打包程序的纯度，即对函数中可以使告诉编译程序打包程序的纯度，即对函数中可以使用的用的SQL语句和包变量进行限制。语句和包变量进行限制。大型数据库系统大型数据库系统47nSERIALLY_REUSEABLEn告诉告诉PL/SQL运行时引擎，在数据引用之间不要保运行时引擎，在数据

30、引用之间不要保持包级数据。持包级数据。nAUTONOMOUS_TRANSACTIONn告诉编译程序，该程序块为自治事务，即该事务的告诉编译程序，该程序块为自治事务，即该事务的提交和回滚是独立进行的。提交和回滚是独立进行的。 大型数据库系统大型数据库系统4814.2.6 PL/SQL14.2.6 PL/SQL中中SQLSQL语句语句n在在PL/SQL中中可以执行的可以执行的SQL语句语句包括包括nSELECT nDML(UPDATE、DELETE、INSERT)n事务控制语句（事务控制语句（COMMIT、ROLLBACK、SAVEPOINT）n注意：注意：nDDL语句不可以直接（独立）使用语句不

31、可以直接（独立）使用nSELECT可以直接（独立）出现，但必须改变可以直接（独立）出现，但必须改变形式形式大型数据库系统大型数据库系统49n没有没有INTO的的SELECT语句语句不能直接出现。不能直接出现。nINTO后的变量用于接收查询的结果后的变量用于接收查询的结果n变量个数、顺序要与查询的目标数据相匹配变量个数、顺序要与查询的目标数据相匹配n可以是可以是记录类型记录类型的变量。的变量。n查询结果必须是一行数据查询结果必须是一行数据n如果没有查询到任何数据，则会产生如果没有查询到任何数据，则会产生NO_DATA_FOUND异常异常n如果查询到多个记录，则会产生如果查询到多个记录，则会产生T

32、OO_MANY_ROW异常。异常。SELECT. INTO大型数据库系统大型数据库系统50BEGIN SELECT * FROM empWHERE ename=SMITH;END; 大型数据库系统大型数据库系统51DECLARE v_emp emp%ROWTYPE; v_ename emp.ename%type; v_sal emp.sal%type;BEGIN SELECT * INTO v_emp FROM empWHERE ename=SMITH;DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.sal); SELECT ename,sal INTO v_

33、ename,v_sal FROM emp WHERE empno=7900; DBMS_OUTPUT.PUT_LINE(v_ename| |v_sal);END; 大型数据库系统大型数据库系统52注意：注意：n“|”运算符的两边：运算符的两边：n允许不同类型的数据允许不同类型的数据（数值、字符串。）（数值、字符串。）大型数据库系统大型数据库系统53nPL/SQL中，中，DML语句语句允许使用变量允许使用变量。n 示例示例DECLARE v_empno emp.empno%TYPE :=7500;BEGIN INSERT INTO emp(empno,ename,sal,deptno) VALU

34、ES(v_empno,JOAN,2300,20); UPDATE emp SET sal=sal+100 WHERE empno=v_empno; DELETE FROM emp WHERE empno=v_empno;END; 大型数据库系统大型数据库系统54n标识符的区分标识符的区分：变量名变量名还是还是列名？列名？n首先查看首先查看是否存在同名列是否存在同名列，如存在则标识符被，如存在则标识符被解释为解释为列名列名n否则，该标识符被认为是否则，该标识符被认为是语句块的变量语句块的变量。程序的程序的SQL语句中标识符的区分语句中标识符的区分declare v_ename emp.ename

35、%TYPE:=SMITH; sal number;begin SELECT sal INTO sal FROM emp WHERE ename=v_ename; DBMS_OUTPUT.PUT_LINE(sal);end;大型数据库系统大型数据库系统55SELECT sal INTO v_sal FROM empWHERE ename=v_ename;n长度不同的字符串比较长度不同的字符串比较n填充比较填充比较：在短字符串后添加空格，使两个字符串达到相：在短字符串后添加空格，使两个字符串达到相同长度，然后再进行比较。同长度，然后再进行比较。nABCABC n非填充比较非填充比较：逐字符比较：逐

36、字符比较ASCII码，码，首先结束的小首先结束的小nABCABC n使用何种方式比较字符串：使用何种方式比较字符串：n对对定长的字符串定长的字符串（CHAR类型、字符串常量）采用类型、字符串常量）采用填充比填充比较：较：n如果比较的字符串中有一个是如果比较的字符串中有一个是变长字符串变长字符串（VARCHAR2类类型），则采用型），则采用非填充比较非填充比较： WHERE子句的变化（一）子句的变化（一）大型数据库系统大型数据库系统56n表定义：表定义：ename VARCHAR2(10);-ABCename CHAR(15);-ABCnPL/SQL程序程序v_ename CHAR(10):=A

37、BC ;SELECT sal INTO v_sal FROM empWHERE ename=v_ename;WHERE子句的变化（二）子句的变化（二）大型数据库系统大型数据库系统57n如果要查询如果要查询当前当前DML语句操作的记录的语句操作的记录的信息信息，可以在，可以在DML语句末尾使用语句末尾使用RETURNING语句返回该记录的信息。语句返回该记录的信息。nRETURNING语句的基本语法：语句的基本语法：nRETURNING select_list_item INTO variable_list|record_variable; RETURNING子句子句大型数据库系统大型数据库系统

38、58DECLARE v_sal emp.sal%TYPE;BEGIN UPDATE emp SET sal=sal+100 WHERE empno=7844; SELECT sal INTO v_sal FROM emp WHERE empno=7844;DBMS_OUTPUT.PUT_LINE(v_sal);END;大型数据库系统大型数据库系统59DECLARE v_sal emp.sal%TYPE;BEGIN UPDATE emp SET sal=sal+100 WHERE empno=7844 RETURNING sal INTO v_sal; DBMS_OUTPUT.PUT_LINE(

39、v_sal);END;大型数据库系统大型数据库系统6014.3 14.3 控制结构控制结构 n选择结构选择结构n循环结构循环结构n跳转结构跳转结构大型数据库系统大型数据库系统6.1选择结构选择结构nIF语句语句IF condition1 THEN statements1;ELSIF condition2 THEN statements2;ELSE else_statements;END IF; n注意注意n条件是一个布尔型变量或表达式，取值只能条件是一个布尔型变量或表达式，取值只能是是TRUE，FALSE，NULL。大型数据库系统大型数据库系统62n例如，例如，输入一个员

40、工号输入一个员工号，修改该员工的工，修改该员工的工资，如果该员工为资，如果该员工为10号部门，工资增加号部门，工资增加100；若为；若为20号部门，工资增加号部门，工资增加150；若；若为为30号部门，工资增加号部门，工资增加200；否则增加；否则增加300。 大型数据库系统大型数据库系统63DECLARE 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

41、_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;大型数据库系统大型数据库系统64等值比较的等值比较的CASECASE语句语句n基本语法基本语法nCASE test_valuen WHEN value1 THEN

42、statements1;n WHEN value2 THEN statements2;n n WHEN valuen THEN statementsn;n ELSE else_ statements;nEND CASE; 大型数据库系统大型数据库系统65DECLARE 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_deptn

43、o 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; 大型数据库系统大型数据库系统66搜索式搜索式CASECASE语句语句n基本语法基本语法nCASEn WHEN condition1 THEN statements1;n WHEN condition2 THEN state

44、ments2;n n WHEN conditionn THEN statementsn;n ELSE else_statements;nEND CASE;大型数据库系统大型数据库系统67n根据根据输入的员工号输入的员工号，修改该员工工资。如，修改该员工工资。如果该员工工资低于果该员工工资低于1000，则工资增加，则工资增加200；如果工资在如果工资在1000-2000之间，则增加之间，则增加150；如果工资在如果工资在2000-3000之间，则增加之间，则增加100；否则增加否则增加50。 大型数据库系统大型数据库系统68DECLARE v_sal emp.sal%type; v_increm

45、ent 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_sal1000 THEN v_increment:=200; WHEN v_sal2000 THEN v_increment:=150; WHEN v_sal 50; END LOOP;END; 循环变量循环变量大型数据库系统大型数据库系统76WHILEWHILE循环循环n基本语法基本语法 WHILE condition LOOP sequence

46、_of_statement; END LOOP; 大型数据库系统大型数据库系统77n利用利用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; 大型数据库系统大型数据库系统78FORFOR循环循环n基本语法基本语法 FOR

47、loop_counter IN REVERSE low_bound . high_bound LOOP sequence_of_statement; END LOOP;n注意：注意：n循环变量无需显式定义循环变量无需显式定义，系统隐含地将它声明为，系统隐含地将它声明为BINARY_INTEGER类型类型；n循环变量只能在循环体中使用循环变量只能在循环体中使用，不能在循环体外使用。，不能在循环体外使用。n系统默认时，循环变量从下界往上界系统默认时，循环变量从下界往上界递增计数递增计数n如果使用如果使用REVERSE关键字，则循环变量从上界向下界关键字，则循环变量从上界向下界递减计数递减计数；大型

48、数据库系统大型数据库系统79n例如，利用例如，利用FOR循环向循环向temp_table表中表中插入插入50条记录。程序为：条记录。程序为：BEGIN FOR v_counter IN 1.50 LOOP INSERT INTO temp_table VALUES(v_counter, Loop Index); END LOOP;END;大型数据库系统大型数据库系统8014.3.314.3.3跳转结构跳转结构n语法格式：语法格式：GOTO 标号；标号；n说明：说明：n块内可以跳转，内层块可以跳到外层块，但块内可以跳转，内层块可以跳到外层块，但外层块不外层块不能跳到内层能跳到内层。n不能跳入不能

49、跳入：IF语句、循环体、子程序的内部。语句、循环体、子程序的内部。n由于由于goto语句的缺点，建议尽量少用甚至不用语句的缺点，建议尽量少用甚至不用goto语句。语句。 大型数据库系统大型数据库系统81DECLARE v_counter BINARY_INTEGER :=1;BEGIN INSERT INTO temp_table VALUES (v_counter, Loop index); v_counter := v_Counter + 1; IF v_counter:1 USING P_SALARY; LOOP FETCH C1 INTO R_EMP; EXIT WHEN C1%NOT

50、FOUND; DBMS_OUTPUT.PUT_LINE(薪水大于薪水大于|TO_CHAR(P_SALARY) |的员工为：的员工为：); DBMS_OUTPUT.PUT_LINE(ID为为TO_CHAR(R_EMP)| 其姓名为：其姓名为：|R_EMP.NAME); END LOOP; CLOSE C1;END CREATE_TABLE;大型数据库系统大型数据库系统124在在PL/SQL接收用户输入接收用户输入n不同开发环境采用不同的方式不同开发环境采用不同的方式nSQL *plus环境采用环境采用替换变量（替换变量（P74）大型数据库系统大型数据库系统12514.5 14.5 异常处理异常处

51、理n异常概述异常概述n异常处理过程异常处理过程 大型数据库系统大型数据库系统126.1异常概述异常概述nOracle错误处理机制错误处理机制n异常的类型异常的类型大型数据库系统大型数据库系统127nPL/SQL错误（错误（ P290 ）n编译错误编译错误n运行时错误（运行时错误（运行时可能出现，也可能不出运行时可能出现，也可能不出现现）：）：PL/SQL运行引擎运行引擎n异常处理机制异常处理机制n一个错误对应一个异常一个错误对应一个异常n程序控制权发生了转移程序控制权发生了转移：异常抛出后，会被：异常抛出后，会被异常处理器异常处理器捕获。捕获。 Oracle错误处理机制错误

52、处理机制大型数据库系统大型数据库系统128n异常的类型异常的类型n预定义的预定义的Oracle异常异常n非预定义的非预定义的Oracle异常异常n用户定义的异常用户定义的异常大型数据库系统大型数据库系统129预定义的异常预定义的异常异常情况名异常情况名错误代码错误代码描述描述CURSOR_ALREADY_OPEN ORA-06511 尝试打开已经打开的游标尝试打开已经打开的游标 INVALID_CURSORORA-01001不合法的游标操作（如要访问不合法的游标操作（如要访问已经关闭的游标）已经关闭的游标） NO_DATA_FOUNDORA-01403没有发现数据（没有发现数据（ SELECT

53、 INTO ）TOO_MANY_ROWSORA-01422返回了多行数据（返回了多行数据（SELECT INTO）INVALID_NUMBERORA-01722转换成数字失败转换成数字失败 （X） VALUE_ERRORORA-06502截断、算法或转换错误，通常截断、算法或转换错误，通常出现在赋值错误出现在赋值错误 ZERO_DIVIDEORA-01476除数为除数为0 ROWTYPE_MISMATCHORA-06504主机游标变量与主机游标变量与PL/SQL游标游标变量类型不匹配变量类型不匹配大型数据库系统大型数据库系统130如何判定字符串是合法数字？如何判定字符串是合法数字？DECLAR

54、E V_INPUT VARCHAR2(20); V_EMPNO NUMBER; V_EMP EMP%ROWTYPE;BEGIN V_EMPNO:= # V_INPUT:=# V_EMPNO:=to_number(V_INPUT); V_EMPNO:=1+V_INPUT; SELECT * INTO V_EMP FROM EMP WHERE EMPNO=V_INPUT;VALUE_ERRORINVALID_NUMBER大型数据库系统大型数据库系统131EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE(不是数字

55、不是数字); WHEN INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE(不是数字不是数字);END;大型数据库系统大型数据库系统132异常情况名异常情况名错误代码错误代码描述描述DUP_VAL_ON_INDEXORA-00001 违反唯一性约束或主键约束违反唯一性约束或主键约束SYS_INVALID_ROWIDORA-01410 转换成转换成ROWID失败失败TIMEOUT_ON_RESOURCEORA-00051 在等待资源中出现超时在等待资源中出现超时LOGIN_DENIEDORA-01017 无效用户名无效用户名/密码密码CASE_NOT_FOUNDOR

56、A-06592 没有匹配的没有匹配的WHEN子句子句NOT_LOGGED_ONORA-01012 没有与数据库建立连接没有与数据库建立连接STORAGE_ERRORORA-06500 内存不足内存不足PROGRAM_ERRORORA-06501 PL/SQL内部错误内部错误大型数据库系统大型数据库系统133异常情况名异常情况名错误代码错误代码描述描述ACCESS_INTO_NULLORA-06530给空对象属性赋值给空对象属性赋值COLLECTION_IS_NULLORA-06531对某对某NULL PL/SQL表或可表或可变数组试图应用集合方法，变数组试图应用集合方法，而不是而不是EXIST

57、S SELF_IS_NULLORA-30625调用空对象实例的方法调用空对象实例的方法SUBSCRIPT_BEYOND_COUNT ORA-06533对嵌套表、索引、可变数对嵌套表、索引、可变数组引用时超出集合中元素组引用时超出集合中元素的数量的数量SUBSCRIPT_OUTSIDE_LIMITORA-06532对对可变数组可变数组的引用超出声的引用超出声明的范围明的范围大型数据库系统大型数据库系统134(系统系统)非预定义异常非预定义异常n特点：系统自动触发（只有编号，没声明）特点：系统自动触发（只有编号，没声明）n错误错误在在Oracle内部有定义，但是没有被声明为异常内部有定义，但是没有

58、被声明为异常n定义方法（在语句块的定义方法（在语句块的声明部分声明部分）：）：n声明一个异常名称：声明一个异常名称： e_integrity EXCEPTION;n通过通过PRAGMA EXCEPTION_INIT 将异常与一个将异常与一个Oracle错误号相关联错误号相关联: PRAGMA EXCEPTION_INIT(e_integrity, -2291)n在异常处理部分捕捉并处理异常：在异常处理部分捕捉并处理异常：nWHEN e_integrity THEN . 大型数据库系统大型数据库系统135大型数据库系统大型数据库系统136用户自定义的异常用户自定义的异常nOracle不认为是错误

59、，用户程序定义的异常：不认为是错误，用户程序定义的异常：n目的？目的？n用户自定义异常必须在声明部分进行声明用户自定义异常必须在声明部分进行声明n无需无需PROGMA EXCEPTION_INITn系统不能自动触发异常系统不能自动触发异常n需要需要用户使用用户使用RAISE语句语句。n和所有异常一样，在和所有异常一样，在异常处理部分异常处理部分捕捉并处理捕捉并处理异常。异常。大型数据库系统大型数据库系统13714.5.2 14.5.2 （总结）异常处理过程（总结）异常处理过程n非预定义异常非预定义异常和和用户定义异常用户定义异常：在声明部分：在声明部分定义定义异常异常ne_exception

60、EXCEPTION;nPRAGMA EXCEPTION_INIT(e_exceptioin,-#);n在执行过程中，在执行过程中，抛出异常的方式抛出异常的方式：n用户用户程序主动程序主动RAISE user_define_exception；n当错误产生时，当错误产生时，系统自动系统自动n在异常处理部分通过异常处理器捕获异常，并进在异常处理部分通过异常处理器捕获异常，并进行行异常处理异常处理。大型数据库系统大型数据库系统138异常的捕获与处理异常的捕获与处理异常处理器的基本形式为：异常处理器的基本形式为：EXCEPTION WHEN exception1OR excetpion2 THEN sequence_of_statements1; WH