te_employee.pks

OReilly Oracle PL SQL Programming第4版源代码

CREATE OR REPLACE PACKAGE 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.pks
--//  Created On: September 05, 1999 20:14:04
--//  Created By: SCOTT
--//  PL/Generator Version: PRO-99.2.1
--//-----------------------------------------------------------------------
IS
--// Data Structures //--
   TYPE pky_rt IS RECORD (
      employee_id employee.employee_id%TYPE
      );

   --// Modified version of %ROWTYPE for table with subset of columns //--
   TYPE allcols_rt IS RECORD (
      employee_id employee.employee_id%TYPE,
      last_name employee.last_name%TYPE,
      first_name employee.first_name%TYPE,
      middle_initial employee.middle_initial%TYPE,
      job_id employee.job_id%TYPE,
      manager_id employee.manager_id%TYPE,
      hire_date employee.hire_date%TYPE,
      salary employee.salary%TYPE,
      commission employee.commission%TYPE,
      department_id employee.department_id%TYPE,
      changed_by employee.changed_by%TYPE,
      changed_on employee.changed_on%TYPE
      );

   TYPE pky_cvt IS REF CURSOR RETURN pky_rt;
   TYPE employee_cvt IS REF CURSOR RETURN allcols_rt;

   TYPE i_employee_name_rt IS RECORD (
      last_name employee.last_name%TYPE,
      first_name employee.first_name%TYPE,
      middle_initial employee.middle_initial%TYPE
      );

   TYPE i_employee_name_cvt IS REF CURSOR RETURN i_employee_name_rt;
   CURSOR totsal_cur
   IS
      SELECT department_id, SUM(salary) total_salary
        FROM employee
       GROUP BY department_id;


--// Cursors //--

   CURSOR compbypky_cur
   IS
      SELECT
         employee_id, salary, commission
        FROM employee
       ORDER BY
         employee_id
      ;

   CURSOR compforpky_cur (
      employee_id_in IN employee.employee_id%TYPE
      )
   IS
      SELECT
         employee_id, salary, commission
        FROM employee
       WHERE
         employee_id = compforpky_cur.employee_id_in
      ;

   --// Specified columns, all rows for this foreign key. //--
   CURSOR emp_dept_lookup_comp_cur (
      department_id_in IN employee.department_id%TYPE
      )
   IS
      SELECT
         employee_id, salary, commission
        FROM employee
       WHERE
          department_id = emp_dept_lookup_comp_cur.department_id_in
      ;

   --// Specified columns, all rows for this foreign key. //--
   CURSOR emp_job_lookup_comp_cur (
      job_id_in IN employee.job_id%TYPE
      )
   IS
      SELECT
         employee_id, salary, commission
        FROM employee
       WHERE
          job_id = emp_job_lookup_comp_cur.job_id_in
      ;

   --// Specified columns, all rows for this foreign key. //--
   CURSOR emp_mgr_lookup_comp_cur (
      manager_id_in IN employee.manager_id%TYPE
      )
   IS
      SELECT
         employee_id, salary, commission
        FROM employee
       WHERE
          manager_id = emp_mgr_lookup_comp_cur.manager_id_in
      ;

   CURSOR namebypky_cur
   IS
      SELECT
         last_name || ', ' || first_name full_name
        FROM employee
       ORDER BY
         employee_id
      ;

   CURSOR nameforpky_cur (
      employee_id_in IN employee.employee_id%TYPE
      )
   IS
      SELECT
         last_name || ', ' || first_name full_name
        FROM employee
       WHERE
         employee_id = nameforpky_cur.employee_id_in
      ;

   --// Specified columns, all rows for this foreign key. //--
   CURSOR emp_dept_lookup_name_cur (
      department_id_in IN employee.department_id%TYPE
      )
   IS
      SELECT
         last_name || ', ' || first_name full_name
        FROM employee
       WHERE
          department_id = emp_dept_lookup_name_cur.department_id_in
      ;

   --// Specified columns, all rows for this foreign key. //--
   CURSOR emp_job_lookup_name_cur (
      job_id_in IN employee.job_id%TYPE
      )
   IS
      SELECT
         last_name || ', ' || first_name full_name
        FROM employee
       WHERE
          job_id = emp_job_lookup_name_cur.job_id_in
      ;

   --// Specified columns, all rows for this foreign key. //--
   CURSOR emp_mgr_lookup_name_cur (
      manager_id_in IN employee.manager_id%TYPE
      )
   IS
      SELECT
         last_name || ', ' || first_name full_name
        FROM employee
       WHERE
          manager_id = emp_mgr_lookup_name_cur.manager_id_in
      ;

   CURSOR allbypky_cur
   IS
      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
       ORDER BY
         employee_id
      ;

   CURSOR allforpky_cur (
      employee_id_in IN employee.employee_id%TYPE
      )
   IS
      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
       WHERE
         employee_id = allforpky_cur.employee_id_in
      ;

   --// Specified columns, all rows for this foreign key. //--
   CURSOR emp_dept_lookup_all_cur (
      department_id_in IN employee.department_id%TYPE
      )
   IS
      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
       WHERE
          department_id = emp_dept_lookup_all_cur.department_id_in
      ;

   --// Specified columns, all rows for this foreign key. //--
   CURSOR emp_job_lookup_all_cur (
      job_id_in IN employee.job_id%TYPE
      )
   IS
      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
       WHERE
          job_id = emp_job_lookup_all_cur.job_id_in
      ;

   --// Specified columns, all rows for this foreign key. //--
   CURSOR emp_mgr_lookup_all_cur (
      manager_id_in IN employee.manager_id%TYPE
      )
   IS
      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
       WHERE
          manager_id = emp_mgr_lookup_all_cur.manager_id_in
      ;

