L9

-- Problema 1
CREATE OR REPLACE PACKAGE prod_pack IS
  PROCEDURE add_prod(
    v_prod_id IN product.prodid%TYPE,
    v_prod_descrip IN product.descrip%TYPE);

  PROCEDURE del_prod(v_prod_id IN product.prodid%TYPE);

  PROCEDURE upd_prod(
    v_prod_id IN product.prodid%TYPE,
    v_prod_descrip IN product.descrip%TYPE);

  FUNCTION Q_PROD(id_prod IN product.prodid%TYPE) RETURN product.descrip%TYPE;

END prod_pack;

CREATE OR REPLACE PACKAGE BODY prod_pack IS
  PROCEDURE add_prod(
    v_prod_id IN product.prodid%TYPE,
    v_prod_descrip IN product.descrip%TYPE)
    IS
  BEGIN
    INSERT INTO product(prodid, descrip) VALUES (v_prod_id, v_prod_descrip);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      DBMS_OUTPUT.PUT_LINE('Eroare');
  END add_prod;


  PROCEDURE del_prod(
    v_prod_id IN product.prodid%TYPE)
    IS
    v_tmp product.prodid%TYPE;
    negasit EXCEPTION;
  BEGIN
    SELECT COUNT(*) INTO v_tmp FROM product WHERE prodid = v_prod_id;
    IF v_tmp = 0 THEN
      RAISE negasit;
    END IF;
    DELETE FROM product WHERE prodid = v_prod_id;
    DBMS_OUTPUT.PUT_LINE('Sters');
  EXCEPTION
    WHEN negasit THEN
      DBMS_OUTPUT.PUT_LINE('Neagsit');
  END del_prod;

  PROCEDURE upd_prod(
  v_prod_id IN product.prodid%TYPE,
  v_prod_descrip IN product.descrip%TYPE)
  IS
  BEGIN
    UPDATE product SET descrip = v_prod_descrip WHERE prodid = v_prod_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Eroare');
  END upd_prod;

  FUNCTION Q_PROD(id_prod IN product.prodid%TYPE) RETURN product.descrip%TYPE
  IS
    v_descript product.descrip%TYPE;
  BEGIN
    SELECT descrip INTO v_descript FROM product WHERE prodid = id_prod;
    RETURN v_descript;
  END;

END prod_pack;

--Problema 2
CREATE OR REPLACE PACKAGE emp_pack IS
  PROCEDURE new_emp(
    v_ename emp.ename%TYPE,
    v_job emp.job%TYPE:= 'SALESMAN',
    v_mgr emp.mgr%TYPE:= 7839,
    v_hiredate emp.hiredate%TYPE,
    v_sal emp.sal%TYPE:=1000,
    v_comm emp.comm%TYPE:=0,
    v_deptno emp.deptno%TYPE:=30);
END emp_pack;

CREATE OR REPLACE PACKAGE BODY emp_pack IS
  FUNCTION valid_deptno(id_departament IN dept.deptno%TYPE) RETURN NUMBER
  IS
    v_id_dep dept.deptno%TYPE;
  BEGIN
    SELECT deptno INTO v_id_dep FROM dept WHERE deptno = id_departament;
    IF ((id_departament > 0 ) AND (v_id_dep = id_departament)) THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  END;

  PROCEDURE new_emp(
    v_ename emp.ename%TYPE,
    v_job emp.job%TYPE:= 'SALESMAN',
    v_mgr emp.mgr%TYPE:= 7839,
    v_hiredate emp.hiredate%TYPE,
    v_sal emp.sal%TYPE:=1000,
    v_comm emp.comm%TYPE:=0,
    v_deptno emp.deptno%TYPE:=30)
  IS
    res NUMBER;
  BEGIN
    DBMS_OUTPUT.put_line('begin');
    IF (LENGTH(TRIM(v_ename)) > 0) THEN
      DBMS_OUTPUT.put_line(v_deptno);
      res:= valid_deptno(v_deptno);
      IF (res = 1) THEN
        DBMS_OUTPUT.put_line('valid');
        INSERT INTO emp (ename, job, mgr, hiredate, sal, comm, deptno) VALUES (v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno);
      ELSE
        DBMS_OUTPUT.put_line('Departamentul nu exista');
      END IF;
    ELSE
      DBMS_OUTPUT.put_line('Introduceti nume angajat');
    END IF;
  END;

END emp_pack;

--Problema 3
CREATE OR REPLACE PACKAGE chk_pack IS
  PROCEDURE chk_hiredate(d IN emp.hiredate%TYPE);
  PROCEDURE chk_dept_mgr(empno1 IN emp.empno%TYPE, empno2 IN emp.empno%TYPE) ;
END chk_pack;

CREATE OR REPLACE PACKAGE BODY chk_pack IS
  FUNCTION isdate(p_string IN VARCHAR2, p_fmt IN VARCHAR2 := NULL)
      RETURN VARCHAR2
    IS
        l_date DATE;
    BEGIN
        l_date := TO_DATE(p_string, p_fmt);
     RETURN 1;
  EXCEPTION
       WHEN OTHERS THEN
           RETURN 0;
  END;
  PROCEDURE chk_hiredate(d IN emp.hiredate%TYPE)
  IS
  BEGIN
    IF isdate(d) = 1 THEN
      IF d BETWEEN SYSDATE - NUMTOYMINTERVAL(50,'year') AND SYSDATE + NUMTOYMINTERVAL(3,'month') THEN
        sys.DBMS_OUTPUT.put_line('Data ok');
      END IF;
    ELSE
      sys.DBMS_OUTPUT.put_line('Data invalida');
    END IF;
  END;
  PROCEDURE chk_dept_mgr(empno1 IN emp.empno%TYPE, empno2 IN emp.empno%TYPE)
  IS
    v_empno1 emp.empno%TYPE;
    v_empno2 emp.empno%TYPE;
  BEGIN
    SELECT deptno INTO v_empno1 FROM emp WHERE empno = empno1;

    SELECT emp1.deptno INTO v_empno2
    FROM emp emp1
    inner join emp emp2 ON emp2.empno = emp1.mgr
    WHERE emp1.empno = empno2;

    IF v_empno1 IS NOT NULL THEN
      IF v_empno2 IS NOT NULL THEN
        IF v_empno1 = v_empno2 THEN
          sys.DBMS_OUTPUT.put_line('Verificare OK');
        END IF;
      ELSE
        sys.DBMS_OUTPUT.put_line('Managerul angajatului nu exista');
      END IF;
    ELSE
      sys.DBMS_OUTPUT.put_line('Angajatul nu exista');
    END IF;
  END;
END chk_pack;