触发器

--声明
declare

  a int := 5;

begin
  select sal into a from emp where ename = 'SCOTT';

  dbms_output.put_line(a || '工资');

end;

--if
declare
  num int :=5;

begin
  if num>4 then
    dbms_output.put_line(num||'大于4');
    elsif  num>3 then
      dbms_output.put_line(num||'大于3');
      elsif num >2 then
        dbms_output.put_line(num||'大于2');
    end if ;
  end;
--loop
declare
  a int := 5;
  b int := 1;

begin
  loop
    b := b * a;
    a := a - 1;
    exit when a = 1;
  end loop;
  dbms_output.put_line('5的阶乘是：'||b);
  end;
--for
declare
  a int := 5;
  b int := 1;
  c int;

begin
  for c in 1 .. a loop
    b := b * a;
    a := a - 1;
  end loop;
  dbms_output.put_line('5的阶乘是：' || b);

end;

--while
declare
  a int :=5;
  b int :=1;
begin
  while a>=1 loop
    b:=b*a;                                                                                                                                                    
    a:=a-1;
 
  end loop;
  dbms_output.put_line('5的阶乘是'||b);
  end;

--存贮过程，procedure
create or replace procedure p1(
       a in integer,
       b in integer,
       c out integer
)
as j integer;
begin c:=0;
      for j in a..b loop
        c:=c+j;
        end loop;
end;
--调用存储过程
declare
  c integer;
begin
  p1(1, 1000, c);
  dbms_output.put_line('c:' || c);
end;
 
--函数  创建
create or  replace function f1(a in emp.empno%type)
   return emp.ename%type
   as rname emp.ename%type;
   begin
     select ename into rname from emp
     where empno=a;
     return rname;
 end;

--触发器
create trigger t1
 after insert on emp
declare
c int;
begin
  select count(*) into c from emp;
  dbms_output.put_line('当前员工有'||c||'条数据');
end;

--强制触发（t1）
select * from emp;
insert into emp   values (7766,'looo','情节',7756,sysdate,16666,null,20);



--建表
create table lz as select * from emp where 1=2;


--触发器t2
create or replace trigger t2 after delete on emp
for each row
  declare
  begin
   insert into lz values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
  end;

--强制触发
  delete from emp where empno=7736;
     select * from lz;

--触发器3
 -- update trigger
     create or replace trigger t3 after update on emp
     for each row
       declare
         
       begin
         dbms_output.put_line('更新前：'||:old.ename);
         dbms_output.put_line('更新后：'||:new.ename);
         end;

-- 打断触发器
create or replace trigger t4 after delete on dept for each row
declare
    n int;
begin
  select count(*) into n from emp where deptno=:old.deptno;
  if n>0 then
    raise_application_error('-20000',:old.dname||'部门有人不能删除');
    end if;
  end;
  --强制触发
  delete dept where deptno=10;