第10章Oracle资料学习

2第10章存储过程、函数、触发器和包本章要点：掌握存储过程的创建。熟练掌握带参数的存储过程的使用。掌握存储过程的管理。掌握函数的创建与使用。了解触发器的类型。理解触发器的作用。熟练掌握各种类型的触发器。了解程序包的创建与使用。310.1存储过程存储过程是一组为了完成特定功能的SQL语句集，它大大提高了SQL语句的功能和灵活性。存储过程经编译后存储在数据库中，所以执行存储过程要比执行存储过程中封装的SQL语句更有效率。410.1.1创建与调用存储过程创建存储过程需要使用CREATEPROCEDURE语句，其语法如下：CREATE[ORREPLACE]PROCEDUREprocedure_name[(parameter[IN|OUT|INOUT]data_type)[,...]]{IS|AS}[declaration_section;]BEGINprocedure_body;END[procedure_name];510.1.1创建与调用存储过程【例10.1】创建一个简单的存储过程update_emp，该过程用于将emp表中empno为6500的员工的姓名修改为CANDY，如下：SQLCREATEPROCEDUREupdate_empAS2BEGIN3UPDATEempSETename='CANDY'WHEREempno=6500;4ENDupdate_emp;SQL/过程已创建。【例10.2】使用EXECUTE语句与CALL语句调用存储过程update_emp，分别如下：SQLEXECUTEupdate_emp;PL/SQL过程已成功完成。610.1.2带参数的存储过程1．IN参数的使用IN参数是指输入参数，由存储过程的调用者为其赋值(也可以使用默认值)。如果不为参数指定模式，则其模式默认为IN。在调用上述存储过程update_emp2时，就需要为该过程的两个输入参数赋值，赋值的形式主要有如下两种。(1)不指定参数名(2)指定参数名2．OUT参数的使用OUT参数是指输出参数，由存储过程中的语句为其赋值，并返回给用户。使用这种模式的参数，必须在参数后面添加OUT关键字。3．INOUT参数的使用INOUT参数同时拥有IN与OUT参数的特性，它既接受用户的传值，又允许在过程体中修改其值，并可以将值返回。使用这种模式的参数需要在参数后面添加INOUT关键字。不过，INOUT参数不接受常量值，只能使用变量为其传值。710.1.2带参数的存储过程【例10.3】创建带IN参数的存储过程update_emp2，为该过程设置两个IN参数，分别用于接受用户提供的empno与ename值，如下：SQLCREATEPROCEDUREupdate_emp22(emp_numINNUMBER,emp_nameINVARCHAR2)AS3BEGIN4UPDATEempSETename=emp_name5WHEREempno=emp_num;6ENDupdate_emp2;7/过程已创建。810.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过程已成功完成。910.1.2带参数的存储过程【例10.6】创建存储过程select_emp，为该过程设置一个IN参数和一个OUT参数，其中IN参数接受用户提供的empno值，然后在过程体中将该empno对应的ename值传递给OUT参数，如下：SQLCREATEPROCEDUREselect_emp2(emp_numINNUMBER,emp_nameOUTVARCHAR2)AS3BEGIN4SELECTenameINTOemp_name5FROMempWHEREempno=emp_num;6ENDselect_emp;7/过程已创建。1010.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;1110.1.2带参数的存储过程【例10.8】创建存储过程exchange_value，通过该过程交换两个变量中的值，过程创建如下：参见教材P227调用exchange_value过程，调用前声明为INOUT参数赋值的变量，调用后使用SELECT语句输出交换值后的结果，如下：参见教材P2271210.1.3修改与删除存储过程修改存储过程是在CREATEPROCEDURE语句中添加ORREPLACE关键字，其他内容与创建存储过程一样，其实质是删除原有过程，然后创建一个全新的过程，只不过前后两个过程的名称相同而已。删除存储过程需要使用DROPPROCEDURE语句，其语法形式如下：DROPPROCEDUREprocedure_name;1310.1.4查询存储过程的定义信息对于创建好的存储过程，如果想要了解其定义信息，可以查询数据字典user_source。【例10.9】通过数据字典user_source查询存储过程select_emp的定义信息，如下：参见教材P228其中，name表示对象名称；type表示对象类型；line表示定义信息中文本所在的行数；text表示对应行的文本信息。1410.2函数创建函数需要使用CREATEFUNCTION语句，其语法如下：CREATE[ORREPLACE]FUNCTIONfunction_name[(parameter[IN|OUT|INOUT]data_type)[,...]]RETURNdata_type{IS|AS}[declaration_section;]BEGINfunction_body;END[function_name];1510.3实验指导——使用存储过程与函数查询图书信息实验指导10-1：使用存储过程和函数查询图书信息1．创建函数get_prompt首先创建函数get_prompt，如下：参见教材P2302．创建存储过程get_book_information3．调用过程存储过程和函数都已经创建好了，需要查询某图书的信息时就可以直接调用get_book_information存储过程。例如获取bookid为2的图书的信息，如下：参见教材P2311610.4触发器触发器是一种特殊的存储过程，它在发生某种数据库事件时由Oracle系统自动触发。触发器通常用于加强数据的完整性约束和业务规则等，对于表来说，触发器可以实现比CHECK约束更为复杂的约束。1710.4.1触发器的类型DML触发器：DML触发器由DML语句触发，例如INSERT、UPDATE和DELETE语句。INSTEADOF触发器：INSTEADOF触发器又称替代触发器，用于执行一个替代操作来代替触发事件的操作。系统事件触发器：系统事件触发器在发生如数据库启动或关闭等系统事件时触发。DDL触发器：DDL触发器由DDL语句触发，例如CREATE、ALTER和DROP语句。DDL触发器同样可以分为BEFORE触发器与AFTER触发器。1810.4.2创建触发器创建触发器需要使用CREATETRIGGER语句，其语法如下：CREATE[ORREPLACE]TRIGGERtrigger_name[BEFORE|AFTER|INSTEADOF]trigger_event{ONtable_name|view_name|DATABASE}[FOREACHROW][ENABLE|DISABLE][WHENtrigger_condition][DECLAREdeclaration_statements;]BEGINtrigger_body;END[trigger_name];1910.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则可以获取该列的新数据。2010.4.3DML触发器【例10.11】为了演示触发器的效果，下面首先创建两个简单的示例表：student(学生表)和record(记录表)，并向student表中添加几条记录，如下：参见教材P233创建AFTERUPDATE触发器，要求在修改student表中的某行数据后，在record表中记录修改操作，并保存修改前的行数据。创建触发器的语句如下：参见教材P2342110.4.4INSTEADOF触发器INSTEADOF触发器用于执行一个替代操作来代替触发事件的操作，而触发事件本身最终不会被执行。如果是DML触发器，则无论是BEFORE触发器还是AFTER触发器，触发事件最终都会被执行。不过，Oracle中的INSTEADOF触发器不能针对表，而只能针对视图。2210.4.4INSTEADOF触发器【例10.12】首先基于student表创建视图student_view，该视图检索student表中的所有数据，但将student表中的sage列加1。视图创建如下：SQLCREATEVIEWstudent_view2AS3SELECTsid,sname,sage+1new_age4FROMstudent5WITHCHECKOPTION;视图已创建。2310.4.5系统事件触发器系统事件触发器是指由数据库系统事件触发的触发器，其所支持的系统事件如表10-1所示。系统事件说明LOGOFF用户从数据库注销LOGON用户登录数据库SERVERERROR服务器发生错误SHUTDOWN关闭数据库实例STARTUP打开数据库实例2410.4.5系统事件触发器【例10.13】在system用户下创建一个系统事件触发器，该触发器由LOGON事件触发，记录登录用户的用户名(USER)与登录时间，如下：SQLCONNECTsystem/admin已连接。SQLCREATETRIGGERlogon_trigger2AFTERLOGON3ONDATABASE4BEGIN