游标和触发器

–create trigger trigger_emp_empno
触发语句
• 触发语句就是那些导致Oracle执行触发器 的事件。触发器事件可以是下面的内容：
–表和一些视图上的数据操纵语言语句，例如 insert、update、delete； –模式对象上的数据定义语言语句，例如create、 alter、drop； –数据库的启动和关闭； –系统错误； –各种系统活动。
Roger
45
44
数据库
3
George
30
提取行
关闭游标
变量
显式游标 2-2
SQL>SET SERVER OUTPUT ON SQL>DECLARE my_toy_price toys.toyprice%TYPE; CURSOR toy_cur IS 声明游标 SELECT toyprice FROM toys WHERE toyprice<250; BEGIN OPEN toy_cur; 打开游标 LOOP 提取行 FETCH toy_cur INTO my_toy_price; EXIT WHEN toy_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('TOYPRICE=:玩具单价=：'||my_toy_price); END LOOP; CLOSE toy_cur; 关闭游标 END;
循环游标 2-1
• 循环游标用于简化游标处理代码 • 当用户需要从游标中提取所有记录时 使用 FOR <record_index> IN <cursor_name> • 循环游标的语法如下： LOOP
<executable statements> END LOOP;
循环游标 2-2
SQL> SET SERVER OUTPUT ON SQL> DECLARE CURSOR mytoy_cur IS SELECT toyid, toyname, toyprice FROM toys; BEGIN FOR toy_rec IN mytoy_cur LOOP DBMS_OUTPUT.PUT_LINE( ‘玩具编号：'||' ' ||toy_rec.toyid||' ' ||‘玩具名称：'||' '||toy_rec.toyname||' ' ||‘玩具单价：'||' '||toy_rec.toyprice); END LOOP; END;
14.1 概述
• 触发器是当特定事件出现时自动执行的存储过程 • 特定事件可以是执行更新的DML语句和DDL语句 • 触发器不能被显式调用 • 使用触发器可以完成的功能如下：
–允许或限制对表的修改； –自动生成派生列； –强制数据一致性； –提供审计和日志记录； –防止无效的事务处理； –启用复杂的业务逻辑。
隐式游标 4-2
SQL> SET SERVEROUTPUT ON SQL> BEGIN UPDATE toys SET toyprice=270 WHERE toyid= 'P005'; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(‘表已更新'); END IF; END; / 只有在 DML 语句影响一行 或多行时，才返回 True
begin :m:=0; end;
触发器名称
• 触发器名称就是触发器的名称。数据库中 的每一个对象都有名称，触发器对象也不 例外。一般应该采用描述性名称来定义触 发器名称。在触发器名称中，应该包括触 发器执行的时间、执行的操作、涉及的表 和列等。 • 下面是一个定义触发器部分语句的示例：
• TYPE Test_CURSOR IS REF CURSOR;
– end TESTPACKAGE;
• 2，建立存储过程，存储过程为：
– CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
练习
• 编写游标打印出emp表中的职员编号、姓名、 上级编号、工资 • 要求
例子
• --触发器 create or replace trigger tri_emp_empno before insert or update --insert or
update 意思是指insert 或update语句执 行时就触发 on emp --触发器依赖的表 for each row --行触发器
WHERE CURRENT OF <cursor_name>
删除的语法
UPDATE <table_name> SET <set_clause> WHERE CURRENT OF <cursor_name>
使用显式游标更新行 2-2
SQL> SET SERVEROUTPUT ON SQL> DECLARE new_price NUMBER; CURSOR cur_toy IS SELECT toyprice FROM toys WHERE toyprice<100 FOR UPDATE OF toyprice; BEGIN OPEN cur_toy; LOOP FETCH cur_toy INTO new_price; EXIT WHEN cur_toy%NOTFOUND; UPDATE toys SET toyprice = 1.1*new_price WHERE CURRENT OF cur_toy; END LOOP; CLOSE cur_toy; COMMIT; END;
隐式游标 4-1
• 显式游标主要是用于对查询语句的处理，尤其是在查 询结果为多条记录的情况下 • 而对于非查询语句，如修改、删除操作，则由 ORACLE 系统自动地为这些操作设置游标并创建其工 作区，这些由系统隐含创建的游标称为隐式游标
• 隐式游标的名字为SQL，这是由ORACLE 系统定义的。 对于隐式游标的操作，如定义、打开、取值及关闭操 作，都由ORACLE 系统自动地完成，无需用户进行处 理。 • 格式调用为： SQL% • 注：INSERT, UPDATE, DELETE, SELECT 语句中不
SELECT INTO 语句 2-2
SQL> SET SERVEROUTPUT ON SQL> DECLARE empid VARCHAR2(10); BEGIN SELECT empno INTO empid FROM employee; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('该查询提取多行'); END; / 如果 SELECT INTO 语句返回多个值， 将引发TOO_MANY_ROWS异常
显式游标
REF 游标
游标
显式游标 2-1
• 显式游标在 PL/SQL 块的声明部分定义查询，该 查询可以返回多行 空 • 显式游标的操作过程：
–Declare----->Open---Fetch------Close stud_no Stud_name Stud_mrks
打开游标
1
2
James
– 职员编号和上级编号都为偶数 – 只打印其中的五条记录
• 注意考虑游标不足五条记录的情况
触发器
14.1 14.2 14.3 14.4 14.5 14.6 14.7 14.8 14.9 概述 语句触发器 行触发器 instead of触发器 系统事件触发器 用户事件触发器 禁用触发器和事务处理 查看触发器信息 事件属性函数
带参数的显式游标
SQL> SET SERVEROUTPUT ON SQL> DECLARE desig VARCHAR2(20); emp_code VARCHAR2(5); empnm VARCHAR2(20); CURSOR emp_cur(desig VARCHAR2) IS SELECT empno, ename FROM employee WHERE designation=desig; CURSOR <cursor_name>(<param_name> BEGIN <param_type>) desig:= ‘1000'; OPEN emp_cur(desig); ISLOOP select_statement; FETCH emp_cur INTO emp_code,empnm; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_code||' '||empnm); END LOOP; CLOSE emp_cur; END;
SELECT INTO 语句 2-1
SQL> SET SERVEROUTPUT ON SQL> DECLARE empid VARCHAR2(10); desig VARCHAR2(10); BEGIN empid:= '&Employeeid'; SELECT designation INTO desig FROM employee WHERE empno=empid; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('职员未找到'); END; / 如果没有与SELECT INTO语句中的条件匹配 的行，将引发NO_DATA_FOUND异常
• 声明显式游标时可以带参数以提高灵 活性 • 声明带参数的显式游标的语法如下：
使用显式游标更新行 2-1
• 允许使用游标删除或更新活动集中的 行 • 声明游标时必须使用 SELECT … FOR UPDATE语句