数据库资料OraPlsqlTps4.ppt

应用于Web的面向对象关系型数据库管理系统：Oracle,使用数据包和数据库触发器,4,2,本章目标,数据包 数据包的优点 数据包说明 数据包主体 使用游标变量 数据库触发器简介,3,本章目标（续）,创建数据库触发器 INSTEAD OF 触发器 触发器的限制 启用/禁用触发器 放置触发器 编译和调试触发器,4,数据包,将逻辑相关的 PL/SQL 类型、对象和子程序进行分组的数据库对象 不能对它们进行调用，也不能对其传送参数或嵌套 这里有两个部分 说明 主体,5,数据包的优点,模块化特性 允许您将相关的类型、对象和子程序封装入一个命名的 PL/SQL 模块 具有简单、清晰、定义明确的接口，便于理解 可以协助应用程序开发,6,数据包的优点（续）,更简捷的应用程序设计 在设计应用程序时，只需要最初在数据包中提供接口信息 可以没有主体而对说明进行编码和编译 也可以编译引用数据包的子程序 在就绪可以完成应用程序之前不必定义数据包主体,7,数据包的优点（续）,信息隐藏 可以指定哪些类型、项目和子程序是公有的还是私有的 私有子程序的定义是隐含的，这样如果定义更改只会影响数据包（而不会影响应用程序） 简化了维护和增强，并保护了数据包的完整性,8,数据包的优点（续）,增加了功能性 数据包的公共变量和游标在会话期间持续存在 它们可以被环境中所执行的所有子程序共享 它们也允许您在事务间维护数据，不用将数据存储在数据库中,9,数据包的优点（续）,更佳的性能 首次调用数据包的子程序时，整个数据包将装载进内存 以后对数据包中相关子程序的调用就不用进行磁盘输入/输出操作了 如果程序包的函数发生更改，Oracle 不用重新编译调用子程序，因为它们不依赖数据包主体,10,数据包说明,是一个与应用程序的接口 声明可用的类型、变量、常数、例外、游标和子程序 让公用声明对应用程序可见 可以被认为是可操作的接口,11,数据包说明（续）,声明的范围对于数据库构架而言是局部的，对数据包而言是全局的 列出可以用于应用程序的数据包资源,12,数据包说明（续）,是使用 CREATE PACKAGE 命令创建的 CREATE OR REPLACE PACKAGE AS - 公共类型和对象声明 - 子程序说明 END ;,13,数据包说明（续）,示例 CREATE PACKAGE airlines AS TYPE flight_day_type is RECORD (flightno flight_sch.flightno%TYPE, flight_day1 NUMBER(1); CURSOR flight_cur RETURN flight_day_type; disp_day CHAR(15); FUNCTION day_fn(mday NUMBER) RETURN CHAR; PROCEDURE branch_sum (p_brnch branch.branch_code%TYPE); END airlines;,14,数据包主体,实现数据包说明 完全定义游标和子程序 将实现细节和私有声明从应用程序隐含 可以将其设想为“黑箱” 可被替换增强或在不更改接口的情况下被替换 可以在不重新编译调用程序的情况下对其进行更改,15,数据包主体（续）,声明范围对于数据包主体是局部的 除了在数据包主体内将不能访问到声明的类型和对象 只在首次引用数据包时，运行一次数据包的初始化部分,16,数据包主体（续）,使用 CREATE PACKAGE BODY 命令生成 CREATE OR REPLACE PACKAGE BODY AS - 私有类型和对象声明 - 子程序主体 BEGIN - 初始化语句 END ;,17,数据包主体（续）,CREATE PACKAGE BODY airlines AS CURSOR flight_cur RETURN flight_day_type IS SELECT flightno, reoute_code, flight_day1, flight_day2 FROM flight_sch; FUNCTION day_fn(mday NUMBER) RETURN CHAR IS BEGIN 语句 ; END day_fn; . . END airlines;,18,引用数据包对象,必须使用点符号引用数据包对象和子程序 数据包名.类型名 数据包名.对象名 数据包名.子程序名 示例 airlines.day_fn(4);,19,维护数据包,可以使用 DROP 命令删除数据包 DROP PACKAGE 示例 DROP PACKAGE airlines; 要删除一个结构，应将其从数据包中删除，然后重新编译数据包,20,使用游标变量,与游标类似，游标变量指向多行查询结果集的当前行 与游标不同，可以为任意类型兼容的查询打开游标变量 可以为游标变量赋新值，可以将它传送给子程序 它为集中数据检索提供了灵活和方便的方式,21,使用游标变量（续）,通常通过将它传送到存储的过程来打开，该过程声明一个游标变量作为它的正式变量之一 当将其作为打开它的子程序的一个正式参数进行声明时，必须指定 IN OUT 模式，从而子程序可以将打开的游标传送回调用者 使用相同的或不同的 REFCURSOR 绑定变量可以让过程执行多次,22,使用游标变量（续）,CREATE PACKAGE dept_data AS TYPE dcur_typ IS REF CURSOR RETURN dept%ROWTYPE; PROCEDURE dept_cv(d_cv IN OUT dcur_typ); END dept_data; CREATE PACKAGE BODY dept_data AS PROCEDURE dept_cv(d_cv IN OUT dcur_typ) IS BEGIN OPEN d_cv FOR SELECT * FROM dept; END dept_cv; END dept_data;,23,使用游标变量（续）,要在 SQL 提示符处查看游标变量的值 声明一个类型为 REFCURSOR 的变量 SQL VARIABLE v_dept REFCURSOR 执行指定的过程 SQL EXECUTE dept_data.dept_cv(:v_dept); 打印绑定变量以查看结果 SQL PRINT v_dept,24,使用游标变量（续）,要集中检索数据，可以在存储过程中组合类型兼容的查询 CREATE PACKAGE emp_data AS TYPE ecur_typ IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE emp_cv(e_cv IN OUT ecur_typ, choice IN NUMBER); END emp_data;,25,使用游标变量（续）,CREATE PACKAGE BODY emp_data AS PROCEDURE emp_cv(e_cv IN OUT ecur_typ, choice IN NUMBER) IS BEGIN IF choice = 1 THEN OPEN e_cv FOR SELECT * FROM emp WHERE sal 2500; ELSIF choice = 2 THEN OPEN e_cv FOR SELECT * FROM emp WHERE deptno = 20; END IF; END emp_cv; END emp_data;,26,使用游标变量（续）,以上程序可这样被执行 SQL VARIABLE v_emp REFCURSOR SQL SET AUTOPRINT ON SQL EXECUTE emp_data.emp_cv(:v_emp,1); （将显示工资超过 2,500 的 emp 表中的所有行）,27,使用游标变量（续）,为了更加灵活，您可以将游标变量和选择器传送到存储过程，它可以使用不同的返回类型执行查询 CREATE PACKAGE gen_cur AS TYPE generic_cur_typ IS REF CURSOR; PROCEDURE open_cv(gen_cv IN OUT generic_cur_typ, choice IN NUMBER); END gen_cur;,28,使用游标变量（续）,CREATE PACKAGE BODY gen_cur AS PROCEDURE open_cv(gen_cv IN OUT generic_cur_typ, choice IN NUMBER) IS BEGIN IF choice = 1 THEN OPEN gen_cv FOR SELECT * FROM emp; ELSIF choice = 2 THEN OPEN gen_cv FOR SELECT * FROM dept; ELSIF choice = 3 THEN OPEN gen_cv FOR SELECT * FROM salgrade; END IF; END open_cv; END gen_cur;,29,使用游标变量（续）,以上程序可这样被执行 SQL VARIABLE cur REFCURSOR SQL SET AUTOPRINT ON SQL EXECUTE gen_cur.open_cv(:cur,2); （将显示 dept 表中的所有行）,30,数据库触发器简介,它是一个与某个特定数据库表关联的存储 PL/SQL 程序单元 只要当 SQL 操作影响到表时， Oracle 就自动执行（触发）触发器 将隐式地调用它们 触发器能帮助自定义数据库 仅在需要时才使用它们,31,使用数据库触发器,自动生成提交的列值 可以防止无效事务 加强复杂的安全授权 在分布式数据库节点间加强引用完整性 加强复杂的商务规则,32,使用数据库触发器（续）,提供透明的事件日志 可以提供复杂的审计 可以维护同步表复制 可以搜集表访问的统计 可以自动获取列值 可以将 DML 操作限制在规律的事务时间,33,使用数据库触发器的场合,触发器主要用于促进引用的完整性 只能在无法使用下列表级别限制时使用它们 NOT NULL UNIQUE KEY PRIMARY KEY FOREIGN KEY CHECK,34,创建数据库触发器,CREATE OR REPLACE TRIGGER BEFORE|AFTER INSERT|DELETE|UPDATE OF ON FOR EACH ROW WHEN () ON 子句中的名称识别与数据库触发器关联的数据库表,35,创建数据库触发器（续）,触发器事件指定了影响表的 SQL DML 语句 ( INSERT、 DELETE 或 UPDATE) AFTER 指定了触发器在处理完成后触发 BEFORE 指定了触发器在处理完成前触发,36,创建数据库触发器（续）,默认情况下，触发器每个表触发一次 FOR EACH ROW 选项指定触发器每行触发一次 要使触发器触发，WHEN 子句中布尔型表达式的值必须判定为 TRUE 可以将 REPLACE 添加到 CREATE 语句以自动删除和重建触发器,37,创建数据库触发器（续）,CREATE TRIGGER flight_update AFTER INSERT ON reservation FOR EACH ROW BEGIN IF :new.class = F THEN 语句 ; ELSIF :new.class = B THEN 语句 ; END IF; END;,38,创建数据库触发器（续）,前缀 : new 为一个引用最近更新列值的相关名 在数据库触发器内，可以引用用来更改行的 :new 和 :old 值 单独触发器也处理多个操作 使用条件谓词识别用于调用代码部分的语句的类型,39,创建数据库触发器（续）,IF Inserting THEN 语句 ; END IF; IF Updating THEN 语句 ; END IF; IF Deleting THEN 语句 ; END IF;,40,INSTEAD OF 触发器,用途：对那些由于基本表包括连接而不能直接通过 INSERT、 UPDATE 和 DELETE 语句修改的视图提供了明晰的修改方法 由于是 Oracle 触发了触发器而不是触发器语句来触发，所以称它为 INSTEAD OF,41,INSTEAD OF 触发器（续）,触发器对用户是透明的，因为用户根据视图编写正常的 DML 语句， INSTEAD OF 触发器负责更改 可以将它置于“对象视图”中以插入、更新或删除基本关系表中的数据,42,INSTEAD OF 触发器（续）,CREATE TRIGGER emp_insert INSTEAD OF INSERT ON emp_view BEGIN 语句 ; END;,43,触发器的限制,SELECT 语句必须是 SELECT IN