Oracle第14章PLSQL语言基础

1、1第14章 PL/SQL语言基础2本章内容容PL/SQL概述PL/SQL基础控制结构构游标异常处理理3本章要求求掌握PL/SQL程序基本本结构掌握PL/SQL程序控制制结构掌握PL/SQL程序游标标应用掌握PL/SQL程序异常常处理机机制414.1PL/SQL概述PL/SQL特点PL/SQL功能特性性PL/SQL执行过程程与开发发工具514.1.1PL/SQL特点与SQL语言紧密密集成。减小网络络流量，提高应应用程序序的运行行性能。模块化的的程序设设计功能能，提高高了系统统可靠性性。服务器端端程序设设计，可可移植性性好。614.1.2PL/SQL功能特性性语句块结结构异常处理理变量和类类型条件

2、语句句循环结构构游标过程、函函数和触触发器包集合动态SQL批绑定对象特性性714.1.3PL/SQL执行过程程与开发发工具PL/SQL块SQL语句客户端应应用程序序PL/SQL引擎数据库服服务器过程化语语句执行行器SQL执行器块中SQL语句PL/SQL执行过程程8PL/SQL开发工具具SQL*PLUSProcedureBuilderOracleForm、OracleReportsPL/SQLDeveloper914.2PL/SQL基础PL/SQL程序结构构词法单元元数据类型型变量与常常量PL/SQL记录编译指示示PL/SQL中的SQL语句1014.2.1PL/SQL程序结构构PL/SQL块的组

3、成成PL/SQL块分类11（1）PL/SQL块的组成成PL/SQL程序的基基本单元元是语句句块，所所有的PL/SQL程序都是是由语句句块构成成的。一个完整整的PL/SQL语句块由由3个部分组组成。12声明部分分主要用于于声明变变量、常常量、数数据类型型、游标标、异常常处理名名称以及及本地（局部）子程序序定义等等。可执行部部分执行部分分是PL/SQL块的功能能实现部部分。该该部分通通过变量量赋值、流程控控制、数数据查询询、数据据操纵、数据定定义、事事务控制制、游标标处理等等实现块块的功能能。异常处理理部分异常处理理部分用用于处理理该块执执行过程程中产生生的异常常。13注意：执行部分分是必须须的，

4、而而声明部部分和异异常部分分是可选选的可以在一一个块的的执行部部分或异异常处理理部分嵌嵌套其他他的PL/SQL块；所有的PL/SQL块都是以以“END;”结束。14DECLAREv_ename VARCHAR2(10);BEGINSELECTenameINTOv_ename FROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_ename);EXCEPTIONWHEN NO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thereisnot suchaemployee);END;15DECLAREv_salNUMBER(6,2);v

5、_deptnoNUMBER(2);BEGINBEGINSELECTdeptno INTOv_deptnoFROMempWHEREempno=7844;END;SELECTavg(sal)INTOv_salFROM empWHEREdeptno=v_deptno;DBMS_OUTPUT.PUT_LINE(v_sal);END;16注意若要在SQL*Plus环境中看看到DBMS_OUTPUT.PUT_LINE方法的输输出结果果，必须须将环境境变量SERVEROUTPUT设置为ON。SETSERVEROUTPUT ON17（2）PL/SQL块分类匿名块匿名块是是指动态态生成，只能执执行一次次的块，不

6、能由由其他应应用程序序调用。命名块命名块是是指一次次编译可可多次执执行的PL/SQL程序，包包括函数数、存储储过程、包、触触发器等等。它们们编译后后放在服服务器中中，由应应用程序序或系统统在特定定条件下下调用执执行。18命名块示示例CREATEORREPLACEPROCEDUREshowavgsal(p_deptnoNUMBER)ASv_salNUMBER(6,2);BEGINSELECTavg(sal)INTOv_salFROM empWHEREdeptno=p_deptno;DBMS_OUTPUT.PUT_LINE(v_sal);ENDshowavgsal;1914.2.2词法单元元字符集

7、标识符分隔符常量值注释20（1）字符集集PL/SQL的字符集集包括：大小写字字母：AZ，az数字：09空白：制制表符、空格和和回车数字符号号：+ -*/=标点符号号： !# $%&*（）_ | ？；：, .“注意PL/SQL字符集不不区分大大小写。21（2）标识符符标识符用用于定义义PL/SQL变量、常常量、异异常、游游标名称称、游标标变量、参数、子程序序名称和和其他的的程序单单元名称称等。在PL/SQL程序中，标识符符是以字字母开头头的，后后边可以以跟字母母、数字字、美元元符号（$）、井号号（#）或下划划线（_），其最最大长度度为30个字符，并且所所有字符符都是有有效的。例如，X，v_emp

