Oracle数据库-存储过程、函数、触发器和包

1、第10章章存存储过程程、函数数、触发发器和包包本章要点点：掌握存储储过程的的创建。熟练掌握握带参数数的存储储过程的的使用。掌握存储储过程的的管理。掌握函数数的创建建与使用用。了解触发发器的类类型。理解触发发器的作作用。熟练掌握握各种类类型的触触发器。了解程序序包的创创建与使使用。210.1存存 储过过程程存储过程程是一组组为了完完成特定定功能的的SQL语句集集，它大大大提高高了SQL语句句的功能能和灵活活性。存储过程程经编译译后存储储在数据据库中，所以执执行存储储过程要要比执行行存储过过程中封封装的SQL语语句更有有效率。310.1.1创创建建与调用用存储过过程创建存储储过程需需要使用用CRE

2、ATE PROCEDURE语句句，其语语法如下下：CREATEORREPLACE PROCEDURE procedure_name ( parameter IN |OUT |INOUT data_type ) ,. IS |AS declaration_section;BEGINprocedure_body;ENDprocedure_name ;410.1.1创创建建与调用用存储过过程【例10.1】创建一个个简单的的存储过过程update_emp，该过程程用于将将emp表中empno为6500的员工工的姓名名修改为为CANDY，如下：SQL CREATEPROCEDUREupdate_empA

3、S2BEGIN3UPDATEempSET ename =CANDYWHEREempno= 6500;4ENDupdate_emp;SQL /过程已创创建。【例10.2】使用EXECUTE语语句与CALL语句调调用存储储过程update_emp，分别别如下：SQL EXECUTEupdate_emp;PL/SQL过过程已已成功完完成。510.1.2带带参参数的存存储过程程1IN参数的的使用IN参数数是指输输入参数数，由存存储过程程的调用用者为其其赋值(也可以以使用默默认值)。如果果不为参参数指定定模式，则其模模式默认认为IN。在调用上上述存储储过程update_emp2时，就需要要为该过过程的两

4、两个输入入参数赋赋值，赋赋值的形形式主要要有如下下两种。(1)不不指定定参数名名(2)指指定参参数名2OUT参数数的使用用OUT参参数是指指输出参参数，由由存储过过程中的的语句为为其赋值值，并返返回给用用户。使使用这种种模式的的参数，必须在在参数后后面添加加OUT关键字字。3IN OUT参数数的使用用INOUT参参数同时时拥有IN与OUT参参数的特特性，它它既接受受用户的的传值，又允许许在过程程体中修修改其值值，并可可以将值值返回。使用这这种模式式的参数数需要在在参数后后面添加加INOUT关键字字。不过过，IN OUT参数数不接受受常量值值，只能能使用变变量为其其传值。610.1.2带带参参数

5、的存存储过程程【例10.3】创建带IN参数数的存储储过程update_emp2，为为该过程程设置两两个IN参数，分别用用于接受受用户提提供的empno与ename值，如下：SQL CREATEPROCEDUREupdate_emp22( emp_numINNUMBER ,emp_nameINVARCHAR2)AS3BEGIN4UPDATEempSET ename =emp_name5WHERE empno =emp_num;6ENDupdate_emp2;7/过程已创创建。710.1.2带带参参数的存存储过程程【例10.4】调用update_emp2过程，通过该该过程将将empno为为6500

6、的员员工的ename修改改为XIAOQI，如如下：SQL EXECupdate_emp2(6500,XIAOQI);PL/SQL过过程已已成功完完成。【例10.5】使用指定定参数名名的形式式调用update_emp2过程程，如下下：SQL EXECupdate_emp2(emp_name = XIAOQI ,emp_num=6500);PL/SQL过过程已已成功完完成。810.1.2带带参参数的存存储过程程【例10.6】创建存储储过程select_emp，为该该过程设设置一个个IN参参数和一一个OUT参数数，其中中IN参参数接受受用户提提供的empno值，然后在在过程体体中将该该empno对对

7、应的ename值传传递给OUT参参数，如如下：SQL CREATEPROCEDUREselect_emp2( emp_numINNUMBER ,emp_nameOUT VARCHAR2 )AS3BEGIN4SELECTenameINTO emp_name5FROMemp WHERE empno =emp_num;6ENDselect_emp;7/过程已创创建。910.1.2带带参参数的存存储过程程【例10.7】调用存储储过程select_emp，为其IN参数数赋值为为6500，并并声明变变量employee_name接受与与输出其其OUT参数的的返回值值，如下下：SQL VARIABLE em

8、ployee_nameVARCHAR2(10) ;SQL EXECselect_emp(6500, :employee_name);PL/SQL过过程已已成功完完成。然后，需需要使用用PRINT命命令查看看变量employee_name中的值值，如下下：SQL PRINT employee_name;EMPLOYEE_NAME-XIAOQI也可以使使用SELECT语句句查看变变量employee_name中中的值，语句如如下：SQL SELECT:employee_nameFROM dual;1010.1.2带带参参数的存存储过程程【例10.8】创建存储储过程exchange_value，通通

