Oracle的触发器与游标

触发器:

触发器概述:

触发器是指被隐含执行的存储过程，它可以使用PL/SQL进行开发

当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码 

触发器类型:

DML触发器

在对数据库表进行DML操作时触发，并且可以对每行或者语句操作上进行触发。

替代触发器

oracle8专门为进行视图操作的一种触发器

系统触发器

对数据库系统事件进行触发，如启动、关闭等

触发器组成:

触发事件

DML或DDL语句。

触发时间

是在触发事件发生之前(before)还是之后(after)触发

触发操作

使用PL/SQL块进行相应的数据库操作

触发对象

表、视图、模式、数据库

触发频率

触发器内定义的动作被执行的次数。

编写触发器执行代码时，需要注意以下限制

触发器不接受参数

一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

一个表上的触发器越多，该表上的DML操作的性能影响就越大

触发器代码的大小不能超过32K。如需要大量的代码创建触发器，则首先创建过程，然后在触发器中使用CALL语句调用过程

触发器代码只能包含SELECT、INSERT、UPDATE和DELETE语句，

不能包含DDL语句（CREATE、ALTER和DROP）和事务控制语句（COMMIT、ROLLBACK和SAVEPOINT）

语句触发器:

语句触发器是指当执行DML语句时被隐含执行的触发器

如果在表上针对某种DML操作创建了语句触发器，则当执行DML操作时会自动地执行触发器的相应代码

为了审计DML操作，或者确保DML操作安全执行时，可以使用语句触发器

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name 
[REFERENCING {OLD [AS] old | NEW [AS] new}]
[FOR EACH ROW ][WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;

触发器触发次序

1. 执行 BEFORE语句级触发器;

2. 对与受语句影响的每一行：

执行 BEFORE行级触发器

执行 DML语句

执行 AFTER行级触发器

3. 执行 AFTER语句级触发器

创建DML触发器:

创建BEFORE语句触发器

如果指定了BEFORE关键字，则表示执行DML操作之前触发触发器

create or replace trigger tri_no_sun
before 
insert or update or delete
on emp
begin
  if to_char(sysdate,'day')in('星期四','星期六') then
  raise_application_error(-20000,'不能在周四或周六修改员工信息');
  end if;
end;

使用条件谓词

当在触发器中同时包含多个触发事件时，为了在触发器代码中区分具体的触发事件，可以使用以下3个条件谓词

INSERTING：当触发事件是INSERT操作时，该条件谓词返回值为TRUE，否则返回FALSE

UPDATING：当触发事件是UPDATE操作时，该条件谓词返回值为TRUE，否则返回FALSE

DELETING：当触发事件是DELETE操作时，该条件谓词返回值为TRUE，否则返回FALSE

create or replace trigger tri_option_dept
before
insert or update or delete
on dept
begin
  if to_char(sysdate,'day') in ('星期四','星期六') then  
  case
    when inserting then
      raise_application_error(-20000,'周六或周四不能进行录入操作');
    when updating then
      raise_application_error(-20001,'周六或周四不能进行修改操作');
    when deleting then
      raise_application_error(-20002,'周六或周四不能进行删除操作'); 
  end case;
  end if;
end;

行级触发器:

for each row

:old 修改前的该行记录

:new 修改后的该行记录

如果修改的是部门编号为30的员工工资，则工资不能降低

create or replace trigger trig_update_sal
before update of sal,comm
or delete on emp
for each row
when(old.deptno=30) 
begin
  case 
    when updating('sal') then
      if :new.sal<:old.sal then
        raise_application_error(-20002,'部门30的人员工资不能降');
      end if;
    when updating('comm') then
      if :new.comm<:old.comm then
        raise_application_error(-20001,'部门30的奖金不能降低！');
      end if;
    when deleting then
      raise_application_error(-20003,'不能删除部门30的员工');
  end case;
end;

创建AFTER语句触发器 :

如果指定了AFTER关键字，则表示在执行DML操作之后触发触发器

利用行触发器实现级联更新。在修改了主表dept中的deptno之后（AFTER），级联的、自动的更新子表emp中原来在该部门的deptno。 

create or replace trigger tri_casupdate
after update of deptno on dept
for each row
begin
  dbms_output.PUT_LINE('旧的deptno值是：'||:old.deptno);
  dbms_output.PUT_LINE('新的deptno值是：'||:new.deptno);
  update emp set deptno=:new.deptno where deptno=:old.deptno;
end;

在触发器中调用存储过程 :

有时业务逻辑过于复杂，触发器内容有限（32K)，只能借助于存储过程

在删除dept表中记录时，将原有的记录保存到一个回收表delDept中 

创建INSTEAD OF 触发器:

为了在不能执行DML操作的复杂视图上执行DML操作，必须基于视图创建INSTEAD OF触发器

创建INSTEAD OF触发器时需要注意以下几点

INSTEAD OF选项只适用于视图

当基于视图创建触发器时，不能指定BEFORE和AFTER选项

当创建INSTEAD OF触发器时，必须指定FOR EACH ROW选项

只能在视图上创建INSTEAD OF触发器，而不能创建其他类型的触发器

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name --只能定义在视图上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;

创建系统事件触发器:

系统事件触发器是指基于Oracle系统事件所创建的触发器

创建系统事件触发器时，应用开发人员经常需要使用事件属性函数

创建登录和退出触发器:

为了记载用户的登录和退出事件，可以分别创建登录和退出触发器

      创建用于存放登录和退出信息的表

create table log_event(
  username varchar2(20),
  ipAddress varchar2(20),
  logonTime timestamp,
  logoffTime timestamp
);

创建登录和退出触发器

