第15章 PLSQL程序设计

1第15章PL/SQL程序设计2本章内容PL/SQL概述PL/SQL基础控制结构游标异常处理存储子程序包触发器3本章要求掌握PL/SQL程序设计基础知识掌握存储过程、函数、包、触发器的应用415.1PL/SQL概述PL/SQL特点PL/SQL功能特性PL/SQL执行过程与开发工具515.1.1PL/SQL特点与SQL语言紧密集成。减小网络流量，提高应用程序的运行性能。模块化的程序设计功能，提高了系统可靠性。服务器端程序设计，可移植性好。615.1.2PL/SQL功能特性语句块结构异常处理变量和类型条件语句循环结构游标过程、函数和触发器包集合动态SQL对象特性715.1.3PL/SQL执行过程与开发工具PL/SQL块SQL语句客户端应用程序PL/SQL引擎数据库服务器过程化语句执行器SQL执行器块中SQL语句PL/SQL执行过程

8PL/SQL开发工具SQL*PLUSProcedureBuilderOracleForm、OracleReportsPL/SQLDeveloper915.2PL/SQL基础PL/SQL程序结构

词法单元

数据类型变量与常量编译指示PL/SQL中的SQL语句10PL/SQL程序结构PL/SQL块的组成PL/SQL块分类11PL/SQL块的组成PL/SQL语言以块为单位，块中可以嵌套子块。

一个基本的PL/SQL块由3部分组成：声明（DECLARE），可执行部分（BEGIN），异常处理部分EXCEPTION）。

12声明部分声明部分以关键字DECLARE开始，BEGIN结束。主要用于声明变量、常量、数据类型、游标、异常处理名称以及本地（局部）子程序定义等。

可执行部分执行部分是PL/SQL块的功能实现部分，以关键字BEGIN开始，EXCEPTION或END结束（如果PL/SQL块中没有异常处理部分，则以END结束）。该部分通过变量赋值、流程控制、数据查询、数据操纵、数据定义、事务控制、游标处理等实现块的功能。异常处理部分异常处理部分以关键字EXCEPTION开始，END结束。该部分用于处理该块执行过程中产生的异常。

13注意：执行部分是必需的，而声明部分和异常部分是可选的；可以在一个块的执行部分或异常处理部分嵌套其他的PL/SQL块；所有的PL/SQL块都是以“END;”结束14PL/SQL块分类匿名块命名块函数存储过程包触发器1515.5.2词法单元字符集标识符分隔符常量值注释16字符集大小写字母：A~Z，a~z数字：0~9空白：制表符、空格和回车数字符号：+-*/〈

〉=标点符号：~!@#$%^&*（）_|{}[]？

；

：,.“

‘

17标识符标识符以字母开头，后边可以跟字母、数字、货币符号、下划线和”#”

