employee_qp.pks

OReilly Oracle PL SQL Programming第4版源代码

CREATE OR REPLACE PACKAGE EMPLOYEE_QP
/*
| Generated by or retrieved from Qnxo - DO NOT MODIFY!
| Qnxo - "Get it right, do it fast" - www.qnxo.com
| Qnxo Universal ID: 1aa62bc9-ca69-4405-9a6d-a74fb4d1a7ce
| Created On: April     04, 2005 07:31:56 Created By: QNXO_DEMO
*/
IS
   -- Return a record for one row of data for a primary key
   FUNCTION onerow (
      employee_id_in IN EMPLOYEE_TP.EMPLOYEE_ID_t
      )
   RETURN EMPLOYEE_TP.EMPLOYEE_rt;

   -- Return TRUE if a row exists for this primary key
   FUNCTION row_exists (
      employee_id_in IN EMPLOYEE_TP.EMPLOYEE_ID_t
      )
   RETURN BOOLEAN;

   -- Return a cursor variable for one row of data for a primary key.
   FUNCTION onerow_CV (
      employee_id_in IN EMPLOYEE_TP.EMPLOYEE_ID_t
      )
   RETURN EMPLOYEE_TP.EMPLOYEE_rc;
   -- Return all rows in a collection of records
   FUNCTION allrows RETURN EMPLOYEE_TP.EMPLOYEE_tc;

   -- Return all rows in collections for each column.
   PROCEDURE allrows (
      employee_id_out OUT EMPLOYEE_TP.EMPLOYEE_ID_cc,
      last_name_out OUT EMPLOYEE_TP.LAST_NAME_cc,
      first_name_out OUT EMPLOYEE_TP.FIRST_NAME_cc,
      middle_initial_out OUT EMPLOYEE_TP.MIDDLE_INITIAL_cc,
      job_id_out OUT EMPLOYEE_TP.JOB_ID_cc,
      manager_id_out OUT EMPLOYEE_TP.MANAGER_ID_cc,
      hire_date_out OUT EMPLOYEE_TP.HIRE_DATE_cc,
      salary_out OUT EMPLOYEE_TP.SALARY_cc,
      commission_out OUT EMPLOYEE_TP.COMMISSION_cc,
      department_id_out OUT EMPLOYEE_TP.DEPARTMENT_ID_cc,
      empno_out OUT EMPLOYEE_TP.EMPNO_cc,
      ename_out OUT EMPLOYEE_TP.ENAME_cc,
      created_by_out OUT EMPLOYEE_TP.CREATED_BY_cc,
      created_on_out OUT EMPLOYEE_TP.CREATED_ON_cc,
      changed_by_out OUT EMPLOYEE_TP.CHANGED_BY_cc,
      changed_on_out OUT EMPLOYEE_TP.CHANGED_ON_cc
   );

   -- Return all rows in table via ref cursor
   FUNCTION allrows_CV RETURN EMPLOYEE_TP.EMPLOYEE_rc;

   FUNCTION allrows_by_CV (
      where_clause_in IN VARCHAR2
     ,column_list_in IN VARCHAR2 DEFAULT NULL) RETURN EMPLOYEE_TP.weak_refcur;

   -- Close the specified cursor variable, only if it is open.
   PROCEDURE close_cursor (cursor_variable_in IN EMPLOYEE_TP.EMPLOYEE_rc);

   -- Hide calls to cursor attributes behind interface.
   FUNCTION cursor_is_open (cursor_variable_in IN EMPLOYEE_TP.weak_refcur) RETURN BOOLEAN;
   FUNCTION row_found (cursor_variable_in IN EMPLOYEE_TP.weak_refcur) RETURN BOOLEAN;
   FUNCTION row_notfound (cursor_variable_in IN EMPLOYEE_TP.weak_refcur) RETURN BOOLEAN;
   FUNCTION cursor_rowcount (cursor_variable_in IN EMPLOYEE_TP.weak_refcur) RETURN PLS_INTEGER;

   -- Use the LIMIT clause to BULK COLLECT N rows through the cursor variable
   -- The current contents of the collection will be deleted.
   FUNCTION fetch_rows (
      cursor_variable_in IN EMPLOYEE_TP.EMPLOYEE_rc
    , limit_in IN PLS_INTEGER DEFAULT 100
    )
      RETURN EMPLOYEE_TP.EMPLOYEE_tc;

   -- Allrows for specified where clause (using dynamic SQL)
   FUNCTION allrows_by (where_clause_in IN VARCHAR2)
      RETURN EMPLOYEE_TP.EMPLOYEE_tc;

   -- Return collection of all rows for primary key column EMPLOYEE_ID
   FUNCTION for_EMPLOYEE_ID (
      employee_id_in IN EMPLOYEE_TP.EMPLOYEE_ID_t
   )
   RETURN EMPLOYEE_TP.EMPLOYEE_tc;

   -- Return ref cursor to all rows for primary key column EMPLOYEE_ID
   FUNCTION for_EMPLOYEE_ID_CV (
      employee_id_in IN EMPLOYEE_TP.EMPLOYEE_ID_t
   )
   RETURN EMPLOYEE_TP.EMPLOYEE_rc;

   -- Return ref cursor to all rows for list of EMPLOYEE_ID values
   -- in format 'A,B,C,D', to be used in an IN list.
   FUNCTION in_EMPLOYEE_ID_CV (
      list_in IN VARCHAR2
   )
   RETURN EMPLOYEE_TP.weak_refcur;


   -- Return a record for one row by I_EMPLOYEE_NAME value
   FUNCTION or_I_EMPLOYEE_NAME (
      last_name_in IN EMPLOYEE_TP.LAST_NAME_t,
      first_name_in IN EMPLOYEE_TP.FIRST_NAME_t,
      middle_initial_in IN EMPLOYEE_TP.MIDDLE_INITIAL_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_rt
      ;

   -- Return a cursor variable for one row by I_EMPLOYEE_NAME value
   FUNCTION or_I_EMPLOYEE_NAME_CV (
      last_name_in IN EMPLOYEE_TP.LAST_NAME_t,
      first_name_in IN EMPLOYEE_TP.FIRST_NAME_t,
      middle_initial_in IN EMPLOYEE_TP.MIDDLE_INITIAL_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_rc
      ;

   -- Return the primary key value for this unique index value
   FUNCTION pky_I_EMPLOYEE_NAME (
      last_name_in IN EMPLOYEE_TP.LAST_NAME_t,
      first_name_in IN EMPLOYEE_TP.FIRST_NAME_t,
      middle_initial_in IN EMPLOYEE_TP.MIDDLE_INITIAL_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_ID_t
      ;

   -- Number of rows for this I_EMPLOYEE_NAME value
   FUNCTION num_I_EMPLOYEE_NAME (
      last_name_in IN EMPLOYEE_TP.LAST_NAME_t,
      first_name_in IN EMPLOYEE_TP.FIRST_NAME_t,
      middle_initial_in IN EMPLOYEE_TP.MIDDLE_INITIAL_t
      )
      RETURN PLS_INTEGER;

   -- Returns TRUE if at least one row exists for this I_EMPLOYEE_NAME value
   FUNCTION ex_I_EMPLOYEE_NAME (
      last_name_in IN EMPLOYEE_TP.LAST_NAME_t,
      first_name_in IN EMPLOYEE_TP.FIRST_NAME_t,
      middle_initial_in IN EMPLOYEE_TP.MIDDLE_INITIAL_t
      )
      RETURN BOOLEAN;

   -- Return a collection of all rows for FK_EMP_DEPARTMENT value
   FUNCTION ar_FK_EMP_DEPARTMENT (
      department_id_in IN EMPLOYEE_TP.DEPARTMENT_ID_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_tc;

   -- Return a cursor variable of all rows for FK_EMP_DEPARTMENT value
   FUNCTION ar_FK_EMP_DEPARTMENT_CV (
      department_id_in IN EMPLOYEE_TP.DEPARTMENT_ID_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_rc;

   -- Return a cursor variable of all rows based on IN list of values
   FUNCTION in_FK_EMP_DEPARTMENT_CV (
      department_id_in IN VARCHAR2
      )
      RETURN EMPLOYEE_TP.weak_refcur;

   PROCEDURE ar_FK_EMP_DEPARTMENT (
      department_id_in IN EMPLOYEE_TP.DEPARTMENT_ID_t,
      employee_id_out OUT EMPLOYEE_TP.EMPLOYEE_ID_cc,
      last_name_out OUT EMPLOYEE_TP.LAST_NAME_cc,
      first_name_out OUT EMPLOYEE_TP.FIRST_NAME_cc,
      middle_initial_out OUT EMPLOYEE_TP.MIDDLE_INITIAL_cc,
      job_id_out OUT EMPLOYEE_TP.JOB_ID_cc,
      manager_id_out OUT EMPLOYEE_TP.MANAGER_ID_cc,
      hire_date_out OUT EMPLOYEE_TP.HIRE_DATE_cc,
      salary_out OUT EMPLOYEE_TP.SALARY_cc,
      commission_out OUT EMPLOYEE_TP.COMMISSION_cc,
      department_id_out OUT EMPLOYEE_TP.DEPARTMENT_ID_cc,
      empno_out OUT EMPLOYEE_TP.EMPNO_cc,
      ename_out OUT EMPLOYEE_TP.ENAME_cc,
      created_by_out OUT EMPLOYEE_TP.CREATED_BY_cc,
      created_on_out OUT EMPLOYEE_TP.CREATED_ON_cc,
      changed_by_out OUT EMPLOYEE_TP.CHANGED_BY_cc,
      changed_on_out OUT EMPLOYEE_TP.CHANGED_ON_cc
      );

   -- Returns TRUE if at least one row exists for this FK_EMP_DEPARTMENT value
   FUNCTION ex_FK_EMP_DEPARTMENT (
      department_id_in IN EMPLOYEE_TP.DEPARTMENT_ID_t
      )
      RETURN BOOLEAN;

   -- Number of rows for this FK_EMP_DEPARTMENT value
   FUNCTION num_FK_EMP_DEPARTMENT (
      department_id_in IN EMPLOYEE_TP.DEPARTMENT_ID_t
      )
      RETURN PLS_INTEGER;

   -- Return a collection of all rows for FK_EMP_JOB value
   FUNCTION ar_FK_EMP_JOB (
      job_id_in IN EMPLOYEE_TP.JOB_ID_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_tc;

   -- Return a cursor variable of all rows for FK_EMP_JOB value
   FUNCTION ar_FK_EMP_JOB_CV (
      job_id_in IN EMPLOYEE_TP.JOB_ID_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_rc;

   -- Return a cursor variable of all rows based on IN list of values
   FUNCTION in_FK_EMP_JOB_CV (
      job_id_in IN VARCHAR2
      )
      RETURN EMPLOYEE_TP.weak_refcur;

   PROCEDURE ar_FK_EMP_JOB (
      job_id_in IN EMPLOYEE_TP.JOB_ID_t,
      employee_id_out OUT EMPLOYEE_TP.EMPLOYEE_ID_cc,
      last_name_out OUT EMPLOYEE_TP.LAST_NAME_cc,
      first_name_out OUT EMPLOYEE_TP.FIRST_NAME_cc,
      middle_initial_out OUT EMPLOYEE_TP.MIDDLE_INITIAL_cc,
      job_id_out OUT EMPLOYEE_TP.JOB_ID_cc,
      manager_id_out OUT EMPLOYEE_TP.MANAGER_ID_cc,
      hire_date_out OUT EMPLOYEE_TP.HIRE_DATE_cc,
      salary_out OUT EMPLOYEE_TP.SALARY_cc,
      commission_out OUT EMPLOYEE_TP.COMMISSION_cc,
      department_id_out OUT EMPLOYEE_TP.DEPARTMENT_ID_cc,
      empno_out OUT EMPLOYEE_TP.EMPNO_cc,
      ename_out OUT EMPLOYEE_TP.ENAME_cc,
      created_by_out OUT EMPLOYEE_TP.CREATED_BY_cc,
      created_on_out OUT EMPLOYEE_TP.CREATED_ON_cc,
      changed_by_out OUT EMPLOYEE_TP.CHANGED_BY_cc,
      changed_on_out OUT EMPLOYEE_TP.CHANGED_ON_cc
      );

   -- Returns TRUE if at least one row exists for this FK_EMP_JOB value
   FUNCTION ex_FK_EMP_JOB (
      job_id_in IN EMPLOYEE_TP.JOB_ID_t
      )
      RETURN BOOLEAN;

   -- Number of rows for this FK_EMP_JOB value
   FUNCTION num_FK_EMP_JOB (
      job_id_in IN EMPLOYEE_TP.JOB_ID_t
      )
      RETURN PLS_INTEGER;

   -- Return a collection of all rows for FK_EMP_MANAGER value
   FUNCTION ar_FK_EMP_MANAGER (
      manager_id_in IN EMPLOYEE_TP.MANAGER_ID_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_tc;

   -- Return a cursor variable of all rows for FK_EMP_MANAGER value
   FUNCTION ar_FK_EMP_MANAGER_CV (
      manager_id_in IN EMPLOYEE_TP.MANAGER_ID_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_rc;

   -- Return a cursor variable of all rows based on IN list of values
   FUNCTION in_FK_EMP_MANAGER_CV (
      manager_id_in IN VARCHAR2
      )
      RETURN EMPLOYEE_TP.weak_refcur;

   PROCEDURE ar_FK_EMP_MANAGER (
      manager_id_in IN EMPLOYEE_TP.MANAGER_ID_t,
      employee_id_out OUT EMPLOYEE_TP.EMPLOYEE_ID_cc,
      last_name_out OUT EMPLOYEE_TP.LAST_NAME_cc,
      first_name_out OUT EMPLOYEE_TP.FIRST_NAME_cc,
      middle_initial_out OUT EMPLOYEE_TP.MIDDLE_INITIAL_cc,
      job_id_out OUT EMPLOYEE_TP.JOB_ID_cc,
      manager_id_out OUT EMPLOYEE_TP.MANAGER_ID_cc,
      hire_date_out OUT EMPLOYEE_TP.HIRE_DATE_cc,
      salary_out OUT EMPLOYEE_TP.SALARY_cc,
      commission_out OUT EMPLOYEE_TP.COMMISSION_cc,
      department_id_out OUT EMPLOYEE_TP.DEPARTMENT_ID_cc,
      empno_out OUT EMPLOYEE_TP.EMPNO_cc,
      ename_out OUT EMPLOYEE_TP.ENAME_cc,
      created_by_out OUT EMPLOYEE_TP.CREATED_BY_cc,
      created_on_out OUT EMPLOYEE_TP.CREATED_ON_cc,
      changed_by_out OUT EMPLOYEE_TP.CHANGED_BY_cc,
      changed_on_out OUT EMPLOYEE_TP.CHANGED_ON_cc
      );

   -- Returns TRUE if at least one row exists for this FK_EMP_MANAGER value
   FUNCTION ex_FK_EMP_MANAGER (
      manager_id_in IN EMPLOYEE_TP.MANAGER_ID_t
      )
      RETURN BOOLEAN;

   -- Number of rows for this FK_EMP_MANAGER value
   FUNCTION num_FK_EMP_MANAGER (
      manager_id_in IN EMPLOYEE_TP.MANAGER_ID_t
      )
      RETURN PLS_INTEGER;

   -- Number of rows in table
   FUNCTION tabcount (where_clause_in IN VARCHAR2 := NULL)
      RETURN PLS_INTEGER;
   -- Number of rows for this primary key value
   FUNCTION pkycount (
      employee_id_in IN EMPLOYEE_TP.EMPLOYEE_ID_t
      )
      RETURN PLS_INTEGER;

   -- Returns TRUE if at least one row exists for this where clause
   FUNCTION ex_EMPLOYEE (
      where_clause_in IN VARCHAR2 := NULL)
   RETURN BOOLEAN;

   -- Returns TRUE if at least one row exists for this primary key
   FUNCTION ex_pky (
      employee_id_in IN EMPLOYEE_TP.EMPLOYEE_ID_t
      )
   RETURN BOOLEAN;
END EMPLOYEE_QP;
/