Oracle学习交流（5）---触发器和游标

 

--以下为显示游标
--隐式游标
--游标 pS:注意 游标的申明!类型在前! 当OPEN游标的时候,上下文区是没数据的!!!!!
declare
cursor firstCursor  is select sal from emp where emp.empno=&empId;
vsalary number;
begin
  open firstCursor;
  fetch firstCursor into vsalary;
  close firstCursor;
  dbms_output.put_line(vsalary);
end;

select * from emp

--游标属性
declare
cursor firstCursor  is select sal from emp where emp.deptno=&deptID;
vsalary number;
begin
  if not firstCursor%isopen then dbms_output.put_line('游标未打开!'); end if;--如何游标未打开输出信息
  open firstCursor;
  loop
  fetch firstCursor into vsalary;
  dbms_output.put_line(firstCursor%rowcount);
  dbms_output.put_line(vsalary);
  exit  when firstCursor%notfound;
  end loop;
  close firstCursor;
end;

select * from emp where emp.deptno = 30

 

--FETCH BULK COLLECT INTO 语句
--将游标指向的当前行的数据一次性插入到ROWTYPE类型中去
 declare
 type empRecord is record(
      empName emp.ename%type,
      empSalary emp.sal%type
 );
 type empTable is table of empRecord index by binary_integer;
 emptab empTable;
 cursor myCursor is select emp.ename,emp.sal from emp where emp.deptno=&deptID;
 begin
   open myCursor;
   fetch myCursor bulk collect into emptab;
   close myCursor;
   for i in 1..emptab.count loop
     dbms_output.put_line(emptab(i).empName||'            '||emptab(i).empSalary);
   end loop;
 end;
 
 
 
 --用ROWTYPE类型类保存游标数据
 declare
 empRow emp%rowtype;
 cursor empCursor is select * from emp where emp.deptno=&deptID;
 begin
   open empCursor;
   loop
     fetch empCursor into empRow;
     exit when empCursor%notfound;
     dbms_output.put_line(empRow.ename||'                '||empRow.sal);
     end loop;
 end;
 
 
 /*参数游标.PS在定义游标参数时,无需为游标参数设置长度.
 而且记得在游标SELECT语句后加上该参数的WHERE 条件,否则参数游标无意义*/
  declare
 empRow emp%rowtype;
 cursor empCursor(a emp.deptno%type) is select * from emp where emp.deptno=a;
 begin
   open empCursor(&deptNo);
   loop
     fetch empCursor into empRow;
     dbms_output.put_line(empRow.ename||'                '||empRow.sal||'           '||empRow.deptNo);
     exit when empCursor%notfound;
     end loop;
 end;
 
 select * from emp where emp.deptno=30
 
 
 
 --UPDATE 游标
 declare
 cursor empCursor(a emp.deptno%type) is select * from emp where emp.deptno=a for update;
 empRow emp%rowtype;
 ecount number:=0;
 begin
   open empCursor(&deptNo);
   loop
     fetch empCursor into empRow;
     exit when empCursor%notfound ;
     if empRow.sal<5000 then
     update emp set sal =sal+1 where  current of empCursor ;
     ecount:=ecount+1;
     end if;
   end loop;
   close empCursor;
   dbms_output.put_line(ecount);
 end;
 
 select * from  emp where emp.deptno = 30
 
 
--使用FOR循环的游标遍历,非常简单.ps无需打开和关闭游标!!!!
--FOR循环里的循环变量直接是游标的ROWTYPE类型
declare
 cursor empCursor is select * from emp where emp.deptno=&deptNo;
 empRow emp%rowtype;
begin
  for empRow in empCursor loop
      dbms_output.put_line(empRow.ename);
  end loop;
end;

--在游标FOR循环中直接使用子查询
declare
empRow emp%rowtype;
begin
    for empRow in (select * from emp where emp.deptno=&deptNo)  loop
        dbms_output.put_line(empRow.ename);
    end loop;
end;

 

--自定义游标变量
declare
type empCursor is ref Cursor;
empCur empCursor;
empRow emp%rowtype;
begin
   open empCur for select * from emp where emp.deptno=&deptNo;
loop
  fetch empCur into empRow;
  exit when empCur%notfound;
  dbms_output.put_line(empRow.ename);
end loop;
close empCur;
end;

select * from emp where emp.deptno = 30

 

--创建返回结果集的存储过程,注意SYS_CURSOR的使用.
create or replace procedure proc_getAllEmpByDeptId(
       empCursor out sys_refcursor,
       a number
)as
begin
  open empCursor for select * from emp where emp.deptno=a;
end;

 

--注意SYS_REFCURSOR类型游标不支持FOR.但可以作为参数传递,
--而CURSOR类型游标不能作为参数传递
declare
  empRow emp%rowtype;
  empCursor sys_refcursor;
begin
  proc_getAllEmpByDeptId(empCursor,&deptNo);
  loop
    fetch empCursor into empRow;
  exit when empCursor%notfound;
  dbms_output.put_line(empRow.ename);
  end loop;
end;

 