标识符的最大长度为30字符，并且所有字符都是有效的。合法：Xv_studentIDTempVar非法：X+y_temp18+-*/=:=<><=>=<>!=~=^=()/**/<<>>%;:.‘“..@||=>**-分隔符19常量值字符型常量数字型常量布尔型常量：TURE、FALSE、NULL日期型常量2015.2.3数据类型数字类型字符类型日期/区间类型行标识类型布尔类型原始类型LOB类型记录类型集合类型21PL/SQL中常用的基本数据类型分类数据类型数字类型NUMBER、BINARY_NUMBERPLS_NUMBER字符类型VARCHAR2、CHAR、LONG、NCHAR、NVARCHAR日期/区间类型DATE、TIMESTAMP、INTERVAL行标识类型ROWID、UROWID布尔类型BOOLEAN（TRUE、FALSE、NULL）原始类型RAW、LONGRAWLOB类型CLOB、BLOB、NCLOB、BFILE记录类型RECORD集合类型TABLE、VARRAY22记录类型的定义TYPErecord_typeISRECORD(field1datatype1[NOTNULL][DEFAULT|:=expr1],field2datatype2[NOTNULL][DEFAULT|:=expr2],……fieldndatatypen[NOTNULL][DEFAULT|:=exprn]);2315.2.4变量与常量变量与常量的定义变量的作用域24变量声明变量与常量的定义变量定义的一般格式：<variablename>[CONSTANT]<datatype>[[NOTNULL]{DEFAULT|:=}<expression>];说明每行只能定义一个标识符。如果加上关键字CONSTANT,则表示所定义的标识符为一个常量，必须为它赋初值。如果定义的标识符不能为空，则必须加上关键字NOTNULL,并赋初值。为标识符赋值时，使用赋值符号‘：=’，默认值为空。25DECLAREv1NUMBER(4);v2NUMBER(4)NOTNULL:=10;v3CONSTANTNUMBER(4)DEFAULT100;BEGINIFv1ISNULLTHENDBMS_OUTPUT.PUT_LINE('V1ISNULL!');ENDIF;DBMS_OUTPUT.PUT_LINE(v2||''||v3);END;declarebegindbms_output.put_line('helloworld');end;Setserverouton;declarev_jobvarchar2(10);v_salnumber;beginv_job:='clerk';selectmax(sal)intov_salfromempwherejob=v_job;dbms_output.put_line(v_sal);end;26declareinumber:=1;beginforiin1..5loopdbms_output.put_line(i||‘dlifangshi‘||i*i*i);endloop;end;2728声明一个变量，使它的类型与某个变量或数据库基本表中某个列的数据类型一致，可以使用%TYPE。示例v_empno1emp.empno%TYPE;v_empno2v_empno1%TYPE;29变量的作用域变量的作用域是指变量的有效作用范围，从变量声明开始，直到块结束。如果PL/SQL块相互嵌套，则在内部块中声明的变量是局部的，只能在内部块中引用，而在外部块中声明的变量是全局的，既可以在外部块中引用，也可以在内部块中引用。如果内部块与外部块中定义了同名变量，则在内部块中引用外部块的全局变量时需要使用外部块名进行标识。30<<OUTER>>DECLAREv_enameCHAR(15);v_outerNUMBER(5);BEGINv_outer:=10;DECLAREv_enameCHAR(20);v_innerDATE;BEGINv_inner:=sysdate;v_ename:='INNERV_ENAME';OUTER.v_ename:='OUTER.V_ENAME';END;DBMS_OUTPUT.PUT_LINE(v_ename);END;3115.2.5编译指示编译指示是对编译程序发出的特殊指令，也称伪指令。关键字：PRAGMAPL/SQL提供以下四种编译指示：EXCEPTION_INIT告诉编译程序将一个特定的错误号与程序中所声明的异常标识符关联起来。RESTRICT_REFERENCES告诉编译程序打包程序的纯度，即对函数中可以使用的SQL语句和包变量进行限制。32SERIALLY_REUSEABLE告诉PL/SQL运行时引擎，在数据引用之间不要保持包级数据。AUTONOMOUS_TRANSACTION告诉编译程序，该程序块为自治事务，即该事务的提交和回滚是独立进行的。3315.2.6PL/SQL中SQL语句可以在PL/SQL中执行的SQL语句包括SELECTDML(UPDATE、DELETE、INSERT)事务控制语句（COMMIT、ROLLBACK、SAVEPOINT）注意DDL语句不可以直接使用34SELECT…INTOSELECT…INTO语句只能查询一个记录的信息，如果没有查询到任何数据，则会产生NO_DATA_FOUND异常；如果查询到多个记录，则会产生TOO_MANY_ROW异常。INTO句子后的变量用于接收查询的结果，变量的个数、顺序应该与查询的目标数据相匹配，也可以是记录类型的变量。DML语句35DECLAREv_empemp%ROWTYPE;v_enameemp.ename%type;v_salemp.sal%type;BEGINSELECT*INTOv_empFROMempWHEREename='SMITH';DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.sal);selectename,salINTOv_ename,v_salFROMempWHEREempno=7900;DBMS_OUTPUT.PUT_LINE(v_ename||''||v_sal);END;36DML语句PL/SQL中DML语句对标准SQL语句中的DML语句进行了扩展，允许使用变量。示例DECLAREv_empnoemp.empno%TYPE:=7500;BEGININSERTINTOemp(empno,ename,sal,deptno)VALUES(v_empno,'JOAN',2300,20);UPDATEempSETsal=sal+100WHEREempno=v_empno;DELETEFROMempWHEREempno=v_empno;END;37WHERE标识符的区分系统首先查看WHERE子句中的标识符是否与表中的列名相同，如果相同，则该标识符被解释为列名；如果没有同名列，系统检查该标识符是不是PL/SQL语句块的变量。字符串比较填充比较：通过在短字符串后添加空格，使两个字符串达到相同长度，然后根据每个字符的ASCII码进行比较。非填充比较：根据每个字符的ASCII码进行比较，最先结束的字符串为小。PL/SQL中规定，对定长的字符串（CHAR类型的字符串和字符串常量）采用填充比较；如果比较的字符串中有一个是变长字符串（VARCHAR2类型的字符串），则采用非填充比较。38RETURNING如果要查询当前DML语句操作的记录的信息，可以在DML语句末尾使用RETURNING语句返回该记录的信息。RETURNING语句的基本语法：RETURNINGselect_list_itemINTOvariable_list|record_variable;39DECLAREv_salemp.sal%TYPE;BEGINUPDATEempSETsal=sal+100WHEREempno=7844RETURNINGsalINTOv_sal;DBMS_OUTPUT.PUT_LINE(v_sal);END;4015.3控制结构选择结构循环结构跳转结构4115.3.1选择结构IF语句IFcondition1THENstatements1;[ELSIFcondition2THENstatements2;]……[ELSEelse_statements];ENDIF;

