ABD - ficha 4

/*2*/
DECLARE
  CURSOR cl IS
    SELECT sal,deptno FROM Emp
      WHERE deptno IN (10, 20) AND job != 'PRESIDENTE'
      FOR UPDATE OF sal;
BEGIN
  FOR too_long_damn_son IN cl
  LOOP
    IF too_long_damn_son.sal < 2000 THEN
      UPDATE Emp SET sal = sal * 1.1 WHERE CURRENT OF cl;
    ELSE
      UPDATE Emp SET sal = sal * 1.06 WHERE CURRENT OF cl;
    END IF;
  END LOOP;
END;
/*3*/
DECLARE
  v_job Emp.job%TYPE := '&funcao';
  v_tot emp.sal%TYPE;

  CURSOR cl IS
    SELECT sal FROM Emp
      WHERE job = v_job
      ORDER BY sal
      FOR UPDATE OF sal;

BEGIN
  SELECT SUM(sal) INTO v_tot FROM Emp WHERE job = v_job;

  IF v_tot IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('ERROR');
    INSERT INTO Erros VALUES('Job inexistente',1,SYSDATE);
  ELSE

    FOR x IN cl
    LOOP
      IF v_tot * 14 < 75000  THEN
        DBMS_OUTPUT.PUT_LINE(v_tot);
        UPDATE Emp SET sal = sal * 1.1 WHERE CURRENT OF cl;
      ELSE
        DBMS_OUTPUT.PUT_LINE('Exit');
        EXIT;
      END IF;
      SELECT SUM(sal) INTO v_tot FROM Emp WHERE job = v_job;
    END LOOP;

  END IF;
END;