te_employee.pkb

OReilly Oracle PL SQL Programming第4版源代码

         rec_in.hire_date,
         rec_in.salary,
         rec_in.commission,
         rec_in.department_id,
         rec_in.changed_by,
         rec_in.changed_on,
         rowcount_out,
         reset_in);
   END upd;

   --// Update procedure for hire_date. --//
   PROCEDURE upd$hire_date (
      employee_id_in IN employee.employee_id%TYPE,
      hire_date_in IN employee.hire_date%TYPE,
      rowcount_out OUT INTEGER
      )
   IS
   BEGIN
      IF PLVxmn.traceactive ('upd$hire_date', PLVxmn.l_upd)
      THEN
         PLVxmn.trace ('upd$hire_date', PLVxmn.l_upd,
            'employee_id='|| employee_id_in
            || 'hire_date='|| hire_date_in
            , override => TRUE);
      END IF;
      UPDATE employee SET hire_date = TRUNC(hire_date_in)
       WHERE
          employee_id = employee_id_in
         ;
      rowcount_out := SQL%ROWCOUNT;
   EXCEPTION
      WHEN OTHERS
      THEN
         plvexc.recnstop;
   END upd$hire_date;

   --// Update procedure for hire_date using records. --//
   PROCEDURE upd$hire_date (
      rec_in IN pky_rt,
      hire_date_in IN employee.hire_date%TYPE,
      rowcount_out OUT INTEGER
      )
   IS
   BEGIN
      upd$hire_date (
         rec_in.employee_id,
         hire_date_in,
         rowcount_out
         );
   END upd$hire_date;

   --// Update procedure for salary. --//
   PROCEDURE upd$salary (
      employee_id_in IN employee.employee_id%TYPE,
      salary_in IN employee.salary%TYPE,
      rowcount_out OUT INTEGER
      )
   IS
   BEGIN
      IF PLVxmn.traceactive ('upd$salary', PLVxmn.l_upd)
      THEN
         PLVxmn.trace ('upd$salary', PLVxmn.l_upd,
            'employee_id='|| employee_id_in
            || 'salary='|| salary_in
            , override => TRUE);
      END IF;
      UPDATE employee SET salary = salary_in
       WHERE
          employee_id = employee_id_in
         ;
      rowcount_out := SQL%ROWCOUNT;
   EXCEPTION
      WHEN OTHERS
      THEN
         plvexc.recnstop;
   END upd$salary;

   --// Update procedure for salary using records. --//
   PROCEDURE upd$salary (
      rec_in IN pky_rt,
      salary_in IN employee.salary%TYPE,
      rowcount_out OUT INTEGER
      )
   IS
   BEGIN
      upd$salary (
         rec_in.employee_id,
         salary_in,
         rowcount_out
         );
   END upd$salary;