注意条件是一个布尔型变量或表达式，取值只能是TRUE，FALSE，NULL。42例如，输入一个员工号，修改该员工的工资，如果该员工为10号部门，工资增加100；若为20号部门，工资增加150；若为30号部门，工资增加200；否则增加300。43DECLAREv_deptnoemp.deptno%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=&x;SELECTdeptnoINTOv_deptnoFROMempWHEREempno=v_empno;IFv_deptno=10THENv_increment:=100;ELSIFv_deptno=20THENv_increment:=150;ELSIFv_deptno=30THENv_increment:=200;ELSEv_increment:=300;ENDIF;UPDATEempSETsal=sal+v_incrementWHEREempno=v_empno;END;44搜索式CASE语句基本语法CASEWHENcondition1THENstatements1;WHENcondition2THENstatements2;

……WHENconditionnTHENstatementsn;[ELSEelse_statements;]ENDCASE;45等值比较的CASE语句基本语法CASEtest_valueWHENvalue1THENstatements1;WHENvalue2THENstatements2;

……WHENvaluenTHENstatementsn;[ELSEelse_statements;]ENDCASE;

46DECLAREv_deptnoemp.deptno%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=&x;SELECTdeptnoINTOv_deptnoFROMempWHEREempno=v_empno;CASEv_deptnoWHEN10THENv_increment:=100;WHEN20THENv_increment:=150;WHEN30THENv_increment:=200;ELSEv_increment:=300;ENDCASE;UPDATEempSETsal=sal+v_incrementWHEREempno=v_empno;END;47根据输入的员工号，修改该员工工资。如果该员工工资低于1000，则工资增加200；如果工资在1000-2000之间，则增加150；如果工资在2000-3000之间，则增加100；否则增加50。48DECLAREv_salemp.sal%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=&x;SELECTsalINTOv_salFROMempWHEREempno=v_empno;CASEWHENv_sal<1000THENv_increment:=200;WHENv_sal<2000THENv_increment:=150;WHENv_sal<3000THENv_increment:=100;ELSEv_increment:=50;ENDCASE;UPDATEempSETsal=sal+v_incrementWHEREempno=v_empno;END;4915.3.2循环结构简单循环WHILE循环FOR循环50简单循环语法

LOOPsequence_of_statement;EXIT[WHENcondition];ENDLOOP;注意：在循环体中一定要包含EXIT语句，否则程序进入死循环。51例如，执行CREATETABLEtemp_table(num_colNUMBER,info_colCHAR(10))语句创建temp_table表，然后利用循环向temp_table表中插入50条记录。程序为：DECLAREv_counterBINARY_INTEGER:=1;BEGINLOOPINSERTINTOtemp_tableVALUES(v_Counter,'Loopindex');v_counter:=v_counter+1;EXITWHENv_counter>50;ENDLOOP;END;52WHILE循环基本语法

WHILEconditionLOOPsequence_of_statement;ENDLOOP;53例如，利用WHILE循环向temp_table表中插入50条记录。程序为：DECLAREv_counterBINARY_INTEGER:=1;BEGINWHILEv_counter<=50LOOPINSERTINTOtemp_tableVALUES(v_counter,'Loopindex');v_counter:=v_counter+1;ENDLOOP;END;54FOR循环基本语法

FORloop_counterIN[REVERSE]low_bound..high_boundLOOPsequence_of_statement;ENDLOOP;注意：循环变量不需要显式定义，系统隐含地将它声明为BINARY_INTEGER变量；系统默认时，循环变量从下界往上界递增计数，如果使用REVERSE关键字，则表示循环变量从上界向下界递减计数；循环变量只能在循环体中使用，不能在循环体外使用。55例如，利用FOR循环向temp_table表中插入50条记录。程序为：BEGINFORv_counterIN1..50LOOPINSERTINTOtemp_tableVALUES(v_counter,'LoopIndex');ENDLOOP;END;5615.3.3跳转结构语法格式：《标号》…GOTO标号；说明：块内可以跳转，内层块可以跳到外层块，但外层块不能跳到内层。IF语句不能跳入。不能从循环体外跳入循环体内。不能从子程序外部跳到子程序中。由于goto语句的缺点，建议尽量少用甚至不用goto语句。5715.4游标游标的概念及类型

显式游标

