《Oracle数据库应用》课件11.ppt

1、第第1111章章 PL/SQLPL/SQLPL/SQL基础基础1PL/SQL支持的支持的SQL语句语句2流程控制语句流程控制语句3游标游标4异常异常5存储过程存储过程6函数函数7触发器触发器811.1 PL/SQL11.1 PL/SQL基础基础n PL(Procedural Language)/SQLPL(Procedural Language)/SQLl为为OracleOracle数据库提供的过程化编程语言数据库提供的过程化编程语言l可用于分组可用于分组SQLSQL语句，将它们一起发向服务器，减少语句，将它们一起发向服务器，减少网络传输，提高程序运行效率网络传输，提高程序运行效率l具有良好的

2、可移植性，可运行于任何有具有良好的可移植性，可运行于任何有Oracle Oracle ServerServer的地方的地方l提供了完善的程序控制结构，可以实现复杂、功能提供了完善的程序控制结构，可以实现复杂、功能强大的应用程序强大的应用程序l集成于集成于Oracle ServerOracle Server与与OracleOracle应用开发工具之中，应用开发工具之中，可方便地实现分布式计算可方便地实现分布式计算n 1.PL/SQL1.PL/SQL程序块的结构程序块的结构l一段完整的一段完整的PL/SQLPL/SQL程序块结构程序块结构DECLAREDECLARE 说明部分说明部分;BEGINB

3、EGIN 执行部分执行部分;EXCEPTION EXCEPTION 异常处理部分异常处理部分;END;END;/说明部分说明部分/异常处理部分可选异常处理部分可选标识符遵循先定义后使用的原则标识符遵循先定义后使用的原则每一条语句必须以分号；结束每一条语句必须以分号；结束在执行部分不能使用在执行部分不能使用DDLDDL语句语句当执行部分发生一个错误时，将会引起一个异常当执行部分发生一个错误时，将会引起一个异常异常发生后，正常的执行被停止并且转移到异常程序处理异常发生后，正常的执行被停止并且转移到异常程序处理异常处理完后，将结束对应异常处理完后，将结束对应PL/SQLPL/SQL块的执行块的执行注

4、释注释w/*注释多行文本注释多行文本 */w-注释一行文本注释一行文本NULLNULL语句属于可执行语句语句属于可执行语句在在SQLSQL*PlusPlus中，中，PL/SQLPL/SQL程序执行完成时总是返回提示程序执行完成时总是返回提示“PL/SQLPL/SQL过程已成功完成过程已成功完成”PL/SQLPL/SQL程序块可以嵌套程序块可以嵌套n 2 2.PL/SQL.PL/SQL编写规则编写规则l标识符以字母开头，后面可以是字母、数字、标识符以字母开头，后面可以是字母、数字、$、下、下划线和划线和#l标识符最长不超过标识符最长不超过3030个字符个字符l标识符的作用范围：视具体情况而定标识

5、符的作用范围：视具体情况而定程序块、子程序、包等程序块、子程序、包等lPL/SQLPL/SQL运算符运算符逻辑运算：逻辑运算：ANDAND、OROR、NOTNOT算术运算：算术运算：+、-、*、/、*(幂幂)关系运算：关系运算：=、!=!=、=、=1010 OROR emp_cursor emp_cursor%NOTFOUND%NOTFOUND;END LOOP;END LOOP;例题：例题：P186P186n 3.3.用于游标的循环用于游标的循环l使用使用FETCHFETCH语句每次只能获取一条记录，若依次获取语句每次只能获取一条记录，若依次获取结果集中的所有记录，需使用循环重复执行结果集中

6、的所有记录，需使用循环重复执行FETCHFETCHlLOOPLOOP循环循环LOOPLOOP 语句语句;END LOOP;END LOOP;LOOPLOOP循环循环当当FETCHFETCH语句不能从游标当前行取出数据时，循环结束语句不能从游标当前行取出数据时，循环结束lFORFOR循环循环FOR FOR 记录型变量名记录型变量名 IN IN 游标名游标名 LOOPLOOP 语句语句;END LOOP;END LOOP;例题：例题：P187P187FORFOR与其它循环语句相比所具有的优势：与其它循环语句相比所具有的优势：w 循环控制变量不需事先定义循环控制变量不需事先定义w 使用使用FORFO