--// 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
      );

   PROCEDURE open_compbypky_cur (
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE open_emp_dept_lookup_comp_cur (
      department_id_in IN employee.department_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE open_emp_job_lookup_comp_cur (
      job_id_in IN employee.job_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE open_emp_mgr_lookup_comp_cur (
      manager_id_in IN employee.manager_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );
   PROCEDURE open_nameforpky_cur (
      employee_id_in IN employee.employee_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE open_namebypky_cur (
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE open_emp_dept_lookup_name_cur (
      department_id_in IN employee.department_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE open_emp_job_lookup_name_cur (
      job_id_in IN employee.job_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE open_emp_mgr_lookup_name_cur (
      manager_id_in IN employee.manager_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );
   PROCEDURE open_allforpky_cur (
      employee_id_in IN employee.employee_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE open_allbypky_cur (
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE open_emp_dept_lookup_all_cur (
      department_id_in IN employee.department_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE open_emp_job_lookup_all_cur (
      job_id_in IN employee.job_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE open_emp_mgr_lookup_all_cur (
      manager_id_in IN employee.manager_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

   --// Close the cursors if they are open. //--
   PROCEDURE close_compforpky_cur;
   PROCEDURE close_compbypky_cur;
   PROCEDURE close_emp_dept_lookup_comp_cur;
   PROCEDURE close_emp_job_lookup_comp_cur;
   PROCEDURE close_emp_mgr_lookup_comp_cur;
   PROCEDURE close_nameforpky_cur;
   PROCEDURE close_namebypky_cur;
   PROCEDURE close_emp_dept_lookup_name_cur;
   PROCEDURE close_emp_job_lookup_name_cur;
   PROCEDURE close_emp_mgr_lookup_name_cur;
   PROCEDURE close_allforpky_cur;
   PROCEDURE close_allbypky_cur;
   PROCEDURE close_emp_dept_lookup_all_cur;
   PROCEDURE close_emp_job_lookup_all_cur;
   PROCEDURE close_emp_mgr_lookup_all_cur;
   PROCEDURE closeall;

--// Functions returning Cursor Variables for each cursor //--

   --// Specified columns for all rows //--
   FUNCTION allbypky$cv RETURN employee_cvt;

   --// Specified columns for one row //--
   FUNCTION allforpky$cv (