隐式游标58DECLAREv_counterBINARY_INTEGER:=1;BEGIN<<LABEL>>INSERTINTOtemp_tableVALUES(v_counter,'Loopindex');v_counter:=v_Counter+1;IFv_counter<=50THENGOTOLABEL;ENDIF;END;5915.4游标游标的概念及类型显式游标隐式游标6015.4.1游标的及类型游标的概念游标（CURSOR）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。使用游标时，SELECT语句查询的结果可以是单条记录，多条记录，也可以是零条记录。游标工作区中，存在着一个指针（POINTER）,在初始状态它指向查询结果的首记录。61游标的类型显式游标由用户定义、操作，用于处理返回多行数据的SELECT查询。隐式游标由系统自动进行操作，用于处理DML语句和返回单行数据的SELECT查询。

6215.4.2显式游标显式游标的操作显式游标的属性参数化显式游标显式游标的检索利用游标更新或删除数据63显式游标的操作步骤定义游标打开游标检索游标关闭游标64定义游标语法格式CURSORcursor_nameISselect_statement;说明游标必须在PL/SQL块的声明部分进行定义；游标定义时可以引用PL/SQL变量，但变量必须在游标定义之前定义；定义游标时并没有生成数据，只是将定义信息保存到数据字典中；游标定义后，可以使用cursor_name%ROWTYPE定义游标类型变量。65打开游标语法格式OPENcursor_name;说明检查变量的值执行游标定义时对应的SELECT语句，将查询结果检索到工作区中。游标指针指向第一个元组一旦游标打开，就无法再次打开，除非先关闭如果游标定义中的变量值发生变化，则只能在下次打开游标时才起作用。66检索游标语法格式FETCHcursor_nameINTOvariable_list|record_variable;

说明在使用FETCH语句之前必须先打开游标对游标第一次使用FETCH语句时，游标指针指向第一条记录，因此操作的对象是第一条记录，使用后，游标指针指向下一条记录。游标指针只能向下移动，不能回退INTO子句中的变量个数、顺序、数据类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应。67关闭游标语法格式

CLOSEcursor_name;说明游标所对应的内存工作区变为无效，释放与游标相关的系统资源。68根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定。69DECLAREv_deptnoemp.deptno%TYPE;CURSORc_empISSELECT*FROMempWHEREdeptno=v_deptno;v_empc_emp%ROWTYPE;BEGINv_deptno:=&x;OPENc_emp;LOOPFETCHc_empINTOv_emp;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename||''||v_emp.sal||''||v_deptno);ENDLOOP;CLOSEc_emp;END;70显式游标的属性

%ISOPEN布尔型。如果游标已经打开，返回TRUE,否则为FALSE。%FOUND布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FALSE;%NOTFOUND布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;%ROWCOUNT数值型，返回到目前为止从游标缓冲区检索的元组数。

71参数化显式游标参数化游标定义语法格式：CURSORcursor_name(parameter1datatype[,parameter2datatype…])ISselect_statement

打开参数化游标的方法OPENcursor_name(parameter1[,parameter2…])72注意：定义游标时，只能指定参数的类型，而不能指定参数的长度、精度、刻度；打开带参数的游标时，实参的个数和数据类型等必须与游标定义时形参个数和数据类型等相匹配。73DECLARECURSORc_emp(p_deptnoemp.deptno%TYPE)ISSELECT*FROMempWHEREdeptno=p_deptno;v_empc_emp%ROWTYPE;BEGINOPENc_emp(10);LOOPFETCHc_empINTOv_emp;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;CLOSEc_emp;OPENc_emp(20);LOOPFETCHc_empINTOv_emp;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;CLOSEc_emp;END;74显式游标的检索利用简单循环检索游标利用WHILE循环检索游标利用FOR循环检索游标75利用简单循环检索游标DECLARECURSORcursor_nameISSELECT…;BEGINOPENcursor_name;LOOPFETCH…INTO…;EXITWHENcursor_name%NOTFOUND;

……ENDLOOP;CLOSEcursor_name;END;

76利用简单循环统计并输出各个部门的平均工资。DECLARECURSORc_dept_statISSELECTdeptno,avg(sal)avgsalFROMempGROUPBYdeptno;v_deptc_dept_stat%ROWTYPE;BEGINOPENc_dept_stat;LOOPFETCHc_dept_statINTOv_dept;EXITWHENc_dept_stat%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_dept.avgsal);ENDLOOP;CLOSEc_dept_stat;END;77利用WHILE循环检索游标DECLARECURSORcursor_nameISSELECT…;BEGINOPENcursor_name;FETCH…INTO…;WHILEcursor_name%FOUNDLOOPFETCH…INTO…;

……ENDLOOP;CLOSEcursor;END;