--// Insert Processing --//

   --// Initialize record with default values. --//
   FUNCTION initrec (allnull IN BOOLEAN := FALSE) RETURN allcols_rt
   IS
      retval allcols_rt;
   BEGIN
      IF allnull THEN NULL; /* Default values are NULL already. */
      ELSE
         retval.employee_id := NULL;
         retval.last_name := NULL;
         retval.first_name := NULL;
         retval.middle_initial := NULL;
         retval.job_id := NULL;
         retval.manager_id := NULL;
         retval.hire_date := SYSDATE;
         retval.salary := NULL;
         retval.commission := NULL;
         retval.department_id := NULL;
         retval.changed_by := USER;
         retval.changed_on := SYSDATE;
      END IF;
      RETURN retval;
   END;

   --// Initialize record with default values. --//
   PROCEDURE initrec (
      rec_inout IN OUT allcols_rt,
      allnull IN BOOLEAN := FALSE)
   IS
   BEGIN
      rec_inout := initrec;
   END;

   PROCEDURE ins$ins (
      employee_id_in IN employee.employee_id%TYPE,
      last_name_in IN employee.last_name%TYPE DEFAULT NULL,
      first_name_in IN employee.first_name%TYPE DEFAULT NULL,
      middle_initial_in IN employee.middle_initial%TYPE DEFAULT NULL,
      job_id_in IN employee.job_id%TYPE DEFAULT NULL,
      manager_id_in IN employee.manager_id%TYPE DEFAULT NULL,
      hire_date_in IN employee.hire_date%TYPE DEFAULT SYSDATE,
      salary_in IN employee.salary%TYPE DEFAULT NULL,
      commission_in IN employee.commission%TYPE DEFAULT NULL,
      department_id_in IN employee.department_id%TYPE DEFAULT NULL,
      changed_by_in IN employee.changed_by%TYPE DEFAULT USER,
      changed_on_in IN employee.changed_on%TYPE DEFAULT SYSDATE,
      upd_on_dup IN BOOLEAN := FALSE
      )
   IS
   BEGIN
      IF PLVxmn.traceactive ('ins', PLVxmn.l_ins)
      THEN
         PLVxmn.trace ('ins', PLVxmn.l_ins,
            '-employee_id='|| employee_id_in ||
            '-last_name='|| last_name_in ||
            '-first_name='|| first_name_in ||
            '-middle_initial='|| middle_initial_in ||
            '-job_id='|| job_id_in ||
            '-manager_id='|| manager_id_in ||
            '-hire_date='|| hire_date_in ||
            '-salary='|| salary_in ||
            '-commission='|| commission_in ||
            '-department_id='|| department_id_in ||
            '-changed_by='|| changed_by_in ||
            '-changed_on='|| changed_on_in
            , override => TRUE);
      END IF;
      validate (
         employee_id_in,
         TRUNC(hire_date_in),
         salary_in,
         department_id_in,
         USER,
         SYSDATE,
         TRUE
         );
      INSERT INTO employee (
         employee_id
         ,last_name
         ,first_name
         ,middle_initial
         ,job_id
         ,manager_id
         ,hire_date
         ,salary
         ,commission
         ,department_id
         ,changed_by
         ,changed_on
         )
      VALUES (
         employee_id_in
         ,UPPER(last_name_in)
         ,UPPER(first_name_in)
         ,UPPER(middle_initial_in)
         ,job_id_in
         ,manager_id_in
         ,TRUNC(hire_date_in)
         ,salary_in
         ,commission_in
         ,department_id_in
         ,USER
         ,SYSDATE
         );
   EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
         IF NOT NVL (upd_on_dup, FALSE)
         THEN
            RAISE;
         ELSE
            DECLARE
               v_errm VARCHAR2(2000) := SQLERRM;
               v_rowcount INTEGER;
               dotloc INTEGER;
               leftloc INTEGER;
               c_owner ALL_CONSTRAINTS.OWNER%TYPE;
               c_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
            BEGIN
               dotloc := INSTR (v_errm,'.');
               leftloc := INSTR (v_errm,'(');
               c_owner :=SUBSTR (v_errm, leftloc+1, dotloc-leftloc-1);
               c_name := SUBSTR (v_errm, dotloc+1, INSTR (v_errm,')')-dotloc-1);

               --// Duplicate based on primary key //--
               IF 'EMP_PK' = c_name AND 'SCOTT' = c_owner
               THEN
                  upd (
                     employee_id_in,
                     last_name_in,
                     first_name_in,
                     middle_initial_in,
                     job_id_in,
                     manager_id_in,
                     hire_date_in,
                     salary_in,
                     commission_in,
                     department_id_in,
                     changed_by_in,
                     changed_on_in,
                     v_rowcount,
                     FALSE
                     );
               ELSE
                  --// Unique index violation. Cannot recover... //--
                  RAISE;
               END IF;
            END;
         END IF;
      WHEN OTHERS
      THEN
         plvexc.recnstop;
   END ins$ins;

   --// Insert 1: with individual fields and return primary key //--
   PROCEDURE ins (
      last_name_in IN employee.last_name%TYPE DEFAULT NULL,
      first_name_in IN employee.first_name%TYPE DEFAULT NULL,
      middle_initial_in IN employee.middle_initial%TYPE DEFAULT NULL,
      job_id_in IN employee.job_id%TYPE DEFAULT NULL,
      manager_id_in IN employee.manager_id%TYPE DEFAULT NULL,
      hire_date_in IN employee.hire_date%TYPE DEFAULT SYSDATE,
      salary_in IN employee.salary%TYPE DEFAULT NULL,
      commission_in IN employee.commission%TYPE DEFAULT NULL,
      department_id_in IN employee.department_id%TYPE DEFAULT NULL,
      changed_by_in IN employee.changed_by%TYPE DEFAULT USER,
      changed_on_in IN employee.changed_on%TYPE DEFAULT SYSDATE,
      employee_id_out IN OUT employee.employee_id%TYPE,
      upd_on_dup IN BOOLEAN := FALSE
      )
   IS
      v_pky INTEGER := nextpky;
   BEGIN
      ins$ins (
         v_pky,
         last_name_in,
         first_name_in,
         middle_initial_in,
         job_id_in,
         manager_id_in,
         hire_date_in,
         salary_in,
         commission_in,
         department_id_in,
         changed_by_in,
         changed_on_in,
         upd_on_dup
         );
      employee_id_out := v_pky;
   END;

   --// Insert 2: with record, returning primary key. //--
   PROCEDURE ins (
      rec_in IN allcols_rt,
      employee_id_out IN OUT employee.employee_id%TYPE,
      upd_on_dup IN BOOLEAN := FALSE
      )
   IS
      v_pky INTEGER := nextpky;
   BEGIN
      ins$ins (
         v_pky,
         rec_in.last_name,
         rec_in.first_name,
         rec_in.middle_initial,
         rec_in.job_id,
         rec_in.manager_id,
         rec_in.hire_date,
         rec_in.salary,
         rec_in.commission,
         rec_in.department_id,
         rec_in.changed_by,
         rec_in.changed_on,
         upd_on_dup
         );
      employee_id_out := v_pky;
   END;

