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

第10章存储过程、函数、触发器和包本章要点：掌握存储过程的创建。熟练掌握带参数的存储过程的使用。掌握存储过程的管理。掌握函数的创建与使用。了解触发器的类型。理解触发器的作用。熟练掌握各种类型的触发器。了解程序包的创建与使用。210.1存储过程存储过程是一组为了完成特定功能的SQL语句集，它大大提高了SQL语句的功能和灵活性。存储过程经编译后存储在数据库中，所以执行存储过程要比执行存储过程中封装的SQL语句更有效率。310.1.1创建与调用存储过程创建存储过程需要使用CREATEPROCEDURE语句，其语法如下：CREATE[ORREPLACE]PROCEDUREprocedure_name[ (parameter[IN|OUT|INOUT]data_type) [,...]]{IS|AS} [declaration_section;]BEGIN procedure_body;END[procedure_name];410.1.1创建与调用存储过程【例10.1】创建一个简单的存储过程update_emp，该过程用于将emp表中empno为6500的员工的姓名修改为CANDY，如下：SQL>CREATEPROCEDUREupdate_empAS2BEGIN3UPDATEempSETename='CANDY'WHEREempno=6500;4ENDupdate_emp;SQL>/过程已创建。【例10.2】使用EXECUTE语句与CALL语句调用存储过程update_emp，分别如下：SQL>EXECUTEupdate_emp;PL/SQL过程已成功完成。510.1.2带参数的存储过程1．IN参数的使用IN参数是指输入参数，由存储过程的调用者为其赋值(也可以使用默认值)。如果不为参数指定模式，则其模式默认为IN。在调用上述存储过程update_emp2时，就需要为该过程的两个输入参数赋值，赋值的形式主要有如下两种。(1) 不指定参数名(2) 指定参数名2．OUT参数的使用OUT参数是指输出参数，由存储过程中的语句为其赋值，并返回给用户。使用这种模式的参数，必须在参数后面添加OUT关键字。3．INOUT参数的使用INOUT参数同时拥有IN与OUT参数的特性，它既接受用户的传值，又允许在过程体中修改其值，并可以将值返回。使用这种模式的参数需要在参数后面添加INOUT关键字。不过，INOUT参数不接受常量值，只能使用变量为其传值。610.1.2带参数的存储过程【例10.3】创建带IN参数的存储过程update_emp2，为该过程设置两个IN参数，分别用于接受用户提供的empno与ename值，如下：SQL>CREATEPROCEDUREupdate_emp22(emp_numINNUMBER,emp_nameINVARCHAR2)AS3BEGIN4UPDATEempSETename=emp_name5WHEREempno=emp_num;6ENDupdate_emp2;7/过程已创建。710.1.2带参数的存储过程【例10.4】调用update_emp2过程，通过该过程将empno为6500的员工的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对应的ename值传递给OUT参数，如下：SQL>CREATEPROCEDUREselect_emp2(emp_numINNUMBER,emp_nameOUTVARCHAR2)AS3BEGIN4SELECTenameINTOemp_name5FROMempWHEREempno=emp_num;6ENDselect_emp;7/过程已创建。910.1.2带参数的存储过程【例10.7】调用存储过程select_emp，为其IN参数赋值为6500，并声明变量employee_name接受与输出其OUT参数的返回值，如下：SQL>VARIABLEemployee_nameVARCHAR2(10);SQL>EXECselect_emp(6500,:employee_name);PL/SQL过程已成功完成。然后，需要使用PRINT命令查看变量employee_name中的值，如下：SQL>PRINTemployee_name;EMPLOYEE_NAME--------------------------------XIAOQI也可以使用SELECT语句查看变量employee_name中的值，语句如下：SQL>SELECT:employee_nameFROMdual;1010.1.2带带参数数的存存储过过程【例10.8】】创建存存储过过程exchange_value，，通过过该过过程交交换两两个变变量中中的值值，过过程创创建如如下：：参见教教材P227调用exchange_value过过程，，调用用前声声明为为INOUT参数数赋值值的变变量，，调用用后使使用SELECT语语句输输出交交换值值后的的结果果，如如下：：参见教教材P2271110.1.3修修改与与删除除存储储过程程修改存存储过过程是是在CREATEPROCEDURE语语句中中添加加ORREPLACE关键键字，，其他他内容容与创创建存存储过过程一一样，，其实实质是是删除除原有有过程程，然然后创创建一一个全全新的的过程程，只只不过过前后后两个个过程程的名名称相相同而而已。。删除存存储过过程需需要使使用DROPPROCEDURE语语句，，其语语法形形式如如下：：DROPPROCEDUREprocedure_name;1210.1.4查查询存存储过过程的的定义义信息息对于创创建好好的存存储过过程，，如果果想要要了解解其定定义信信息，，可以以查询询数据据字典典user_source。。【例10.9】】通过数数据字字典user_source查询询存储储过程程select_emp的定定义信信息，，如下下：参见教教材P228其中，，name表示示对象象名称称；type表表示对对象类类型；；line表示示定义义信息息中文文本所所在的的行数数；text表表示对对应行行的文文本信信息。。1310.2函函数数创建函函数需需要使使用CREATEFUNCTION语句句，其其语法法如下下：CREATE[ORREPLACE]FUNCTIONfunction_name[(parameter[IN|OUT|INOUT]data_type)[,...]]RETURNdata_type{IS|AS}[declaration_section;]BEGINfunction_body;END[function_name];1410.3实验指指导———使使用存存储过过程与与函数数查询询图书书信息息实验指指导10-1：：使用用存储储过程程和函函数查查询图图书信信息1．创创建函函数get_prompt首先创创建函函数get_prompt，，如下下：参见教教材P2302．创创建存存储过过程get_book_information3．调调用过过程存储过过程和和函数数都已已经创创建好好了，，需要要查询询某图图书的的信息息时就就可以以直接接调用用get_book_information存储储过程程。例例如获获取bookid为为2的的图书书的信信息，，如下下：参见教教材P2311510.4触触发发器器触发器器是一一种特特殊的的存储储过程程，它它在发发生某某种数数据库库事件件时由由Oracle系统统自动动触发发。触发器器通常常用于于加强强数据据的完完整性性约束束和业业务规规则等等，对对于表表来说说，触触发器器可以以实现现比CHECK约束束更为为复杂杂的约约束。。1610.4.1触触发器器的类类型DML触发发器：：DML触触发器器由DML语句句触发发，例例如INSERT、、UPDATE和DELETE语语句。。INSTEADOF触触发器器：INSTEADOF触触发器器又称称替代代触发发器，，用于于执行行一个个替代代操作作来代代替触触发事事件的的操作作。系统事事件触触发器器：系系统事事件触触发器器在发发生如如数据据库启启动或或关闭闭等系系统事事件时时触发发。DDL触发发器：：DDL触触发器器由DDL语句句触发发，例例如CREATE、、ALTER和和DROP语句句。DDL触发发器同同样可可以分分为BEFORE触触发器器与AFTER触发发器。。1710.4.2创建触触发器创建触发器需需要使用CREATETRIGGER语句，，其语法如下下：CREATE[ORREPLACE]TRIGGERtrigger_name[BEFORE|AFTER|INSTEADOF]trigger_event{ONtable_name|view_name|DATABASE}[FOREACHROW][ENABLE|DISABLE][WHENtrigger_condition][DECLAREdeclaration_statements;]BEGINtrigger_body;END[trigger_name];1810.4.3DML触发器DML触发器器由DML语语句触发，其其对应的trigger_event具体内容容如下：{INSERT|DELETE|UPDATE[OFcolumn[,...]]}关于DML触触发器的说明明如下：DML操作主主要包括INSERT、、DELETE和UPDATE操作作，通常根据据触发器所针针对的具体事事件将DML触发器分为为INSERT触发器、、UPDATE触发器和和DELETE触发器。。可以将DML操作细化到到列，即针对对某列进行DML操作时时激活触发器器。任何DML触触发器都可以以按触发时间间分为BEFORE触发发器与AFTER触发器器。在行级触发器器中，为了获获取某列在DML操作前前后的数据，，Oracle提供了两两种特殊的标标识符——:OLD和:NEW，通通过:OLD.column_name的形式式可以获取该该列的旧数据据，而通过:NEW.column_name则可以获取取该列的新数数据。1910.4.3DML触发器【例10.11】为了演示触发发器的效果，，下面首先创创建两个简单单的示例表：：student(学生生表)和record(记录表)，，并向student表表中添加几条条记录，如下下：参见教材P233创建AFTERUPDATE触发发器，要求在在修改student表表中的某行数数据后，在record表中记录修修改操作，并并保存修改前前的行数据。。创建触发器器的语句如下下：参见教材P2342010.4.4INSTEADOF触发器器INSTEADOF触触发器用于执执行一个替代代操作来代替替触发事件的的操作，而触触发事件本身身最终不会被被执行。如果是DML触发器，则则无论是BEFORE触触发器还是AFTER触触发器，触发发事件最终都都会被执行。。不过，Oracle中的的INSTEADOF触发器不能能针对表，而而只能针对视视图。2110.4.4INSTEADOF触发器器【例10.12】首先基于student表创建视图图student_view，该视视图检索student表中的所有有数据，但将将student表中的的sage列列加1。视图图创建如下：：SQL>CREATEVIEWstudent_view2AS3SELECTsid,sname,sage+1new_age4FROMstudent5WITHCHECKOPTION;视图已创建。。2210.4.5系统事事件触发器系统事件触发发器是指由数数据库系统事事件触发的触触发器，其所所支持的系统统事件如表10-1所示示。系统事件说