78利用WHILE循环统计并输出各个部门的平均工资。DECLARECURSORc_dept_statISSELECTdeptno,avg(sal)avgsalFROMempGROUPBYdeptno;v_deptc_dept_stat%ROWTYPE;BEGINOPENc_dept_stat;FETCHc_dept_statINTOv_dept;WHILEc_dept_stat%FOUNDLOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_dept.avgsal);FETCHc_dept_statINTOv_dept;ENDLOOP;CLOSEc_dept_stat;END;79利用FOR循环检索游标DECLARECURSORcursor_nameISSELECT…;BEGINFORloop_variableINcursor_nameLOOP

……ENDLOOP;END;80FOR循环说明系统隐含地定义了一个数据类型为%ROWTYPE的变量，并以此作为循环的计算器。系统自动打开游标，不用显式地使用OPEN语句打开；系统重复地自动从游标工作区中提取数据并放入计数器变量中。系统自动进行%FOUND属性检查以确定是否有数据当游标工作区中所有的记录都被提取完毕或循环中断时，系统自动地关闭游标。81利用FOR循环统计并输出各个部门的平均工资。DECLARECURSORc_dept_statISSELECTdeptno,avg(sal)avgsalFROMempGROUPBYdeptno;BEGINFORv_deptINc_dept_statLOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_dept.avgsal);ENDLOOP;END;82隐式FOR游标BEGIN

FORv_empIN(select*fromempwheredeptno=10)LOOP

DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);

ENDLOOP;END;83利用游标更新或删除数据游标定义语法

CURSORcursor_nameISSELECTselect_list_itemFROMtableFORUPDATE

更新或修改数据的语法为：

UPDATE|DELETE…WHERECURRENTOFcursor_name

注意由于COMMIT语句会释放会话拥有的任何锁，因此如果在检索游标的循环内使用COMMIT语句会释放定义游标时对数据加的锁，从而导致利用游标修改或删除数据的操作失败。84修改员工的工资，如果员工的部门号为10，工资提高100；部门号为20，工资提高150；部门号为30，工资提高200；否则工资提高250。85DECLARECURSORc_empISSELECT*FROMempFORUPDATE;v_incrementNUMBER;BEGINFORv_empINc_empLOOPCASEv_emp.deptnoWHEN10THENv_increment:=100;WHEN20THENv_increment:=150;WHEN30THENv_increment:=200;ELSEv_increment:=250;ENDCASE;UPDATEempSETsal=sal+v_incrementWHERECURRENTOFc_emp;ENDLOOP;END;8615.4.3隐式游标用于处理INSERT、UPDATE、DELETE和SELECT…INTO语句没有OPEN、FETCH、CLOSE命令属性

SQL%ISOPEN SQL%FOUND SQL%NOTFOUNDSQL%ROWCOUNT87修改员工号为1000的员工工资，将其工资增加100。如果该员工不存在，则向emp表中插入一个员工号为1000，工资为1500的员工。BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%NOTFOUNDTHENINSERTINTOemp(empno,sal)VALUES(1000,1500);ENDIF;END;或BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%ROWCOUNT=0THENINSERTINTOemp(empno,sal)VALUES(1000,1500);ENDIF;END;8815.5异常处理异常概述异常处理过程异常的传播8915.5.1异常概述Oracle错误处理机制异常的类型90Oracle错误处理机制概念一个错误对应一个异常，当错误产生时抛出相应的异常，并被异常处理器捕获，程序控制权传递给异常处理器，由异常处理器来处理运行时错误。91异常的类型预定义的Oracle异常非预定义的Oracle异常用户定义的异常92预定义的异常异常情况名错误代码描述CURSOR_ALREADY_OPENORA-06511尝试打开已经打开的游标

INVALID_CURSORORA-01001不合法的游标操作（如要打开已经关闭的游标）

NO_DATA_FOUNDORA-01403没有发现数据

TOO_MANY_ROWSORA-01422一个SELECTINTO语句匹配多个数据行INVALID_NUMBERORA-01722转换成数字失败

（‘X’）

VALUE_ERRORORA-06502截断、算法或转换错误，通常出现在赋值错误

ZERO_DIVIDEORA-01476除数为0

ROWTYPE_MISMATCHORA-06504主机游标变量与PL/SQL游标变量类型不匹配93异常情况名错误代码描述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内部错误94异常情况名错误代码描述ACCESS_INTO_NULLORA-06530给空对象属性赋值COLLECTION_IS_NULLORA-06531对某NULLPL/SQL表或可变数组试图应用集合方法，而不是EXISTS

SELF_IS_NULLORA-30625调用空对象实例的方法SUBSCRIPT_BEYOND_COUNTORA-06533对嵌套表或数组索引引用时超出集合中元素的数量SUBSCRIPT_OUTSIDE_LIMITORA-06532对嵌套表或可变数组索引的引用超出声明的范围95非预定义异常在语句块的声明部分声明一个异常名称e_integrityEXCEPTION;通过PRAGMAEXCEPTION-INIT将异常与一个Oracle错误号相关联:PRAGMAEXCEPTION-INIT(e_integrity.-2291)在异常处理部分捕捉并处理异常：WHENe_integrityTHEN...