8、no，v_$等都是有有效的标标识符，而X+y，_temp则是非法法的标识识符。注意如果标识识符区分分大小写写、使用用预留关关键字或或包含空空格等特特殊符号号，则需需要用“”括起来，称为引引证标识识符。例例如标识识符“mybook”和“exception”。22（3）分隔符符+-*/=:=!=()/*/%;:.“.|=*-分隔符是是指有特特定含义义的单个个符号或或组合符符号23（4）常量值值字符型文文字以单引号号引起来来的字符符串，在在字符串串中的字字符区分分大小写写。如果果字符串串中本身身包含单单引号，则用两两个连续续的单引引号进行行转义。数字型文文字分为整数数与实数数两类。其中，整数没没有小

9、数数点，如如123；而实数数有小数数点，如如123.45。可以用用科学计计数法表表示数字字型文字字，如123.45可以表示示为1.2345E2。布尔型文文字预定义的的布尔型型变量的的取值，包括TRUE，FALSE，NULL三个值。日期型文文字表示日期期值，其其格式随随日期类类型格式式不同而而不同。24（5）注释单行注释释-多行注释释以“/*”开始，以以“*/”结束。DECLAREv_departmentCHAR(10);-variabletohold thedepartment nameBEGIN/*querythedepartmentname whichdepartment numberis

10、10ouputthedepartmentname intov_department*/SELECTdnameINTOv_departmentFROMdeptWHEREdeptno=10;END;2514.2.3数据类型型数字类型型字符类型型日期/区间类型型行标识类类型布尔类型型原始类型型LOB类型引用类型型记录类型型集合类型型%TYPE与%ROWTYPE26PL/SQL中常用的的基本数数据类型型分类数据类型数字类型NUMBER、BINARY_NUMBER PLS_NUMBER字符类型VARCHAR2、CHAR、LONG、NCHAR、NVARCHAR日期/区间类型 DATE、TIMESTAMP、

11、INTERVAL行标识类型ROWID、UROWID布尔类型BOOLEAN（TRUE、FALSE、NULL）原始类型RAW、LONG RAWLOB类型CLOB、BLOB、NCLOB、BFILE引用类型 REF CURSOR，REF object_type。 记录类型RECORD集合类型TABLE、VARRAY27数字类型型NUMBER类型以十十进制形形式存储储整数和和浮点数数，语法法为NUMBER（p，s）。其中中，p为精度，即所有有有效数数字位数数；s为刻度范范围，即即小数位位数。p的取值范范围为138。BINARY_INTEGER类型用于于表示从从-2147483647+2147483647

12、之间的整整数，以以二进制制形式存存储。当当发生溢溢出时，将自动动转换成成NUMBER类型。PLS_INTEGER类型表示示范围与与BINARY_INTEGER相同，但但发生溢溢出时会会产生错错误。28字符类型型PL/SQL中的字符符类型与与Oracle数据库中中的字符符类型类类似，但但是允许许字符串串的长度度有所不不同。VARCHAR2，CHAR主要用于于存储来来自本地地数据库库字符集集的字符符，而NCHAR，NVARCHAR2用于存储储来自国国家字符符集的字字符串。类 型PL/SQL中最大字节数Oracle中最大字节数VARCHAR2327674000NVARCHAR2327674000CH

13、AR327672000NCHAR327672000LONG327602GB29日期/区间类型型DATE：与数据据库中的的DATE类型相同同，存储储日期和和时间信信息，包包括世纪纪、年、月、日日、小时时、分和和秒，不不包括秒秒的小数数部分。TIMESTAMP：与DATE类型相似似，但包包括秒的的小数部部分，有有以下3种形式。TIMESTAMP(p)：其中p为秒字段段的小数数部分精精度。TIMESTAMP(p)WITHTIMEZONE：返回当当前时区区的时间间戳。TIMESTAMP(p)WITHLOACLTIMEZONE：返回数数据库时时区的时时间戳。30INTERVAL：用于存存储两个个时间戳戳

14、之间的的时间间间隔，有有下面两两种形式式。INTERVALYEAR (p)TO MONTH：两个时时间戳相相差的年年数和月月数。INTERVALDAY(dp)TOSECOND(sp)：两个时时间戳相相差的天天数和秒秒数。31行标识类类型ROWID表示行的的物理地地址UROWID既可以表表示行的的物理地地址，也也可以表表示行的的逻辑地地址。布尔类型型（BOOLEAN）只能在PL/SQL中使用，其取值值为逻辑辑值，包包括TRUE、FALSE、NULL。原始类型型与Oracle数据库中中的原始始类型相相似，但但子节数数不同。类 型PL/SQL中最大字节数Oracle中最大字节数RAW32767200

