dba经典辅助学习15游标与触发器

1、过程语言 PL/SQL游标触发器 大纲 游标游标的概念游标的属性FOR循环中游标的使用带参数游标的使用触发器触发器的概念触发器的类型触发的条件触发器的语法触发器的示例游标的使用 游标的概念 游标的属性 FOR循环中游标的使用 带参数游标的使用 4.4.1游标的概念 游标的定义游标（cursor）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。说明使用游标时，select语句查询的结果可以是单条记录，多条记录，也可以是零条记录。游标工作区中，存在着一个指针（POINTER）,在初始状态它指向查询结果的首记录。要访问查询结果的所有记录，可以通过FETCH语句，

2、进行指针的移动来实现。使用游标进行操作，包括定义游标、打开游标、提取数据以及关闭游标几步。4.4.1游标的概念定义游标语法格式CURSOR IS ;说明PL/SQL块中，游标的定义应该放在定义部分。打开游标语法格式：OPEN ；说明打开游标，实际上是执行游标定义时对应的SELECT语句，将查询结果检索到工作区中。关闭游标语法格式：CLOSE ；说明关闭游标的作用在于，使游标所对应的内存工作区变为无效，并释放与游标相关的系统资源。4.4.1游标的概念提取数据语法格式：FETCH INTO 变量1,变量2,说明在使用FETCH语句之前必须先打开游标，这样才能保证工作区中有数据。对游标第一次使用FE

3、TCH语句时，游标指针指向第一条记录，因此操作的对象是第一条记录，使用后，游标指针指向下一条记录。游标指针只能向下移动，不能回退。如果想查完第二条记录后又回到第一条记录，则必须关闭游标，然后重新打开游标。INTO子句中的变量个数、顺序、数据类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应。示例 1修改表emp中各个雇员的工资，若雇员属于10号部门，则增加$100，若雇员属于20号部门，则增加$200，若雇员属于30号部门，则增加$300.DECLARErow emp%rowtype;increment number(4);CURSOR cursor_emp IS SELECT *

4、FROM EMP;i number(2);BEGIN select count(*) into i from emp;OPEN cursor_emp;LOOP FETCH cursor_emp INTO row; if row.deptno=10 then increment:=100;elsif row.deptno=20 thenincrement:=200;elseincrement:=300;end if;update emp set sal=sal+increment where ename=row.ename;i:=i-1;Exit when i0;END LOOP;CLOSE c

5、ursor_emp;END;示例 2利用游标查询EMP中部门号为10的雇员的雇员号与参加工作的日期；示例 2declare cursor c1 is select empno,sal,hiredate,rowid from emp where deptno=10; emp_record c1%rowtype; begin open c1; loop fetch c1 into emp_record; if c1%found then dbms_output.put_line(emp_record.empno| |emp_record.hiredate); else exit; end if;

6、end loop; close c1; end;4.4.2游标的属性 游标的属性%ISOPEN该属性是布尔型。如果游标已经打开，返回TRUE,否则为FALSE。%FOUND布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FALSE;%NOTFOUND布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;%ROWCOUNT数值型，描述的是到目前为止实际从游标工作区抽取的记录数。说明游标属性只能在PL/SQL块中使用，不能在SQL命令中使用。属性的引用格式游标名属性名说明属性名与游标名之间没有空格。4.4.2游标的属性4.4.2游标的属性示例修改

7、表emp中各个雇员的工资，若雇员属于10号部门，则增加$100，若雇员数据20号部门，则增加$200；若雇员数据30号部门，则增加$300。DECLARE row emp%rowtype; increment number(4); CURSOR cursor_emp IS SELECT * FROM EMP; i NUMBER(2);BEGIN OPEN cursor_emp; LOOP FETCH cursor_emp INTO row; if row.deptno = 10 then increment:=100; elsif row.deptno = 20 then increment