96用户自定义的异常用户自定义异常必须在声明部分进行声明。当异常发生时，系统不能自动触发，需要用户使用RAISE语句。在异常处理部分捕捉并处理异常。9715.5.2异常处理过程在声明部分为错误定义异常，包括非预定义异常和用户定义异常。e_exceptionEXCEPTION;PRAGMAEXCEPTION_INIT(e_exceptioin,-#####);在执行过程中当错误产生时抛出与错误对应的异常。RAISEuser_define_exception；在异常处理部分通过异常处理器捕获异常，并进行异常处理。98异常的捕获与处理异常处理器的基本形式为EXCEPTIONWHENexception1[ORexcetpion2…]THENsequence_of_statements1;WHENexceptioin3[ORexception4…]THENsequence_of_statements2;

……WHENOTHERSTHENsequence_of_statementsn;END;99注意：一个异常处理器可以捕获多个异常，只需要在WHEN子句中用OR连接即可；一个异常只能被一个异常处理器捕获，并进行处理。100查询名为SMITH的员工工资，如果该员工不存在，则输出“Thereisnotsuchanemployee!”；如果存在多个同名的员工，则输出其员工号和工资。DECLAREv_salemp.sal%type;BEGINSELECTsalINTOv_salFROMempWHEREename='SMITH';DBMS_OUTPUT.PUT_LINE(v_sal);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemplyee!');WHENTOO_MANY_ROWSTHENFORv_empIN(SELECT*FROMempWHEREename='SMITH')LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.sal);ENDLOOP;END;101删除dept表中部门号为10的部门信息，如果不能删除则输出“Therearesubrecordsinemptable!”。DECLAREe_deptno_fkEXCEPTION;PRAGMAEXCEPTION_INIT(e_deptno_fk,-2292);BEGINDELETEFROMdeptWHEREdeptno=10;EXCEPTIONWHENe_deptno_fkTHENDBMS_OUTPUT.PUT_LINE('Therearesubrecordsinemptable!');END;102修改7844员工的工资，保证修改后工资不超过6000。DECLAREe_highlimitEXCEPTION;v_salemp.sal%TYPE;BEGINUPDATEempSETsal=sal+100WHEREempno=7844RETURNINGsalINTOv_sal;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;END;103OTHERS异常处理器OTHERS异常处理器是一个特殊的异常处理器，可以捕获所有的异常。通常，OTHERS异常处理器总是作为异常处理部分的最后一个异常处理器，负责处理那些没有被其他异常处理器捕获的异常。104DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;BEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';UPDATEempSETsal=sal+100WHEREempno=7900;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thereissomewronginselecting!');END;105可以通过两个函数来获取错误相关信息。SQLCODE：返回当前错误代码。如果是用户定义错误返回值为1；如果是ORA-1403：NODATAFOUND错误，返回值为100；其他Oracle内部错误返回相应的错误号。SQLERRM：返回当前错误的消息文本。如果是Oracle内部错误，返回系统内部的错误描述；如果是用户定义错误，则返回信息文本为“User-definedException”。106DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;v_codeNUMBER(6);v_textVARCHAR2(200);BEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';UPDATEempSETsal=sal+100WHEREempno=7900;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;WHENOTHERSTHENv_code:=SQLCODE;v_text:=SQLERRM;DBMS_OUTPUT.PUT_LINE(v_code||''||v_text);END;10715.5.3异常的传播可执行部分异常的传播如果当前语句块有该异常的处理器，则执行之，并且成功完成该语句块。然后，控制权传递到外层语句块。

如果当前语句块没有该异常的处理器，则通过在外层语句块中产生该异常来传播该异常。然后，执行对外层语句块执行步骤1。如果没有外层语句块，则该异常将传播到调用环境。

108DECLAREv_salemp.sal%TYPE;BEGINBEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');END;DBMS_OUTPUT.PUT_LINE('Nowthisisoutputtedbyouterblock!');END;/Thereisnotsuchanemployee!Nowthisisoutputtedbyouterblock!109DECLAREv_salemp.sal%TYPE;BEGINBEGINSELECTsalINTOv_salFROMempWHEREdeptno=10;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');END;DBMS_OUTPUT.PUT_LINE('Nowthisisoutputtedbyouterblock!');EXCEPTIONWHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('Therearemorethanoneemployee!');END;/Therearemorethanoneemployee!110声明部分异常的传播声明部分的异常立刻传播到外层语句块，即使当前语句块有异常处理器。