15、0LONG RAW327672G32LOB类型包括BLOB，CLOB，NCLOB和BFILE四种类型型。其中中BLOB存放二进进制数据据，CLOB，NCLOB存放文本本数据，而BFILE存放指向向操作系系统文件件的指针针。LOB类型变量量可以存存储4 GB的数据量量。引用类型型引用类型型类似于于其他高高级语言言中的指指针类型型。在PL/SQL中，引用用类型包包括游标标的引用用类型和和对象的的引用类类型，即即REFCURSOR和REFobject_type。33记录类型型记录类型型是复合合类型，类似于于C语言中的的结构体体，是一一个包含含若干个个成员分分量的复复合类型型。在使用记记录类型型时，需

16、需要先在在声明部部分定义义记录类类型和记记录类型型的变量量，然后后在执行行部分引引用该记记录类型型变量或或其成员员分量。集合类型型集合类型型是复合合类型，包括索索引表类类型、嵌嵌套表类类型和可可变数组组类型。集合类型型与记录录类型的的区别在在于，记记录类型型中的成成员分量量可以是是不同类类型的，类似于于结构体体，而集集合类型型中所有有的成员员分量必必须具有有相同的的数据类类型，类类似于数数组。34%TYPE与%ROWTYPE如果要定定义一个个类型与与某个变变量的数数据类型型或数据据库表中中某个列列的数据据类型一一致（不不知道该该变量或或列的数数据类型型）的变变量，可可以利用用%TYPE来实现。

17、如果要定定义一个个与数据据库中某某个表结结构一致致的记录录类型的的变量，可以使使用%ROWTYPE来实现。注意变量的类类型随参参照的变变量类型型、数据据库表列列类型、表结构构的变化化而变化化；如果数据据库表列列中有NOTNULL约束，则则%TYPE与%ROWTYPE返回的数数据类型型没有此此限制。35DECLAREv_salemp.sal%TYPE;v_empemp%ROWTYPE;BEGINSELECTsal INTOv_salFROMempWHEREempno=7844;SELECT*INTO v_emp FROMemp WHERE empno=7900;DBMS_OUTPUT.PUT_L

18、INE(v_sal);DBMS_OUTPUT.PUT_LINE(v_emp.ename|v_emp.sal);END;3614.2.4变量与常常量变量与常常量的定定义变量的作作用域37变量声明明（1）变量与与常量的的定义变量定义义的一般般格式variable_name CONSTANTdatatypeNOTNULLDEFAULT|:=expression;说明变量或常常量名称称是一个个PL/SQL标识符，应符合合标识符符命名规规范；每行只能能定义一一个变量量；如果加上上关键字字CONSTANT，则表示示所定义义的是一一个常量量，必须须为它赋赋初值；如果定义义变量时时使用了了NOTNULL关键字

19、，则必须须为变量量赋初值值；如果变量量没有赋赋初值，则默认认为NULL；使用DEFAULT或“:=”运算符为为变量初初始化。38DECLAREv1NUMBER(4);v2NUMBER(4)NOT NULL:=10;v3CONSTANTNUMBER(4)DEFAULT100;BEGINIFv1ISNULL THENDBMS_OUTPUT.PUT_LINE(V1ISNULL!);ENDIF;DBMS_OUTPUT.PUT_LINE(v2|v3);END;39（2）变量的的作用域域变量的作作用域是是指变量量的有效效作用范范围，从从变量声声明开始始，直到到块结束束。如果PL/SQL块相互嵌嵌套，则则在

20、内部部块中声声明的变变量是局局部的，只能在在内部块块中引用用，而在在外部块块中声明明的变量量是全局局的，既既可以在在外部块块中引用用，也可可以在内内部块中中引用。如果内部部块与外外部块中中定义了了同名变变量，则则在内部部块中引引用外部部块的全全局变量量时需要要使用外外部块名名进行标标识。40DECLAREv_enameCHAR(16);v_outerNUMBER(5);BEGINv_outer :=10;DECLAREv_ename CHAR(20);v_inner DATE;BEGINv_inner:=sysdate;v_ename:=INNERV_ENAME;OUTER.v_ename:=

