oracle游标、触发器创建与使用

游标

SQL语言是面向集合的，是对指定列的操作。如果要对列中的指定行进行操作，就必须使用游标。
当在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，Oracle会为其分配一个上下文区（Context Area）
游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法

游标分为显式游标和隐含游标两种
隐含游标用于处理SELECT INTO和DML语句
显式游标则用于处理SELECT语句返回的多行数据

显式游标属性使用格式为：游标名 + 属性名
%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_type is table of dept%rowtype;
       v_dept v_dept_type;
       cursor dept_cursor is select * from dept;
begin
  open dept_cursor;
  fetch dept_cursor bulk collect into v_dept;
  close dept_cursor;
  
  for i in v_dept.first..v_dept.last
    loop
      dbms_output.put_line(v_dept(i).deptno||'  '||v_dept(i).dname||'     '||v_dept(i).loc);
    end loop;
end;

用table结构提取游标数据结果：

参数游标 ：在定义了参数游标之后，当使用了不同参数值多次打开游标时，可以生成不同的结果集，定义参数游标时，游标参数只能指定数据类型，而不能指定长度

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
  		dbms_output.put_line('第'||dept_cursor%rowcount||
				'个部门：'||dept_row.dname);
  	end loop;
end;
//当使用游标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;

隐含游标

触发器

触发器是指被隐含执行的存储过程，它可以使用PL/SQL进行开发 ，当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码 ，主要用来备份数据

1. 触发器类型
   DML触发器：在对数据库表进行DML操作时触发，并且可以对每行或者语句操作上进行触发。
   替代触发器：oracle8专门为进行视图操作的一种触发器
   系统触发器：对数据库系统事件进行触发，如启动、关闭等
   2.触发器的创建 语句触发器：指当执行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;

3.使用案例
现有一张表myemp 字段有empno,ename,job,mgr…

快速创建一张新表，这里为了简单只创建myemp中部分字段
create table new_myemp as select empno,ename,job,sal from myemp where 1=2;
新表new_myemp表结构及字段如下：

接下来创建一个触发器tri_myemp当对myemp表进行删除操作时会将删除的数据备份到new_myemp中

//创建一个触发器tri_myemp
create or replace trigger tri_myemp
after delete on myemp
for each row
begin 
  insert into new_myemp(empno,ename,job,sal) values(:old.empno,:old.ename,:old.job,:old.sal);
end;

验证结果：delete from myemp where empno=7369;

到这里一个简单的触发器就完成啦！！
复杂点的触发器比如触发器里使用存储过程

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

create or replace procedure pro_deltemp_insertHis(v_empno      emp_history.empno%type,
                                                  v_ename      emp_history.ename%type,
                                                  v_job        emp_history.job%type,
                                                  v_sal        emp_history.sal%type,
                                                  v_todo       emp_history.todo%type,
                                                  v_updatedate emp_history.updatedate%type) as
begin
  insert into emp_history
    (empno, ename, job, sal, todo, updatedate)
  values
    (v_empno, v_ename, v_job, v_sal, v_todo, v_updatedate);
end;
//多种触发事件
create or replace trigger tri_emp
  after delete or  update or insert on emp1
  for each row
begin
  case when inserting then
       pro_deltemp_insertHis(:new.empno,:new.ename,:new.job,:new.sal,'insert',sysdate);
     when updating then
      pro_deltemp_insertHis(:old.empno,:old.ename,:old.job,:old.sal,'update',sysdate);
     when deleting then
     pro_deltemp_insertHis(:old.empno,:old.ename,:old.job,:old.sal,'delete',sysdate);
     end case;
end;