ResEx3

DECLARE
    v_job       	      VARCHAR2(12);
    v_total_sal_group         NUMBER;
    v_sal_limit               NUMBER := 75000;
    v_total_group_after_raise NUMBER;
    v_raise                   NUMBER;
    e_invalid_group           EXCEPTION;
BEGIN
    v_job := '&job';
 
    SELECT SUM (sal * 14)
    INTO   v_total_sal_group
    FROM   emp
    WHERE  job = v_job;
 
    IF v_total_sal_group IS NULL THEN
      RAISE e_invalid_group;
    END IF;
 
    IF v_total_sal_group < v_sal_limit THEN
      DECLARE
          CURSOR cs_emps_by_sal_asc IS
              SELECT sal
              FROM   emp
              WHERE  job = v_job
              ORDER  BY sal
              FOR UPDATE OF sal;
      BEGIN
          FOR cs_rec IN cs_emps_by_sal_asc LOOP
              v_raise := cs_rec.sal * 0.1;
 
              UPDATE emp
              SET    sal = cs_rec.sal + v_raise
              WHERE CURRENT OF cs_emps_by_sal_asc;
 
              v_total_group_after_raise := v_total_sal_group + v_raise;
              EXIT WHEN v_total_group_after_raise >= v_sal_limit;
          END LOOP;
          COMMIT;
      END;
    END IF;
EXCEPTION
    WHEN e_invalid_group THEN
      DBMS_OUTPUT.Put_line('Erro: Tipo de fun���§���£o inv���¡lida!');
 
END;