8、:= 200; else increment:= 300; end if; update emp set sal = sal+ increment where ENAME = ROW.ENAME;EXIT WHEN cursor_emp%NOTFOUND;END LOOP;END;/4.4.3FOR循环中游标的使用语法格式FOR IN LOOP END LOOP;说明系统自动打开游标，不用显式地使用OPEN语句打开；系统隐含地定义了一个数据类型为%ROWTYPE的变量，并以此作为循环的计算器。系统重复地自动从游标工作区中提取数据并放入计数器变量中。当游标工作区中所有的记录都被提取完毕或循环中断

9、时，系统自动地关闭游标。4.4.3FOR循环中游标的使用示例修改表emp中各个雇员的工资，若雇员属于10号部门，则增加$100，若雇员数据20号部门，则增加$200；若雇员数据30号部门，则增加$300。DECLARE increment number(4); CURSOR cursor_emp IS SELECT * FROM EMP;BEGIN FOR emp_record in cursor_emp LOOP if emp_record.deptno = 10 then increment:=100; elsif emp_record.deptno = 20 then increment

10、 := 200; else increment:= 300; end if; update emp set sal = sal+ increment where EMPNO = emp_record.EMPNO; END LOOP;END;/隐式游标特点：在Declare段中无隐式游标说明把SELECT语句直接安排中行中，PL/SQL会隐含地处理游标定义。每个隐式游标必须有一个INTO；常见异常为no_data_found和too_many_rows例：beginif counter = 20 thenselect last_nameinto lname from personwhere pi

11、n = pin_in;else触发器的概念数据库触发器是一个与特定表相连的存储过程。当应用程序用一条满足触发器条件的SQL DML语句指向该表时，Oracle将自动执行该触发器以执行任务。触发器是数据库内部的特殊存储过程。生成后，这些子程序通常不再被更改，它们可以多次执行。其功能是，当某种触发事件出现时，触发器便被显式地执行。触发器的类型ROW_Level TriggersStatement_level TriggersBEFOR and AFTER TriggersINSTEAD of TriggersSchema TriggersDatabase Triggers触发条件触发条件表示发生什

12、么情况时，触发器执行。触发条件一般由以下三部分组成： 触发时间BEFORE：在操作发生之前执行触发。AFTER：在操作发生之后执行触发。 触发事件INSERT：插入触发；UPDATE：更新触发；DELETE：删除触发。 触发类型ROW：行触发，对每一行操作都要触发。STATEMENT：语句触发，只对该类操作触发一次。 一般在进行SQL语句操作时，都应该使用行触发；只有在进行对表的安全检查时才用语句触发。触发器系统权限需求：ALTER TABLECREATE TRIGGER ALTER ANY TRIGGER定义的一般语法：CREATE OR REPLACE TRIGGER triggerBEF

13、ORE|AFTERDELETE|INSERT|UPDATE OF column ,columnORDELETE|INSERT|UPDATE OF column ,columnON tableFOR EACH ROW WHEN conditionDECLARE BEGINpl/sql block.END trigger触发器示例1CREATE OR REPLACE TRIGGER logempBEFORE INSERT OR UPDATE OR DELETE ON EMPFOR EACH ROWDECLAREstatementtype CHAR(20);BEGINIF INSERTING THE

14、Nstatementtype:=INSERT TRIGGER!;ELSIF UPDATING THENstatementtype:=UPDATE TRIGGER!;ELSEstatementtype:=DELETE TRIGGER!;END IF;DBMS_OUTPUT.PUT_LINE(statementtype);END;下面分别做插入、删除和修改操作：insert into emp (empno,ename) values( 7938, JK);INSERT TRIGGER!1 row inserted.delete emp where empno=7938;DELETE TRIGGER

15、!1 row deleted.update emp set sal=sal+10 where deptno=10;UPDATE TRIGGER! UPDATE TRIGGER! UPDATE TRIGGER!3 row updated.触发器示例2create table emp(user_name varchar(30) default USER NOT NULL,time_stamp DATE default SYSDATE NOT NULL);定义触发器CREATE OR REPLACE TRIGGER tri_empBEFORE INSERT OR UPDATE ON EMPFOR EACH ROWBEGIN:new.time_stamp := SYSDATE;if :n