Oracle第章LSQL语言基础

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功能特性语句块结构异常处理变量和类型条件语句循环结构游标过程、函数和触发器包集合动态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块的组成PL/SQL块分类11（1）PL/SQL块的组成成PL/SQL程序的基基本单元元是语句句块，所所有的PL/SQL程序都是是由语句句块构成成的。。一个完整整的PL/SQL语句块由由3个部分组组成。12声明部分分主要用于于声明变变量、常常量、数数据类型型、游标标、异常常处理名名称以及及本地（（局部））子程序序定义等等。可执行部分执行部分是PL/SQL块的功能实现现部分。该部部分通过变量量赋值、流程程控制、数据据查询、数据据操纵、数据据定义、事务务控制、游标标处理等实现现块的功能。。异常处理部分分异常处理部分分用于处理该该块执行过程程中产生的异异常。13注意：执行部分是必必须的，而声声明部分和异异常部分是可可选的可以在一个块块的执行部分分或异常处理理部分嵌套其其他的PL/SQL块；所有的PL/SQL块都是以“END;”结束。14DECLAREv_enameVARCHAR2(10);BEGINSELECTenameINTOv_enameFROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_ename);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchaemployee');END;15DECLAREv_salNUMBER(6,2);v_deptnoNUMBER(2);BEGINBEGINSELECTdeptnoINTOv_deptnoFROMempWHEREempno=7844;END;SELECTavg(sal)INTOv_salFROMempWHEREdeptno=v_deptno;DBMS_OUTPUT.PUT_LINE(v_sal);END;16注意若要在SQL*Plus环境中看到DBMS_OUTPUT.PUT_LINE方法的输出结结果，必须将将环境变量SERVEROUTPUT设置为ON。SETSERVEROUTPUTON17（2）PL/SQL块分类匿名块匿名块是指动动态生成，只只能执行一次次的块，不能能由其他应用用程序调用。。命名块命名块是指一一次编译可多多次执行的PL/SQL程序，包括函函数、存储过过程、包、触触发器等。它它们编译后放放在服务器中中，由应用程程序或系统在在特定条件下下调用执行。。18命名块示例CREATEORREPLACEPROCEDUREshowavgsal(p_deptnoNUMBER)ASv_salNUMBER(6,2);BEGINSELECTavg(sal)INTOv_salFROMempWHEREdeptno=p_deptno;DBMS_OUTPUT.PUT_LINE(v_sal);ENDshowavgsal;1914.2.2词法单元字符集标识符分隔符常量值注释20（1）字符集PL/SQL的字符集包括括：大小写字母：：A~Z，a~z数字：0~9空白：制表符符、空格和回回车数字符号：+-*/〈〉=标点符号：~!@#$%^&*（）_|{}[]？；：,.“‘注意PL/SQL字符集不区分分大小写。21（2）标识符标识符用于定定义PL/SQL变量、常量、、异常、游标标名称、游标标变量、参数数、子程序名名称和其他的的程序单元名名称等。在PL/SQL程序中，标识识符是以字母母开头的，后后边可以跟字字母、数字、、美元符号（（$）、井号（#）或下划线（（_），其最大长长度为30个字符，并且且所有字符都都是有效的。。例如，X，v_empno，v_$等都是有效的的标识符，而而X+y，_temp则是非法的标标识符。注意如果标识符区区分大小写、、使用预留关关键字或包含含空格等特殊殊符号，则需需要用“”括起来，称为为引证标识符符。例如标识识符“mybook”和“exception”。22（3）分隔符+-*/=:=<><=>=<>!=~=^=()/**/<<>>%;:.‘“..@||=>**-分隔符是指有有特定含义的的单个符号或或组合符号23（4）常量值字符型文字以单引号引起起来的字符串串，在字符串串中的字符区区分大小写。。如果字符串串中本身包含含单引号，则则用两个连续续的单引号进进行转义。数字型文字分为整数与实实数两类。其其中，整数没没有小数点，，如123；而实数有小小数点，如123.45。可以用科学学计数法表示示数字型文字字，如123.45可以表示为1.2345E2。布尔型文字预定义的布尔尔型变量的取取值，包括TRUE，FALSE，NULL三个值。日期型文字表示日期值，，其格式随日日期类型格式式不同而不同同。24（5）注释单行注释--多行注释以“/*”开始，以“*/”结束。DECLAREv_departmentCHAR(10);--variabletoholdthedepartmentnameBEGIN/*querythedepartmentnamewhichdepartmentnumberis10ouputthedepartmentnameintov_department*/SELECTdnameINTOv_departmentFROMdeptWHEREdeptno=10;END;2514.2.3数据类型数字类型字符类型日期/区间类型行标识类型布尔类型原始类型LOB类型引用类型记录类型集合类型%TYPE与%ROWTYPE26PL/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引用类型REFCURSOR，REFobject_type。记录类型RECORD集合类型TABLE、VARRAY27数字类型NUMBER类型以十进制制形式存储整整数和浮点数数，语法为NUMBER（p，s）。其中，p为精度，即所所有有效数字字位数；s为刻度范围，，即小数位数数。p的取值范围为为1～38。BINARY_INTEGER类型用于表示示从-2147483647～+2147483647之间的整数，，以二进制形形式存储。当当发生溢出时时，将自动转转换成NUMBER类型。PLS_INTEGER类型表示范围围与BINARY_INTEGER相同，但发生生溢出时会产产生错误。28字符类型PL/SQL中的字符类型型与Oracle数据库中的字字符类型类似似，但是允许许字符串的长长度有所不同同。VARCHAR2，CHAR主要用于存储储来自本地数数据库字符集集的字符，而而NCHAR，NVARCHAR2用于存储来自自国家字符集集的字符串。。类型PL/SQL中最大字节数Oracle中最大字节数VARCHAR2327674000NVARCHAR2327674000CHAR327672000NCHAR327672000LONG327602GB29日期/区间类型DATE：与数据库中中的DATE类型相同，存存储日期和时时间信息，包包括世纪、年年、月、日、、小时、分和和秒，不包括括秒的小数部部分。TIMESTAMP：与DATE类型相似，但但包括秒的小小数部分，有有以下3种形式。TIMESTAMP[(p)]：其中p为秒字段的的小数部分分精度。TIMESTAMP[(p)]WITHTIMEZONE：返回当前前时区的时时间戳。TIMESTAMP[(p)]WITHLOACLTIMEZONE：返回数据据库时区的的时间戳。。30INTERVAL：用于存储储两个时间间戳之间的的时间间隔隔，有下面面两种形式式。INTERVALYEAR[(p)]TOMONTH：两个时间间戳相差的的年数和月月数。INTERVALDAY[(dp)]TOSECOND[(sp)]：两个时间间戳相差的的天数和秒秒数。31行标识类型型ROWID表示行的物物理地址UROWID既可以表示示行的物理理地址，也也可以表示示行的逻辑辑地址。布尔类型（（BOOLEAN）只能在PL/SQL中使用，其其取值为逻逻辑值，包包括TRUE、FALSE、NULL。原始类型与Oracle数据库中的的原始类型型相似，但但子节数不不同。类型PL/SQL中最大字节数Oracle中最大字节数RAW327672000LONGRAW327672G32LOB类型包括BLOB，CLOB，NCLOB和BFILE四种类型。。其中BLOB存放二进制制数据，CLOB，NCLOB存放文本数数据，而BFILE存放指向操操作系统文文件的指针针。LOB类型变量可可以存储4GB的数据量。。引用类型引用类型类类似于其他他高级语言言中的指针针类型。在在PL/SQL中，引用类类型包括游游标的引用用类型和对对象的引用用类型，即即REFCURSOR和REFobject_type。33记录类型记录类型是是复合类型型，类似于于C语言中的结结构体，是是一个包含含若干个成成员分量的的复合类型型。在使用记录录类型时，，需要先在在声明部分分定义记录录类型和记记录类型的的变量，然然后在执行行部分引用用该记录类类型变量或或其成员分分量。集合类型集合类型是是复合类型型，包括索索引表类型型、嵌套表表类型和可可变数组类类型。集合类型与与记录类型型的区别在在于，记录录类型中的的成员分量量可以是不不同类型的的，类似于于结构体，，而集合类类型中所有有的成员分分量必须具具有相同的的数据类型型，类似于于数组。34%TYPE与%ROWTYPE如果要定义义一个类型型与某个变变量的数据据类型或数数据库表中中某个列的的数据类型型一致（不不知道该变变量或列的的数据类型型）的变量量，可以利利用%TYPE来实现。如果要定义义一个与数数据库中某某个表结构构一致的记记录类型的的变量，可可以使用%ROWTYPE来实现。注意变量的类型型随参照的的变量类型型、数据库库表列类型型、表结构构的变化而而变化；如果数据库库表列中有有NOTNULL约束，则%TYPE与%ROWTYPE返回的数据据类型没有有此限制。。35DECLAREv_salemp.sal%TYPE;v_empemp%ROWTYPE;BEGINSELECTsalINTOv_salFROMempWHEREempno=7844;SELECT*INTOv_empFROMempWHEREempno=7900;DBMS_OUTPUT.PUT_LINE(v_sal);DBMS_OUTPUT.PUT_LINE(v_emp.ename||v_emp.sal);END;3614.2.4变量与常量量变量与常量量的定义变量的作用用域37变量声明（1）变量与常常量的定义义变量定义的的一般格式式variable_name[CONSTANT]datatype[NOTNULL][DEFAULT|:=expression];说明变量或常量量名称是一一个PL/SQL标识符，应应符合标识识符命名规规范；每行只能定定义一个变变量；如果加上关关键字CONSTANT，则表示所所定义的是是一个常量量，必须为为它赋初值值；如果定义变变量时使用用了NOTNULL关键字，则则必须为变变量赋初值值；如果变量没没有赋初值值，则默认认为NULL；使用DEFAULT或“:=”运算符为变变量初始化化。38DECLAREv1NUMBER(4);v2NUMBER(4)NOTNULL:=10;v3CONSTANTNUMBER(4)DEFAULT100;BEGINIFv1ISNULLTHENDBMS_OUTPUT.PUT_LINE('V1ISNULL!');ENDIF;DBMS_OUTPUT.PUT_LINE(v2||''||v3);END;39（2）变量的作作用域变量的作用用域是指变变量的有效效作用范围围，从变量量声明开始始，直到块块结束。如果PL/SQL块相互嵌套套，则在内内部块中声声明的变量量是局部的的，只能在在内部块中中引用，而而在外部块块中声明的的变量是全全局的，既既可以在外外部块中引引用，也可可以在内部部块中引用用。如果内部块块与外部块块中定义了了同名变量量，则在内内部块中引引用外部块块的全局变变量时需要要使用外部部块名进行行标识。40<<OUTER>>DECLAREv_enameCHAR(16);v_outerNUMBER(5);BEGINv_outer:=10;DECLAREv_enameCHAR(20);v_innerDATE;BEGINv_inner:=sysdate;v_ename:='INNERV_ENAME';OUTER.v_ename:='OUTERV_ENAME';END;DBMS_OUTPUT.PUT_LINE(v_ename);END;4114.2.5PL/SQL记录用户定义记记录类型及及变量利用%ROWTYPE获取记录类类型定义变变量记录类型变变量的应用用在SELECT语句中使用用记录类型型变量在INSERT语句中使用用记录类型型变量在UPDATE语句中使用用记录类型型变量在DELETE语句中使用用记录类型型变量42（1）用户定义义记录类型型及变量定义记录类类型的语法法为TYPErecord_typeISRECORD(field1datatype1[NOTNULL][DEFAULT|:=expr1],field2datatype2[NOTNULL][DEFAULT|:=expr2],……fieldndatatypen[NOTNULL][DEFAULT|:=exprn]);注意：相同记录类类型的变量量可以相互互赋值；不同记录类类型的变量量，即使成成员完全相相同也不能能相互赋值值；记录类型只只能应用于于定义该记记录类型的的PL/SQL块中，即记记录类型是是局部的。43利用记录类类型以及记记录类型变变量，保存存员工信息息。DECLARETYPEt_empISRECORD(empnoNUMBER(4),enameCHAR(10),salNUMBER(6,2));v_empt_emp;BEGINSELECTempno,ename,salINTOv_empFROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.ename||''||v_emp.sal);END;44（2）利利用用%ROWTYPE获取取记记录录类类型型定定义义变变量量DECLAREv_emp1emp%ROWTYPE;v_emp2emp%ROWTYPE;CURSORc_empISSELECTempno,enameFROMempWHEREdeptno=10;v_emp10c_emp%ROWTYPE;BEGINSELECT*INTOv_emp1FROMempWHEREempno=7844;OPENc_emp;LOOPFETCHc_empINTOv_emp10;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp10.empno||‘'||v_emp10.ename);ENDLOOP;CLOSEc_emp;END;45（3）记记录录类类型型变变量量的的应应用用在SELECT语句句中中使使用用记记录录类类型型变变量量在SELECTINTO语句句中中使使用用记记录录类类型型变变量量DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename||''||v_emp.sal);END;注意意记录录类类型型变变量量中中分分量量的的个个数数、、顺顺序序、、类类型型应应该该与与查查询询列列表表中中列列的的个个数数、、顺顺序序、、类类型型完完全全匹匹配配。。46在SELECT语句句中中使使用用记记录录类类型型变变量量在SELECTINTO语句句中中使使用用记记录录类类型型变变量量成成员员DECLAREv_empemp%ROWTYPE;BEGINSELECTempno,ename,salINTOv_emp.empno,v_emp.ename,v_emp.salFROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.empno||v_emp.ename||v_emp.sal);END;47在INSERT语句句中中使使用用记记录录类类型型变变量量在VALUES子句句中中使使用用记记录录类类型型变变量量DECLAREv_deptdept%ROWTYPE;BEGINv_dept.deptno:=50;v_dept.loc:='BEIJING';V_dept.dname:='COMPUTER';INSERTINTODEPTVALUESv_dept;END;注意意记录录类类型型变变量量中中分分量量的的个个数数、、顺顺序序、、类类型型应应该该与与表表中中列列的的个个数数、、顺顺序序、、类类型型完完全全匹匹配配。。48在INSERT语句句中中使使用用记记录录类类型型变变量量在VALUES子句中使用记记录类型变量量成员DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=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;注意记录类型变量量中分量的个个数、顺序、、类型应该与与表中列的个个数、顺序、、类型完全匹匹配。50在UPDATE语句中使用记记录类型变量量在SET子句中使用记记录类型变量量成员DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=7844;UPDATEempSETsal=v_emp.sal,comm=v_mWHEREempno=7369;END;51在DELETE语句中使用记记录类型变量量DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=7844;DELETEFROMempWHEREdeptno=v_emp.deptno;END;5214.2.6编译指示编译指示是对对编译程序发发出的特殊指指令，也称为为伪指令，不不会改变程序序含义。它只只是向编译程程序传递信息息，类似于嵌嵌入在SQL中的注释。在PL/SQL中使用PRAGMA关键字通知编编译程序，PL/SQL语句的剩余部部分是一个编编译指示或命命令。编译指指示在编译时时被处理，而而不会在运行行时被执行，，类似于C语言中的#define。53PL/SQL提供以下4种编译指示EXCEPTION_INIT：告诉编译程程序将一个特特定的错误号号与程序中所所声明的异常常标识符关联联起来。RESTRICT_REFERENCES：告诉编译程程序打包程序序的纯度，即即对函数中可可以使用的SQL语句和包变量量进行限制。。SERIALLY_REUSEABLE：告诉PL/SQL运行引擎时，，在数据引用用之间不要保保持包级数据据。AUTONOMOUS_TRANSACTION：告诉编译程程序，该程序序块为自治事事务，即该事事务的提交和和回滚是独立立进行的。5414.2.7PL/SQL中SQL语句由于PL/SQL执行采用早期期绑定，即在在编译阶段对对变量进行绑绑定，识别程程序中标识符符的位置，检检查用户权限限、数据库对对象等信息，，因此在PL/SQL中只允许出现现:SELECTDML(UPDATE、DELETE、INSERT)事务控制语句句（COMMIT、ROLLBACK、SAVEPOINT）注意DDL语句不可以直直接使用55通常，利用SQL语句对数据库库进行操作时时，各种相关关量都在代码码中以常量的的形式指定，，而在PL/SQL中可以通过变变量动态指定定各种相关量量的值，从而而实现对数据据库的动态操操作。DECLAREv_empnoNUMBER(4);BEGINv_empno:=&x;UPDATEempSETsal=sal+100WHEREempno=v_empno;END;56SELECT语句在PL/SQL程序中，使用用SELECT…INTO语句查询一个个记录的信息息。其语法为：SELECTselect_list_itemINTOvariable_list|record_variableFROMtableWHEREcondition;57根据员工名或或员工号查询询员工信息，，程序为：DECLAREv_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;58注意：SELECT…INTO语句只能查询询一个记录的的信息，如果果没有查询到到任何数据，，会产生NO_DATA_FOUND异常；如果查查询到多个记记录，则会产产生TOO_MANY_ROWS异常。INTO句子后的变量量用于接收查查询的结果，，变量的个数数、顺序应该该与查询的目目标数据相匹匹配，也可以以是记录类型型的变量。59用SELECT…INTO语句查询10号部门所有员员工信息。DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREdeptno=10;END;/*ERROR位于第1行:ORA-01422:实际返回的行行数超出请求求的行数ORA-06512:在line460DML语句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;61WHERE标识符的区分分系统首先查看看WHERE子句中的标识识符是否与表表中的列名相相同，如果相相同，则该标标识符被解释释为列名；如如果没有同名名列，系统检检查该标识符符是不是PL/SQL语句块的变量量。字符串比较填充比较：通通过在短字符符串后添加空空格，使两个个字符串达到到相同长度，，然后根据每每个字符的ASCII码进行比较。。非填充比较：：根据每个字字符的ASCII码进行行比较较，最最先结结束的的字符符串为为小。。62那么何何时采采用填填充比比较，，何时时采用用非填填充比比较呢呢？PL/SQL中规定定，对对定长长的字字符串串（CHAR类型的的字符符串和和字符符串常常量））采用用填充充比较较；如如果比比较的的字符符串中中有一一个是是变长长字符符串（（VARCHAR2类型的的字符符串）），则则采用用非填填充比比较。。63例如，，已知知emp表中ename列类型型为VARCHAR2（10），执执行下下面的的代码码。DECLAREv_enameCHAR(10):='TURNER';--v_enameVARCHAR2(20);--v_enameemp.ename%TYPE:='TURNER';v_salemp.sal%TYPE;BEGINSELECTsalINTOv_salFROMempWHEREename=v_ename;dbms_output.put_line(v_sal);END;/DECLARE*第1行出现现错误误:ORA-01403:未找到数据据ORA-06512:在line664产生错误的的原因是VARCHAR2（10）类型与CHAR（10）类型比较较时采用非非填充比较较，因此无无法查询到到员工名为为“TURNER”的员工。可可以将v_ename变量类型修修改为VARCHAR2（10）类型，也也可以直接接采用emp.ename%TYPE方式定义。。因此，为了了保证程序序的正确执执行，一定定要使PL/SQL语句块中的的变量与要要比较的数数据库列拥拥有相同的的数据类型型，可以使使用%TYPE或%ROWTYPE来定义变量量。65RETURNING如果要查询询当前DML语句操作的的记录的信信息，可以以在DML语句末尾使使用RETURNING语句返回该该记录的信信息。RETURNING语句的基本本语法：RETURNINGselect_list_itemINTOvariable_list|record_variable;66DECLAREv_salemp.sal%TYPE;BEGINUPDATEempSETsal=sal+100WHEREempno=7844RETURNINGsalINTOv_sal;DBMS_OUTPUT.PUT_LINE(v_sal);END;6714.3控制结构选择结构循环结构跳转结构6814.3.1选择结构IF语句CASE语句69（1）IF语句语法IFcondition1THENstatements1;[ELSIFcondition2THENstatements2;]……[ELSEelse_statements];ENDIF;注意条件是一个个布尔型变变量或表达达式，取值值只能是TRUE，FALSE，NULL。70例如，输入入一个员工工号，修改改该员工的的工资，如如果该员工工为10号部门，工工资增加100；若为20号部门，工工资增加160；若为30号部门，工工资增加200；否则增加加300。71DECLAREv_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:=160;ELSIFv_deptno=30THENv_increment:=200;ELSEv_increment:=300;ENDIF;UPDATEempSETsal=sal+v_incrementWHEREempno=v_empno;END;72由于PL/SQL中的逻辑运运算结果有有TRUE，FALSE和NULL三种，因此此在进行选选择条件判判断时，要要考虑条件件为NULL的情况。例例如，下面面两个程序序，如果不不考虑条件件为NULL的情况，则则运行结果果是一致的的，但是若若考虑条件件为NULL的情况，则则结果就不不同了。7374为了避免条条件为NULL时出现歧义义，应该在在程序中进进行条件是是否为NULL的检查。75（2）CASE语句基本语法CASEWHENcondition1THENstatements1;WHENcondition2THENstatements2;……WHENconditionnTHENstatementsn;[ELSEelse_statements;]ENDCASE;注意在CASE语句中，当当第一个WHEN条件为真时时，执行其其后的操作作，操作完完后结束CASE语句。其他他的WHEN条件不再判判断，其后后的操作也也不执行。。76根据输入的的员工号，，修改该员员工工资。。如果该员员工工资低低于1000，则工资增增加200；如果工资资在1000～2000之间，则增增加150；如果工资资在2000～3000之间，则增增加100；否则增加加50。77DECLAREv_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;78等值比较的的CASE语句基本语语法CASEtest_valueWHENvalue1THENstatements1;WHENvalue2THENstatements2;……WHENvaluenTHENstatementsn;[ELSEelse_statements;]ENDCASE;79DECLAREv_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;8014.3.2循环结构简单循环WHILE循环FOR循环81（1）简单循环环语法LOOPsequence_of_statement;EXIT[WHENcondition];ENDLOOP;注意在循环体中中一定要包包含EXIT语句，否则则程序进入入死循环82执行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;83（2）WHILE循环基本语法WHILEconditionLOOPsequence_of_statement;ENDLOOP;84利用用WHILE循环环向向temp_table表中中插插入入50条记记录录。。DECLAREv_counterBINARY_INTEGER:=1;BEGINWHILEv_counter<=50LOOPINSERTINTOtemp_tableVALUES(v_counter,'Loopindex');v_counter:=v_counter+1;ENDLOOP;END;85（3）FOR循环环基本本语语法法FORloop_counterIN[REVERSE]low_bound..high_boundLOOPsequence_of_statement;ENDLOOP;注意意：：循环环变变量量不不需需要要显显式式定定义义，，系系统统隐隐含含地地将将它它声声明明为为BINARY_INTEGER变量量；；系统统默默认认时时，，循循环环变变量量从从下下界界往往上上界界递递增增计计数数，，如如果果使使用用REVERSE关键键字字，，则则表表示示循循环环变变量量从从上上界界向向下下界界递递减减计计数数；；循环环变变量量只只能能在在循循环环体体中中使使用用，，不不能能在在循循环环体体外外使使用用。。86利用用FOR循环环向向temp_table表中中插插入入50条记记录录。。BEGINFORv_counterIN1..50LOOPINSERTINTOtemp_tableVALUES(v_counter,'LoopIndex');ENDLOOP;END;8714.3.3跳转转结结构构语法法格格式式：：《标号号》…GOTO标号号；；说明明：：块内内可可以以跳跳转转，，内内层层块块可可以以跳跳到到外外层层块块，，但但外外层层块块不不能能跳跳到到内内层层。。IF语句句不不能能跳跳入入。。不不能能从从循循环环体体外外跳跳入入循循环环体体内内。。不不能能从从子子程程序序外外部部跳跳到到子子程程序序中中。。由于于goto语句句的的缺缺点点，，建建议议尽尽量量少少用用甚甚至至不不用用goto语句句。。88DECLAREv_counterBINARY_INTEGER:=1;BEGIN<<LABEL>>INSERTINTOtemp_tableVALUES(v_counter,'Loopindex');v_counter:=v_Counter+1;IFv_counter<=50THENGOTOLABEL;ENDIF;END;8914.4游标游标的概念及及类型显式游标隐式游标游标变量9014.4.1游标的概念及类型游标的概念游标（CURSOR）是Oracle系统在内存中中开辟的一个个工作区，在在其中存放SELECT语句返回的查查询结果。使用游标时，，SELECT语句查询的结结果可以是单单条记录，多多条记录，也也可以是零条条记录。游标工作区中中，存在着一一个指针（POINTER）,在初始状态它它指向查询结结果的首记录录。91游标的类型显式游标由用户定义、、操作，用于于处理返回多多行数据的SELECT查询。隐式游标由系统自动进进行操作，用用于处理DML语句和返回单单行数据的SELECT查询。9214.4.2显式游标显式游标的操操作显式游标的属属性参数化显式游游标显式游标的检检索利用游标更新新或删除数据据93（1）显式游标的的操作步骤定义游标打开游标检索游标关闭游标94定义游标语法CURSORcursor_nameISselect_statement;说明游标必须在PL/SQL块的声明部分分进行定义；；游标定义时可可以引用PL/SQL变量，但变量量必须在游标标定义之前定定义；定义游标时并并没有生成数数据，只是将将定义信息保保存到数据字字典中；游标定义后，，可以使用cursor_name%ROWTYPE定义游标类型型变量。95打开游标语法OPENcursor_name;说明检查变量的值值执行游标定义义时对应的SELECT语句，将查询询结果检索到到工作区中。。游标指针指向向第一个元组组一旦游标打开开，就无法再再次打开，除除非先关闭如果游标定义义中的变量值值发生变化，，则只能在下下次打开游标标时才起作用用。96检索游标语法格式FETCHcursor_nameINTOvariable_list|record_variable;说明在使用FETCH语句之前必须须先打开游标标对游标第一次次使用FETCH语句时，游标标指针指向第第一条记录，，因此操作的的对象是第一一条记录，使使用后，游标标指针指向下下一条记录。。游标指针只能能向下移动，，不能回退INTO子句中的变量量个数、顺序序、数据类型型必须与工作作区中每行记记录的字段数数、顺序以及及数据类型一一一对应。97关闭游标语法格式CLOSEcursor_name;说明游标所对应的的内存工作区区变为无效，，释放与游标标相关的系统统资源。98根据输入的部部门号查询某某个部门的员员工信息，部部门号在程序序运行时指定定。99DECLAREv_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;100（2）显式游标的的属性%ISOPEN布尔型。如果果游标已经打打开，返回TRUE,否则为FALSE。%FOUND布尔型，如果果最近一次使使用FETCH语句，有返回回结果则为TRUE,否则为FALSE;%NOTFOUND布尔型，如果果最近一次使使用FETCH语句,没有返回结果果则为TRUE,否则为FALSE;%ROWCOUNT数值型，返回回到目前为止止从游标缓冲冲区检索的元元组数。%BULK_ROWCOUNT（i）数值型，用于于取得FORALL语句执行批绑绑定操作时第第i个元素所影响响的行数。101（3）参数化显式式游标参数化游标定定义语法格式式CURSORcursor_name(parameter1datatype[,parameter2datatype…])ISselect_statement打开参数化游游标的方法OPENcursor_name(parameter1[,parameter2…])102注意：定义参数化游游标时，只能能指定参数的的类型，而不不能指定参数数的长度、精精度、刻度；；打开带参数的的游标时，实实参的个数和和数据类型等等必须与游标标定义时形参参个数和数据据类型等相匹匹配。103DECLARECURSORc_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;104（4）显式游标的的检索利用简单循环环检索游标利用WHILE循环检索游标标利用FOR循环检索游标标105利用简单循环环检索游标语法DECLARECURSORcursor_nameISSELECT…;BEGINOPENcursor_name;LOOPFETCH…INTO…;EXITWHENcursor_name%NOTFOUND;……ENDLOOP;CLOSEcursor_name;END;注意EXITWHEN子句应该是FETCH……INTO语句的下一条条语句。106利用简单循环环统计并输出出各个部门的的平均工资。。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;107利用WHILE循环检索索游标语法DECLARECURSORcursor_nameISSELECT…;BEGINOPENcursor_name;FETCH…INTO…;…WHILEcursor_name%FOUNDLOOPFETCH…INTO…;……ENDLOOP;CLOSEcursor;END;注意在循环体体外进行行一次FETCH操作，作作为第一一次循环环的条件件。108利用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;109利用FOR循环检索索游标语法DECLARECURSORcursor_nameISSELECT…;BEGINFORloop_variableINcursor_nameLOOP……ENDLOOP;END;110系统隐含含地定义义了一个个数据类类型为%ROWTYPE的变量，，并以此此作为循循环的计计算器。。系统自动动打开游游标，不不用显式式地使用用OPEN语句打开开；系统重复复地自动动从游标标工作区区中提取取数据并并放入计计数器变变量中。。系统自动动进行%FOUND属性检查查以确定定是否有有数据当游标工工作区中中所有的的记录都都被提取取完毕或或循环中中断时，，系统自自动地关关闭游标标。111利用FOR循环统计计并输出出各个部部门的平平均工资资。DECLARECURSORc_dept_statISSELECTdeptno,avg(sal)avgsalFROMempGROUPBYdeptno;BEGINFORv_deptINc_dept_statLOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_dept.avgsal);ENDLOOP;END;112由于用FOR循环检索索游标时时，游标标的打开开、数据据的检索索、是否否检索到到数据的的判断以以及游标标的关闭闭都是自自动进行行的，因因此，可可以不在在声明部部分定义义游标，，而在FOR语句中直直接使用用子查询询。BEGINFORv_empIN(select*fromempwheredeptno=10)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);

