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

2,第10章存储过程、函数、触发器和包,本章要点：掌握存储过程的创建。熟练掌握带参数的存储过程的使用。掌握存储过程的管理。掌握函数的创建与使用。了解触发器的类型。理解触发器的作用。熟练掌握各种类型的触发器。了解程序包的创建与使用。,3,10.1存储过程,存储过程是一组为了完成特定功能的SQL语句集，它大大提高了SQL语句的功能和灵活性。存储过程经编译后存储在数据库中，所以执行存储过程要比执行存储过程中封装的SQL语句更有效率。,4,10.1.1创建与调用存储过程,创建存储过程需要使用CREATEPROCEDURE语句，其语法如下：CREATEORREPLACEPROCEDUREprocedure_name(parameterIN|OUT|INOUTdata_type),.IS|ASdeclaration_section;BEGINprocedure_body;ENDprocedure_name;,5,10.1.1创建与调用存储过程,【例10.1】创建一个简单的存储过程update_emp，该过程用于将emp表中empno为6500的员工的姓名修改为CANDY，如下：SQLCREATEPROCEDUREupdate_empAS2BEGIN3UPDATEempSETename=CANDYWHEREempno=6500;4ENDupdate_emp;SQL/过程已创建。【例10.2】使用EXECUTE语句与CALL语句调用存储过程update_emp，分别如下：SQLEXECUTEupdate_emp;PL/SQL过程已成功完成。,6,10.1.2带参数的存储过程,1IN参数的使用IN参数是指输入参数，由存储过程的调用者为其赋值(也可以使用默认值)。如果不为参数指定模式，则其模式默认为IN。在调用上述存储过程update_emp2时，就需要为该过程的两个输入参数赋值，赋值的形式主要有如下两种。(1)不指定参数名(2)指定参数名2OUT参数的使用OUT参数是指输出参数，由存储过程中的语句为其赋值，并返回给用户。使用这种模式的参数，必须在参数后面添加OUT关键字。3INOUT参数的使用INOUT参数同时拥有IN与OUT参数的特性，它既接受用户的传值，又允许在过程体中修改其值，并可以将值返回。使用这种模式的参数需要在参数后面添加INOUT关键字。不过，INOUT参数不接受常量值，只能使用变量为其传值。,7,10.1.2带参数的存储过程,【例10.3】创建带IN参数的存储过程update_emp2，为该过程设置两个IN参数，分别用于接受用户提供的empno与ename值，如下：SQLCREATEPROCEDUREupdate_emp22(emp_numINNUMBER,emp_nameINVARCHAR2)AS3BEGIN4UPDATEempSETename=emp_name5WHEREempno=emp_num;6ENDupdate_emp2;7/过程已创建。,8,10.1.2带参数的存储过程,【例10.4】调用update_emp2过程，通过该过程将empno为6500的员工的ename修改为XIAOQI，如下：SQLEXECupdate_emp2(6500,XIAOQI);PL/SQL过程已成功完成。【例10.5】使用指定参数名的形式调用update_emp2过程，如下：SQLEXECupdate_emp2(emp_name=XIAOQI,emp_num=6500);PL/SQL过程已成功完成。,9,10.1.2带参数的存储过程,【例10.6】创建存储过程select_emp，为该过程设置一个IN参数和一个OUT参数，其中IN参数接受用户提供的empno值，然后在过程体中将该empno对应的ename值传递给OUT参数，如下：SQLCREATEPROCEDUREselect_emp2(emp_numINNUMBER,emp_nameOUTVARCHAR2)AS3BEGIN4SELECTenameINTOemp_name5FROMempWHEREempno=emp_num;6ENDselect_emp;7/过程已创建。,10,10.1.2带参数的存储过程,【例10.7】调用存储过程select_emp，为其IN参数赋值为6500，并声明变量employee_name接受与输出其OUT参数的返回值，如下：SQLVARIABLEemployee_nameVARCHAR2(10);SQLEXECselect_emp(6500,:employee_name);PL/SQL过程已成功完成。然后，需要使用PRINT命令查看变量employee_name中的值，如下：SQLPRINTemployee_name;EMPLOYEE_NAME-XIAOQI也可以使用SELECT语句查看变量employee_name中的值，语句如下：SQLSELECT:employee_nameFROMdual;,11,10.1.2带参数的存储过程,【例10.8】创建存储过程exchange_value，通过该过程交换两个变量中的值，过程创建如下：参见教材P227调用exchange_value过程，调用前声明为INOUT参数赋值的变量，调用后使用SELECT语句输出交换值后的结果，如下：参见教材P227,12,10.1.3修改与删除存储过程,修改存储过程是在CREATEPROCEDURE语句中添加ORREPLACE关键字，其他内容与创建存储过程一样，其实质是删除原有过程，然后创建一个全新的过程，只不过前后两个过程的名称相同而已。删除存储过程需要使用DROPPROCEDURE语句，其语法形式如下：DROPPROCEDUREprocedure_name;,13,10.1.4查询存储过程的定义信息,对于创建好的存储过程，如果想要了解其定义信息，可以查询数据字典user_source。【例10.9】通过数据字典user_source查询存储过程select_emp的定义信息，如下：参见教材P228其中，name表示对象名称；type表示对象类型；line表示定义信息中文本所在的行数；text表示对应行的文本信息。,14,10.2函数,创建函数需要使用CREATEFUNCTION语句，其语法如下：CREATEORREPLACEFUNCTIONfunction_name(parameterIN|OUT|INOUTdata_type),.RETURNdata_typeIS|ASdeclaration_section;BEGINfunction_body;ENDfunction_name;,15,10.3实验指导使用存储过程与函数查询图书信息,实验指导10-1：使用存储过程和函数查询图书信息1创建函数get_prompt首先创建函数get_prompt，如下：参见教材P2302创建存储过程get_book_information3调用过程存储过程和函数都已经创建好了，需要查询某图书的信息时就可以直接调用get_book_information存储过程。例如获取bookid为2的图书的信息，如下：参见教材P231,16,10.4触发器,触发器是一种特殊的存储过程，它在发生某种数据库事件时由Oracle系统自动触发。触发器通常用于加强数据的完整性约束和业务规则等，对于表来说，触发器可以实现比CHECK约束更为复杂的约束。,17,10.4.1触发器的类型,DML触发器：DML触发器由DML语句触发，例如INSERT、UPDATE和DELETE语句。INSTEADOF触发器：INSTEADOF触发器又称替代触发器，用于执行一个替代操作来代替触发事件的操作。系统事件触发器：系统事件触发器在发生如数据库启动或关闭等系统事件时触发。DDL触发器：DDL触发器由DDL语句触发，例如CREATE、ALTER和DROP语句。DDL触发器同样可以分为BEFORE触发器与AFTER触发器。,18,10.4.2创建触发器,创建触发器需要使用CREATETRIGGER语句，其语法如下：CREATEORREPLACETRIGGERtrigger_nameBEFORE|AFTER|INSTEADOFtrigger_eventONtable_name|view_name|DATABASEFOREACHROWENABLE|DISABLEWHENtrigger_conditionDECLAREdeclaration_statements;BEGINtrigger_body;ENDtrigger_name;,19,10.4.3DML触发器,DML触发器由DML语句触发，其对应的trigger_event具体内容如下：INSERT|DELETE|UPDATEOFcolumn,.关于DML触发器的说明如下：DML操作主要包括INSERT、DELETE和UPDATE操作，通常根据触发器所针对的具体事件将DML触发器分为INSERT触发器、UPDATE触发器和DELETE触发器。可以将DML操作细化到列，即针对某列进行DML操作时激活触发器。任何DML触发器都可以按触发时间分为BEFORE触发器与AFTER触发器。在行级触发器中，为了获取某列在DML操作前后的数据，Oracle提供了两种特殊的标识符:OLD和:NEW，通过:OLD.column_name的形式可以获取该列的旧数据，而通过:NEW.column_name则可以获取该列的新数据。,20,10.4.3DML触发器,【例10.11】为了演示触发器的效果，下面首先创建两个简单的示例表：student(学生表)和record(记录表)，并向student表中添加几条记录，如下：参见教材P233创建AFTERUPDATE触发器，要求在修改student表中的某行数据后，在record表中记录修改操作，并保存修改前的行数据。创建触发器的语句如下：参见教材P234,21,10.4.4INSTEADOF触发器,INSTEADOF触发器用于执行一个替代操作来代替触发事件的操作，而触发事件本身最终不会被执行。如果是DML触发器，则无论是BEFORE触发器还是AFTER触发器，触发事件最终都会被执行。不过，Oracle中的INSTEADOF触发器不能针对表，而只能针对视图。,22,10.4.4INSTEADOF触发器,【例10.12】首先基于student表创建视图student_view，该视图检索student表中的所有数据，但将student表中的sage列加1。视图创建如下：SQLCREATEVIEWstudent_view2AS3SELECTsid,sname,sage+1new_age4FROMstudent5WITHCHECKOPTION;视图已创建。,23,10.4.5系统事件触发器,系统事件触发器是指由数据库系统事件触发的触发器，其所支持的系统事件如表10-1所示。,24,10.4.5系统事件触发器,【例10.13】在system用户下创建一个系统事件触发器，该触发器由LOGON事件触发，记录登录用户的用户名(USER)与登录时间，如下：SQLCONNECTsystem/admin已连接。SQLCREATETRIGGERlogon_trigger2AFTERLOGON3ONDATABASE4BEGIN5INSERTINTOlogon_logVALUES(USER,SYSDATE);6ENDlogon_trigger;7/触发器已创建,25,10.4.6DDL触发器,DDL触发器由DDL语句触发，按触发时间可以分为BEFORE触发器与AFTER触发器，其所针对的事件包括CREATE、ALTER、DROP、ANALYZE、GRANT、COMMENT、REVOKE、RENAME、TRUNCATE、AUDIT、NOTAUDIT、ASSOCIATESTATISTICS和DISASSOCIATESTATISTICS。创建DDL触发器需要用户具有DBA权限。,26,10.4.7禁用与启用触发器,在创建触发器时，可以使用ENABLE与DISABLE关键字指定触发器的初始状态为启用或禁用，默认情况下为ENABLE。在需要的时候，也可以使用ALTERTRIGGER语句修改触发器的状态，其语法如下：ALTERTRIGGERtrigger_nameENABLE|DISABLE;如果需要修改某个表上的所有触发器的状态，还可以使用如下形式：ALTERTABLEtable_nameENABLE|DISABLEALLTRIGGERS;,27,10.4.8修改与删除触发器,修改触发器只需要在CREATETRIGGER语句中添加ORREPLACE关键字。删除触发器需要使用DROPTRIGGER语句，其语法如下：DROPTRIGGERtrigger_name;,28,10.5程序包,使用程序包主要是为了实现程序模块化，程序包可以将相关的存储过程、函数、变量、常量和游标等PL/SQL程序组合在一起，通过这种方式可以构建供程序人员重用的代码库。另外，当首次调用程序包中的存储过程或函数等元素时，Oracle会将整个程序包调入内存，在下次调用包中的元素时，Oracle就可以直接从内存中读取，从而提高程序的运行效率。,29,10.5.1创建程序包,1创建包规范创建包规范需要使用CREATEPACKAGE语句，其简要语法如下：CREATEORREPLACEPACKAGEpackage_nameIS|ASpackage_specification;ENDpackage_name;语法说明如下。package_name：创建的包名。package_specification：用于列出用户可以使用的公共存储过程、函数、类型和对象。,30,10.5.1创建程序包,2创建包体创建包体需要使用CREATEPACKAGEBODY语句，并且在创建时需要指定已创建的包，其简要语法如下：CREATEORREPLACEPACKAGEBODYpackage_nameIS|ASpackage_body;ENDpackage_name;,31,10.5.2调用程序包中的元素,DBMS_OUTPUT是系统定义的包，而PUT_LINE是该包中的存储过程。可见调用程序包中的元素时，是使用如下形式：package_name.element_name;其中，element_name表示元素名称，可以是