7、R之前，系统能自动打开游标。之前，系统能自动打开游标。FORFOR循环结束后，系统循环结束后，系统能自动关闭游标，无需人为操作能自动关闭游标，无需人为操作w 在在FORFOR中，系统能自动执行中，系统能自动执行FETCHFETCH，无需人为操作，无需人为操作n 4.4.使用游标更新表中的数据使用游标更新表中的数据l用游标锁定记录用游标锁定记录在定义游标的在定义游标的SELECTSELECT语句中带语句中带FOR UPDATEFOR UPDATE子句子句l在在UPDATEUPDATE语句中使用游标限定要修改的记录语句中使用游标限定要修改的记录UPDATE tableUPDATE tableSET

8、 column_name=sql_expressionSET column_name=sql_expressionWHERE CURRENT OF WHERE CURRENT OF cursor_name;cursor_name;l在在DELETEDELETE语句中使用游标限定要删除的记录语句中使用游标限定要删除的记录DELETE from tableDELETE from tableWHERE CURRENT OF WHERE CURRENT OF cursor_name;cursor_name;例题：例题：P188P18811.5 11.5 异常异常n 异常异常l是是PL/SQLPL/SQ

9、L执行期间引发的一个错误或警告执行期间引发的一个错误或警告l可由可由OracleOracle错误引发或由程序显式地引发错误引发或由程序显式地引发l任何异常都将终止当前任何异常都将终止当前PL/SQLPL/SQL程序块的执行程序块的执行n PL/SQLPL/SQL中包括中包括3 3种异常：种异常：l预定义异常：预定义异常：2121个常见错误，由个常见错误，由SQLSQL语句隐式引发语句隐式引发l非预定义异常：非预定义异常：OracleOracle服务器返回的其他错误服务器返回的其他错误l用户自定义异常：由用户定义并用用户自定义异常：由用户定义并用RAISERAISE语句显式引语句显式引发发n 异

10、常处理部分的语法异常处理部分的语法EXCEPTION WHEN exception1 THEN statement1;statement2;WHEN exception2 THEN statement1;statement2;WHEN OTHERS THEN statement1;statement2;n 1 1.系统预定义异常系统预定义异常l系统对常见发生的错误事先定义好的异常，有错误系统对常见发生的错误事先定义好的异常，有错误编号和异常名称编号和异常名称l常见的预定义异常：常见的预定义异常：ACCESS_INTO_NULLCASE_NOT_FOUNDCOLLECTION_IS_NULLCU

11、RSOR_ALREADY_OPENDUP_VAL_ON_INDEXINVALID_CURSORINVALID_NUMBERLOGIN_DENIEDNO_DATA_FOUNDNOT_LOGGED_ONPROGRAM_ERRORROWTYPE_MISMATCHSELF_IS_NULLSTORAGE_ERRORSUBSCRIPT_BEYOND_COUNTSUBSCRIPT_OUTSIDE_LIMITSYS_INVALID_IDTIMEOUT_ON_RESOURCETOO_MANY_ROWSVALUE_ERRORZERO_DIVIDElNO_DATA_FOUNDNO_DATA_FOUND和和TOO_M

12、ANY_ROWSTOO_MANY_ROWS的防止的防止SET serveroutput ONDECLARE ret NUMBER;BEGIN SELECT COUNT(*)INTO ret FROM emp WHERE empno=9;-NO_DATA_FOUND IF ret=0 THEN DBMS_OUTPUT.PUT_LINE(没有符合条件的记录！没有符合条件的记录！);return;END IF;-TOO_MANY_ROWS IF ret1 THEN DBMS_OUTPUT.PUT_LINE(数据有错误，符合条件的记录数据有错误，符合条件的记录有多条！有多条！);return;END