21、OUTERV_ENAME;END;DBMS_OUTPUT.PUT_LINE(v_ename);END;4114.2.5PL/SQL记录用户定义义记录类类型及变变量利用%ROWTYPE获取记录录类型定定义变量量记录类型型变量的的应用在SELECT语句中使使用记录录类型变变量在INSERT语句中使使用记录录类型变变量在UPDATE语句中使使用记录录类型变变量在DELETE语句中使使用记录录类型变变量42（1）用户定定义记录录类型及及变量定义记录录类型的的语法为为TYPE record_type IS RECORD(field1datatype1 NOTNULLDEFAULT|:=expr1,fie

22、ld2datatype2 NOTNULL DEFAULT|:=expr2,fieldndatatypen NOTNULL DEFAULT|:=exprn);注意：相同记录录类型的的变量可可以相互互赋值；不同记录录类型的的变量，即使成成员完全全相同也也不能相相互赋值值；记录类型型只能应应用于定定义该记记录类型型的PL/SQL块中，即即记录类类型是局局部的。43利用记录录类型以以及记录录类型变变量，保保存员工工信息。DECLARETYPE t_emp IS RECORD(empnoNUMBER(4),enameCHAR(10),salNUMBER(6,2);v_empt_emp;BEGINSELE

23、CTempno,ename,sal INTOv_empFROM empWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.ename|v_emp.sal);END;44（2）利用%ROWTYPE获取记录录类型定定义变变量DECLAREv_emp1emp%ROWTYPE;v_emp2emp%ROWTYPE;CURSORc_empISSELECT empno,ename FROMempWHEREdeptno=10;v_emp10 c_emp%ROWTYPE;BEGINSELECT*INTO v_emp1FROM empWHEREempno=7844;OPEN c_

24、emp;LOOPFETCHc_empINTO v_emp10;EXIT WHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp10.empno|v_emp10.ename);ENDLOOP;CLOSEc_emp;END;45（3）记录类类型变量量的应用用在SELECT语句中使使用记录录类型变变量在SELECTINTO语句中使使用记录录类型变变量DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTO v_emp FROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.

25、ename|v_emp.sal);END;注意记录类型型变量中中分量的的个数、顺序、类型应应该与查查询列表表中列的的个数、顺序、类型完完全匹配配。46在SELECT语句中使使用记录录类型变变量在SELECTINTO语句中使使用记录录类型变变量成员员DECLAREv_empemp%ROWTYPE;BEGINSELECTempno,ename,sal INTOv_emp.empno,v_emp.ename,v_emp.salFROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename|v_emp.sal);END;47在IN

26、SERT语句中使使用记录录类型变变量在VALUES子句中使使用记录录类型变变量DECLAREv_deptdept%ROWTYPE;BEGINv_dept.deptno:=50;v_dept.loc:=BEIJING;V_dept.dname:=COMPUTER;INSERTINTODEPT VALUESv_dept;END;注意记录类型型变量中中分量的的个数、顺序、类型应应该与表表中列的的个数、顺序、类型完完全匹配配。48在INSERT语句中使使用记录录类型变变量在VALUES子句中使使用记录录类型变变量成员员DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTO v

27、_emp FROMempWHEREempno=7844;INSERTINTOemp(empno,ename,mgr,sal)VALUES(1234,TOM,v_emp.mgr,v_emp. sal);END;49在UPDATE语句中使使用记录录类型变变量在SET子句中使使用记录录类型变变量（使使用ROW关键字）DECLAREv_deptdept%ROWTYPE;BEGINv_dept.deptno:=50;v_dept.loc:=TIANJIN;V_dept.dname:=COMPUTER;UPDATEdeptSETROW=v_deptWHEREdeptno=50;END;注意记录类型型变量中

28、中分量的的个数、顺序、类型应应该与表表中列的的个数、顺序、类型完完全匹配配。50在UPDATE语句中使使用记录录类型变变量在SET子句中使使用记录录类型变变量成员员DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTO v_emp FROMempWHEREempno=7844;UPDATEemp SETsal=v_emp.sal,comm=v_mWHEREempno=7369;END;51在DELETE语句中使使用记录录类型变变量DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTO v_emp FROMempWHEREempno=7844;

29、DELETEFROMempWHEREdeptno=v_emp.deptno;END;5214.2.6编译指示示编译指示示是对编编译程序序发出的的特殊指指令，也也称为伪伪指令，不会改改变程序序含义。它只是是向编译译程序传传递信息息，类似似于嵌入入在SQL中的注释释。在PL/SQL中使用PRAGMA关键字通通知编译译程序，PL/SQL语句的剩剩余部分分是一个个编译指指示或命命令。编编译指示示在编译译时被处处理，而而不会在在运行时时被执行行，类似似于C语言中的的#define。53PL/SQL提供以下下4种编译指指示EXCEPTION_INIT：告诉编编译程序序将一个个特定的的错误号号与程序序中所声