9、过该过过程交换换两个变变量中的的值，过过程创建建如下：参见教材材P227调用exchange_value过程，调用前前声明为为INOUT参数赋赋值的变变量，调调用后使使用SELECT语句句输出交交换值后后的结果果，如下下：参见教材材P2271110.1.3修修改改与删除除存储过过程修改存储储过程是是在CREATEPROCEDURE语语句中添添加OR REPLACE关关键字，其他内内容与创创建存储储过程一一样，其其实质是是删除原原有过程程，然后后创建一一个全新新的过程程，只不不过前后后两个过过程的名名称相同同而已。删除存储储过程需需要使用用DROPPROCEDURE语语句，其其语法形形式如下下：

10、DROP PROCEDUREprocedure_name;1210.1.4查查询询存储过过程的定定义信息息对于创建建好的存存储过程程，如果果想要了了解其定定义信息息，可以以查询数数据字典典user_source。【例10.9】通过数据据字典user_source查询询存储过过程select_emp的的定义信信息，如如下：参见教材材P228其中，name表示对对象名称称；type表表示对象象类型；line表示示定义信信息中文文本所在在的行数数；text表表示对应应行的文文本信息息。1310.2函函数数创建函数数需要使使用CREATEFUNCTION语句句，其语语法如下下：CREATEORREPL

11、ACE FUNCTIONfunction_name ( parameter IN |OUT |INOUT data_type ) ,. RETURNdata_type IS |AS declaration_section;BEGINfunction_body ;ENDfunction_name ;1410.3实验指导导使使用存储储过程与与函数查查询图书书信息实验指导导10-1：使使用存储储过程和和函数查查询图书书信息1创建建函数get_prompt首先创建建函数get_prompt，如下下：参见教材材P2302创建建存储过过程get_book_information3调用用过程存储过程程和函数

12、数都已经经创建好好了，需需要查询询某图书书的信息息时就可可以直接接调用get_book_information存储储过程。例如获获取bookid为2的图书书的信息息，如下下：参见教材材P2311510.4触触发发器器触发器是是一种特特殊的存存储过程程，它在在发生某某种数据据库事件件时由Oracle系系统自动动触发。触发器通通常用于于加强数数据的完完整性约约束和业业务规则则等，对对于表来来说，触触发器可可以实现现比CHECK约束更更为复杂杂的约束束。1610.4.1触触发发器的类类型DML触触发器：DML触发器器由DML语句句触发，例如INSERT、UPDATE和DELETE语句句。INSTEA

13、D OF触发器器：INSTEADOF触触发器又又称替代代触发器器，用于于执行一一个替代代操作来来代替触触发事件件的操作作。系统事件件触发器器：系统统事件触触发器在在发生如如数据库库启动或或关闭等等系统事事件时触触发。DDL触触发器：DDL触发器器由DDL语句句触发，例如CREATE、ALTER和和DROP语句句。DDL触发发器同样样可以分分为BEFORE触发发器与AFTER触发发器。1710.4.2创创建建触发器器创建触发发器需要要使用CREATETRIGGER语句句，其语语法如下下：CREATEORREPLACE TRIGGERtrigger_name BEFORE| AFTER |INST

14、EADOF trigger_event ON table_name|view_name|DATABASE FOREACH ROW ENABLE| DISABLE WHENtrigger_condition DECLAREdeclaration_statements; BEGINtrigger_body;ENDtrigger_name;1810.4.3DML触发发器DML触触发器由由DML语句触触发，其其对应的的trigger_event具体体内容如如下： INSERT| DELETE| UPDATE OF column ,. 关于DML触发发器的说说明如下下：DML操操作主要要包括INSERT

15、、DELETE和UPDATE操作作，通常常根据触触发器所所针对的的具体事事件将DML触触发器分分为INSERT触发发器、UPDATE触触发器和和DELETE触发器器。可以将DML操操作细化化到列，即针对对某列进进行DML操作作时激活活触发器器。任何DML触发发器都可可以按触触发时间间分为BEFORE触触发器与与AFTER触触发器。在行级触触发器中中，为了了获取某某列在DML操操作前后后的数据据，Oracle提供供了两种种特殊的的标识符符:OLD和:NEW，通过:OLD.column_name的形式式可以获获取该列列的旧数数据，而而通过:NEW.column_name则可以以获取该该列的新新数据