明LOGOFF用户从数据库注销LOGON用户登录数据库SERVERERROR服务器发生错误SHUTDOWN关闭数据库实例STARTUP打开数据库实例2310.4.5系统事事件触发器【例10.13】在system用户下创创建一个系统统事件触发器器，该触发器器由LOGON事件触发发，记录登录录用户的用户户名(USER)与登录录时间，如下下：SQL>CONNECTsystem/admin已连接。SQL>CREATETRIGGERlogon_trigger2AFTERLOGON3ONDATABASE4BEGIN5INSERTINTOlogon_logVALUES(USER,SYSDATE);6ENDlogon_trigger;7/触发器已创建建2410.4.6DDL触发器DDL触发器器由DDL语语句触发，按按触发时间可可以分为BEFORE触触发器与AFTER触发发器，其所针针对的事件包包括CREATE、ALTER、DROP、ANALYZE、GRANT、COMMENT、REVOKE、RENAME、、TRUNCATE、AUDIT、、NOTAUDIT、ASSOCIATESTATISTICS和和DISASSOCIATESTATISTICS。创建DDL触触发器需要用用户具有DBA权限。2510.4.7禁用与与启用触发器器在创建触发器器时，可以使使用ENABLE与DISABLE关键字指定定触发器的初初始状态为启启用或禁用，，默认情况下下为ENABLE。在需要的时候候，也可以使使用ALTERTRIGGER语语句修改触发发器的状态，，其语法如下下：ALTERTRIGGERtrigger_nameENABLE|DISABLE;如果果需需要要修修改改某某个个表表上上的的所所有有触触发发器器的的状状态态，，还还可可以以使使用用如如下下形形式式：：ALTERTABLEtable_nameENABLE|DISABLEALLTRIGGERS;2610.4.8修修改改与与删删除除触触发发器器修改改触触发发器器只只需需要要在在CREATETRIGGER语语句句中中添添加加ORREPLACE关关键键字字。。删除除触触发发器器需需要要使使用用DROPTRIGGER语语句句，，其其语语法法如如下下：：DROPTRIGGERtrigger_name;2710.5程程序序包包使用用程程序序包包主主要要是是为为了了实实现现程程序序模模块块化化，，程程序序包包可可以以将将相相关关的的存存储储过过程程、、函函数数、、变变量量、、常常量量和和游游标标等等PL/SQL程程序序组组合合在在一一起起，，通通过过这这种种方方式式可可以以构构建建供供程程序序人人员员重重用用的的代代码码库库。。另外外，，当当首首次次调调用用程程序序包包中中的的存存储储过过程程或或函函数数等等元元素素时时，，Oracle会会将将整整个个程程序序包包调调入入内内存存，，在在下下次次调调用用包包中中的的元元素素时时，，Oracle就就可可以以直直接接从从内内存存中中读读取取，，从从而而提提高高程程序序的的运运行行效效率率。。2810.5.1创创建建程程序序包包1．．创创建建包包规规范范创建建包包规规范范需需要要使使用用CREATEPACKAGE语语句句，，其其简简要要语语法法如如下下：：CREATE[ORREPLACE]PACKAGEpackage_name{IS|AS}package_specification;ENDpackage_name;语法法说说明明如如下下。。package_name：：创创建建的的包包名名。。package_specification：：用用于于列列出出用用户户可可以以使使用用的的公公共共存存储储过过程程、、函函数数、、类类型型和和对对象象。。2910.5.1创创建建程程序序包包2．创创建包包体创建包包体需需要使使用CREATEPACKAGEBODY语句句，并并且在在创建建时需需要指指定已已创建建的包包，其其简要要语法法如下下：CREATE[ORREPLACE]PACKAGEBODYpackage_name{IS|AS}package_body;ENDpackage_name;3010.5.2调调用程程序包包中的的元素素DBMS_OUTPUT是系系统定定义的的包，，而PUT_LINE是是该包包中的的存储储过程程。可可见调调用程程序包包中的的元素素时，，是使使用如如下形形式：：package_name.[element_name];其中，，element_name表示示元素素名称称，可可以是是存储储过程程名、、函数数名、、变量量名和和常量量名等等。3110.5.2调调用程程序包包中的的元素素【例10.17】调用student_package包中中的add_student_procedure过程程，向向student表表中添添加一一条记记录，，如下下：