--// Delete Processing --//

   PROCEDURE del (
      employee_id_in IN employee.employee_id%TYPE,
      rowcount_out OUT INTEGER)
   IS
   BEGIN
      IF PLVxmn.traceactive ('del', PLVxmn.l_del)
      THEN
         PLVxmn.trace ('del', PLVxmn.l_del,
            '-employee_id='|| employee_id_in
            , override => TRUE);
      END IF;
      DELETE FROM employee
       WHERE
          employee_id = employee_id_in
         ;
      rowcount_out := SQL%ROWCOUNT;
      IF SQL%ROWCOUNT > 0
      THEN
         loadtab.DELETE (employee_id_in);
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         plvexc.recnstop;
   END del;

   --// Record-based delete --//
   PROCEDURE del
      (rec_in IN pky_rt,
      rowcount_out OUT INTEGER)
   IS
   BEGIN
      del (
         rec_in.employee_id,
         rowcount_out);
   END del;

   PROCEDURE del (rec_in IN allcols_rt,
      rowcount_out OUT INTEGER)
   IS
   BEGIN
      del (
         rec_in.employee_id,
         rowcount_out);
   END del;

   --// Delete all records for foreign key EMP_DEPT_LOOKUP. //--
   PROCEDURE delby_emp_dept_lookup (
      department_id_in IN employee.department_id%TYPE,
      rowcount_out OUT INTEGER
      )
   IS
   BEGIN
      IF PLVxmn.traceactive ('delby_emp_dept_lookup', PLVxmn.l_del)
      THEN
         PLVxmn.trace ('delby_emp_dept_lookup', PLVxmn.l_del,
            '-department_id='|| department_id_in
            , override => TRUE);
      END IF;
      DELETE FROM employee
       WHERE
          department_id = delby_emp_dept_lookup.department_id_in
         ;
      rowcount_out := SQL%ROWCOUNT;
   EXCEPTION
      WHEN OTHERS
      THEN
         plvexc.recnstop;
   END delby_emp_dept_lookup;

   --// Delete all records for foreign key EMP_JOB_LOOKUP. //--
   PROCEDURE delby_emp_job_lookup (
      job_id_in IN employee.job_id%TYPE,
      rowcount_out OUT INTEGER
      )
   IS
   BEGIN
      IF PLVxmn.traceactive ('delby_emp_job_lookup', PLVxmn.l_del)
      THEN
         PLVxmn.trace ('delby_emp_job_lookup', PLVxmn.l_del,
            '-job_id='|| job_id_in
            , override => TRUE);
      END IF;
      DELETE FROM employee
       WHERE
          job_id = delby_emp_job_lookup.job_id_in
         ;
      rowcount_out := SQL%ROWCOUNT;
   EXCEPTION
      WHEN OTHERS
      THEN
         plvexc.recnstop;
   END delby_emp_job_lookup;

   --// Delete all records for foreign key EMP_MGR_LOOKUP. //--
   PROCEDURE delby_emp_mgr_lookup (
      manager_id_in IN employee.manager_id%TYPE,
      rowcount_out OUT INTEGER
      )
   IS
   BEGIN
      IF PLVxmn.traceactive ('delby_emp_mgr_lookup', PLVxmn.l_del)
      THEN
         PLVxmn.trace ('delby_emp_mgr_lookup', PLVxmn.l_del,
            '-manager_id='|| manager_id_in
            , override => TRUE);
      END IF;
      DELETE FROM employee
       WHERE
          manager_id = delby_emp_mgr_lookup.manager_id_in
         ;
      rowcount_out := SQL%ROWCOUNT;
   EXCEPTION
      WHEN OTHERS
      THEN
         plvexc.recnstop;
   END delby_emp_mgr_lookup;


   --// Program called by database initialization script to pin the package. //--
   PROCEDURE pinme
   IS
   BEGIN
      --// Doesn't do anything except cause the package to be loaded. //--
      NULL;
   END;


   PROCEDURE load_to_memory
   IS
   BEGIN
      loadtab.DELETE;
      FOR rec IN (
         SELECT
            employee_id,
            last_name,
            first_name,
            middle_initial,
            job_id,
            manager_id,
            hire_date,
            salary,
            commission,
            department_id,
            changed_by,
            changed_on
           FROM employee
           )
      LOOP
         loadtab (rec.employee_id) := rec;
      END LOOP;
   END;

   PROCEDURE showload (
      start_inout IN INTEGER := NULL,
      end_inout IN INTEGER := NULL
      )
   IS
      v_row PLS_INTEGER := loadtab.FIRST;
      v_last PLS_INTEGER := loadtab.LAST;
   BEGIN
      IF v_row IS NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('In-memory table for employee is empty!');
      ELSE
         IF start_inout > v_row
         THEN
            v_row := loadtab.NEXT (v_row-1);
         END IF;
         IF end_inout < v_last
         THEN
            v_row := loadtab.PRIOR (v_last+1);
         END IF;
         LOOP
            EXIT WHEN v_row >= v_last OR v_row IS NULL;
            DBMS_OUTPUT.PUT_LINE ('PKY Value/Row: ' || loadtab(v_row).employee_id);
            v_row := loadtab.NEXT (v_row);
         END LOOP;
      END IF;
   END;
--// Initialization section for the package. --//
BEGIN
   NULL; -- Placeholder.
   load_to_memory;
END te_employee;
/


/*======================================================================
| Supplement to the third edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2002 O'Reilly &
| Associates, Inc. To submit corrections or find more code samples visit
| http://www.oreilly.com/catalog/oraclep3/
*/