第7章-子程序和触发器

第7章存储过程、函数和触发器第二单元本章相关学习资源《存储过程、函数和触发器》专题《Web前端基础及数据库开发》学习平台“Oracle数据库”课程学生用书预习检查简述存储过程和函数在语法上的区别简述存储过程传递参数的三种模式简述程序包的组成什么是触发条件？提问本章任务任务1：运用子程序完成与雇员有关的增删改查业务需求任务2：运用程序包完成与雇员有关的增删改查业务需求任务3：运用触发器完成与雇员有关的增删改查业务需求能够熟练运用存储过程完成业务需求会运用函数完成业务需求能够熟练运用程序包进行编程了解触发器的执行原理本章目标点重点难点重串讲：子程序根据你的理解，说一说什么是子程序？子程序有几个组成部分？哪些部分是可选的？子程序有哪些优点？提问串讲：创建存储过程CREATE[OR

REPLACE]PROCEDURE

<procedurename>[(<parameterlist>)]IS|AS

<localvariabledeclaration>BEGIN<executablestatements>[EXCEPTION<exceptionhandlers>]END;创建存储过程，可指定运行过程需传递的参数语法处理异常包括在存储过程中要执行的语句演示示例：创建存储过程串讲：调用存储过程用命令在SQL提示符下调用参数传递的三种方式？按位置传递参数按名称传递参数混合方式传递参数默认值的调用方法？如何在PL/SQL块中调用？语法EXEC[UTE]procedure_name(parameters_list);演示示例：调用存储过程问题串讲：存储过程的参数模式IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值INOUT用于接受调用程序的值，并向调用程序返回更新的值提问存储过程传递参数有哪三种模式？串讲：存储过程的访问权限和删除存储过程如何将过程的执行权限授予其他用户？删除存储过程的语法是什么？SQL>GRANTEXECUTEONfind_empTOMARTIN;SQL>GRANTEXECUTEONswapTOPUBLIC;DROP

PROCEDUREprocedure_name;语法问题上机练习需求说明编写存储过程。根据雇员编号，查询该雇员的姓名和薪水，并通过输出参数输出分析考虑如果雇员编号不存在，查询到空记录，如何进行异常处理完成时间：10分钟共性问题集中讲解练习讲解：存储过程和游标的用法如果需要存储过程返回所有员工薪水该怎么办？（1）将存储过程的参数定义为游标类型，且该游标类型为输出类型参数（2）把查询到的结果集赋值给参数游标（3）调用这个存储过程时就可以得到这个结果集问题实现步骤演示示例：存储过程和参数为输出类型游标上机练习需求说明创建显示员工薪水的存储过程要求将薪水集合作为参数传入存储过程完成时间：10分钟共性问题集中讲解练习串讲：创建和调用函数CREATE[OR

REPLACE]FUNCTION

<functionname>[(param1,param2)]RETURN<datatype>IS|AS

[localdeclarations]BEGINExecutableStatements;

RETURNresult;EXCEPTIONExceptionhandlers;END;语法函数是可以返回值的命名的PL/SQL子程序演示示例：

函数创建和调用串讲：函数和存储过程的区别存储过程函数用于在数据库中完成特定操作或者任务（如插入，删除等）用于特定数据（如查询返回值）程序头部声明用PROCEDURE程序头部声明用FUNCTION程序头部声明时不需要返回类型程序头部声明要描述返回类型，而且PL/SQL块至少要包括一个有效的RETURN语句可以使用IN/OUT/INOUT三种参数模式可以使用IN/OUT/INOUT三种参数模式可以作为独立的PL/SQL语句执行不能独立执行，必须作为表达式的一部分调用可以通过OUT/INOUT返回零个或多个值通过RETURN语句返回一个值，且该值要与声明部分一致，也可以通过OUT类型的参数带出变量SQL语句（DML或SELECT）中不可以调用存储过程SQL语句（DML或SELECT）中可以调用函数串讲：使用存储过程与函数的原则简述存储过程与函数使用场合区别简述存储过程与函数用途区别函数的特色是什么？提问第16页/共34页小结什么是子程序？两种类型的PL/SQL子程序，即（）和（）函数存储过程

