SET SERVEROUTPUT ON

-- ////////////////////////////////////////////
-- Exemplu
-- ////////////////////////////////////////////

CREATE OR REPLACE PACKAGE pack_cur
IS
    CURSOR c1 IS SELECT empno FROM emp ORDER BY empno desc;
    PROCEDURE proc1_3rows;
END pack_cur;

CREATE OR REPLACE PACKAGE BODY pack_cur
IS
    v_empno NUMBER;
    PROCEDURE proc1_3rows
    IS
    BEGIN
        OPEN c1;
        LOOP
            FETCH c1 INTO v_empno;
            dbms_output.put_line('Id: ' || (v_empno));
            EXIT WHEN c1%ROWCOUNT>=3;
        END LOOP;
        CLOSE c1;
    END proc1_3rows;
END pack_cur;

EXECUTE pack_cur.proc1_3rows;

-- ////////////////////////////////////////////
-- Problema 1
-- ////////////////////////////////////////////

CREATE OR REPLACE PACKAGE PROD_PACK
IS
    PROCEDURE ADD_PROD(ProductId IN PRODUCT.PRODID%TYPE);
    PROCEDURE UPD_PROD(ProductId IN PRODUCT.PRODID%TYPE);
    PROCEDURE DEL_PROD(ProductId IN PRODUCT.PRODID%TYPE);
END PROD_PACK;

CREATE OR REPLACE PACKAGE BODY PROD_PACK
IS
    -- ADD_PROD
    PROCEDURE ADD_PROD(ProductId IN PRODUCT.PRODID%TYPE)
    AS
    BEGIN
        INSERT INTO PRODUCT VALUES(ProductId, 'new product');
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line('ID-ul exista deja');
    END ADD_PROD;

    -- UPD_PROD
    PROCEDURE UPD_PROD(ProductId IN PRODUCT.PRODID%TYPE)
    AS
        aux PRODUCT.DESCRIP%TYPE;
    BEGIN
        SELECT descrip
        INTO aux
        FROM PRODUCT
        WHERE prodid = ProductId;

        UPDATE PRODUCT
        SET descrip = 'new description'
        WHERE prodid = ProductId;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Nu exista un produs cu id-ul specificat!');
    END UPD_PROD;

    -- DEL_PROD
    PROCEDURE DEL_PROD(ProductId IN PRODUCT.PRODID%TYPE)
    AS
        aux PRODUCT.DESCRIP%TYPE;
    BEGIN
        SELECT descrip
        INTO aux
        FROM PRODUCT
        WHERE prodid = ProductId;

        DELETE FROM PRODUCT
        WHERE prodid = ProductId;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Nu exista un produs cu id-ul specificat!');
    END DEL_PROD;
END PROD_PACK;

EXECUTE PROD_PACK.ADD_PROD(33);
EXECUTE PROD_PACK.UPD_PROD(32);
EXECUTE PROD_PACK.DEL_PROD(33);

SELECT * FROM PRODUCT;

-- ////////////////////////////////////////////
-- Problema 2
-- ////////////////////////////////////////////

CREATE OR REPLACE PACKAGE EMP_PACK
IS
    PROCEDURE NEW_EMP(v_deptId IN emp.empno%TYPE);
END EMP_PACK;

CREATE OR REPLACE PACKAGE BODY EMP_PACK
IS
    FUNCTION VALID_DEPTNO(v_deptId IN emp.empno%TYPE, v_aux OUT NUMBER)
    RETURN NUMBER
    IS
    BEGIN
        SELECT COUNT(*)
        INTO v_aux
        FROM emp
        WHERE v_deptId=deptno;
        RETURN v_aux;
    END VALID_DEPTNO;

    PROCEDURE NEW_EMP(v_deptId IN emp.empno%TYPE)
    IS
        v_aux NUMBER(2);
    BEGIN
        IF VALID_DEPTNO(v_deptId, v_aux) > 0 THEN
            INSERT INTO emp VALUES(7802, 'Joshua', 'SALESMAN', 7698, '17-NOV-80', 5000, null, v_deptId, null);
        ELSE
            dbms_output.put_line('Departamentul nu exista');
        END IF;
    END NEW_EMP;
END EMP_PACK;

EXECUTE EMP_PACK.NEW_EMP(99);
EXECUTE EMP_PACK.NEW_EMP(30);
SELECT * FROM emp;