Posted By

Nando on 10/16/10


Tagged


Versions (?)

ResEx3


 / Published in: PL/SQL
 

  1. v_job VARCHAR2(12);
  2. v_total_sal_group NUMBER;
  3. v_sal_limit NUMBER := 75000;
  4. v_total_group_after_raise NUMBER;
  5. v_raise NUMBER;
  6. e_invalid_group EXCEPTION;
  7. v_job := '&job';
  8.  
  9. SELECT SUM (sal * 14)
  10. INTO v_total_sal_group
  11. FROM emp
  12. WHERE job = v_job;
  13.  
  14. IF v_total_sal_group IS NULL THEN
  15. RAISE e_invalid_group;
  16.  
  17. IF v_total_sal_group < v_sal_limit THEN
  18. CURSOR cs_emps_by_sal_asc IS
  19. SELECT sal
  20. FROM emp
  21. WHERE job = v_job
  22. ORDER BY sal
  23. FOR UPDATE OF sal;
  24. FOR cs_rec IN cs_emps_by_sal_asc LOOP
  25. v_raise := cs_rec.sal * 0.1;
  26.  
  27. UPDATE emp
  28. SET sal = cs_rec.sal + v_raise
  29. WHERE CURRENT OF cs_emps_by_sal_asc;
  30.  
  31. v_total_group_after_raise := v_total_sal_group + v_raise;
  32. EXIT WHEN v_total_group_after_raise >= v_sal_limit;
  33. END;
  34. WHEN e_invalid_group THEN
  35. DBMS_OUTPUT.Put_line('Erro: Tipo de fun���§���£o inv���¡lida!');
  36.  

Report this snippet  

You need to login to post a comment.