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;19词法法单单元元字符符集集标识识符符分隔隔符符常量量值值注释释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;25数据据类类型型数字字类类型型字符符类类型型日期期/区间间类类型型行标标识识类类型型布尔尔类类型型原始始类类型型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;36变量与常常量变量与常常量的定定义变量的作作用域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;41记录用户定义义记录类类型及变变量利用%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;52编译指示编译指示是是对编译程程序发出的的特殊指令令，也称为为伪指令，，不会改变变程序含义义。它只是是向编译程程序传递信信息，类似似于嵌入在在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控制结构选择结构循环结构跳转结构68选择结构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;80循环结结构简单循循环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;87跳转结构语法格式：：《标号》…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游标游标的概念念及类型显式游标隐式游标游标变量90游标的概念及类型游标的概念游标（CURSOR）是Oracle系统在内存存中开辟的的一个工作作区，在其其中存放SELECT语句返回的的查询结果果。使用游标时时，SELECT语句查询的的结果可以以是单条记记录，多条条记录，也也可以是零零条记录。。游标工作区区中，存在在着一个指指针（POINTER）,在初始状态态它指向查查询结果的的首记录。。91游标的类型型显式游标由用户定义义、操作，，用于处理理返回多行行数据的SELECT查询。隐式游标由系统自动动进行操作作，用于处处理DML语句和返回回单行数据据的SELECT查询。92显式游标显式游标的的操作显式游标的的属性参数化显式式游标显式游标的的检索利用游标更更新或删除除数据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;117隐式游标概念所有的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;121游标变量概念游标变量是一一个指向多行行查询结果集集的指针，不不与特定的查查询绑定，因因此具有非常常大的灵活性性，可以在打打开游标变量量时定义查询询，可以返回回不同结构的的结果集使用游标变量量包括游标引用类型型（REFCURSOR）声明游标变量量打开游标变量量检索游标变量量关闭游标变量量122（1）定义游标引引用类型及游游标变量语法TYPEref_cursor_type_nameISREFCURSOR[RETURNreturn_type]RETURN子句用于指定定定义的游标标类型返回结结果集的类型型，该类型必必须是记录类类型。如果定定义游标引用用类型时带有有RETURN子句，则用其其定义的变量量称为强游标