13、IF;END;例题：例题：P190n 2 2.非预定义异常非预定义异常lOracle ServerOracle Server错误号格式错误号格式ORA-ORA-数字数字l非预定义异常的处理过程如下：非预定义异常的处理过程如下：(1)(1)声明一个异常类型变量声明一个异常类型变量异常类型变量名异常类型变量名 EXCEPTION;EXCEPTION;(2)(2)将异常类型变量与将异常类型变量与Oracle ServerOracle Server错误号进行关联错误号进行关联PRAGMA EXCEPTION_INIT(PRAGMA EXCEPTION_INIT(异常类型变量名异常类型变量名,Oracl

14、e ServerOracle Server错误号错误号););(3)(3)在异常处理部分引用异常类型变量名捕获并处理该异常在异常处理部分引用异常类型变量名捕获并处理该异常例题：例题：P191P191n 3 3.用户自定义异常用户自定义异常l用户自定义的异常处理需要用户自定义的异常处理需要3 3个步骤：个步骤：(1)(1)在在PL/SQLPL/SQL块的说明部分定义异常类型变量块的说明部分定义异常类型变量异常类型变量名异常类型变量名 EXCEPTION;EXCEPTION;(2)(2)在在PL/SQLPL/SQL块的执行部分将其引发块的执行部分将其引发RAISE RAISE 异常类型变量名异常类

15、型变量名;(3)(3)在异常处理部分引用异常类型变量名捕获并处理该异常在异常处理部分引用异常类型变量名捕获并处理该异常例题：例题：P191P19111.6 11.6 存储过程存储过程n 存储过程存储过程l能完成一定处理能完成一定处理/计算功能并存储在数据字典中的程计算功能并存储在数据字典中的程序，调用时在数据库服务器上运行，如存储过程、序，调用时在数据库服务器上运行，如存储过程、函数、触发器函数、触发器l一种命名的一种命名的PL/SQLPL/SQL程序块，可以设置程序块，可以设置0 0个或多个输入个或多个输入、输出参数、输出参数l经编译后存储在数据库中，执行效率比经编译后存储在数据库中，执行效

