-----------------------------3----------------------------
set serveroutput on;
DECLARE
    TYPE rec_grad IS RECORD(grade salgrade.grade%type, hisal SALGRADE.HISAL%type, losal SALGRADE.loSAL%type);
    TYPE rc_grad IS REF CURSOR RETURN rec_grad;
    TYPE rec_nume IS RECORD(ename emp.ename%TYPE);
    TYPE rc_nume IS REF CURSOR RETURN rec_nume;
    cv_grad rc_grad;
    cv_nume rc_nume;

    v_ename emp.ename%type;
    v_grade salgrade.grade%type;
    v_losal SALGRADE.loSAL%type;
    v_hisal salgrade.hisal%type;
BEGIN
    OPEN cv_grad FOR
        SELECT  grade, hisal, losal
        FROM SALGRADE
        WHERE grade = 2;
    LOOP
        FETCH cv_grad into v_grade, v_hisal, v_losal;
        EXIT WHEN cv_grad%notfound;

            OPEN cv_nume FOR
                SELECT ename
                FROM emp
                WHERE sal < v_hisal AND sal>v_losal;
            LOOP
                FETCH cv_nume into v_ename;
                EXIT WHEN cv_nume%notfound;

            dbms_output.put_line('Nume : '||v_ename||' grad: '||v_grade||' sal max: '||v_hisal);
        END LOOP;
        close cv_nume;

         END LOOP;
       CLOSE cv_grad;
end;
/


-------------------------------------2---------------------------------------
CREATE OR REPLACE PACKAGE test_trigger IS
    procedure p2 (proc_ordid TRANSFER.ORDID%type, v_shipdate TRANSFER.SHIPDATE%type);
END test_trigger;
/

CREATE OR REPLACE PACKAGE BODY test_trigger IS
    procedure p2(proc_ordid TRANSFER.ORDID%type, v_shipdate TRANSFER.SHIPDATE%type)
    IS
    v2_shipdate TRANSFER.SHIPDATE%type;
    v_ordid TRANSFER.ORDID%type;
    --v_itemOrdid item.ordid%type;
    v_actualprice ITEM.ACTUALPRICE%type;
    v_itemtot ITEM.ITEMTOT%type;
    v_total ORD.TOTAL%type;
    --v_ordOrdid ORD.ORDID%type;
    v_itemid ITEM.ITEMID%type;
     exceptie exception;
BEGIN
    SELECT ordid, shipdate into v_ordid, v2_shipdate from transfer where ordid=proc_ordid;
    SELECT itemid, actualprice, itemtot into v_itemid, v_actualprice, v_itemtot  from item where ordid=proc_ordid;
    SELECT total into v_total from ord where ordid=proc_ordid;

    dbms_output.put_line('Ord id'||proc_ordid|| ', shipdate'||v2_shipdate);
    dbms_output.put_line('ITEm id'|| v_itemid || 'item tot' || v_itemtot || 'AC' || v_actualprice);
    dbms_output.put_line('Total' || v_total);


    UPDATE transfer
        SET shipdate = v_shipdate
        WHERE ordid=proc_ordid;
    if(sql%rowcount=0) then
        raise exceptie;
    end if;
    exception
        when exceptie then
            dbms_output.put_line('ordid nu exista');

   SELECT ordid, shipdate into v_ordid, v2_shipdate from transfer where ordid=proc_ordid;
    SELECT itemid, actualprice, itemtot into v_itemid, v_actualprice, v_itemtot  from item where ordid=proc_ordid;
    SELECT total into v_total from ord where ordid=proc_ordid;

    dbms_output.put_line('Ord id'||proc_ordid|| ', shipdate'||v2_shipdate);
    dbms_output.put_line('ITEm id'|| v_itemid || 'item tot' || v_itemtot || 'AC' || v_actualprice);
    dbms_output.put_line('Total' || v_total);

    END p2;
END test_trigger;
/
 begin
    TEST_TRIGGER.P2(601, '10-JAN-86');
 end;
 /
 rollback;



 -------------------------------------------1------------------------------------
 alter trigger monitor enable;
 alter trigger monitor disable;
 CREATE OR REPLACE TRIGGER monitor
BEFORE UPDATE ON transfer
FOR EACH ROW
DECLARE
    v_ordid ord.ordid%type;
    v_qty item.qty%type;
    v_itemid item.itemid%type;
    v_actualprice item.actualprice%type;
    creditlimit number(30):=1500;
    v_total ord.total%TYPE;
    CURSOR maxCant IS
        SELECT max(qty), itemid, ordid
        FROM item;
    CURSOR totalCalc (v_ordidord item.ordid%type)IS
        SELECT total
        FROM ord
        where ordid=v_ordidord;

BEGIN
    IF UPDATING ('SHIPDATE') THEN
        IF :old.shipdate > :new.shipdate then --sau cu 2 variabile in care se scade nr de zile
                                              --pt old si new si se compara
            OPEN maxCant;
            LOOP
                FETCH maxCant into v_qty, v_itemid, v_ordid;
                EXIT WHEN maxCant%NOTFOUND;

                UPDATE item
                    SET ACTUALPRICE = ACTUALPRICE - ACTUALPRICE*0.3   where itemid = v_itemid;
                UPDATE item
                    SET ITEMTOT = qty * actualprice where itemid = v_itemid;
               -- UPDATE ord
                   -- SET total = sum(item.itemtot) where ordid = v_ordid;
                    OPEN totalCalc(v_ordid);
                    LOOP
                        FETCH totalCalc INTO v_total;
                        EXIT WHEN totalCalc%NOTFOUND;
                        IF (v_total > creditlimit) then --in ce tabela e creditlimit??
                            RAISE_APPLICATION_ERROR(-22, 'eroare');
                        END IF;
                    END LOOP;
            END LOOP;
        END IF;
    END IF;
END;
/