10Oracle-包游标和触发器

cursor 游标名 is select 语句
打开游标: 当打开游标的时候，Oracle 会执行游标所对应的 select 语句，并且将 select 语句的 结果暂时存放到结果集中。
open 游标名称
提取数据: 打开游标之后，select 语句的结果被临时存放到游标结果集中，为了处理结果集中的 数据，需要使用 fetch 语句提取游标数据。
http://shop34513748.taobao.com/
Copyright©2010 吴青版权所有
吴老师教学讲义
9
件 发 生 的 时 候 ， 比 如 向 Student 表 中 插 入 数 据 ， Insert into student(name,age) values(‘张三’,20) new 和 old 中的状态是: new:
指明哪些数据库动作会触发此触发器,可取的值有：insert：数据库插入会触发此 触发器； update： 数据库修改会触发此触发器； delete： 数据库删除会触发此触发器。
表名: 数据库触发器所在的表或者视图 for each row: 对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。
2.1 触发器的功能
允许/限制对表的修改 自动生成派生列，比如自增字段 强制数据一致性 提供审计和日志记录 防止无效的事务处理 启用复杂的业务逻辑
2.2 语法
create [or replace] trigger 触发器名 触发时间 触发事件 on 表名 [for each row] pl/sql 块
Copyright©2010 吴青版权所有
吴老师教学讲义
begin for emp_record in (select ename,sal from emp) loop dbms_output.put_line(emp_record.ename); end loop; end;
7
2. 触发器
触发器是指存放在数据库中， 并被隐含执行的存储过程。 当发生特定事件 （如修改表， 建立对象，登录到数据库）时，Oracle 会自动执行触发器的响应代码。
1.3 例子
显示部门编号为 10 的所有雇员名和工资。
http://shop34513748.taobao.com/
Copyright©2010 吴青版权所有
吴老师教学讲义
declare --定义游标 cursor emp_cursor is select ename,sal from emp where deptno=10; --定义变量 v_ename emp.ename%type; v_sal emp.sal%type; begin --打开游标 open emp_cursor; loop --提取数据 fetch emp_cursor into v_ename,v_sal; exit when emp_cursor%notfound; dbms_output.put_line(v_ename||':'||v_sal); end loop; --关闭游标 close emp_cursor; end;
4
使用游标属性:
declare --定义游标 cursor emp_cursor is select ename from emp where deptno=10; --定义数据类型 type ename_table_type is table of varchar2(10); --定义变量 ename_table ename_table_type; begin if not emp_cursor%ISOPEN THEN --如果游标没有打开，则打开游标 open emp_cursor; end if; fetch emp_cursor bulk collect into ename_table; dbms_output.put_line('提取的总计行数:'||emp_cursor%ROWCOUNT); --关闭游标 close emp_cursor; end;
id name age
•没有数据
•张三
•20
old 中没有任何数据 所以触发器的代码块中只需要将 id 的值确定，触发器执行之后，insert 语句开始执行，将 new 中的数据送入物理表中。 经过以上的分析，编写触发器如下:
1.1 显式游标
为了处理 select 语句返回的多行数据，开发人员可以使用显示游标，使用显示游标 包括定义游标，打开游标，提取数据和关闭游标四个阶段。
DECLARE
定义游标:
OPEN
FETCH
CLOSE
在使用显示游标之前，必须首先在定义部分定义游标。定义游标用于指定游标所对应 的 select 语句。
1.6 在游标 for 循环中直接使用子查询
当使用游标 for 循环时，习惯做法是首先在定义部分定义游标，然后再游标 for 循环 中引用该游标。如果在使用游标 for 循环时不需要使用任何游标属性，那么可以直接在游 标 for 循环中使用子查询。
http://shop34513748.taobao.com/
declare --定义游标 cursor emp_cursor is select ename,sal from emp; --定义变量,变量为游标类型 emp_record emp_cursor%ROWTYPE; begin if not emp_cursor%ISOPEN THEN --如果游标没有打开，则打开游标 open emp_cursor; end if; loop fetch emp_cursor into emp_record; exit when emp_cursor%NOTFOUND; dbms_output.put_line(emp_record.ename||':'||emp_record.sal); end loop; --关闭游标 close emp_cursor; end;
当在 PL/SQL 块中执行查询语句（select 语句）和数据操纵语句(DML)时，Oracle 会为其分配上下文区(Context Area),游标就是指向上下文区的指针。 对于数据操纵语句和 单行 select into 语句来说，Oracle 会为它们分配隐含游标。PL/SQL 包含隐含游标和显 示游标等两种游标类型，其中隐含游标用于处理 select into 和 DML 语句，而显式游标则 专门用于处理 select 语句返回多行数据。
1
吴老师教学讲义
抚尺而已
之 ，一 人 、一 桌 、一 椅 、一 扇 、一
忽 然 抚 尺 一 下 ，群 响 毕 绝 。撤 屏 视
吴 青
QQ:16910735
wuqing_bean@126.com http://blog.sina.com/accpwulaoshi
吴老师教学讲义
2
游标，触发器和包
1. 游标
declare --定义游标 cursor emp_cursor(no number) is select ename,sal from emp where deptno=no; --定义变量,变量为游标类型 emp_record emp_cursor%ROWTYPE; begin if not emp_cursor%ISOPEN THEN --如果游标没有打开，则打开游标 open emp_cursor(10); end if; loop fetch emp_cursor into emp_record; exit when emp_cursor%NOTFOUND; dbms_output.put_line(emp_record.ename||':'||emp_record.sal); end loop; --关闭游标 close emp_cursor; end;
http://shop34513748.taobao.com/
Copyright©2010 吴青版权所有
吴老师教学讲义
语法一：
fetch 游标名 into 变量 1，变量 2
3
语法二：
fetch 游标名 bulk collect into 集合 1，集合 2 [limit rows]
关闭游标: 在提取并处理了结果集的所有数据之后，就可以关闭游标，并释放其结果集了。
1.4 参数游标:
参数游标是指带有参数的游标。在定义了参数游标之后，当使用不同参数值多次打开 游标时，可以生成不同的结果集。
cursor 游标名(参数名 类型) is select 语句
例子:
http://shop34513748.taobao.com/
Copyright©2010 吴青版权所有
吴老师教学讲义
create sequence SEQ_STUDENT_ID minvalue 1 start with 1 increment by 1
当我们向表中插入数据，如: Insert into student(name,age) values(‘张三’,20) 的时候，Oracle 服务器在将 数据送入物理表之前，我们需要确定 id 的值，所以触发时间应该是 before，因为只需要 在插入的时候确定 id 的值，所以事件应该是 insert，因为是作用在 student 表中，所以 表是 student。 因为需要在插入每条数据的时候， 都确定 id 的值， 所以应该是:for each row. 在触发器中我们可以使用两个特殊的变量 :new 和:old， 它们在触发器代码执行的时 候有效。其中:new 中保存的是正在更新的数据，:old 表示操作之前的数据。当 insert 事
close 游标名
1.2 显示游标属性
显示游标属性用于返回显示游标的执行信息。这些属性包括 : %ISOPEN :用于确定游标是否已经打开。如果游标已经打开，则返回 true，否则返回 false %FOUND:该属性用于检查是否从结果集中提取到了数据。如果提取到数据，则返回 true，否则返回 false %NOTFOUND:与%FOUND 刚好相反， 如果提取到数据， 则返回 false， 否则返回 true %ROWCOUNT:用于返回到当前行为止一斤共提取到的实际行数。 当使用显示游标属性的时候，必须要在显示游标属性之前带有显式游标名作为前缀 （游标名.属性名）
6
1.5 游标 for 循环
当使用游标 for 循环时，oracle 会隐含打开游标，提取游标数据并关闭游标。建议使 用游标 for 循环。
declare --定义游标 cursor emp_cursor is select ename,sal from emp ; begin for emp_record in emp_cursor loop dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员'||emp_record.ename); end loop; end;
触发器名: 触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称， 没有实质的用途 触发时间:
http://shop34513748.taobao.com/
Copyright©2010 吴青版权所有
吴老师教学讲义
8
指明触发器何时执行，该值可取： before--- 表示在数据库动作之前触发器执行； after---表示在数据库动作之后出发器执行。 触发事件:
2.3 执行过程
下面使用例子说明 oracle 触发器如何执行.首先建立一张表：
create table student ( ID NUMBER not null primary key, NAME VARCHAR2(20) not null, AGE NUMBER )
现在要求让 id 进行自动增长，也就是在插入数据的时候，不必插入 id 的值，为此需 要建立一个序列用来产生唯一的编号.
基于游标定义记录变量:
http://shop34513748.taobao.com/
Copyright©2010 吴青版权所有
吴老师教学讲义
Leabharlann Baidu
5
使用%ROWTYPE 属性不仅可以基于表和视图定义记录变量， 也可以基于游标定义记 录变量。当基于游标定义记录标量时，记录成员名实际就是 select 语句的列名或者列别 名。为了简化显示游标的数据处理，建议开发使用记录变量存放游标数据。