CREATE OR REPLACE
PACKAGE empdept_maint
AS
/******************************************************************************
|| System:     SPMTS
||
|| Description: 
||   Procedures used for an EMP-DEPT block query source and target.
||
|| Usage:
||   SLCT
||      Queries all records from the EMP-DEPT table join
|| 
||   INS
||      Inserts a record in the EMP-DEPT table.
||
||   UPD
||      Updates a record as specified by the empno parameter passed in.
||
||   DEL
||      Deletes the record specified by the empno parametere passed in.
||
||   LCK
||      Locks the record specified by the empno parametere passed in.
||      
******************************************************************************/
   TYPE emp_t IS RECORD(
      empno     emp.empno%TYPE,
      job       emp.job%TYPE,
      hiredate  emp.hiredate%TYPE,
      deptno    dept.deptno%TYPE,
      dname     dept.dname%TYPE);

   -- For SELECT
   TYPE rc_empdept IS REF CURSOR 
      RETURN emp_t;

   -- For the DML - a table of records
   TYPE t_empdept 
      IS TABLE OF emp_t 
      INDEX BY BINARY_INTEGER;
   --
   PROCEDURE slct( 
      p_empqry IN OUT rc_empdept);
   --
   PROCEDURE ins(
      p_emprec IN OUT t_empdept);
   --
   PROCEDURE upd(
      p_emprec IN OUT t_empdept);
   --
   PROCEDURE del(
      p_emprec IN OUT t_empdept);
   --
   PROCEDURE lck(
      p_emprec IN OUT t_empdept);
   --
END;
/

CREATE OR REPLACE
PACKAGE BODY empdept_maint 
AS

/*
||   Private procedures
*/
   FUNCTION dept_not_exists(
      p_dept_num  NUMBER)
      RETURN BOOLEAN
   IS
      CURSOR c_dept (
         p_dept dept.deptno%TYPE)
      IS
         SELECT 1
         FROM   dept
         WHERE  deptno = p_dept;
      --
      v_dummy    dept.deptno%TYPE;
      v_return   BOOLEAN;
   BEGIN
      OPEN c_dept(p_dept_num);
      FETCH c_dept INTO v_dummy;
      v_return := c_dept%NOTFOUND;
      CLOSE c_dept;
      RETURN v_return;
   END;

/*
||   Public procedures
*/
   PROCEDURE slct( 
      p_empqry IN OUT rc_empdept)
   IS
   BEGIN
      -- This can be more complex and include conditional
      -- logic to select from one source or another.
      OPEN p_empqry
      FOR
         SELECT emp.empno, 
                emp.job, 
                emp.hiredate, 
                emp.deptno, 
                dept.dname
         FROM   emp emp, dept dept
         WHERE  emp.deptno = dept.deptno;
   END;
--
--
   PROCEDURE ins(
      p_emprec IN OUT t_empdept)
   IS
      v_message  VARCHAR2(100);
   BEGIN
      FOR v_ct IN 1 .. p_emprec.count
      LOOP
         -- If the dept doesn't exist, insert it
         IF p_emprec(v_ct).deptno IS NOT NULL
         THEN
            IF dept_not_exists(p_emprec(v_ct).deptno)
            THEN
               v_message:= 'Insert of DEPT';
               INSERT INTO dept(
                       deptno, 
                       dname)
               VALUES (p_emprec(v_ct).deptno, 
                       p_emprec(v_ct).dname);
               -- exception handling goes here. This might even be a separate
               -- procedure to do the insert.
               -- 
            END IF;
            --
            v_message := 'Insert of EMP';
            INSERT INTO emp(
               empno,
               job,
               hiredate,
               deptno)
            VALUES (
               p_emprec(v_ct).empno, 
               p_emprec(v_ct).job, 
               p_emprec(v_ct).hiredate,
               p_emprec(v_ct).deptno);
            -- add an exception handler here
         END IF;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         raise_application_error(-20002, 'Error: Inserting record using ' ||
            'EMPDEPT_MAINT.INS. Inform Technical Support. '|| v_message);
   END;
--
--
   PROCEDURE upd(
      p_emprec IN OUT t_empdept)
   IS
      v_message   VARCHAR2(200);
   BEGIN
      -- update the records from the table of records
      -- check the dept table first
      -- You could also update the DEPT record with the values 
      -- in the DEPT columns
      -- For this exercise, just update the EMP columns
      FOR v_ct IN 1 .. p_emprec.count
      LOOP
         IF dept_not_exists(p_emprec(v_ct).deptno)
         THEN
            v_message:= 'Insert of DEPT';
            INSERT INTO dept(
                   deptno, 
                   dname)
            VALUES (p_emprec(v_ct).deptno, 
                    p_emprec(v_ct).dname);
            -- exception handling goes here. This might even be a separate
            -- procedure to do the insert.
            -- 
         END IF;
         --
         UPDATE emp
         SET    job = p_emprec(v_ct).job,
                hiredate = p_emprec(v_ct).hiredate,
                deptno = p_emprec(v_ct).deptno
         WHERE  empno = p_emprec(v_ct).empno;
      END LOOP;
   END;
--
--
   PROCEDURE del(
      p_emprec IN OUT t_empdept)
   IS
   BEGIN
      FOR v_ct IN 1 .. p_emprec.count
      LOOP
         DELETE FROM emp 
         WHERE  empno = p_emprec(v_ct).empno;
      END LOOP;
   END;

   PROCEDURE lck(
      p_emprec IN OUT t_empdept)
   IS
      v_empno    emp.empno%TYPE;
   BEGIN
      FOR v_ct IN 1 .. p_emprec.count
      LOOP
         SELECT empno
         INTO   v_empno
         FROM   emp
         WHERE  empno = p_emprec(v_ct).empno
         FOR UPDATE;
      END LOOP;
   END;

END;
/

CREATE VIEW emp_v AS
SELECT ename, LENGTH(ename) AS ename_length
FROM emp
/

SELECT v.ename, v.ename_length
FROM emp_v v
ORDER BY v.ename
/

SELECT v.ename, v.ename_length
FROM ( SELECT ename, LENGTH(ename) AS ename_length FROM emp ) v
ORDER BY v.ename
/