异常处理部分的异常的传播异常处理器中产生的异常，可以有RAISE语句显式产生，也可以通过运行时错误而隐含产生。异常立即被传播到外层语句块。

111BEGINDECLAREv_numberNUMBER(6):='ABC';BEGINv_number:=10;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thisisoutputtedbyinnerblock!');END;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thisisoutputtedbyouterblock!');END;/Thisisoutputtedbyouterblock!11215.6存储子程序存储过程函数局部子程序113存储子程序是指被命名的PL/SQL块，以编译的形式存储在数据库服务器中，可以在应用程序中进行调用，是PL/SQL程序模块化的一种体现。存储子程序是以独立对象的形式存储在数据库服务器中，因此是一种全局结构，与之对应的是局部子程序，即嵌套在PL/SQL块中的局部过程和函数，其存储位置取决于其所在的父块的位置。114存储过程存储过程的创建存储过程的调用存储过程的管理115存储过程的创建CREATE[ORREPLACE]PROCEDUREprocedure_name(parameter1_name[mode]datatype[DEFAULT|:=value][,parameter2_name[mode]datatype[DEFAULT|:=value],…])AS|IS/*Declarativesectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsectionishere*/END[procedure_name];

PROCEDUREBODY116参数模式IN

当过程被调用时，实参值被传递给过程。在过程内，该参数起常数作用，可读不可写。调用结束，实参值不变。（默认参数类型）OUT

当过程被调用时，实参值被忽略。在过程内，该参数起未初始化的变量作用，值为NULL。过程内，该参数可读可写。调用结束，形参赋给实参。INOUT

当过程被调用时，实参值被传递给过程。在过程内，该参数起已初始化变量作用，过程内，该参数可读可写。调用结束，形参赋给实参。117参数限制声明形参时不能定义形参的长度或精度、刻度参数传递IN参数为引用传递，即实参的指针被传递给形参；OUT、INOUT参数为值传递，即实参的值被复制给形参。118创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。CREATEORREPLACEPROCEDUREshow_emp(p_deptnoemp.deptno%TYPE)ASv_salemp.sal%TYPE;BEGINSELECTavg(sal)INTOv_salFROMempWHEREdeptno=p_deptno;DBMS_OUTPUT.PUT_LINE(p_deptno||''||'averagesalaryis:'||v_sal);FORv_empIN(SELECT*FROMempWHEREdeptno=p_deptnoANDsal>v_sal)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedepartmentdoesn’’texists!');ENDshow_emp;119通常，存储过程不需要返回值，如果需要返回一个值可以通过函数调用实现。但是，如果希望返回多个值，可以使用OUT或INOUT模式参数来实现。120创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。CREATEORREPLACEPROCEDUREreturn_deptinfo(p_deptnoemp.deptno%TYPE,p_avgsalOUTemp.sal%TYPE,p_countOUTemp.sal%TYPE)ASBEGINSELECTavg(sal),count(*)INTOp_avgsal,p_countFROMempWHEREdeptno=p_deptno;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedepartmentdon’’texists!');ENDreturn_deptinfo;121存储过程的调用在SQL*PLUS中调用EXECprocedure_name(parameter_list)EXECUTEshow_emp(10)在PL/SQL块中调用BEGINprocedure_name(parameter_list);END；122DECLAREv_avgsalemp.sal%TYPE;v_countNUMBER;BEGINshow_emp(20);return_deptinfo(10,v_avgsal,v_count);DBMS_OUTPUT.PUT_LINE(v_avgsal||''||v_count);END;123存储过程的管理修改存储过程CREATEORREPLACEPROCEDURE重新编译存储过程ALTERPROCEDUREprocedure_nameCOMPILE；删除存储过程DROPPROCEDUREprocedure_name名；查看过程源代码selecttextfromuser_sourcewherename=procedure_name;12415.6.2函数函数概述函数的创建函数的调用函数的管理125函数概述函数用于返回特定数据，可以返回一个或多个值。在一个函数中必须包含一个或多个RETURN语句函数调用是PL/SQL表达式的一部分，而过程调用可以是一个独立的PL/SQL语句126函数的创建CREATE[ORREPLACE]FUNCTIONfunction_name(parameter1_name[mode]datatype[DEFAULT|:=value][,parameter2_name[mode]datatype[DEFAULT|:=value],…])RETURNreturn_datatypeAS|IS/*Declarativesectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsectionishere*/END[function_name];FUNCTIONBODY127创建一个以部门号为参数，返回该部门最高工资的函数。CREATEORREPLACEFUNCTIONreturn_maxsal(p_deptnoemp.deptno%TYPE)RETURNemp.sal%TYPEASv_maxsalemp.sal%TYPE;BEGINSELECTmax(sal)INTOv_maxsalFROMempWHEREdeptno=p_deptno;RETURNv_maxsal;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedeptnoisinvalid!');ENDreturn_maxsal;128函数的调用在SQL语句中调用函数在PL/SQL中调用函数129DECLAREv_salemp.sal%TYPE;BEGINFORv_deptIN(SELECTDISTINCTdeptnoFROMemp)LOOPv_sal:=return_maxsal(v_dept.deptno);DBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_sal);ENDLOOP;END;