提问第17页/共34页讲解：程序包为什么使用程序包？使程序设计模块化提高程序的执行效率什么是程序包？程序包是对相关存储过程、函数、变量、游标和异常等对象的封装程序包由规范和主体两部分组成声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等声明程序包私有对象和实现在包规范中声明的子程序和游标程序包规范主体问题第18页/共34页讲解：创建程序包CREATE[OR

REPLACE]

PACKAGEpackage_nameIS|AS[Publicitemdeclarations][Subprogramspecification]END[package_name];CREATE[OR

REPLACE]PACKAGE

BODYpackage_nameIS|AS[Privateitemdeclarations][Subprogrambodies][BEGINInitialization]END[package_name];程序包规范程序包主体语法语法讲解：程序包的开发步骤程序包的开发需要以下几个步骤：由于程序包较大，所以一定将每个子程序调试通过后再写程序包注意演示示例：

开发程序包讲解：程序包中的游标静态游标如何在包中使用？游标的定义分为游标规范和游标主体两部分在包规范中声明游标规范时必须使用RETURN子句指定游标的返回类型RETURN子句指定的数据类型可以是：用%ROWTYPE属性引用表定义的记录类型程序员定义的记录类型动态游标如何在包中使用？不可在包中声明游标变量问题演示示例：

程序包中的静态游标注意演示示例：

程序包中的动态游标小结判断对错程序包是对相关类型、变量、常量、游标、异常、存储过程和函数的封装程序包由包规范和包主体两部分组成包规范是包的接口，包含公用对象及其类型提问讲解：为什么要使用触发器如何实现跟踪并记录所有对雇员表的表结构进行改变的操作？只要一变动表结构就要记录所有操作到一个审计表中不需要显式调用来执行，而是修改表结构的操作来启动运行使用触发器可以实现问题分析结论讲解：触发器特性使用触发器能够满足哪些需求？不需要显式调用来执行，而是由一个事件来启动运行可以通过其他Oracle事件触发调用的程序，因为触发器不能接收参数Oracle

事件包括哪几种情况？INSERT、UPDATE及DELETE操作或对视图进行类似的操作执行DDL操作数据库的启动与关闭问题讲解：触发器语法CREATE[OR

REPLACE]TRIGGERtrigger_nameAFTER|BEFORE|INSTEADOF[INSERT][[OR]UPDATE[OFcolumn_list]][[OR]DELETE]ONtable_or_view_name[REFERENCING{OLD[AS]old/NEW[AS]new}][FOR

EACH

ROW][WHEN(condition)]pl/sql_block;语法演示示例：

创建触发器讲解：触发器的组成部分触发器由三部分组成：触发器语句（事件）定义激活触发器的DML事件和DDL事件触发器限制执行触发器的条件，该条件必须为真才能激活触发器触发器操作（主体）包含一些SQL语句和代码，它们在发出了触发器语句且触发限制的值为真时运行BEFOREDELETEONempFOREACHROW……WHEN(old.deptno<>10)BEGIN--将修改前数据插入到日志记录表del_emp,以供监督使用。INSERTINTOdel_emp(deptno,empno,……)VALUES(:old.deptno,:old.empno,……);END;讲解：触发器类型触发器的类型有：触发器类型模式(DDL)触发器DML触发器数据库级触发器语句级触发器行级触发器INSTEADOF触发器讲解：触发器类型DDL触发器数据库级触发器DML触发器语句级触发器行级触发器INSTEADOF触发器在模式中执行DDL语句时执行在发生打开、关闭、登录和退出数据库等系统事件时执行在对表或视图执行DML语句时执行无论受影响的行数是多少，都只执行一次对DML语句修改的每个行执行一次用于用户不能直接使用DML语句修改的视图演示示例：

行级、语句级触发器、INSTEADOF触发器如何启用和禁用触发器？如何删除触发器？是否选择使用触发器？能用其他方法替代就不要用触发器讲解：触发器的管理SQL>ALTERTRIGGERaiu_itemfileDISABLE;SQL>ALTERTRIGGERaiu_itemfileENABLE;SQL>DROPTRIGGERaiu_itemfile;问题上机练习需求说明实现跟踪并记录所有对USER1用户下的雇员表的表结构进行改变的操作分析使用DDL触发器完成时间：10分钟共性问题集中讲解练习答疑时间同学们请就以下方面的问题请教老师