30、声明的异异常标识识符关联联起来。RESTRICT_REFERENCES：告诉编编译程序序打包程程序的纯纯度，即即对函数数中可以以使用的的SQL语句和包包变量进进行限制制。SERIALLY_REUSEABLE：告诉PL/SQL运行引擎擎时，在在数据引引用之间间不要保保持包级级数据。AUTONOMOUS_TRANSACTION：告诉编编译程序序，该程程序块为为自治事事务，即即该事务务的提交交和回滚滚是独立立进行的的。5414.2.7PL/SQL中SQL语句由于PL/SQL执行采用用早期绑绑定，即即在编译译阶段对对变量进进行绑定定，识别别程序中中标识符符的位置置，检查查用户权权限、数数据库对对象等信

31、信息，因因此在PL/SQL中只允许许出现:SELECTDML(UPDATE、DELETE、INSERT)事务控制制语句（COMMIT、ROLLBACK、SAVEPOINT）注意DDL语句不可可以直接接使用55通常，利利用SQL语句对数数据库进进行操作作时，各各种相关关量都在在代码中中以常量量的形式式指定，而在PL/SQL中可以通通过变量量动态指指定各种种相关量量的值，从而实实现对数数据库的的动态操操作。DECLAREv_empno NUMBER(4);BEGINv_empno:=&x;UPDATEemp SETsal=sal+100WHEREempno=v_empno;END;56SELECT

32、语句在PL/SQL程序中，使用SELECTINTO语句查询询一个记记录的信信息。其语法为为：SELECTselect_list_itemINTOvariable_list|record_variableFROM tableWHEREcondition;57根据员工工名或员员工号查查询员工工信息，程序为为：DECLAREv_empemp%ROWTYPE;v_ename emp.ename%type;v_salemp.sal%type;BEGINSELECT*INTO v_emp FROMempWHEREename=SMITH;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_

33、emp.sal);SELECTename,sal INTOv_ename,v_salFROM empWHEREempno=7900;DBMS_OUTPUT.PUT_LINE(v_ename| |v_sal);END;58注意：SELECTINTO语句只能能查询一一个记录录的信息息，如果果没有查查询到任任何数据据，会产产生NO_DATA_FOUND异常；如如果查询询到多个个记录，则会产产生TOO_MANY_ROWS异常。INTO句子后的的变量用用于接收收查询的的结果，变量的的个数、顺序应应该与查查询的目目标数据据相匹配配，也可可以是记记录类型型的变量量。59用SELECTINTO语句查询询10号