-- 使用函数来返回系统游标
create or replace function fun_getAllEmpsByDeptId(a emp.deptno%type) return sys_refcursor
as
empCursor sys_refcursor;
begin
  open empCursor for select * from emp where emp.deptno=a;
  return empCursor;
end;

--调用刚刚创建的函数
declare
deptNo number:=&deptNo;
empCursor sys_refcursor;
empRow emp%rowtype;
begin
  empCursor:=fun_getAllEmpsByDeptId(deptNo);
  loop
    fetch empCursor into empRow;
  exit when empCursor%notfound;
  dbms_output.put_line(empRow.ename);
  end loop;
end;

 

--触发器
create or replace trigger emp_insertTrigger before insert or delete or update on emp
begin
    RAISE_APPLICATION_ERROR(-20000,'暂时不能操作此表');
end;

-- 测试触发器
delete from emp where emp.empno=108
--删除
drop trigger emp_insertTrigger
--条件谓词
create or replace trigger emp_insertTrigger before insert or delete or update on emp
begin
  case
    when INSERTING then
    RAISE_APPLICATION_ERROR(-20000,'暂时不能插入记录');
    when UPDATING then
    RAISE_APPLICATION_ERROR(-20000,'暂时不能更新记录');
    when DELETING then
    RAISE_APPLICATION_ERROR(-20000,'暂时不能删除记录');
    else
       RAISE_APPLICATION_ERROR(-20000,'反正就是不让你操作此表');
       end case;
end;

 

--使用UPDATE(列名)判断是否更新此列
create or replace trigger emp_insertTrigger before insert or delete or update on emp
begin
    if updating('sal') then
       RAISE_APPLICATION_ERROR(-20000,'你休想修改工资!');
    end if;
end;

--测试
update emp set sal=sal+1 where emp.empno=108;

drop trigger emp_insertTrigger

--创建审计表
create table audit_table(
       AID number primary key,
       tb_name varchar2(20) not null,
       ins number not null,
       del number not null,
       upd number not null,
       starttime date,
       endtime date
);

drop table audit_table
--为审计表创建序列
create sequence seq_audit start with 1 increment by 1;

--创建关于审计表的触发器
create or replace trigger tri_audit after insert or update or delete on emp
declare Acount number;
begin
  select count(*) into Acount from audit_table where tb_name='EMP';
  if acount = 0 then
    insert into audit_table values(seq_audit.nextval,'EMP',0,0,0,sysdate,null);
    end if;
  case
    --判断当前触发的是什么事件
    when inserting then update audit_table set ins=ins+1,endtime=sysdate where tb_name='EMP';
    when deleting then update audit_table set del=del+1,endtime=sysdate where tb_name='EMP';
     when updating then update audit_table set upd=upd+1,endtime=sysdate where tb_name='EMP';
    end case;
end;

--测试
update emp set sal =sal+1 where emp.empno=108;
select * from audit_table
drop table audit_table
drop trigger tri_audit

--BEFORE行级触发器 PS:注意,行级触发器实际是作用在某表的某个字段的
--而且,在语法上是先写OF字段再ON表明
create or replace trigger tri_emp_row_before before update of sal  on emp for each row
begin
  if :NEW.sal < :OLD.sal THEN
    raise_application_error(-20000,'不能降低员工工资!');
   end if;
end;

--测试EMP表的BEFORE行级触发器
update emp set sal=sal-100 where emp.empno=7499;
drop trigger tri_emp_row_before

create table test(
       id number,
       sal number
)

--AFTER行级触发器
create or replace trigger tri_emp_salary_after after update of sal on emp for each row
begin
  if :NEW.sal < :OLD.sal THEN
    insert into test values(1,:NEW.sal);
  end if;
end;

--测试AFTER行级触发器
update emp set sal=sal-100 where emp.empno=7499;
select * from test;
drop trigger tri_emp_salary_after;
drop table test;

 

--创建BEFORE行级带有WHEN子句的触发器 PS:注意,此处的WHEN子句里用到旧表时应为OLD;
--此触发器会在修改EMP表里员工ID为108的工资时触发!
create or replace trigger tri_emp_salary_before_when before update of sal on emp for each row when (old.empno=7499)
begin
  raise_application_error(-20000,'请不要修改Allen的工资');
end;

--测试BEFORE行级带有WHEN子句的触发器
update emp set sal=100 where empno=7499;
drop trigger tri_emp_salary_before_when

--创建用户登陆触发器PS:普通用户无此权限,使用SYS用户
create or replace trigger tri_login after logon on database when(ora_login_user not in('SYS','SYSTEM'))
begin
  insert into loginInfo values(seq_loginInfo.nextval,ora_login_user);
end;

--在SYS用户下创建此表
create table loginInfo(
       LID number primary key,
       LoginName varchar2(10)
);
--创建此表的自动增长列
create sequence seq_loginInfo start with 1 increment by 1;
--给SCOTT用户授权
grant select on loginInfo to scott
select * from sys.loginInfo
--创建DDL触发器 需SYS或SYSTEM用户
create or replace trigger tri_create after create on database
begin
  dbms_output.put_line('创建了表');
end;