2 第10章存储过程 函数 触发器和包 本章要点 掌握存储过程的创建 熟练掌握带参数的存储过程的使用 掌握存储过程的管理 掌握函数的创建与使用 了解触发器的类型 理解触发器的作用 熟练掌握各种类型的触发器 了解程序包的创建与使用 3 10 1存储过程 存储过程是一组为了完成特定功能的SQL语句集 它大大提高了SQL语句的功能和灵活性 存储过程经编译后存储在数据库中 所以执行存储过程要比执行存储过程中封装的SQL语句更有效率 4 10 1 1创建与调用存储过程 创建存储过程需要使用CREATEPROCEDURE语句 其语法如下 CREATE ORREPLACE PROCEDUREprocedure name parameter IN OUT INOUT data type IS AS declaration section BEGINprocedure body END procedure name 5 10 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过程已成功完成 6 10 1 2带参数的存储过程 1 IN参数的使用IN参数是指输入参数 由存储过程的调用者为其赋值 也可以使用默认值 如果不为参数指定模式 则其模式默认为IN 在调用上述存储过程update emp2时 就需要为该过程的两个输入参数赋值 赋值的形式主要有如下两种 1 不指定参数名 2 指定参数名2 OUT参数的使用OUT参数是指输出参数 由存储过程中的语句为其赋值 并返回给用户 使用这种模式的参数 必须在参数后面添加OUT关键字 3 INOUT参数的使用INOUT参数同时拥有IN与OUT参数的特性 它既接受用户的传值 又允许在过程体中修改其值 并可以将值返回 使用这种模式的参数需要在参数后面添加INOUT关键字 不过 INOUT参数不接受常量值 只能使用变量为其传值 7 10 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 过程已创建 8 10 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过程已成功完成 9 10 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 过程已创建 10 10 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 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语句 其语法如下 CREATE ORREPLACE FUNCTIONfunction name parameter IN OUT INOUT data type RETURNdata type IS AS declaration section BEGINfunction body END function 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语句 其语法如下 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 19 10 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则可以获取该列的新数据 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 视图创建如下 SQL CREATEVIEWstudent view2AS3SELECTsid sname sage 1new age4FROMstudent5WITHCHECKOPTION 视图已创建 23 10 4 5系统事件触发器 系统事件触发器是指由数据库系统事件触发的触发器 其所支持的系统事件如表10 1所示 24 10 4 5系统事件触发器 例10 13 在system用户下创建一个系统事件触发器 该触发器由LOGON事件触发 记录登录用户的用户名 USER 与登录时间 如下 SQL CONNECTsystem admin已连接 SQL CREATETRIGGERlogon 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