te_employee.pkb

OReilly Oracle PL SQL Programming第4版源代码

CREATE OR REPLACE PACKAGE BODY te_employee
--//-----------------------------------------------------------------------
--//  ** Table Encapsulator for "employee"
--//-----------------------------------------------------------------------
--//  (c) COPYRIGHT Personnel Policies, Inc. 1999.
--//               All rights reserved.
--//
--//  No part of this copyrighted work may be reproduced, modified,
--//  or distributed in any form or by any means without the prior
--//  written permission of Personnel Policies, Inc..
--//-----------------------------------------------------------------------
--//  Stored In:  te_employee.pkb
--//  Created On: September 05, 1999 20:14:04
--//  Created By: SCOTT
--//  PL/Generator Version: PRO-99.2.1
--//-----------------------------------------------------------------------
IS
   --// Package name and program name globals --//
   c_pkgname VARCHAR2(30) := 'te_employee';
   g_progname VARCHAR2(30) := NULL;

   --// Update Flag private data structures. --//
   TYPE frcflg_rt IS RECORD (
      last_name CHAR(1),
      first_name CHAR(1),
      middle_initial CHAR(1),
      job_id CHAR(1),
      manager_id CHAR(1),
      hire_date CHAR(1),
      salary CHAR(1),
      commission CHAR(1),
      department_id CHAR(1),
      changed_by CHAR(1),
      changed_on CHAR(1)
      );

   frcflg frcflg_rt;
   emptyfrc frcflg_rt;
   c_set CHAR(1) := 'Y';
   c_noset CHAR(1) := 'N';

   TYPE tab_tabtype IS TABLE OF allcols_rt INDEX BY BINARY_INTEGER;
   loadtab tab_tabtype;
   FUNCTION version RETURN VARCHAR2
   IS
   BEGIN
      RETURN '7.09';
   END;

--// Private Modules //--

   --// For Dynamic SQL operations; currently unused. //--
   PROCEDURE initcur (cur_inout IN OUT INTEGER)
   IS
   BEGIN
      IF NOT DBMS_SQL.IS_OPEN (cur_inout)
      THEN
         cur_inout := DBMS_SQL.OPEN_CURSOR;
      END IF;
   EXCEPTION
      WHEN invalid_cursor
      THEN
         cur_inout := DBMS_SQL.OPEN_CURSOR;
   END;

   PROCEDURE start_program (nm IN VARCHAR2, msg IN VARCHAR2 := NULL) IS
   BEGIN
      g_progname := nm;
      IF PLVxmn.traceactive (nm, PLVxmn.l_start)
      THEN
         PLVxmn.trace (nm, PLVxmn.l_start, msg, override => TRUE);
      END IF;
   END;

   PROCEDURE end_program IS
   BEGIN
      g_progname := NULL;
   END;