ENDLOOP;END;113（5）利用游游标更新新或删除除数据游标定义义语法CURSORcursor_nameISSELECTselect_list_itemFROMtableFORUPDATE[OFcolumn_reference][NOWAIT];注意打开游标标时对相相应的表表加锁（（通常SELECT操作不在在数据上上设置任任何锁）），其他他用户不不能对该该表进行行DML操作；若数据对对象已经经被其他他会话加加锁，则则当前会会话挂起起等待（（默认状状态），，若指定定了NOWAIT子句，则则不等待待，返回回ORACLE错误。对于多表表查询时时，可以以通过OF子句指定定某个要要加锁的的表的列列的形式式，对特特定的表表加锁，，而其他他表不加加锁；否否则所有有表都加加锁。当用户执执行COMMIT或ROLLBACK操作时，，数据上上的锁会会自动被被释放。。114更新或修修改数据据的语法法为UPDATE|DELETE…WHERECURRENTOFcursor_name注意如果游标标定义时时没有使使用FORUPDATE子句，则则不能利利用该游游标修改改或删除除数据库库中的数数据。115修改员工工的工资资，如果果员工的的部门号号为10，则工资资提高100；如果部部门号为为20，则工资资提高150；如果部部门号为为30，则工资资提高200；否则工工资提高高250。116DECLARECURSORc_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;COMMIT;END;11714.4.3隐式游标标概念所有的SQL语句都有有一个执执行的缓缓冲区，，隐式游游标就是是指向该该缓冲区区的指针针，由系系统隐含含地打开开、处理理和关闭闭。隐式式游标又又称为SQL游标。隐式游标标主要用用于处理理INSERT、UPDATE，DELETE以及单行行的SELECT…INTO语句，没没有OPEN，FETCH，CLOSE等操作命命令。118隐式游标标属性SQL%ISOPEN：布尔型型值，判判断隐式式游标是是否已经经打开。。对用户户而言，，该属性性值始终终为FALSE，因为操操作时系系统自动动打开，，操作完完后立即即自动关关闭。SQL%FOUND：布尔型型值，判判断当前前的操作作是否会会对数据据库产生生影响。。如果有有数据的的插入、、删除、、修改或或查询到到数据，，则返回回TRUE，否则返返回FALSE。SQL%NOTFOUND：布尔型型值，判判断当前前的操作作是否对对数据库库产生影影响。如如果没有有数据的的插入、、删除、、修改或或没有查查询到数数据，则则返回TRUE，否则返返回FALSE。SQL%ROWCOUNT：数值型型，返回回当前操操作所涉涉及的数数据库中中的行数数。119修改员工工号为1000的员工工工资，将将其工资资增加100。如果该该员工不不存在，，则向emp表中插入入一个员员工号为为1000，工资为为1600的员工。。120BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%NOTFOUNDTHENINSERTINTOemp(empno,sal)VALUES(1000,1600);ENDIF;END;或BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%ROWCOUNT=0THENINSERTINTOemp(empno,sal)VALUES(1000,1600);ENDIF;END;12114.4.4游标变量量概念游标变量量是一个个指向多多行查询询结果集集的指针针，不与与特定的的查询绑绑定，因因此具有有非常大大的灵活活性，可可以在打打开游标标变量时时定义查查询，可可以返回回不同结结构的结结果集使用游标标变量包包括游标引用用类型（（REFCURSOR）声明游标标变量打开游标标变量检索游标标变量关闭游标标变量122（1）定义游游标引用用类型及及游标变变量语法TYPEref_cursor_type_nameISREFCURSOR[RETURNreturn_type]RETURN子句用用于指指定定定义的的游标标类型型返回回结果果集的的类型型，该该类型型必须须是记记录类类型。。如果果定义义游标标引用用类型型时带带有RETURN子句，，则用用其定定义的的变量量称为为强游游标变变量，，否则则称为为弱游游标变变量。。在Oracle10g中，系系统预预定义义了一一个游游标引引用类类型，，称为为SYS_REFCURSOR，可以以直接接使用用它定定义游游标变变量。。123语法ref_cursor_type_namevariable_name;例如TYPEemp_cursor_typeISREFCURSORRETURNemp%ROWTYPE;TYPEgeneral_cursor_typeISREFCURSOR;v_empemp_cursor_type;v_generalgeneral_cursor_type;my_cursorSYS_REFCURSOR;124（2）打开开游标标变量量语法OPENcursor_variableFORselect_statement;注意如果打打开的的游标标变量量是强强游标标变量量，则则查询询语句句的返返回类类型必必须与与游标标引用用类型型定义义中RETURN子句指指定的的返回回类型型相匹匹配。。例如OPENv_empFORSELECT*FROMemp;OPENv_generalFORSELECTempno,ename,sal,deptnoFROMemp;OPENmy_cursorFORSELECT*FROMdept;125（3）检索索游标标变量量语法LOOPFETCHcursor_variableINTOvariable1,variable2,…;EXITWHENcursor_variable%NOTFOUND;……ENDLOOP;注意检索游游标变变量时时只能能使用用简单单循环环或WHILE循环，，不能能采用用FOR循环。。126（4）关闭闭游标标变量量语法CLOSEcursor_variable;127DECLARETYPEemp_cursor_typeISREFCURSORRETURN