create or replace trigger logon_trigger
after logon on database
begin
  insert into log_event(username,ipaddress,logonTime)
  values(ora_login_user,ora_client_ip_address,sysdate);
end;

create or replace trigger logoff_trigger
before logoff on database
begin
  insert into log_event(username,ipaddress,logoffTime)
  values(ora_login_user,ora_client_ip_address,sysdate);
end;

用户登录数据库或断开与数据库的连接时执行相应的触发器代码

管理触发器:

显示触发器信息

通过查询数据字典视图USER_TRIGGERS，可以显示当前用户所包含的所有触发器信息

禁用或启用触发器

重新编译触发器

当使用ALTER TABLE命令修改表的结构时，会使触发器变为无效状态

为了使触发器继续生效，需要重新编译触发器   

删除触发器

游标:

SQL语言是面向集合的，是对指定列的操作。如果要对列中的指定行进行操作，就必须使用游标。

当在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，Oracle会为其分配一个上下文区（ContextArea）

游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法

显式游标：

游标分为显式游标和隐含游标两种

隐含游标用于处理SELECTINTO和DML语句

显式游标则用于处理SELECT语句返回的多行数据

使用显式游标

定义游标 CURSORcursor_name ISselect_statement;

打开游标 OPENcursor_name;

提取数据

FETCHcursor_nameINTOvariable1,variable2,...;

FETCHcursor_namebulk collect into collect1…;

关闭游标 CLOSEcursor_name; 

显示游标属性用于返回显式游标的执行信息

  游标属性使用格式为：游标名 +属性名%ISOPEN

  用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE；否则返回FALSE%FOUND

  检查是否从结果集中提取到数据。如果提取到数据，则返回值为TRUE；否则返回FALSE%NOTFOUND与%FOUND属性恰好相反，如果提取到数据，则返回值为FALSE；否则返回TRUE%ROWCOUNT

   返回到当前行为止已经提取到的实际行数 

显示游标示例:

根据条件查询并输出部门信息

declare
	v_dept dept%rowtype;
	cursor dept_cursor is select * from dept where deptno>10;  --定义游标
begin
  	open dept_cursor;  --打开游标
  	loop
    		fetch dept_cursor into v_dept;  --提取数据
    		exit when dept_cursor%notfound;  --判断循环退出条件
   		 dbms_output.put_line('编号：'||v_dept.deptno 
				 ||'  名称：'||v_dept.dname||'  地址：'||v_dept.loc);
  	end loop;
  	close dept_cursor;  --关闭游标
end;

用table结构提取游标数据

declare
	type v_dept is table of dept%rowtype  index by binary_integer;
	cursor dept_cursor is select * from dept;
	v_dept_table v_dept;
begin
  	open dept_cursor;
  	fetch dept_cursor bulk collect into v_dept_table;
  	close dept_cursor;
  	for i in v_dept_table.first..v_dept_table.last loop
    		dbms_output.put_line(v_dept_table(i).deptno||'  
			'||v_dept_table(i).dname||'  '||v_dept_table(i).loc);
  	end loop;  
end;

参数游标:

带有参数的游标

在定义了参数游标之后，当使用了不同参数值多次打开游标时，可以生成不同的结果集

CURSOR cursor_name(param_name datatype)
 IS select_statement;              --定义参数游标时，游标参数只能指定数据类型，而不能指定长度

参数游标示例:

查询指定部门的员工信息

declare
	cursor emp_cursor(param_dept  number) is --定义游标参数名及类型。
	select empno,ename from emp where deptno=param_dept;--把参数作为条件。
	emp_record emp_cursor%rowtype;    --定义和游标集合相同的数据结构。
begin
  	open emp_cursor(20);           --在打开游标时传入参数值。
  	loop
    		fetch emp_cursor into emp_record;
    		exit when emp_cursor%notfound;
    		dbms_output.put_line(emp_record.empno||'  '||emp_record.ename);  
  	end loop;
 	close emp_cursor;
end;

游标for循环简化游标处理 :

使用游标for循环时，oracle隐含打开游标，提取数据并关闭游标。

示例：获取每个部门的名称

declare
	cursor dept_cursor is select * from dept;
begin
  	for dept_row in dept_cursor loop       --用for循环迭代游标数据。
  		dbms_output.put_line('第'||dept_cursor%rowcount||'个部门：'||dept_row.dname);  --游标迭代的次数。
  	end loop;
end;

游标for循环简化游标处理 :

当使用游标for循环时，可以直接使用子查询。

示例：获取每个部门的名称

begin
  	for dept_row in(select deptno,dname from dept) loop     ---用子查询内容作为游标内容。
  		dbms_output.put_line('第'||dept_dept.deptno||'个部门：'||dept_row.dname);
  	end loop;
end;

隐含游标:

当执行一条DML语句或者SELECT...INTO语句时，都会创建一个隐含游标

隐含游标的名称是SQL，不能对SQL游标显式执行OPEN、FETCH和CLOSE语句。

Oracle隐式地打开、提取，并总是自动地关闭SQL游标

隐式游标属性包括

SQL%FOUND、

SQL%NOTFOUND

SQL%ROWCOUNT

SQL%ISO

declare
	v_empno number(4):=7369;
begin                                                  --判断隐含游标“sql"是否有影响的行数。
  	delete from emp where empno=v_empno;
  	if sql%found then
    		dbms_output.put_line('存在该员工！');
  	else
    		dbms_output.put_line('不存在该员工！');
  	end if;
end;

小结：

定义游标

CURSOR cursor_name IS select_statement;

打开游标

OPENcursor_name;

提取数据

FETCHcursor_nameINTO variable1,variable2,...;

关闭游标

CLOSEcursor_name;