--// Cursor management procedures //--

   --// Open the cursors with some options. //--
   PROCEDURE open_compforpky_cur (
      employee_id_in IN employee.employee_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      start_program ('open_compforpky_cur');

      IF compforpky_cur%ISOPEN AND v_close
      THEN
         CLOSE compforpky_cur;
      ELSIF compforpky_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
          OPEN compforpky_cur (
             employee_id_in
             );
      END IF;

      end_program;
   END;

   PROCEDURE open_compbypky_cur (
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF compbypky_cur%ISOPEN AND v_close
      THEN
         CLOSE compbypky_cur;
      ELSIF compbypky_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN compbypky_cur;
      END IF;
   END;

   PROCEDURE open_emp_dept_lookup_comp_cur (
      department_id_in IN employee.department_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF emp_dept_lookup_comp_cur%ISOPEN AND v_close
      THEN
         CLOSE emp_dept_lookup_comp_cur;
      ELSIF emp_dept_lookup_comp_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN emp_dept_lookup_comp_cur (
            department_id_in
            );
      END IF;
   END;

   PROCEDURE open_emp_job_lookup_comp_cur (
      job_id_in IN employee.job_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF emp_job_lookup_comp_cur%ISOPEN AND v_close
      THEN
         CLOSE emp_job_lookup_comp_cur;
      ELSIF emp_job_lookup_comp_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN emp_job_lookup_comp_cur (
            job_id_in
            );
      END IF;
   END;

   PROCEDURE open_emp_mgr_lookup_comp_cur (
      manager_id_in IN employee.manager_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF emp_mgr_lookup_comp_cur%ISOPEN AND v_close
      THEN
         CLOSE emp_mgr_lookup_comp_cur;
      ELSIF emp_mgr_lookup_comp_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN emp_mgr_lookup_comp_cur (
            manager_id_in
            );
      END IF;
   END;
   --// Open the cursors with some options. //--
   PROCEDURE open_nameforpky_cur (
      employee_id_in IN employee.employee_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      start_program ('open_nameforpky_cur');

      IF nameforpky_cur%ISOPEN AND v_close
      THEN
         CLOSE nameforpky_cur;
      ELSIF nameforpky_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
          OPEN nameforpky_cur (
             employee_id_in
             );
      END IF;

      end_program;
   END;

   PROCEDURE open_namebypky_cur (
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF namebypky_cur%ISOPEN AND v_close
      THEN
         CLOSE namebypky_cur;
      ELSIF namebypky_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN namebypky_cur;
      END IF;
   END;

   PROCEDURE open_emp_dept_lookup_name_cur (
      department_id_in IN employee.department_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF emp_dept_lookup_name_cur%ISOPEN AND v_close
      THEN
         CLOSE emp_dept_lookup_name_cur;
      ELSIF emp_dept_lookup_name_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN emp_dept_lookup_name_cur (
            department_id_in
            );
      END IF;
   END;

   PROCEDURE open_emp_job_lookup_name_cur (
      job_id_in IN employee.job_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF emp_job_lookup_name_cur%ISOPEN AND v_close
      THEN
         CLOSE emp_job_lookup_name_cur;
      ELSIF emp_job_lookup_name_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN emp_job_lookup_name_cur (
            job_id_in
            );
      END IF;
   END;

   PROCEDURE open_emp_mgr_lookup_name_cur (
      manager_id_in IN employee.manager_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF emp_mgr_lookup_name_cur%ISOPEN AND v_close
      THEN
         CLOSE emp_mgr_lookup_name_cur;
      ELSIF emp_mgr_lookup_name_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN emp_mgr_lookup_name_cur (
            manager_id_in
            );
      END IF;
   END;
   --// Open the cursors with some options. //--
   PROCEDURE open_allforpky_cur (
      employee_id_in IN employee.employee_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      start_program ('open_allforpky_cur');

      IF allforpky_cur%ISOPEN AND v_close
      THEN
         CLOSE allforpky_cur;
      ELSIF allforpky_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
          OPEN allforpky_cur (
             employee_id_in
             );
      END IF;

      end_program;
   END;

   PROCEDURE open_allbypky_cur (
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF allbypky_cur%ISOPEN AND v_close
      THEN
         CLOSE allbypky_cur;
      ELSIF allbypky_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN allbypky_cur;
      END IF;
   END;

   PROCEDURE open_emp_dept_lookup_all_cur (
      department_id_in IN employee.department_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF emp_dept_lookup_all_cur%ISOPEN AND v_close
      THEN
         CLOSE emp_dept_lookup_all_cur;
      ELSIF emp_dept_lookup_all_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN emp_dept_lookup_all_cur (
            department_id_in
            );
      END IF;
   END;

   PROCEDURE open_emp_job_lookup_all_cur (
      job_id_in IN employee.job_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF emp_job_lookup_all_cur%ISOPEN AND v_close
      THEN
         CLOSE emp_job_lookup_all_cur;
      ELSIF emp_job_lookup_all_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN emp_job_lookup_all_cur (
            job_id_in
            );
      END IF;
   END;

   PROCEDURE open_emp_mgr_lookup_all_cur (
      manager_id_in IN employee.manager_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      )
   IS
      v_close BOOLEAN := NVL (close_if_open, TRUE);
      v_open BOOLEAN := TRUE;
   BEGIN
      IF emp_mgr_lookup_all_cur%ISOPEN AND v_close
      THEN
         CLOSE emp_mgr_lookup_all_cur;
      ELSIF emp_mgr_lookup_all_cur%ISOPEN AND NOT v_close
      THEN
         v_open := FALSE;
      END IF;

      IF v_open
      THEN
         OPEN emp_mgr_lookup_all_cur (
            manager_id_in
            );
      END IF;
   END;

   --// Close the cursors if they are open. //--
   PROCEDURE close_compforpky_cur
   IS BEGIN
      IF compforpky_cur%ISOPEN
      THEN
         CLOSE compforpky_cur;
      END IF;
   END;

   PROCEDURE close_compbypky_cur
   IS BEGIN
      IF compbypky_cur%ISOPEN
      THEN
         CLOSE compbypky_cur;
      END IF;
   END;

   PROCEDURE close_emp_dept_lookup_comp_cur
   IS BEGIN
      IF emp_dept_lookup_comp_cur%ISOPEN
      THEN
         CLOSE emp_dept_lookup_comp_cur;
      END IF;
   END;

   PROCEDURE close_emp_job_lookup_comp_cur
   IS BEGIN
      IF emp_job_lookup_comp_cur%ISOPEN
      THEN
         CLOSE emp_job_lookup_comp_cur;
      END IF;
   END;

   PROCEDURE close_emp_mgr_lookup_comp_cur
   IS BEGIN
      IF emp_mgr_lookup_comp_cur%ISOPEN
      THEN
         CLOSE emp_mgr_lookup_comp_cur;
      END IF;
   END;

   PROCEDURE close_nameforpky_cur
   IS BEGIN
      IF nameforpky_cur%ISOPEN
      THEN
         CLOSE nameforpky_cur;
      END IF;
   END;

   PROCEDURE close_namebypky_cur
   IS BEGIN
      IF namebypky_cur%ISOPEN
      THEN
         CLOSE namebypky_cur;
      END IF;
   END;

   PROCEDURE close_emp_dept_lookup_name_cur
   IS BEGIN
      IF emp_dept_lookup_name_cur%ISOPEN
      THEN
         CLOSE emp_dept_lookup_name_cur;
      END IF;