16、率比SQLSQL语句高语句高n 创建存储过程语法创建存储过程语法CREATE OR REPLACE PROCEDURE procedure_nameCREATE OR REPLACE PROCEDURE procedure_name (argument IN|OUT|IN OUT datatype,(argument IN|OUT|IN OUT datatype,)IS|AS IS|AS 说明部分说明部分;BEGINBEGIN 执行部分执行部分;EXCEPTIONEXCEPTION 异常处理部分异常处理部分;END procedure_name;END procedure_name;n 对比对

17、比SQL ServerSQL Server存储过程的语法：存储过程的语法：CREATE PROC EDURE 存储过程名存储过程名;编号编号 参数参数 数据类型数据类型 VARYING =默认值默认值 OUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS SQL语句语句 .n l参数参数ININ型参数：由实际参数单向传递给形式参数型参数：由实际参数单向传递给形式参数OUTOUT型参数：用于返回值型参数：用于返回值IN OUTIN OUT型参数：给定实际参数值并返回一个结果值型参数：给定实际参数值并返回一个结果值默认为默认为IN

18、IN形式参数说明时不可指定数据宽度形式参数说明时不可指定数据宽度形式参数应以形式参数应以“P_P_”前缀命名前缀命名l注释：注释：-、/*/、REMREMlSHOW ERRORSSHOW ERRORS查看创建错误查看创建错误SHOW ERRORS FUNCTION|PROCEDURE|PACKAGE SHOW ERRORS FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|TRIGGER|VIEW schema.name|PACKAGE BODY|TRIGGER|VIEW schema.name l不可在存储过程中引用绑定变量不可在存储过程中引用绑定变量l1 1）

19、无参数的存储过程）无参数的存储过程不带输入参数和输出参数不带输入参数和输出参数l2 2）带）带inin参数的存储过程参数的存储过程带输入参数，可将数据传递给存储过程带输入参数，可将数据传递给存储过程l3 3）带）带outout参数的存储过程参数的存储过程可利用输出参数输出数据可利用输出参数输出数据l4 4）带）带in outin out参数的存储过程参数的存储过程参数类型为输入输出型参数类型为输入输出型例题：例题：P192P192n 存储过程的调用方式存储过程的调用方式lexecute/execexecute/execlcallcalllbegin.end;begin.end;例题：例题：P1

20、94P194l有参数的存储过程调用有参数的存储过程调用1 1、按参数名称传递参数，无需考虑参数顺序、按参数名称传递参数，无需考虑参数顺序如：如：SQLSQLexec exec insert_empinsert_emp(enoeno=7369,=7369,enameename=smithsmith,salsal=1500=1500););2 2、按参数、按参数位置位置传递传递参数参数，需，需考虑参数考虑参数顺序、类型和个数顺序、类型和个数如：如：SQLSQLexec exec insert_empinsert_emp(7369(7369,smithsmith,1500););3 3、有的参数按名

21、称传递，有的参数按位置传递。必须将按、有的参数按名称传递，有的参数按位置传递。必须将按位置的参数值写在最左边，按名称的参数写在最右边位置的参数值写在最左边，按名称的参数写在最右边如：如：SQLSQLexec exec insert_empinsert_emp(7369(7369,smithsmith,salsal=1500=1500););n 查看存储过程参数查看存储过程参数DESCRIBE procedure_nameDESCRIBE procedure_namen 删除存储过程删除存储过程DROP PROCEDURE procedure_name;DROP PROCEDURE proced

22、ure_name;n 函数函数l程序块，可通过程序块，可通过returnreturn返回函数的执行结果返回函数的执行结果l使用函数可简化客户端程序的开发，提高程序性能使用函数可简化客户端程序的开发，提高程序性能11.7 11.7 函数函数l创建函数语法创建函数语法CREATE OR REPLACE FUNCTION function_nameCREATE OR REPLACE FUNCTION function_name (argument IN datatype,(argument IN datatype,)RETURN return_typeRETURN return_type IS|AS

23、 IS|AS 说明部分说明部分;BEGINBEGIN return return 返回值表达式返回值表达式;EXCEPTION EXCEPTION 异常处理部分异常处理部分;return return 返回值表达式返回值表达式;END function_name;END function_name;例题：例题：P195P195l在在SQLSQL*PlusPlus中调用函数中调用函数方法方法1 1：借助虚表：借助虚表dualdualSELECT SELECT function_namefunction_name FROM DUAL;FROM DUAL;SELECT SELECT function

24、_namefunction_name(para1,para2)(para1,para2)FROM DUAL;FROM DUAL;方法方法2 2：使用绑定变量：使用绑定变量variable variable 绑定变量名绑定变量名 变量类型变量类型;exec:exec:绑定变量名绑定变量名:=:=函数名函数名(参数参数);print print 绑定变量名绑定变量名方法方法3 3：使用：使用dbms_outputdbms_outputexec exec dbms_output.put_linedbms_output.put_line(函数名函数名(参数参数););例题：例题：P196P196l查看

25、函数的参数和返回值类型查看函数的参数和返回值类型DESCRIBE function_nameDESCRIBE function_namel删除函数删除函数DROP FUNCTION function_name;DROP FUNCTION function_name;11.8 11.8 触发器触发器n 触发器是特定事件出现的时候，自动执行的代触发器是特定事件出现的时候，自动执行的代码块，类似于存储过程，但用户不能直接调用码块，类似于存储过程，但用户不能直接调用它们它们n 触发器的功能触发器的功能l允许允许/限制对表的修改限制对表的修改l自动生成派生列，比如自增字段自动生成派生列，比如自增字段 l

26、强制数据一致性强制数据一致性 l提供审计和日志记录提供审计和日志记录 l防止无效的事务处理防止无效的事务处理 l启用复杂的业务逻辑启用复杂的业务逻辑n 触发器概述触发器概述l触发器的组成部分触发器的组成部分 触发器名称触发器名称 触发语句触发语句 触发器限制触发器限制 触发操作触发操作l触发器类型触发器类型 按触发时间划分按触发时间划分w BeforeBefore触发器触发器w AfterAfter触发器触发器w Instead ofInstead of触发器触发器按触发事件划分按触发事件划分w 语句触发器语句触发器 w 行触发器行触发器 w 数据库事件触发器数据库事件触发器 w 系统事件触发

27、器系统事件触发器 w 用户事件触发器用户事件触发器 n 1.DML1.DML触发器触发器l创建创建DMLDML触发器触发器触发事件：触发事件：INSERTINSERT、UPDATEUPDATE和和DELETEDELETE中中触发事件的作用对象：指定表触发事件的作用对象：指定表/视图和表的某些列视图和表的某些列触发器主体执行的时机：触发器主体执行的时机：BEFOREBEFORE、AFTERAFTER触发器的类型：行触发器、语句触发器触发器的类型：行触发器、语句触发器触发动作的条件：触发动作的条件：WHENWHEN条件子句条件子句如何区分新老记录：如何区分新老记录：NEWNEW、OLDOLDw 这

28、两个变量只有在使用了关键字这两个变量只有在使用了关键字FOR EACH ROWFOR EACH ROW才存在才存在.且且updateupdate语句两个都有，语句两个都有，insertinsert只有只有:new,delect:new,delect只有只有:old:old触发器主体：满足触发条件后的动作，为触发器主体：满足触发条件后的动作，为PL/SQLPL/SQL块块l创建创建DMLDML触发器的语法触发器的语法CREATE OR REPLACE TRIGGER schema.trigger_nameCREATE OR REPLACE TRIGGER schema.trigger_name

29、BEFOREBEFORE|AFTER AFTER DELETEDELETE|INSERTINSERT|UPDATEUPDATE OF column1,.OF column1,.ONON schema.table schema.table REFERENCING OLD AS old|NEW AS new REFERENCING OLD AS old|NEW AS new FOR EACH ROW FOR EACH ROWWHEN(when_condition)WHEN(when_condition)pl/sql_block;pl/sql_block;l在触发器主体在触发器主体PL/SQLPL/

30、SQL块引用新、旧值行的列块引用新、旧值行的列:new.new.列名列名 :old.old.列名列名l行级触发器行级触发器创建触发器创建触发器时使用时使用for each rowfor each row子句，子句，DMLDML语句每操作一行语句每操作一行记录，该触发器就被触发一次记录，该触发器就被触发一次l语句级触发器语句级触发器创建触发器时未使用创建触发器时未使用for each rowfor each row子句，子句，DMLDML语句执行之前语句执行之前或之后，该触发器只被触发一次或之后，该触发器只被触发一次例题：例题：P200P200l使用条件谓词对各使用条件谓词对各DMLDML操作分

31、别做出响应操作分别做出响应lDMLDML条件谓词包括条件谓词包括插入：插入：INSERTINGINSERTINGw 若激发触发器的操作是若激发触发器的操作是insertinsert，则，则insertinginserting为为truetrue修改：修改：UPDATINGUPDATINGw 若激发触发器的操作是若激发触发器的操作是updateupdate，则，则updatingupdating为为truetruew UPDATINGUPDATING可带列名参数，如可带列名参数，如 UPDATING(sal)UPDATING(sal)删除：删除：DELETINGDELETINGw 若激发触发器的

32、操作是若激发触发器的操作是deletedelete，则，则deletingdeleting为为truetrue例题：例题：P201P201n 2.2.替代触发器替代触发器(INSTEAD OF)INSTEAD OF)l在在oracleoracle中，若视图的数据源来自单表，则该视图中，若视图的数据源来自单表，则该视图可进行更新。若视图数据源来自两个以上表时，这可进行更新。若视图数据源来自两个以上表时，这个视图不可更新个视图不可更新l有时为了操作方便，需要对多表视图进行更新。这有时为了操作方便，需要对多表视图进行更新。这时，可通过建立替代触发器来替代该视图原有更新时，可通过建立替代触发器来替代该

33、视图原有更新以达到多表更新的效果以达到多表更新的效果l语法：语法：CREATE OR REPLACE TRIGGER trigger_nameCREATE OR REPLACE TRIGGER trigger_nameINSTEAD OF INSTEAD OF event1 OR event2 OR event3 event1 OR event2 OR event3 ONON view_nameview_nameREFERENCING OLD AS old|NEW AS newREFERENCING OLD AS old|NEW AS newFOR EACH ROW FOR EACH ROW

34、trigger_body;trigger_body;例题：例题：P201P201n 3 3.DDLDDL触发器触发器l用于监控用户对方案对象的创建用于监控用户对方案对象的创建/删除删除/修改等行为修改等行为l可建立到方案用户或数据库中可建立到方案用户或数据库中l语法语法CREATE OR REPLACE TRIGGER trigger_nameCREATE OR REPLACE TRIGGER trigger_name BEFOREBEFORE|AFTERAFTER ddl_event1 OR ddl_event2 OR.ddl_event1 OR ddl_event2 OR.ONON DAT

35、ABASE|SCHEMA DATABASE|SCHEMAtrigger_body;trigger_body;lddl_eventddl_event包括包括CREATECREATE、ALTERALTER、DROPDROP例题：例题：P202P202n 4.4.系统事件触发器系统事件触发器l对用户登录连接对用户登录连接/退出系统进行监视退出系统进行监视l可对启动可对启动/停止数据库以及发生的特定例外进行监视停止数据库以及发生的特定例外进行监视l语法语法CREATE OR REPLACE TRIGGER trigger_nameCREATE OR REPLACE TRIGGER trigger_na

36、me timing timing database_event1 OR database_event2 OR.database_event1 OR database_event2 OR.ONON DATABASEDATABASE|SCHEMASCHEMA trigger_body;trigger_body;例题：例题：P203P203n 管理数据库触发器管理数据库触发器l数据字典数据字典USER_TRIGGERSUSER_TRIGGERSUSER_ERRORSUSER_ERRORSl系统特权系统特权CREATE/ALTER/DROP(ANY)TRIGGERCREATE/ALTER/DROP(A

37、NY)TRIGGERADMINISTER DATABASE TRIGGERADMINISTER DATABASE TRIGGER触发器创建者应具有触发器使用的其他方案中对象的访问触发器创建者应具有触发器使用的其他方案中对象的访问权限权限触发器在执行时使用的是创建者的权限触发器在执行时使用的是创建者的权限l管理触发器的相关操作语法管理触发器的相关操作语法ALTER TRIGGER trigger_name DISABLE|ENABLE;ALTER TRIGGER trigger_name DISABLE|ENABLE;ALTER TABLE table_name DISABLE|ENABLE A

38、LL ALTER TABLE table_name DISABLE|ENABLE ALL TRIGGERS;TRIGGERS;ALTER TRIGGER trigger_name COMPILE;ALTER TRIGGER trigger_name COMPILE;DROP TRIGGER trigger_name;DROP TRIGGER trigger_name;例题：例题：P204P204n 触发器的运用场合触发器的运用场合l服务器安全性控制服务器安全性控制实施对所有用户都有效的限制等实施对所有用户都有效的限制等l审计审计可对特定的数据及可对特定的数据及DMLDML操作操作(语句级、行级

39、语句级、行级)进行审计进行审计l数据完整性控制数据完整性控制l参照完整性参照完整性l表复制表复制可实现同步的服务器间数据复制可实现同步的服务器间数据复制l自动计算自动计算用触发器实现同步自动计算用触发器实现同步自动计算l事件日志事件日志用触发器实现对用触发器实现对DML/DDLDML/DDL操作以及系统事件进行日志记录操作以及系统事件进行日志记录n 触发器设计指南触发器设计指南l为执行相关的操作而使用触发器为执行相关的操作而使用触发器l为集中进行的全局性处理而使用触发器为集中进行的全局性处理而使用触发器l不要为不要为OracleOracle已经实现的功能设计触发器已经实现的功能设计触发器l若触发器代码很长，应将处理代码设计成子程序，若触发器代码很长，应将处理代码设计成子程序，然后在触发器中调用它们然后在触发器中调用它们l过度使用触发器将会导致复杂的互相依赖过度使用触发器将会导致复杂的互相依赖n 掌握掌握PL/SQLPL/SQL程序块编写的方法，包括定义各种程序块编写的方法，包括定义各种变量、流程控制语句等变量、流程控制语句等n 掌握游标的使用掌握游标的使用n 掌握掌握OracleOracle提供的预定义异常，并可自定义异提供的预定义异常，并可自定义异常来解决实际问题常来解决实际问题n 掌握如何编写存储过程掌握如何编写存储过程/函数函数/触发器触发器本章小结本章小结