16、。1910.4.3DML触发发器【例10.11】为了演示示触发器器的效果果，下面面首先创创建两个个简单的的示例表表：student(学生表表)和record(记录表表)，并并向student表表中添加加几条记记录，如如下：参见教材材P233创建AFTER UPDATE触发发器，要要求在修修改student表表中的某某行数据据后，在在record表中记记录修改改操作，并保存存修改前前的行数数据。创创建触发发器的语语句如下下：参见教材材P2342010.4.4INSTEADOF触触发器INSTEAD OF触发器器用于执执行一个个替代操操作来代代替触发发事件的的操作，而触发发事件本本身最终终不会被被

17、执行。如果是DML触触发器，则无论论是BEFORE触发发器还是是AFTER触触发器，触发事事件最终终都会被被执行。不过，Oracle中中的INSTEADOF触触发器不不能针对对表，而而只能针针对视图图。2110.4.4INSTEADOF触触发器【例10.12】首先基于于student表创创建视图图student_view，该视视图检索索student表中中的所有有数据，但将student表中的的sage列加加1。视视图创建建如下：SQL CREATEVIEW student_view2AS3SELECTsid ,sname,sage +1new_age4FROM student5WITH CH

18、ECK OPTION;视图已创创建。2210.4.5系系统统事件触触发器系统事件件触发器器是指由由数据库库系统事事件触发发的触发发器，其其所支持持的系统统事件如如表10-1所所示。系统事件说 明LOGOFF用户从数据库注销LOGON用户登录数据库SERVERERROR服务器发生错误SHUTDOWN关闭数据库实例STARTUP打开数据库实例2310.4.5系系统统事件触触发器【例10.13】在system用户下下创建一一个系统统事件触触发器，该触发发器由LOGON事件件触发，记录登登录用户户的用户户名(USER)与登登录时间间，如下下：SQL CONNECTsystem/admin已连接。SQL

19、 CREATETRIGGER logon_trigger2AFTERLOGON3ONDATABASE4BEGIN5INSERTINTO logon_logVALUES(USER ,SYSDATE) ;6ENDlogon_trigger;7/触发器已已创建2410.4.6DDL触发发器DDL触触发器由由DDL语句触触发，按按触发时时间可以以分为BEFORE触触发器与与AFTER触触发器，其所针针对的事事件包括括CREATE、ALTER、DROP、ANALYZE、GRANT、COMMENT、REVOKE、RENAME、TRUNCATE、AUDIT、NOTAUDIT、ASSOCIATE STATIS

20、TICS和和DISASSOCIATE STATISTICS。创建DDL触发发器需要要用户具具有DBA权限限。2510.4.7禁禁用用与启用用触发器器在创建触触发器时时，可以以使用ENABLE与与DISABLE关键键字指定定触发器器的初始始状态为为启用或或禁用，默认情情况下为为ENABLE。在需要的的时候，也可以以使用ALTERTRIGGER语句修修改触发发器的状状态，其其语法如如下：ALTERTRIGGER trigger_nameENABLE|DISABLE ;如果需要要修改某某个表上上的所有有触发器器的状态态，还可可以使用用如下形形式：ALTERTABLEtable_name ENABLE

21、| DISABLEALL TRIGGERS ;2610.4.8修修改改与删除除触发器器修改触发发器只需需要在CREATETRIGGER语句句中添加加ORREPLACE关键键字。删除触发发器需要要使用DROP TRIGGER语语句，其其语法如如下：DROP TRIGGERtrigger_name ;2710.5程程序序包包使用程序序包主要要是为了了实现程程序模块块化，程程序包可可以将相相关的存存储过程程、函数数、变量量、常量量和游标标等PL/SQL程序序组合在在一起，通过这这种方式式可以构构建供程程序人员员重用的的代码库库。另外，当当首次调调用程序序包中的的存储过过程或函函数等元元素时，Orac

22、le会将整整个程序序包调入入内存，在下次次调用包包中的元元素时，Oracle就可以以直接从从内存中中读取，从而提提高程序序的运行行效率。2810.5.1创创建建程序包包1创建建包规范范创建包规规范需要要使用CREATEPACKAGE语句句，其简简要语法法如下：CREATEORREPLACE PACKAGEpackage_name IS |ASpackage_specification;ENDpackage_name ;语法说明明如下。package_name：创建的的包名。package_specification：用于列列出用户户可以使使用的公公共存储储过程、函数、类型和和对象。2910.5

23、.1创创建建程序包包2创建建包体创建包体体需要使使用CREATEPACKAGE BODY语语句，并并且在创创建时需需要指定定已创建建的包，其简要要语法如如下：CREATEORREPLACE PACKAGEBODY package_name IS |ASpackage_body;ENDpackage_name ;3010.5.2调调用用程序包包中的元元素DBMS_OUTPUT是系系统定义义的包，而PUT_LINE是该包包中的存存储过程程。可见见调用程程序包中中的元素素时，是是使用如如下形式式：package_name.element_name;其中，element_name表表示元素素名称，可以是是