第7章-子程序及触发器

1、第7章存储过程、函数和触发器存储过程、函数和触发器第二单元本章相关学习资源存储过程、函数和触发器专题Web前端基础及数据库开发预习检查 简述存储过程和函数在语法上的区别简述存储过程和函数在语法上的区别 简述存储过程传递参数的三种模式简述存储过程传递参数的三种模式 简述程序包的组成简述程序包的组成 什么是触发条件？什么是触发条件？提问提问本章任务 任务任务1 1：运用子程序完成与雇员有关的增删改查业：运用子程序完成与雇员有关的增删改查业务需求务需求 任务任务2 2：运用程序包完成与雇员有关的增删改查业：运用程序包完成与雇员有关的增删改查业务需求务需求 任务任务3 3：运用触发器完成与雇员有关的增

2、删改查业：运用触发器完成与雇员有关的增删改查业务需求务需求本章目标点点重重点点难难点点重重串讲：子程序 根据你的理解，说一说什么是子程序？根据你的理解，说一说什么是子程序？ 子程序有几个组成部分？哪些部分是可选的？子程序有几个组成部分？哪些部分是可选的？ 子程序有哪些优点？子程序有哪些优点？提问提问串讲：创建存储过程CREATE OR REPLACE PROCEDURE ()IS|AS BEGIN EXCEPTION END;创建存储过程，可指定运行过程需传递的创建存储过程，可指定运行过程需传递的参数参数语法语法处理异常处理异常 包括在存储过程中要执行的语句包括在存储过程中要执行的语句 演示示

3、例：演示示例：创建存储过程创建存储过程串讲：调用存储过程 用命令在用命令在SQL SQL 提示符下调用提示符下调用参数传递的三种方式？ 按位置传递参数 按名称传递参数 混合方式传递参数默认值的调用方法？如何在PL/SQL 块中调用？语法语法EXECUTE procedure_name(parameters_list);演示示例：调用演示示例：调用存储过程存储过程问题问题串讲：存储过程的参数模式 ININ用于接受调用程序的值默认的参数模式 OUTOUT用于向调用程序返回值 IN OUTIN OUT用于接受调用程序的值，并向调用程序返回更新的值提问提问 存储过程传递参数有哪三种模式？存储过程传递参

4、数有哪三种模式？串讲：存储过程的访问权限和删除存储过程 如何将过程的执行权限授予其他用户？如何将过程的执行权限授予其他用户？ 删除存储过程的语法是什么？删除存储过程的语法是什么？SQL GRANT EXECUTE ON find_emp TO MARTIN;SQL GRANT EXECUTE ON swap TO PUBLIC;DROP PROCEDURE procedure_name;语法语法问题问题上机练习完成时间：完成时间：10分钟分钟共性问题集中讲解共性问题集中讲解练习练习讲解：存储过程和游标的用法 如果需要存储过程返回所有员工薪水该怎么办？如果需要存储过程返回所有员工薪水该怎么办？

5、（1 1）将存储过程的参数定义为游标类型，且该游）将存储过程的参数定义为游标类型，且该游标类型为输出类型参数标类型为输出类型参数 （2 2）把查询到的结果集赋值给参数游标）把查询到的结果集赋值给参数游标 （3 3）调用这个存储过程时就可以得到这个结果集）调用这个存储过程时就可以得到这个结果集问题问题实现步骤实现步骤演示示例：演示示例：存储过程和参数为输出类型游标存储过程和参数为输出类型游标上机练习完成时间：完成时间：10分钟分钟共性问题集中讲解共性问题集中讲解练习练习串讲：创建和调用函数CREATE OR REPLACE FUNCTION (param1,param2)RETURN IS|AS

6、 local declarationsBEGIN Executable Statements; RETURN result;EXCEPTION Exception handlers;END;语法语法函数是可以返回值的命名的函数是可以返回值的命名的 PL/SQL 子程序子程序演示示例：演示示例： 函数创建和调用函数创建和调用串讲：函数和存储过程的区别串讲：函数和存储过程的区别存储过程存储过程函数函数用于在数据库中完成特定操作或者任务（如插入，删除等）用于特定数据（如查询返回值）程序头部声明用PROCEDURE程序头部声明用FUNCTION程序头部声明时不需要返回类型程序头部声明要描述返回类型，而

7、且PL/SQL块至少要包括一个有效的RETURN语句可以使用IN/OUT/IN OUT三种参数模式可以使用IN/OUT/IN OUT三种参数模式可以作为独立的PL/SQL语句执行不能独立执行，必须作为表达式的一部分调用可以通过OUT/IN OUT返回零个或多个值通过RETURN语句返回一个值，且该值要与声明部分一致，也可以通过OUT类型的参数带出变量SQL语句（DML或SELECT）中不可以调用存储过程SQL语句（DML或SELECT）中可以调用函数串讲：使用存储过程与函数的原则串讲：使用存储过程与函数的原则 简述存储过程与函数使用场合区别简述存储过程与函数使用场合区别 简述存储过程与函数用途

