PL╱SQL程序设计讲义

第第6章章 PL/SQL程序设计程序设计pPL/SQL概述pPL/SQL基础p控制结构p游标p异常处理p存储子程序p包p触发器p集合 6.1 PL/SQL概述概述pPL/SQL特点pPL/SQL功能特性pPL/SQL执行过程与开发工具 PL/SQL PL/SQL语言是语言是OracleOracle数据库数据库专用的专用的一种高级程序设计语言，一种高级程序设计语言，是对标准是对标准SQLSQL语言进行了语言进行了过程化扩展过程化扩展的语言。的语言。6.1.1 PL/SQL特点特点p与SQL语言紧密集成。p减小网络流量，提高应用程序的运行性能。p模块化的程序设计功能，提高了系统可靠性。p服务器端程序设计，可移植性好。6.1.2 PL/SQL功能特性功能特性p在SQL语言的基础上引入了过程化的程序设计因素：n语句块结构语句块结构n异常处理异常处理n变量和数据类型变量和数据类型n流量控制流量控制(条件语句、循环结构条件语句、循环结构)n游标游标p开发特定的Oracle程序，创建过程、函数、包和触发器等数据库对象。6.1.3 PL/SQL执行过程与开发工具执行过程与开发工具PL/SQL块块SQL语句语句客户端客户端应用程序应用程序PL/SQL引擎引擎数据库数据库服务器服务器过程化语句执行器过程化语句执行器SQL执行器执行器块中块中SQL语句语句pPL/SQL程序的编译与执行是通过PL/SQL引擎来完成的，其执行过程如下图：pPL/SQL开发工具nSQL*PLUSnProcedure BuildernOracle Form、Oracle ReportsnPL/SQL Developer6.2 PL/SQL基础基础pPL/SQL程序结构 p词法单元 p数据类型p变量与常量pPL/SQL记录 p编译指示pPL/SQL中的SQL语句pPL/SQL程序的基本单元是语句块，所有的PL/SQL程序都是由语句块构成的。p一个完整的PL/SQL语句块由3个部分组成。n执行执行部分是部分是必须的必须的,而声明部分和异常部分是可选的；而声明部分和异常部分是可选的；n执行部分或异常处理部分可嵌套其他的执行部分或异常处理部分可嵌套其他的PL/SQL块；块；n所有的所有的PL/SQL块都是以块都是以“END;”结束。结束。6.2.1 PL/SQL程序结构程序结构DECLARE v_ename VARCHAR2(10);BEGIN SELECT ename INTO v_ename FROM emp WHERE empno=7944;DBMS_OUTPUT.PUT_LINE(v_ename);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(There is not such an employee);END;注意注意：若要在：若要在SQL*PlusSQL*Plus环境中看到环境中看到DBMS_OUTPUT.PUT_LINEDBMS_OUTPUT.PUT_LINE方法的输出结果，方法的输出结果，必须将环境变量必须将环境变量SERVEROUTPUTSERVEROUTPUT设置为设置为ONON。SQL SET SERVEROUTPUT ONp例如，定义一个包含声明、执行和异常处理的块Declare/*本部分可以定义变量、游标等本部分可以定义变量、游标等*/V_name varchar2(10);V_sql number(7,12);Begin/*执行部分执行部分*/。Begin exception /*执行异常处理部分执行异常处理部分*/end;exception/*执行异常部分执行异常部分*/End;PL/SQL块的嵌套块的嵌套（2）PL/SQL块分类块分类p匿名块n指指动态生成动态生成，只能执行一次只能执行一次的块，不能由其他应用程的块，不能由其他应用程序调用。序调用。p命名块n指一次编译指一次编译可多次执行可多次执行的的PL/SQL程序程序，包括，包括函数函数、存存储过程储过程、包包、触发器触发器等。它们等。它们编译后放在服务器中编译后放在服务器中，由应用程序或系统在特定条件下调用执行。由应用程序或系统在特定条件下调用执行。p命名块示例CREATE OR REPLACE PROCEDURE showavgsal(p_deptno NUMBER)AS 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;6.2.2 词法单元词法单元p字符集p标识符p分隔符p常量值p注释 词法单元就是一个词法单元就是一个字符序列字符序列，其中的字符取自，其中的字符取自PL/SQLPL/SQL语言所允许语言所允许的字符集。的字符集。（1）字符集）字符集pPL/SQL的字符集包括：n大小写字母：大小写字母：AZ，azn数字：数字：09n空白：制表符、空格和回车空白：制表符、空格和回车n数字符号：数字符号：+-*/=n标点符号：标点符号：!#$%&*（）（）_|？；：,.“注意注意：PL/SQLPL/SQL字符集字符集不区分不区分大小写。大小写。（2）标识符）标识符p标识符用于定义PL/SQL变量、常量、异常、游标名称、游标变量、参数、子程序名称和其他的程序单元名称等。p在PL/SQL程序中，标识符是以字母开头的，后边可以跟字母、数字、美元符号($)、井号(#)或下划线(_)，其最大长度为30个字符，并且所有字符都是有效的。n例如，例如，X，v_empno，v_$等都是有效的标识符，而等都是有效的标识符，而X+y，_temp则是则是非法非法的标识符。的标识符。注意注意：如果标识符：如果标识符区分大小写区分大小写、使用预留关键字使用预留关键字或或包含空格等特殊符号包含空格等特殊符号，则需要用则需要用“”括起来，称为引证标识符。括起来，称为引证标识符。例如标识符例如标识符“my bookmy book”和和“exceptionexception”。（3）分隔符）分隔符p+p-p*p/p=p:=pp=pp!=p=p=p(p)p/*p*/pp%p;p:p.pp“p.pp|p=p*p-p 分隔符是指有特定含义的单个符号或组合符号。（4）常量值）常量值n字符型字符型p以以单引号引起来单引号引起来的字符串，在字符串中的字符区分大小写。如的字符串，在字符串中的字符区分大小写。如字符串中本身包含单引号，用两个连续的单引号进行字符串中本身包含单引号，用两个连续的单引号进行转义转义。n数字型数字型p分为整数与实数两类。其中，整数没有小数点，如分为整数与实数两类。其中，整数没有小数点，如123123；而实；而实数有小数点，如数有小数点，如123.45123.45。可以用科学计数法表示数字型文字，。可以用科学计数法表示数字型文字，如如123.45123.45可以表示为可以表示为1.2345E21.2345E2。n布尔型布尔型p预定义的布尔型变量的取值，包括预定义的布尔型变量的取值，包括TRUETRUE，FALSEFALSE，NULLNULL三个值。三个值。n日期型日期型p表示日期值，其格式随日期类型格式不同而不同。表示日期值，其格式随日期类型格式不同而不同。（5）注释）注释p单行注释n-p多行注释n以以“/*”开始，以开始，以“*/”结束。结束。DECLARE v_department CHAR(10);-variable to hold the department name BEGIN /*query the department name which department number is 10 ouput the department name into v_department*/SELECT dname INTO v_department FROM dept WHERE deptno=10;END;6.2.3 PL/SQL的数据类型的数据类型p数字类型:NUMBER、BINARY_NUMBER、PLS_NUMBERp字符类型:VARCHAR2、CHAR、LONG、NVARCHAR、NCHARp日期/区间类型:DATE、TIMESTAMP、INTERVALp行标识类型:ROWID、UROWIDp布尔类型:BOOLEAN（TRUE、FALSE、NULL）p原始类型:RAW、LONG RAWpLOB类型:CLOB、BLOB、NCLOB、BFILEp引用类型:REF CURSOR，REF object_typep记录类型:RECORDp集合类型:TABLE、VARRAYp%TYPE与%ROWTYPEp数字类型 nNUMBER类型以十进制形式存储整数和浮点数，语法为类型以十进制形式存储整数和浮点数，语法为NUMBER(p，s)。其中，。其中，p为精度，即所有有效数字位数；为精度，即所有有效数字位数；s为刻度为刻度范围，即小数位数。范围，即小数位数。p的取值范围为的取值范围为138。nBINARY_INTEGER类型用于表示从类型用于表示从-2147483647+2147483647之间的整数，以之间的整数，以二进制形式存储二进制形式存储。当发生。当发生溢溢出时出时，将，将自动转换成自动转换成NUMBER类型。类型。nPLS_INTEGER类型表示范围与类型表示范围与BINARY_INTEGER相同，相同，但发生但发生溢出时会产生错误溢出时会产生错误。p字符类型 nPL/SQL中的字符类型与中的字符类型与Oracle数据库中的字符类型类数据库中的字符类型类似，但是允许字符串的似，但是允许字符串的长度长度有所不同。有所不同。nVARCHAR2，CHAR主要用于存储来自本地数据库字主要用于存储来自本地数据库字符集的字符，而符集的字符，而NCHAR，NVARCHAR2 用于存储来自用于存储来自国家字符集的字符串。国家字符集的字符串。类类 型型PL/SQL中最大字节数中最大字节数Oracle中最大字节数中最大字节数VARCHAR2327674000NVARCHAR2327674000CHAR327672000NCHAR327672000LONG327602GBp日期/区间类型 nDATE：与数据库中的：与数据库中的DATE类型相同，存储日期和时类型相同，存储日期和时间信息，包括世纪、年、月、日、小时、分和秒，不间信息，包括世纪、年、月、日、小时、分和秒，不包括秒的小数部分。包括秒的小数部分。nTIMESTAMP：与：与DATE类型相似，但包括秒的小数部类型相似，但包括秒的小数部分，有以下分，有以下3种形式。种形式。pTIMESTAMP(p)：其中p为秒字段的小数部分精度。pTIMESTAMP(p)WITH TIME ZONE：返回当前时区的时间戳。pTIMESTAMP(p)WITH LOACL TIME ZONE：返回数据库时区的时间戳。p行标识类型nROWID表示行的物理地址表示行的物理地址nUROWID既可以表示行的物理地址，也可以表示行的既可以表示行的物理地址，也可以表示行的逻辑地址。逻辑地址。p布尔类型(BOOLEAN)n只能在只能在PL/SQL中使用中使用，其取值为逻辑值，包括，其取值为逻辑值，包括TRUE、FALSE、NULL。p原始类型n可变长度的二进制数据，与可变长度的二进制数据，与Oracle数据库中的原始类数据库中的原始类型字节数不同。型字节数不同。类类 型型PL/SQL中最大字节数中最大字节数Oracle中最大字节数中最大字节数RAW327672000LONG RAW327672GpLOB类型n包括包括BLOB，CLOB，NCLOB和和BFILE四种类型。其中四种类型。其中BLOB存放二进制数据，存放二进制数据，CLOB，NCLOB存放文本数据，存放文本数据，而而BFILE存放指向操作系统文件的指针。存放指向操作系统文件的指针。nLOB类型变量可以存储类型变量可以存储4 GB的数据量。的数据量。p引用类型n引用类型类似于其他高级语言中的指针类型。在引用类型类似于其他高级语言中的指针类型。在PL/SQL中，中，引用类型包括引用类型包括游标游标的引用类型和的引用类型和对象对象的引用类型，即的引用类型，即REF CURSOR和和REF object_type。p记录类型n记录类型是复合类型，类似于记录类型是复合类型，类似于C语言语言中的结构体，是中的结构体，是一个包含若干个成员分量的复合类型。一个包含若干个成员分量的复合类型。n在使用记录类型时，需要先在声明部分在使用记录类型时，需要先在声明部分定义记录类型定义记录类型和记录类型的和记录类型的变量变量，然后在，然后在执行部分引用执行部分引用该记录类型该记录类型变量或其成员分量。变量或其成员分量。p集合类型n是一种复合类型，包括是一种复合类型，包括索引表索引表类型、类型、嵌套表嵌套表类型和类型和可变数组可变数组类型。类型。n与记录类型的与记录类型的区别区别：p记录类型中的记录类型中的成员分量可以是不同类型成员分量可以是不同类型的，类似于结构体；的，类似于结构体；p集合类型中所有的集合类型中所有的成员分量成员分量必须必须具有具有相同的相同的数据类型数据类型，类，类似于数组。似于数组。p%TYPE与%ROWTYPE n如果要定义一个变量其类型与如果要定义一个变量其类型与某个变量某个变量的数据类型或数据库表的数据类型或数据库表中中某个列某个列的数据类型一致的数据类型一致(不知道该变量或列的数据类型不知道该变量或列的数据类型)，可，可以利用以利用%TYPE来实现。来实现。n如果要定义一个与数据库中如果要定义一个与数据库中某个表结构某个表结构一致的记录类型的变量，一致的记录类型的变量，可以使用可以使用%ROWTYPE来实现。来实现。n变量的类型随参照的变量类型、数据库表列类型、表结构的变变量的类型随参照的变量类型、数据库表列类型、表结构的变化而变化；化而变化；DECLARE v_sal emp.sal%TYPE;/*变量变量v_sal的类型与的类型与 emp表的表的sal列类型相同列类型相同*/v_emp emp%ROWTYPE;/*变量变量v_emp是一个记录类型，其中是一个记录类型，其中 各个分量的构成和类型与表各个分量的构成和类型与表emp相同相同*/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;变量声明p变量定义的一般格式variable_name CONSTANT datatype NOT NULL DEFAULT|:=expression;p说明n变量或常量名称是一个变量或常量名称是一个PL/SQL标识符，应符合标识符命名规范；标识符，应符合标识符命名规范；n每行只能定义每行只能定义一个一个变量；变量；n如果加上关键字如果加上关键字CONSTANT，则表示所定义的是一个，则表示所定义的是一个常量，必须常量，必须为它赋初值为它赋初值；n如果定义变量时如果定义变量时使用了使用了NOT NULL关键字，则关键字，则必须为变量赋初值必须为变量赋初值；n如果变量没有赋初值，则如果变量没有赋初值，则默认为默认为NULL；n使用使用DEFAULT或或“:=”运算符为变量初始化。运算符为变量初始化。6.2.4 变量与常量变量与常量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;v1,v2 NUMBER(4);变量的常用赋值方式变量的常用赋值方式:=SELECT INTO 变量集变量集 或或 FETCH INTO 变量集变量集6.2.5 PL/SQL记录记录 p两种方式定义记录变量：n用户用户定义记录类型定义记录类型及及变量变量 n利用利用%ROWTYPE获取记录类型定义变量获取记录类型定义变量（1）用户定义记录类型及变量）用户定义记录类型及变量 p定义记录类型的语法为TYPE record_type IS RECORD(field1 datatype1 NOT NULLDEFAULT|:=expr1,field2 datatype2 NOT NULL DEFAULT|:=expr2,fieldn datatypen NOT NULL DEFAULT|:=exprn);p注意：n相同记录类型相同记录类型的变量的变量可以相互赋值可以相互赋值；n不同不同记录类型的变量，即使成员完全相同也记录类型的变量，即使成员完全相同也不能相互赋值不能相互赋值；n记录类型只能应用于定义该记录类型记录类型只能应用于定义该记录类型的的PL/SQL块中，即块中，即记记录类型是局部的录类型是局部的。p例：利用记录类型以及记录类型变量，保存员工信息。DECLARE TYPE t_emp IS RECORD(empno NUMBER(4),ename CHAR(10),sal NUMBER(6,2);v_emp t_emp;BEGIN SELECT empno,ename,sal INTO v_emp FROM emp WHERE empno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.ename|v_emp.sal);END;说明说明：记录变量的成员引用使用：记录变量的成员引用使用“.”！（2）利用）利用%ROWTYPE获取记录类型获取记录类型定义变量定义变量DECLARE v_emp1 emp%ROWTYPE;v_emp2 emp%ROWTYPE;CURSOR c_emp IS SELECT empno,ename FROM emp WHERE deptno=10;v_emp10 c_emp%ROWTYPE;BEGIN SELECT*INTO v_emp1 FROM emp WHERE empno=7844;OPEN c_emp;LOOP FETCH c_emp INTO v_emp10;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp10.empno|v_emp10.ename);END LOOP;CLOSE c_emp;END;p在SELECT语句中使用记录类型变量 n在在SELECT INTO 语句语句中使用记录类型变量中使用记录类型变量DECLARE v_emp emp%ROWTYPE;BEGIN SELECT*INTO v_emp FROM emp WHERE empno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename|v_emp.sal);END;n在在SELECT INTO语句中使用记录类型变量成员语句中使用记录类型变量成员DECLARE v_emp emp%ROWTYPE;BEGIN SELECT empno,ename,sal INTO v_emp.empno,v_emp.ename,v_emp.sal FROM emp WHERE empno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename|v_emp.sal);END;p在INSERT语句中使用记录类型变量n在在VALUES子句中使用记录类型变量子句中使用记录类型变量 n在在VALUES子句子句中使用记录类型变量成员中使用记录类型变量成员DECLARE v_dept dept%ROWTYPE;BEGIN v_dept.deptno:=50;v_dept.loc:=BEIJING;V_dept.dname:=COMPUTER;INSERT INTO DEPT VALUES v_dept;END;注意注意：记录类型变量中分量的个数、顺序、类型应该与查询列表中列的个数、：记录类型变量中分量的个数、顺序、类型应该与查询列表中列的个数、顺序、类型完全匹配。顺序、类型完全匹配。DECLARE v_emp emp%ROWTYPE;BEGIN SELECT*INTO v_emp FROM emp WHERE empno=7844;INSERT INTO emp(empno,ename,mgr,sal)VALUES(1234,TOM,v_emp.mgr,v_emp.sal);END;p在UPDATE语句中使用记录类型变量n在在SET子句中使用记录类型变量子句中使用记录类型变量(使用使用ROW关键字关键字)n在在SET子句子句中使用记录类型变量成员中使用记录类型变量成员 DECLARE v_dept dept%ROWTYPE;BEGIN v_dept.deptno:=50;v_dept.loc:=TIANJIN;V_dept.dname:=COMPUTER;UPDATE dept SET ROW=v_dept WHERE deptno=50;END;DECLARE v_emp emp%ROWTYPE;BEGIN SELECT*INTO v_emp FROM emp WHERE empno=7844;UPDATE emp SET sal=v_emp.sal,comm=v_m WHERE empno=7369;END;p在DELETE语句中使用记录类型变量DECLARE v_emp emp%ROWTYPE;BEGIN SELECT*INTO v_emp FROM emp WHERE empno=7844;DELETE FROM emp WHERE deptno=v_emp.deptno;END;6.2.6 PL/SQL中中SQL语句语句pPL/SQL执行采用早期绑定，即在编译阶段对变量进行绑定，识别程序中标识符的位置，检查用户权限、数据库对象等信息。因此在PL/SQL中只允许出现:nSELECT nDML(UPDATE、DELETE、INSERT)n事务控制语句事务控制语句(COMMIT、ROLLBACK、SAVEPOINT)注意注意：DDLDDL语句不可以直接使用。语句不可以直接使用。p通过PL/SQL中的变量可以动态指定各种相关量的值，从而实现对数据库的动态操作。例如，用变量指定员工号，然后根据员工号修改员工工资。DECLARE v_empno NUMBER(4);BEGIN v_empno:=&x;UPDATE emp SET sal=sal+100 WHERE empno=v_empno;END;pSELECT语句nPL/SQL使用使用SELECTINTO语句查询一个记录的信息。语句查询一个记录的信息。n其语法为：其语法为：SELECT select_list_item INTO variable_list|record_variableFROM tableWHERE condition;例如，根据员工名或员工号查询员工信息，程序为：例如，根据员工名或员工号查询员工信息，程序为：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;p注意：nSELECTINTO语句语句只能查询一个记录只能查询一个记录的信息，如果没有查的信息，如果没有查询到任何数据，会产生询到任何数据，会产生NO_DATA_FOUND异常；如果查询到异常；如果查询到多个记录，则会产生多个记录，则会产生TOO_MANY_ROWS异常。异常。nINTO句子后的变量用于接收查询的结果，变量的个数、顺序句子后的变量用于接收查询的结果，变量的个数、顺序应该与查询的目标数据相匹配，也可以是记录类型的变量。应该与查询的目标数据相匹配，也可以是记录类型的变量。例如，用SELECTINTO语句查询10号部门所有员工信息。DECLARE v_emp emp%ROWTYPE;BEGIN SELECT*INTO v_emp FROM emp WHERE deptno=10;END;/*ERROR 位于第位于第 1 行行:ORA-01422:实际返回的行数超出请求的行数实际返回的行数超出请求的行数ORA-06512:在在line 4pDML语句nPL/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;pWHEREn标识符的区分标识符的区分p系统首先查看系统首先查看WHERE子句中的标识符是否与表中的列名相同，子句中的标识符是否与表中的列名相同，如果如果相同相同，则该标识符，则该标识符被解释为列名被解释为列名；如果没有同名列，系统；如果没有同名列，系统检查该标识符是不是检查该标识符是不是PL/SQL语句块的变量。语句块的变量。n字符串比较字符串比较p填充比较填充比较：通过在短字符串后添加空格，使两个字符串达到相：通过在短字符串后添加空格，使两个字符串达到相同长度，然后根据每个字符的同长度，然后根据每个字符的ASCIIASCII码进行比较。码进行比较。p非填充比较非填充比较：根据每个字符的：根据每个字符的ASCIIASCII码进行比较，最先结束的字码进行比较，最先结束的字符串为小。符串为小。那么何时采用填充比较，何时采用非填充比较呢？nPL/SQL中中规定规定，对，对定长的定长的字符串字符串(CHAR类型的字符串和字符类型的字符串和字符串常量串常量)采用填充比较采用填充比较；如果比较的字符串中；如果比较的字符串中有一个是变长有一个是变长字字符串符串(VARCHAR2类型的字符串类型的字符串)，则采用，则采用非填充比较非填充比较。p例如,已知emp表中ename列类型为VARCHAR2(10)，执行下面的代码。DECLARE v_ename CHAR(10):=TURNER;-v_ename VARCHAR2(20):=TURNER;-v_ename emp.ename%TYPE:=TURNER;v_sal emp.sal%TYPE;BEGIN SELECT sal INTO v_sal FROM emp WHERE ename=v_ename;dbms_output.put_line(v_sal);END;/DECLARE*第第 1 行出现错误行出现错误:ORA-01403:未找到数据未找到数据ORA-06512:在在 line 6 p产生错误的原因是VARCHAR2(10)类型与CHAR(10)类型比较时采用非填充比较，因此无法查询到员工名为“TURNER”的员工。可以将v_ename变量类型修改为VARCHAR2(10)类型，也可以直接采用emp.ename%TYPE方式定义。p因此，为了保证程序的正确执行，一定要使PL/SQL语句块中的变量与要比较的数据库列拥有相同的数据类型，可以使用%TYPE或%ROWTYPE来定义变量。pRETURNINGn如果要查询当前如果要查询当前DML语句操作的记录的信息，可以语句操作的记录的信息，可以在在DML语句末尾语句末尾使用使用RETURNING语句返回该记录的信息。语句返回该记录的信息。nRETURNING语句的基本语法：语句的基本语法：RETURNING select_list_item INTO variable_list|record_variable;p例如，将员工号为7844的员工工资提高100，同时返回修改后的工资。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;6.3 6.3 控制结构控制结构 p选择结构：IF语句、CASE语句p循环结构：WHILE循环、FOR循环p跳转结构：GOTO语句6.3.1 选择结构选择结构-（1）IF语句语句p语法IF condition1 THEN statements1;ELSIF condition2 THEN statements2;ELSE else_statements;END IF;注意注意：条件是一个布尔型变量或表达式，取值只能是：条件是一个布尔型变量或表达式，取值只能是TRUETRUE，FALSEFALSE，NULLNULL。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:=60;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;例如，输入一个员工号，修改该员工的工资，如果该员工为例如，输入一个员工号，修改该员工的工资，如果该员工为10号部门，工资增加号部门，工资增加100；若为；若为20号部门，工资增加号部门，工资增加60；若为若为30号部门，工资增加号部门，工资增加200；否则增加；否则增加300。（2）CASE语句语句p等值比较的CASE语句语法CASE test_value WHEN value1 THEN statements1;WHEN value2 THEN statements2;WHEN valuen THEN statementsn;ELSE else_ statements;END CASE;p注意 n在在CASE语句中，当第一个语句中，当第一个WHEN条件为真时，执行条件为真时，执行其后的操作，操作完后结束其后的操作，操作完后结束CASE语句。其他的语句。其他的WHEN条件不再判断，其后的操作也不执行条件不再判断，其后的操作也不执行。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;p例如，将前面的IF语句程序改成CASE语句的形式。p多种条件比较的CASE语句语法CASE WHEN condition1 THEN statements1;WHEN condition2 THEN statements2;WHEN conditionn THEN statementsn;ELSE else_statements;END CASE;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_sal1000 THEN v_increment:=200;WHEN v_sal2000 THEN v_increment:=150;WHEN v_sal 50;END LOOP;END;（2）WHILE循环循环p基本语法WHILE condition LOOP sequence_of_statement;END LOOP;p利用WHILE循环向temp_table表中插入50条记录。DECLARE v_counter BINARY_INTEGER:=1;BEGIN WHILE v_counter(SELECT AVG(sal)FROM emp WHERE deptno=10);ELSIF v_table=dept THEN OPEN v_cursor FOR SELECT deptno,count(*)num FROM emp GROUP BY deptno;ELSE RAISE_APPLICATION_ERROR(-20000,Input must be emp or dept);END IF;p要求根据输入的不同表名进行不同处理，若表名为emp，则显示高于10号部门平均工资的员工信息；若表名为dept，则显示各个部门的人数。LOOP IF v_table=emp THEN FETCH v_cursor INTO v_emp;EXIT WHEN v_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename|v_emp.sal|v_emp.deptno);ELSE FETCH v_cursor INTO v_deptno,v_num;EXIT WHEN v_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_deptno|v_num);END IF;END LOOP;CLOSE v_cursor;END;6.5 异常处理异常处理p异常概述p异常处理过程 p异常的传播PL/SQL是如何处理异常的?-程序引发异常-每当引发异常时，都将控制权传递给异常处理程序-异常处理程序处理异常.异常中涉及的步骤-声明异常-引发异常-处理异常异常处理部分异常处理部分【例】下面是一个异常处理的例子：SET SERVEROUTPUT ON;DECLARE x NUMBER;BEGIN x:=aa123;EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE(数据类型错误数据类型错误);END;运行结果为：数据类型错误数据类型错误PL/SQL 过程已成功完成过程已成功完成 6.5.1 异常概述异常概述 一个错误对应一个异常，当错误产生时抛出相应的异常，并被异常处理器捕获，程序控制权传递给异常处理器，由异常处理器来处理运行时错误。p预定义的Oracle异常(Oracle错误)p非预定义的Oracle异常(Oracle错误)p用户定义的异常(用户定义错误)p预定义的Oracle异常n当当Oracle错误产生时，与错误对应的预定义异常被自动错误产生时，与错误对应的预定义异常被自动抛出，通过捕获该异常可以对错误进行处理。抛出，通过捕获该异常可以对错误进行处理。n由于系统可以自动识别由于系统可以自动识别Oracle内部错误，因此当错误内部错误，因此当错误产生时系统会产生时系统会自动抛出自动抛出与之对应的与之对应的预定义异常预定义异常.n常用预定义异常包括：常用预定义异常包括：异常情况名错误代码描述CURSOR_ALREADY_OPEN ORA-06511 尝试打开已经打开的游标 INVALID_CURSORORA-01001不合法的游标操作（如要打开已经关闭的游标）NO_DATA_FOUNDORA-01403没有发现数据 TOO_MANY_ROWSORA-01422一个SELECT INTO语句匹配多个数据行INVALID_NUMBERORA-01722转换成数字失败（X）VALUE_ERRORORA-06502截断、算法或转换错误，通常出现在赋值错误 ZERO_DIVIDEORA-01476除数为0 ROWTYPE_MISMATCHORA-06504主机游标变量与PL/SQL游标变量类型不匹配DUP_VAL_ON_INDEXORA-00001违反唯一性约束或主键约束SYS_INVALID_ROWIDORA-01410转换成ROWID失败异常情况名错误代码描述TIMEOUT_ON_RESOURCEORA-00051在等待资源中出现超时LOGIN_DENIEDORA-01017无效用户名/密码CASE_NOT_FOUNDORA-06592没有匹配的WHEN子句NOT_LOGGED_ONORA-01012没有与数据库建立连接STORAGE_ERRORORA-06500PL/SQL内部错误PROGRAM_ERRORORA-06501PL/SQL内部错误ACCESS_INTO_NULLORA-06530给空对象属性赋值COLLECTION_IS_NULLORA-06531对某NULL PL/SQL表或可变数组试图应用集合方法，而不是EXISTS SELF_IS_NULLORA-30625调用空对象实例的方法SUBSCRIPT_BEYOND_COUNTORA-06533对嵌套表或数组索引引用时超出集合中元素的数量SUBSCRIPT_OUTSIDE_LIMITORA-06532对嵌套表或可变数组索引的引用超出声明的范围p非预定义异常n有一些有一些Oracle错误没有预定义异常与其关联，需要在语句块错误没有预定义异常与其关联，需要在语句块的声明部分声明一个异常名称，然后通过的声明部分声明一个异常名称，然后通过编译指示编译指示PRAGMA EXCEPTION_INIT将该异常名称与一个将该异常名称与一个Oracle错误相关联。错误相关联。当错误产生时系统会当错误产生时系统会自动抛出自动抛出该该非预定义异常非预定义异常。1、声明一个异常名称e_integrity EXCEPTION;2、将异常与一个Oracle错误号相绑定PRAGMA EXCEPTION_INIT(e_integrity,-2291)p示例DECLARE e_deptno_fk EXCEPTION;PRAGMA EXCEPTION_INIT(e_deptno_fk,-2292);BEGINEXCEPTIONEND;p用户自定义的异常n用户定义错误是指，有些操作并不会产生用户定义错误是指，有些操作并不会产生Oracle错误，但是错误，但是从从业务规则业务规则角度考虑，认为是一种错误。角度考虑，认为是一种错误。n用户自定义异常必须在用户自定义异常必须在声明部分声明部分进行声明。进行声明。方法方法:e_exception EXCEPTION;n系统无法识别用户定义错误，因此当系统无法识别用户定义错误，因此当用户定义错误用户定义错误产生时，产生时，需要用户需要用户手动抛出手动抛出与之对应的异常。抛出语法为与之对应的异常。抛出语法为 RAISE user_define_exception；n在异常处理部分捕捉并处理异常。在异常处理部分捕捉并处理异常。p异常处理分3个步骤进行：n在声明部分为错误在声明部分为错误定义异常定义异常，包括非预定义异常和用户定义，包括非预定义异常和用户定义异常。异常。n在执行过程中当错误产生时在执行过程中当错误产生时抛出抛出与错误对应的异常，与错误对应的异常，只有要只有要显式显式抛出抛出。n在异常处理部分通过异常处理器捕获异常并进行在异常处理部分通过异常处理器捕获异常并进行异常处理异常处理。6.5.2 异常处理过程异常处理过程异常的捕获与处理异常的捕获与处理p异常处理器的基本形式为EXCEPTIONWHEN exception1OR excetpion2THEN sequence_of_statements1;WHEN exception3OR exception4THEN sequence_of_statements2;WHEN OTHERS THEN sequence_of_statementsn;END;p注意：n一个异常处理器可以捕获多个异常，只需在一个异常处理器可以捕获多个异常，只需在WHEN子句中用子句中用OR连接；连接；n一个异常只能被一个异常处理器捕获，并进行处理。一个异常只能被一个异常处理器捕获，并进行处理。p例如，查询名为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;预定义异常p例如，删除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;非预定义异常p例如，修改7844员工的工资，保证修改后工资不超过2000。DECLARE e_highlimit EXCEPTION;v_sal emp.sal%TYPE;BEGIN UPDATE emp SET sal=sal+300 WHERE empno=7844 RETURNING sal INTO v_sal;IF v_sal2000 THEN RAISE e_highlimit;END IF;EXCEPTION WHEN e_highlimit THEN DBMS_OUTPUT.PUT_LINE(The salary is too large!);ROLLBACK;END;用户定义异常OTHERS异常处理器异常处理器pOTHERS异常处理器是一个特殊的异常处理器，可以捕获所有的异常。p通常，OTHERS异常处理器总是作为异常处理部分的最后一个异常处理器，负责处理那些没有被其他异常处理器捕获的异常。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+300 WHERE empno=7900;IF v_sal2000 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;There is some wrong in selecting!-不存在名为不存在名为JOAN的员工。的员工。p可以通过两个函数来获取错误相关信息。nSQLCODE：返回当前错误代码。：返回当前错误代码。p如果是用户定义错误返回值为如果是用户定义错误返回值为1；p如果是如果是ORA-1403：NO DATA FOUND错误，返回值为错误，返回值为100p其他其他Oracle内部错误返回相应的错误号。内部错误返回相应的错误号。nSQLERRM：返回当前错误的消息文本。：返回当前错误的消息文本。p如果是如果是Oracle内部错误，返回系统内部的错误描述；内部错误，返回系统内部的错误描述；p如果是用户定义错误，则返回信息文本为如果是用户定义错误，则返回信息文本为“User-defined Exception”。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_sal6000 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;100 ORA-01403:未找到数据6.5.3 异常的传播异常的传播p执行部分的异常 p声明部分和异常处理部分的异常 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!p执行部分：如果当前语句块有该异常的处理器，则执行之，并且成功完成该语句块。然后，控制权传