当前位置:网站首页>Write stored procedures and triggers to increase bonus for Scott employees

Write stored procedures and triggers to increase bonus for Scott employees

2020-11-10 12:53:19 osc_p6dyctjv

To write stored procedure and trigger , Realize to scott Company employees increase bonus ,
10 The increase in the Department is for wages 20%,
20 The increase in the Department is for wages 15%,
30 The increase in the Department is for wages 10%,
For positions MANAGER And the salary is higher than 2500 We don't increase the bonus .



select * from emp;
-- 1. trigger 
create or replace trigger t_up_emp
before update on emp
for each row
begin
  if :old.job='MANAGER' and :old.sal > 2500 then
    select :old.sal into :new.sal from dual;
  end if;
end;
-- 1.1 validation trigger 
update emp set sal=sal+150 where empno='7698';


-- 2. stored procedure 
create or replace procedure p_up_sal
is
  emp_ emp%rowtype;
  cursor c1(deptno_ emp.deptno%type) is select * from emp where deptno=deptno_;
begin
  open c1(10);-------------10 department 
    loop
      fetch c1 into emp_;
      exit when c1%notfound;
      update emp set comm = nvl(comm,0) + 0.2*emp_.sal where empno=emp_.empno;
      commit;
    end loop;
  close c1;
  open c1(20);-------------20 department 
    loop
      fetch c1 into emp_;
      exit when c1%notfound;
      update emp set comm = nvl(comm,0) + 0.15*emp_.sal where empno=emp_.empno;
      commit;
    end loop;
  close c1;
  open c1(30);-------------30 department 
    loop
      fetch c1 into emp_;
      exit when c1%notfound;
      update emp set comm = nvl(comm,0) + 0.1*emp_.sal where empno=emp_.empno;
      commit;
    end loop;
  close c1;
end;
-- 2.1 Execute stored procedures 
begin
   p_up_sal;
end;

版权声明
本文为[osc_p6dyctjv]所创,转载请带上原文链接,感谢