8、区别简述存储过程与函数用途区别 函数的特色是什么？函数的特色是什么？提问提问第16页/共34页小结 什么是子程序？什么是子程序？ 两种类型的两种类型的PL/SQLPL/SQL子程序，即（子程序，即（ ）和）和（ ）函数函数存储过程存储过程 提问提问第17页/共34页讲解：程序包 为什么使用程序包？为什么使用程序包？使程序设计模块化提高程序的执行效率 什么是程序包？什么是程序包？程序包是对相关存储过程、函数、变量、游标和异常等对象的封装程序包由规范和主体两部分组成声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等声明程序包私有对象和实现在包规范中声明的子程序和游标程序包规范

9、主体问题问题第18页/共34页讲解：创建程序包CREATE OR REPLACE PACKAGE package_name IS|ASPublic item declarationsSubprogram specificationEND package_name;CREATE OR REPLACE PACKAGE BODY package_name IS|ASPrivate item declarationsSubprogram bodiesBEGINInitializationEND package_name;程序包规范程序包规范 程序包主体程序包主体 语法语法语法语法讲解：程序包的开发步骤

10、 程序包的开发需要以下几个步骤：程序包的开发需要以下几个步骤：由于程序包较大，所以一定将每个子程序调试通过后再写程序包注意注意演示示例：演示示例： 开发程序包开发程序包讲解：程序包中的游标 静态游标如何在包中使用？静态游标如何在包中使用？游标的定义分为游标规范和游标主体两部分在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型RETURN子句指定的数据类型可以是： 用 %ROWTYPE 属性引用表定义的记录类型 程序员定义的记录类型 动态游标如何在包中使用？动态游标如何在包中使用？不可在包中声明游标变量问题问题演示示例：演示示例： 程序包中的静态游标程序包中的静态游标注意注意

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

12、题分析分析结论结论讲解：触发器特性 使用触发器能够满足哪些需求？使用触发器能够满足哪些需求？不需要显式调用来执行，而是由一个事件来启动运行可以通过其他Oracle 事件触发调用的程序，因为触发器不能接收参数 OracleOracle 事件包括哪几种情况？事件包括哪几种情况？INSERT、UPDATE 及DELETE 操作或对视图进行类似的操作执行DDL操作数据库的启动与关闭问题问题讲解：触发器语法CREATE OR REPLACE TRIGGER trigger_nameAFTER | BEFORE | INSTEAD OFINSERT OR UPDATE OF column_list OR

13、DELETEON table_or_view_nameREFERENCING OLD AS old / NEW AS newFOR EACH ROWWHEN (condition)pl/sql_block;语法语法演示示例：演示示例： 创建触发器创建触发器讲解：触发器的组成部分触发器由三部分组成：触发器由三部分组成： 触发器语句（事件）触发器语句（事件）定义激活触发器的 DML 事件和 DDL 事件 触发器限制触发器限制执行触发器的条件，该条件必须为真才能激活触发器 触发器操作（主体）触发器操作（主体）包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行BEFORE D

14、ELETEON empFOR EACH ROWWHEN (old.deptno 10)BEGIN -将修改前数据插入到日志记录表将修改前数据插入到日志记录表del_emp,以供监督使用。以供监督使用。 INSERT INTO del_emp(deptno, empno, ) VALUES( :old.deptno, :old.empno, );END;讲解：触发器类型 触发器的类型有：触发器的类型有：触发器类型触发器类型模式模式(DDL)触发器触发器DML触发器触发器数据库级数据库级触发器触发器语句级触发器行级触发器INSTEAD OF触发器讲解：触发器类型DDL 触发器数据库级触发器DML

15、触发器语句级触发器行级触发器INSTEAD OF 触发器在模式中执行在模式中执行 DDL 语句时执行语句时执行在发生打开、关闭、登录和退出数据库等在发生打开、关闭、登录和退出数据库等系统事件时执行系统事件时执行在对表或视图执行在对表或视图执行DML语句时执行语句时执行无论受影响的行数是多少，都只执行一次无论受影响的行数是多少，都只执行一次对对DML语句修改的每个行执行一次语句修改的每个行执行一次用于用户不能直接使用用于用户不能直接使用 DML 语句修改的语句修改的视图视图演示示例：演示示例： 行级、语句级触发器、行级、语句级触发器、INSTEAD OF触发器触发器 如何启用和禁用触发器？如何启用和禁用触发器？ 如何删除触发器？如何删除触发器？ 是否选择使用触发器？是否选择使用触发器？能用其他方法替代就不要用触发器讲解：触发器的管理SQL ALTER TRIGGER aiu_itemfile DISABLE;SQL ALTER TRIGGER aiu_itemfile ENABLE;SQL DROP TRIGGER aiu_itemfile;问题问题上机练习完成时间：完成时间：10分钟分钟共性问题集中讲解共性问题集中讲解练习练习答疑时间 同学们请就以下方面的问题请教老师同学们请就以下方面的问题请教老师存储过程的跟踪和调试存