34、部门所所有员工工信息。DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTO v_emp FROMemp WHERE deptno=10;END; /*ERROR位于第1行:ORA-01422:实际返回回的行数数超出请请求的行行数ORA-06512:在line 460DML语句PL/SQL中DML语句对标标准SQL语句中的的DML语句进行行了扩展展，允许许使用变变量。DECLAREv_empno emp.empno%TYPE :=7500;BEGININSERTINTOemp(empno,ename,sal,deptno)VALUES(v_empno,JOAN,230

35、0,20);UPDATEemp SETsal=sal+100WHEREempno=v_empno;DELETEFROMempWHEREempno=v_empno;END;61WHERE标识符的的区分系统首先先查看WHERE子句中的的标识符符是否与与表中的的列名相相同，如如果相同同，则该该标识符符被解释释为列名名；如果果没有同同名列，系统检检查该标标识符是是不是PL/SQL语句块的的变量。字符串比比较填充比较较：通过过在短字字符串后后添加空空格，使使两个字字符串达达到相同同长度，然后根根据每个个字符的的ASCII码进行比比较。非填充比比较：根根据每个个字符的的ASCII码进行比比较，最最先结束束

36、的字符符串为小小。62那么何时时采用填填充比较较，何时时采用非非填充比比较呢？PL/SQL中规定，对定长长的字符符串（CHAR类型的字字符串和和字符串串常量）采用填填充比较较；如果果比较的的字符串串中有一一个是变变长字符符串（VARCHAR2类型的字字符串），则采采用非填填充比较较。63例如，已已知emp表中ename列类型为为VARCHAR2（10），执行行下面的的代码。DECLAREv_ename CHAR(10):=TURNER;-v_enameVARCHAR2(20);-v_enameemp.ename%TYPE:=TURNER;v_salemp.sal%TYPE;BEGINSELEC

37、Tsal INTOv_salFROMempWHEREename=v_ename;dbms_output.put_line(v_sal);END;/DECLARE*第1行出现错错误:ORA-01403:未找到数数据ORA-06512:在line 664产生错误误的原因因是VARCHAR2（10）类型与与CHAR（10）类型比比较时采采用非填填充比较较，因此此无法查查询到员员工名为为“TURNER”的员工。可以将将v_ename变量类型型修改为为VARCHAR2（10）类型，也可以以直接采采用emp.ename%TYPE方式定义义。因此，为为了保证证程序的的正确执执行，一一定要使使PL/SQL语句

38、块中中的变量量与要比比较的数数据库列列拥有相相同的数数据类型型，可以以使用%TYPE或%ROWTYPE来定义变变量。65RETURNING如果要查查询当前前DML语句操作作的记录录的信息息，可以以在DML语句末尾尾使用RETURNING语句返回回该记录录的信息息。RETURNING语句的基基本语法法：RETURNINGselect_list_itemINTO variable_list|record_variable;66DECLAREv_salemp.sal%TYPE;BEGINUPDATEemp SETsal=sal+100WHEREempno=7844RETURNINGsal INTOv

39、_sal;DBMS_OUTPUT.PUT_LINE(v_sal);END;6714.3控制结构构选择结构构循环结构构跳转结构构6814.3.1选择结构构IF语句CASE语句69（1）IF语句语法IFcondition1 THENstatements1;ELSIFcondition2THEN statements2;ELSEelse_statements;ENDIF;注意条件是一一个布尔尔型变量量或表达达式，取取值只能能是TRUE，FALSE，NULL。70例如，输输入一个个员工号号，修改改该员工工的工资资，如果果该员工工为10号部门，工资增增加100；若为20号部门，工资增增加160；若为30

40、号部门，工资增增加200；否则增增加300。71DECLAREv_deptnoemp.deptno%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=&x;SELECTdeptno INTOv_deptnoFROMempWHEREempno=v_empno;IFv_deptno=10THEN v_increment:=100;ELSIFv_deptno=20THEN v_increment:=160;ELSIFv_deptno=30THEN v_increment:=200;ELSEv_increment:=300;EN

41、DIF;UPDATEemp SETsal=sal+v_incrementWHEREempno=v_empno;END;72由于PL/SQL中的逻辑辑运算结结果有TRUE，FALSE和NULL三种，因因此在进进行选择择条件判判断时，要考虑虑条件为为NULL的情况。例如，下面两两个程序序，如果果不考虑虑条件为为NULL的情况，则运行行结果是是一致的的，但是是若考虑虑条件为为NULL的情况，则结果果就不同同了。7374为了避免免条件为为NULL时出现歧歧义，应应该在程程序中进进行条件件是否为为NULL的检查。75（2）CASE语句基本语法法CASEWHEN condition1THENstateme

42、nts1;WHEN condition2THENstatements2;WHEN conditionnTHENstatementsn;ELSEelse_statements;ENDCASE;注意 在CASE语句中，当第一一个WHEN条件为真真时，执执行其后后的操作作，操作作完后结结束CASE语句。其其他的WHEN条件不再再判断，其后的的操作也也不执行行。76根据输入入的员工工号，修修改该员员工工资资。如果果该员工工工资低低于1000，则工资资增加200；如果工工资在10002000之间，则则增加150；如果工工资在20003000之间，则则增加100；否则增增加50。77DECLAREv_sa

43、lemp.sal%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=&x;SELECTsal INTOv_salFROMempWHEREempno=v_empno;CASEWHEN v_sal1000THENv_increment:=200;WHEN v_sal2000THENv_increment:=150;WHEN v_sal50;ENDLOOP;END;83（2）WHILE循环基本语法法WHILEconditionLOOPsequence_of_statement;ENDLOOP;84利用WHILE循环向temp_

44、table表中插入入50条记录。DECLAREv_counterBINARY_INTEGER:=1;BEGINWHILEv_counter=50LOOPINSERTINTOtemp_table VALUES(v_counter,Loopindex);v_counter:=v_counter +1;ENDLOOP;END;85（3）FOR循环基本语法法FORloop_counter IN REVERSElow_bound.high_boundLOOPsequence_of_statement;ENDLOOP;注意：循环变量量不需要要显式定定义，系系统隐含含地将它它声明为为BINARY_INTEG

45、ER变量；系统默认认时，循循环变量量从下界界往上界界递增计计数，如如果使用用REVERSE关键字，则表示示循环变变量从上上界向下下界递减减计数；循环变量量只能在在循环体体中使用用，不能能在循环环体外使使用。86利用FOR循环向temp_table表中插入入50条记录。BEGINFORv_counter IN 1.50 LOOPINSERTINTOtemp_table VALUES(v_counter,LoopIndex);ENDLOOP;END;8714.3.3跳转结构构语法格式式：标号 GOTO标号；说明：块内可以以跳转，内层块块可以跳跳到外层层块，但但外层块块不能跳跳到内层层。IF语句不能

46、能跳入。不能从从循环体体外跳入入循环体体内。不不能从子子程序外外部跳到到子程序序中。由于goto语句的缺缺点，建建议尽量量少用甚甚至不用用goto语句。88DECLAREv_counterBINARY_INTEGER:=1;BEGININSERTINTOtemp_table VALUES(v_counter,Loopindex);v_counter:=v_Counter +1;IFv_counter(SELECTAVG(sal)FROMempWHEREdeptno=10);ELSIFv_table =dept THENOPEN v_cursor FORSELECTdeptno,count(*)

47、 numFROM empGROUPBYdeptno;ELSERAISE_APPLICATION_ERROR(-20000,Input mustbeempordept);ENDIF;131LOOPIFv_table =empTHENFETCHv_cursorINTO v_emp;EXIT WHENv_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename|v_emp.sal|v_emp.deptno);ELSEFETCHv_cursorINTO v_deptno,v_num;EXIT WHENv_cursor%NOTFOUND;D

48、BMS_OUTPUT.PUT_LINE(v_deptno|v_num);ENDIF;ENDLOOP;CLOSEv_cursor;END;13214.5异常处理理异常概述述异常处理理过程异常的传传播13314.5.1异常概述述Oracle错误处理理机制异常的类类型134（1）Oracle错误处理理机制Oracle中对运行行时错误误的处理理采用了了异常处处理机制制。一个错误误对应一一个异常常，当错错误产生生时抛出出相应的的异常，并被异异常处理理器捕获获，程序序控制权权传递给给异常处处理器，由异常常处理器器来处理理运行时时错误。135（2）异常的的类型预定义的的Oracle异常（Oracle错误）非

49、预定义义的Oracle异常（Oracle错误）用户定义义的异常常（用户户定义错错误）136预定义的的Oracle异常当Oracle错误产生生时，与与错误对对应的预预定义异异常被自自动抛出出，通过过捕获该该异常可可以对错错误进行行处理。常用预定定义异常常包括：137异常情况名错误代码描述CURSOR_ALREADY_OPEN ORA-06511 尝试打开已经打开的游标 INVALID_CURSORORA-01001不合法的游标操作（如要打开已经关闭的游标） NO_DATA_FOUNDORA-01403没有发现数据 TOO_MANY_ROWSORA-01422一个SELECT INTO语句匹配多个

50、数据行INVALID_NUMBERORA-01722转换成数字失败 （X） VALUE_ERRORORA-06502截断、算法或转换错误，通常出现在赋值错误 ZERO_DIVIDEORA-01476除数为0 ROWTYPE_MISMATCHORA-06504主机游标变量与PL/SQL游标变量类型不匹配138异常情况名错误代码描述DUP_VAL_ON_INDEXORA-00001违反唯一性约束或主键约束SYS_INVALID_ROWIDORA-01410转换成ROWID失败TIMEOUT_ON_RESOURCEORA-00051在等待资源中出现超时LOGIN_DENIEDORA-01017无效用

51、户名/密码CASE_NOT_FOUNDORA-06592没有匹配的WHEN子句NOT_LOGGED_ONORA-01012没有与数据库建立连接STORAGE_ERRORORA-06500PL/SQL内部错误PROGRAM_ERRORORA-06501PL/SQL内部错误139异常情况名错误代码描述ACCESS_INTO_NULLORA-06530给空对象属性赋值COLLECTION_IS_NULLORA-06531对某NULL PL/SQL表或可变数组试图应用集合方法，而不是EXISTS SELF_IS_NULLORA-30625调用空对象实例的方法SUBSCRIPT_BEYOND_COUNT

52、ORA-06533对嵌套表或数组索引引用时超出集合中元素的数量SUBSCRIPT_OUTSIDE_LIMITORA-06532对嵌套表或可变数组索引的引用超出声明的范围140非预定义义异常有一些Oracle错误没有有预定义义异常与与其关联联，需要要在语句句块的声声明部分分声明一一个异常常名称，然后通通过编译译指示PRAGMAEXCEPTION_INIT将该异常常名称与与一个Oracle错误相关关联。此此后，当当执行过过程出现现该错误误时将自自动抛出出该异常常。141声明一个个异常名名称e_integrityEXCEPTION;将异常与与一个Oracle错误号相相绑定PRAGMAEXCEPTIO

53、N-INIT(e_integrity.-2291)示例DECLAREe_deptno_fkEXCEPTION;PRAGMAEXCEPTION_INIT(e_deptno_fk,-2292);BEGINEXCEPTIONEND;142用户自定定义的异异常用户定义义错误是是指，有有些操作作并不会会产生Oracle错误，但但是从业业务规则则角度考考虑，认认为是一一种错误误。用户自定定义异常常必须在在声明部部分进行行声明。当异常发发生时，系统不不能自动动触发，需要用用户使用用RAISE语句。在异常处处理部分分捕捉并并处理异异常。14314.5.2异常处理理过程异常的定定义异常的抛抛出异常的捕捕获与处处

54、理OTHERS异常处理理器144异常处理理分3个步骤进进行：在声明部部分为错错误定义义异常，包括非非预定义义异常和和用户定定义异常常。在执行过过程中当当错误产产生时抛抛出与错错误对应应的异常常。在异常处处理部分分通过异异常处理理器捕获获异常，并进行行异常处处理。145（1）异常的的定义Oracle中的3种异常，其中预预定义异异常由系系统定义义，而其其他两种种异常则则需要用用户定义义。定义异常常方法e_exceptionEXCEPTION;如果是非非预定义义的异常常，需要要将异常常与一个个Oracle错误相关关联，其其语法为为：PRAGMAEXCEPTION_INIT(e_exception,

55、-#);注意Oracle内部错误误号用一一个负的的5位数表示示，如-02292。其中-20999-20000为用户定定义错误误的保留留号。146（2）异常的的抛出由于系统统可以自自动识别别Oracle内部错误误，因此此当错误误产生时时系统会会自动抛抛出与之之对应的的预定义义异常或或非预定定义异常常。但是是，系统统无法识识别用户户定义错错误，因因此当用用户定义义错误产产生时，需要用用户手动动抛出与与之对应应的异常常。用户定义义异常的的抛出语语法为RAISEuser_define_exception；147（3）异常的的捕获与与处理异常处理理器的基基本形式式为EXCEPTIONWHEN excep

56、tion1ORexcetpion2THENsequence_of_statements1;WHEN exception3ORexception4THENsequence_of_statements2;WHEN OTHERSTHENsequence_of_statementsn;END;注意：一个异常常处理器器可以捕捕获多个个异常，只需在在WHEN子句中用用OR连接即可可；一个异常常只能被被一个异异常处理理器捕获获，并进进行处理理。148查询名为为SMITH的员工工工资，如如果该员员工不存存在，则则输出“Thereisnotsuchanemployee!”；如果存存在多个个同名的的员工，则输出出

57、其员工工号和工工资。DECLAREv_salemp.sal%type;BEGINSELECTsal INTOv_salFROMempWHEREename=SMITH;DBMS_OUTPUT.PUT_LINE(v_sal);EXCEPTIONWHEN NO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thereisnot suchanemplyee!);WHEN TOO_MANY_ROWSTHENFORv_empIN(SELECT* FROMemp WHERE ename=SMITH)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.

58、sal);ENDLOOP;END;149删除dept表中部门门号为10的部门信信息，如如果不能能删除则则输出“Therearesubrecordsinemptable!”。DECLAREe_deptno_fkEXCEPTION;PRAGMAEXCEPTION_INIT(e_deptno_fk,-2292);BEGINDELETEFROMdept WHERE deptno=10;EXCEPTIONWHEN e_deptno_fk THENDBMS_OUTPUT.PUT_LINE( There aresubrecords in emptable!);END;150修改7844员工的工工资，保保证修

59、改改后工资资不超过过6000。DECLAREe_highlimitEXCEPTION;v_salemp.sal%TYPE;BEGINUPDATEemp SETsal=sal+100WHEREempno=7844 RETURNINGsalINTOv_sal;IFv_sal6000 THENRAISEe_highlimit;ENDIF;EXCEPTIONWHEN e_highlimit THENDBMS_OUTPUT.PUT_LINE(The salaryistoolarge!);ROLLBACK;END;151（4）OTHERS异常处理理器OTHERS异常处理理器是一一个特殊殊的异常常处理器器，

60、可以以捕获所所有的异异常。通常，OTHERS异常处理理器总是是作为异异常处理理部分的的最后一一个异常常处理器器，负责责处理那那些没有有被其他他异常处处理器捕捕获的异异常。152DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;BEGINSELECTsal INTOv_salFROMempWHEREename=JOAN;UPDATEemp SETsal=sal+100WHEREempno=7900;IFv_sal6000 THENRAISEe_highlimit;ENDIF;EXCEPTIONWHEN e_highlimit THENDBMS_OUTPU