第八章上机课触发器和内置程序包

END;
/ （4）向表中插入数据测试触发器。
INSERT INTO order_item(prod_id, quantity, price)
VALUES ('P001', 5, 56.00);
INSERT INTO order_item(prod_id, quantity, price)
VALUES ('P002', 3, 128.00);
老二牛车教育 《Oracle 数据库应用 》上机课
INSERT INTO itemfile_log(who, When, action)
VALUES ( USER, SYSDATE, l_action);
END;
/ （4）测试触发器。 执行以下语句对 itemfile 进行更新。
UPDATE itemfile
提示：使用 UTL-FILE 包。
老二牛车教育 《Oracle 数据库应用 》上机课
触发器和内置程序包
目标： 能够使用触发器 能够使用内置程序包
第一部分 指导 本阶段的重点学习如何编写创建触发器的语句，还将学习编写以使用各种内置程序包的方法。
阶段一：创建语句触发器 需求说明： 公司经理认为 itemfile 表中的数据非常重要，希望所有对该表进行的更新都要有记录，但只记录 所做的操作，不记录用户更新的具体数据，请提出解决方案。 实现思路： 语句级触发器对每个 DML 语句执行一次，通常用于强制实施在表上执行操作的额外安全性措施。 语句级触发器 CREATE TRIGGER 命令创建的触发器的默认类型。可以首先创建一个记录信息 的表，然后在 itemfile 表上创建一个执行 UPDATE、INSERT 和 DELETE 语句都会激活的语句级 触发器。
SELECT * FROM order_item WHERE prod_id = 'P003'; 上述语句的输出结果如下所示：
PROD_ID QUANTITY PRICE
TOTAL
------- -------- -------- ----------
P003
2 28.00
56.00
阶段三：创建 INSTEAD OF 触发器 需求说明： 已知公司的职员表中有职员编号、职员姓名、职务和薪水信息。现创建一个视图将职员姓名和 职务合并起来显示，中间以逗号分开。请问能否直接修改此视图以达到修改职员姓名和职务的 功能？ 实现思路： 使用 INSTEAD OF 触发器可以实行此功能。使用了表达式的视图不能用 DML 语句进行更新操 作。INSTEAD OF 触发器是在视图上定义的触发器，它是用来替换所使用实际语句的触发器。 可以使用触发器代码将职员姓名和职务分解之后修改雇员表，以达到更新的目的。
作业
老二牛车教育 《Oracle 数据库应用 》上机课
1 创建一个 trg-anti-alter 触发器，在用户修改数据库对象是激活。在激活触发器时，应显示错误信息“无 法更改对象”。
提示：创建模式触发器，使用 RAISE-APPLICATION-ERROR 显示错误消息。 2 编写一个以可写方式打开 sample.txt 文件的程序，将用户输入的字符串存储在此文件中。
SET itemrate = itemrate + 20
WHERE itemcode ='i205'; 上述语句显示信息“已更新 1 行”。 执行以下语句查看 itemfile-log 表的信息。
SELECT * FROM itemfile_log; 输出结果如下：
WHO
WHEN
ACTION
------------------------------ ----------- --------------------
select * from emp where empno = 7934; 输出结果如下所示。
EMPNO NAME
SAL
----- -------------------- ---------
7934 MILLER,CLERK
பைடு நூலகம்
1300.00
阶段四：使用 DSMS RANDOM 程序包
老二牛车教育 《Oracle 数据库应用 》上机课
老二牛车教育 《Oracle 数据库应用 》上机课
CREATE OR REPLACE TRIGGER biu_total_orderitem
BEFORE INSERT OR UPDATE OF quantity, price
ON order_item
FOR EACH ROW
BEGIN
:NEW.total := :NEW.quantity * :NEW.price;
TOTAL
------- -------- -------- ----------
P001
5 56.00 280.00
P002
3 128.00 384.00
P003
2 36.00
72.00
（5）更新表中数据测试触发器。
UPDATE order_item SET price = 28
WHERE prod_id = 'P003'; 执行以下语句查询插入的数据。
参考步骤：
老二牛车教育 《Oracle 数据库应用 》上机课
（1） 启动“ SQL Plus”。 （2） 输入以下语句创建职员视图。
CREATE OR REPLACE VIEW emp_view AS
SELECT empno, ename||','||job name, sal
FROM emp;
（3）输入以下命令查询视图。
job = substr(:, instr(:,',')+1),
sal = :New.sal
WHERE empno = :Old.empno;
END;
/ 触发器代码将 name 列的值分解成姓名和职务，然后更新 emp 表。 （6）再次输入以下命令试图更新视图
update emp_view set name='Fred,Manager' where empno=7934; 上述命令执行成功，使用以下语句查询修改的效果。
SELECT * FROM emp_view WHERE empno=7934; 输出结果如下：
EMPNO NAME
SAL
----- -------------------- ---------
7934 MILLER,CLERK
1300.00
（4）输入以下命令试图更新视图 UPDATE emp_view SET name='Fred,Manager' WHERE empno=7934; 命令执行失败，输出结果如下所示。
NEWPASSWD ---------------------------------------------------------PNDLQGF2
第二部分 练习
1 order-detail 表包含 orderno、odate、vencodeitemcode、qty- order ed、ostatus 和 del-date 等列。创建 一个 trg-del-ord 触发器，在 order-detail 表中的记录被删除时激活。此触发器将所删除记录的 orderno 列值以及删除时的系统时间插入名为 deleted- order 的新表。 2 完善第一部分 练习 3，编写一个代替 INSERT 命令的触发器，可以在 emp-view 视图上进行插入操 作。
（5）输入以下代码创建 INSTEAD OF 触发器。
CREATE OR REPLACE TRIGGER update_emp_view
INSTEAD OF UPDATE
ON emp_view
BEGIN
UPDATE emp
SET empno = :New.empno,
ename = substr(:, 1, instr(:, ',')-1),
参考步骤： （1） 启动“ SQL Plus”。 （2） 输入以下语句创建表。 CREATE TABLE order_item( prod_id VARCHAR2(6), quantity NUMBER(3), price NUMBER(6,2), total NUMBER(8,2) ); （3）输入以下代码创建触发器。
INSERT INTO order_item(prod_id, quantity, price) VALUES ('P003', 2, 36.00);
执行以下语句查询插入的数据。
SELECT * FROM order_item; 上述语句的输出结果如下所示。
PROD_ID QUANTITY PRICE
需求说明： 某公司的网站接受用户注册为会员，系统需要每个新注册的用户生成一个随机密码。要求编写 一个生成随机密码的函数，该函数可以生成 6-12 位的随机字符串作为用户密码。
实现思路： DSMS RANDOM 包可生成随机数和随机字符串，因此可使用它创建此函数。函数接受密码长度， 如果指定长度小于 6 位，则生成 6 位密码，如果指定长度大于 12 位，则生成 12 位密码。
参考步骤： （1） 启动“ SQL Plus”。 （2） 输入以下命令，创建记录操作的表。 CREATE TABLE itemfile_log ( who VARCHAR2(30), When DATE, action VARCHAR2(20) ); （3）输入以下命令，创建语句级触发器。 CREATE OR REPLACE TRIGGER modify_itemfile BEFORE INSERT OR UPDATE OR DELETE ON itemfile DECLARE l_action itemfile_log.action%TYPE; BEGIN IF INSERTING THEN l_action := '插入'; ELSIF UPDATING THEN l_action := '更新'; ELSIF DELETING THEN l_action := '删除'; END IF;
参考步骤： （1） 启动“ SQL Plus”。 （2） 输入以下代码创建函数。 CREATE OR REPLACE FUNCTION gen_passwd(pwdlen NUMBER) RETURN VARCHAR2 AS p_len INTEGER; result VARCHAR2(12); BEGIN p_len := pwdlen; IF p_len<6 THEN p_len := 6; ELSIF p_len>12 THEN p_len := 12; END IF; result := DBMS_RANDOM.STRING('X', p_len); RETURN result; END; / 要调用此函数，可使用以下查询语句。 SELECT gen_passwd(8) newpasswd FROM DUAL; 上述查询的输出结果如下所示。
CHENGSHI CHENGSHI CHENGSHI
2010-10-3 2 更新 2010-10-3 2 更新 2010-10-3 2 更新
阶段二：创建行级触发器 需求说明： 产品销售订单条目上包括产品的编号、数量和单价，由于统计的需要，订单上还需要显示每种 产品的总金额，它的数量和单价的乘积。为了确保此数据的正确性，需要对此字段进行自动计 算。 实现思路： 行级触发器在执行更新的 DML 语句时自动执行，行级触发器对 DML 语句影响的每个行执行一 次。可以使用行级触发器实现自动计算总金额的功能，也可以在 CREATE TRIGGER 命令中指 定 FOR EACH ROW 子句创建行级触发器。在触发器中，可以引用 DML 语句中涉及的旧值和新 值。“旧”是指在 DML 语句之前存在的数据；“新”是指在 DML 语句创建的数据值（如插入记 录的列）。此处因为要更新正要被修改的行，所以应该创建 BEFORE INSERT OR UPDATE 方式 的行级触发器。