examen anul trecut

SET serveroutput ON;
CREATE OR REPLACE PROCEDURE verifica_plati
  (v_ordid ord.ordid%TYPE,
  v_total ord.total%TYPE)
IS
  v_empno emp.empno%TYPE;
  v_sal emp.sal%TYPE;
  v_sal_nou emp.sal%TYPE;
  v_sum ord.total%TYPE;
BEGIN
  UPDATE ord
  SET total=total+v_total
  WHERE ordid=v_ordid;
  SELECT empno,sal,SUM(total)
  INTO v_empno,v_sal,v_sum
  FROM emp e join customer c ON
  e.empno=c.repid join ord o ON
  o.custid = c.custid
  WHERE empno IN
    (SELECT repid
    FROM customer c join ord o ON
    c.custid=o.custid
    WHERE ordid=v_ordid)
  GROUP BY empno,sal;
    IF v_total > v_sal*10 THEN
      SELECT losal
      INTO v_sal_nou
      FROM salgrade
      WHERE grade =
        (SELECT grade
        FROM salgrade
        WHERE v_sal BETWEEN losal AND hisal)+1;
      UPDATE emp
      SET sal=v_sal_nou
      WHERE empno=v_empno;
      END IF;
END;
/
CREATE OR REPLACE TRIGGER trg_ver_plati
after INSERT OR DELETE OR UPDATE OF itemtot ON item
FOR each ROW
BEGIN
    IF inserting THEN
      verifica_plati(:NEW.ordid,:NEW.itemtot);
    ELSIF deleting THEN
      verifica_plati(:old.ordid,-:old.itemtot);
    ELSIF updating THEN
      verifica_plati(:NEW.ordid,:NEW.itemtot);
    END IF;
END;
/
CREATE OR REPLACE PACKAGE pack_salgrade
IS
  PROCEDURE af_agent
      (v_custid customer.custid%TYPE);
  PROCEDURE af_client
      (v_ordid ord.ordid%TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY pack_salgrade
IS
  PROCEDURE af_agent
      (v_custid customer.custid%TYPE)
  IS
      v_empno emp.empno%TYPE;
      v_sal emp.sal%TYPE;
      v_grad salgrade.grade%TYPE;
      v_sum ord.total%TYPE;
  BEGIN
      SELECT empno,sal,grade,SUM(total)
      INTO v_empno,v_sal,v_grad,v_sum
      FROM emp e join customer c ON
          c.repid=e.empno join ord o ON
          o.custid=c.custid join salgrade
          ON sal BETWEEN losal AND hisal
      WHERE empno IN
          (SELECT repid
          FROM customer
          WHERE custid=v_custid)
      GROUP BY empno,sal,grade;
      DBMS_OUTPUT.put_line('Agentul nr. '|| v_empno || ' are salariul ' || v_sal || ' ,gradul ' || v_grad||' si totalul platilor clientilor sai '||v_sum);
  END af_agent;

  PROCEDURE af_client
      (v_ordid ord.ordid%TYPE)
  IS
      v_custid customer.custid%TYPE;
  BEGIN
      SELECT custid
      INTO v_custid
      FROM ord
      WHERE ordid=v_ordid;
      af_agent(v_custid);
  END af_client;
END pack_salgrade;
/
BEGIN
    DBMS_OUTPUT.put_line('Initial: ');
    pack_salgrade.af_client(601);
    INSERT INTO item(ordid,itemid,prodid,itemtot) VALUES (601,3,100860,20000);
    DBMS_OUTPUT.put_line('Final: ');
    pack_salgrade.af_client(601);
END;