130函数的管理修改函数CREATEORREPLACEFUNCTIONfunction_name重新编译存储过程ALTERFUNCTIONfunction_nameCOMPILE；删除存储过程DROPFUNCTIONfunction_name；查看过程源代码selecttextfromuser_sourcewherename=function_name;13115.6.3局部子程序局部子程序嵌套在其他PL/SQL块中的子程序。只能在其定义的块内部被调用，而不能在其父块外被调用。使用局部子程序时需要注意：局部子程序只在当前语句块内有效；局部子程序必须在PL/SQL块声明部分的最后进行定义；局部子程序必须在使用之前声明，如果是子程序间相互引用，则需要采用预先声明；局部子程序可以重载。132在一个块内部定义一个函数和一个过程。函数以部门号为参数返回该部门的平均工资；过程以部门号为参数，输出该部门中工资低于部门平均工资的员工的员工号、员工名。133DECLAREv_deptnoemp.deptno%TYPE;v_avgsalemp.sal%TYPE;FUNCTIONreturn_avgsal(p_deptnoemp.deptno%TYPE)RETURNemp.sal%TYPEASv_salemp.sal%TYPE;BEGINSELECTavg(sal)INTOv_salFROMempWHEREdeptno=p_deptno;RETURNv_sal;ENDreturn_avgsal;PROCEDUREshow_emp(p_deptnoemp.deptno%TYPE)ASCURSORc_empISSELECT*FROMempWHEREsal<return_avgsal(p_deptno);BEGINFORv_empINc_empLOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;ENDshow_emp;BEGINv_deptno:=&x;v_avgsal:=return_avgsal(v_deptno);show_emp(v_deptno);END;134存储子程序与局部子程序区别在于：存储子程序己经编译好放在数据库服务器端，可以直接调用，而局部子程序存在于定义它的语句块中，在运行时先进行编译；存储子程序不能重载，而局部子程序可以进行重载；存储子程序可以被任意的PL/SQL块调用，而局部子程序只能在定义它的块中被调用。135在一个PL/SQL块中重载两个过程，一个以员工号为参数，输出该员工信息；另一个以员工名为参数，输出员工信息。利用这两个过程分别查询员工号为7902，7934，以及员工名为SMITH，FORD的员工信息。136DECLAREPROCEDUREshow_empinfo(p_empnoemp.empno%TYPE)ASv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=p_empno;DBMS_OUTPUT.PUT_LINE(v_emp.ename||''||v_emp.deptno);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');ENDshow_empinfo;

PROCEDUREshow_empinfo(p_enameemp.ename%TYPE)ASv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREename=p_ename;DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.deptno);137EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('Therearemorethanonemployee!');ENDshow_empinfo;BEGINshow_empinfo(7902);show_empinfo(7934);show_empinfo('SMITH');show_empinfo('FORD');END;138存储子程序与局部子程序区别存储子程序己经编译好放在数据库服务器端，可以直接调用，而局部子程序存在于定义它的语句块中，在运行时先进行编译；存储子程序不能重载，而局部子程序可以进行重载；存储子程序可以被任意的PL/SQL块调用，而局部子程序只能在定义它的块中被调用13915.7包包概述包的创建包的调用包的重载包的初始化包的管理140包概述包是包含一个或多个子程序单元（过程、函数等）的容器包是全局的包类型数据库内置包用户创建的包包由包规范和包体两部分组成，在数据库中独立存储141包规范声明了软件包中所有内容，如过程、函数、游标、类型、异常和变量等，其中过程和函数只包括原型信息，不包含任何子程序代码。包体中包含了在包头中的过程和函数的实现代码。包体中还可以包括在规范中没有声明的变量、游标、类型、异常、过程和函数，但是它们是私有元素，只能由同一包体中其他过程和函数使用。142创建包规范语法CREATEORREPLACEPACKAGEpackage_name

IS|AS[PRAGMASERIALLY_RESUABLE]type_definition|variable_declaration|exception_declaration|cursor_declaration|procedure_declaration|function_declarationEND[package_name];143注意：元素声明的顺序可以是任意的，但必须先声明后使用；所有元素是可选的；过程和函数的声明只包括原型，不包括具体实现。144创建一个软件包，包括2个变量、2个过程和1个异常。CR