department_qp.pkb

OReilly Oracle PL SQL Programming第4版源代码

CREATE OR REPLACE PACKAGE BODY DEPARTMENT_QP
/*
| Generated by or retrieved from Qnxo - DO NOT MODIFY!
| Qnxo - "Get it right, do it fast" - www.qnxo.com
| Qnxo Universal ID: ce63f2f4-b478-47a2-b142-b1c577af0c40
| Created On: April     04, 2005 08:01:10 Created By: QNXO_DEMO
*/
IS
   FUNCTION onerow (
      department_id_in IN DEPARTMENT_TP.DEPARTMENT_ID_t
      )
   RETURN DEPARTMENT_TP.DEPARTMENT_rt
   IS
      onerow_rec DEPARTMENT_TP.DEPARTMENT_rt;
   BEGIN
      SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
        INTO onerow_rec
        FROM DEPARTMENT
       WHERE
             DEPARTMENT_ID = department_id_in
      ;
      RETURN onerow_rec;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN NULL;
      WHEN TOO_MANY_ROWS
      THEN
         DECLARE
            l_err_instance_id qd_err_instance_tp.id_t;
         BEGIN
            qd_runtime.register_error ('MULTIPLE-UNIQUE-ROWS'
               ,err_instance_id_out => l_err_instance_id);
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'TABLE'
              ,value_in => 'DEPARTMENT'
              ,validate_in => FALSE
              );
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'DEPARTMENT_ID'
              ,value_in => 'department_id_in'
              ,validate_in => FALSE
              );
            qd_runtime.raise_error_instance (
              err_instance_id_in => l_err_instance_id);
         END;
   END onerow;

   FUNCTION row_exists (
      department_id_in IN DEPARTMENT_TP.DEPARTMENT_ID_t
      )
   RETURN BOOLEAN
   IS
      l_dummy PLS_INTEGER;
      retval BOOLEAN;
   BEGIN
      SELECT 1 INTO l_dummy
        FROM DEPARTMENT
       WHERE
             DEPARTMENT_ID = department_id_in
      ;
      RETURN TRUE;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN FALSE;
      WHEN TOO_MANY_ROWS THEN RETURN TRUE;
   END row_exists;

   FUNCTION onerow_CV (
      department_id_in IN DEPARTMENT_TP.DEPARTMENT_ID_t
      )
   RETURN DEPARTMENT_TP.DEPARTMENT_rc
   IS
      retval DEPARTMENT_TP.DEPARTMENT_rc;
   BEGIN
      OPEN retval FOR
         SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
        FROM DEPARTMENT
       WHERE
             DEPARTMENT_ID = department_id_in
      ;
      RETURN retval;
   END onerow_cv;

   FUNCTION allrows RETURN DEPARTMENT_TP.DEPARTMENT_tc
   IS
      CURSOR allrows_cur
      IS
         SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM DEPARTMENT

           ;
      l_rows PLS_INTEGER;
      retval DEPARTMENT_TP.DEPARTMENT_tc;
   BEGIN
      OPEN allrows_cur;
      FETCH allrows_cur BULK COLLECT INTO retval;
      RETURN retval;
   END allrows;

   FUNCTION allrows_cv RETURN DEPARTMENT_TP.DEPARTMENT_rc
   IS
      retval DEPARTMENT_TP.DEPARTMENT_rc;
   BEGIN
      OPEN retval FOR
         SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM DEPARTMENT

           ;
      RETURN retval;
   END allrows_cv;

   FUNCTION allrows_by_CV (where_clause_in IN VARCHAR2
      , column_list_in IN VARCHAR2 DEFAULT NULL) RETURN DEPARTMENT_TP.weak_refcur
   IS
      retval DEPARTMENT_TP.weak_refcur;
   BEGIN
      IF where_clause_in IS NULL AND column_list_in IS NULL
      THEN
         retval := allrows_cv;
      ELSIF column_list_in IS NULL
      THEN
         OPEN retval FOR
            'SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM DEPARTMENT WHERE ' || where_clause_in
             || ' ' || ''
           ;
      ELSE
         OPEN retval FOR
            'SELECT ' || column_list_in ||
             ' FROM DEPARTMENT WHERE ' || where_clause_in
             || ' ' || ''
           ;
      END IF;
      RETURN retval;
   END allrows_by_cv;

   -- Close the specified cursor variable, only if it is open.
   PROCEDURE close_cursor (cursor_variable_in IN DEPARTMENT_TP.DEPARTMENT_rc)
   IS
   BEGIN
      IF cursor_variable_in%ISOPEN
      THEN
         CLOSE cursor_variable_in;
      END IF;
   END close_cursor;

   -- Hide calls to cursor attributes behind interface.
   FUNCTION cursor_is_open (cursor_variable_in IN DEPARTMENT_TP.weak_refcur) RETURN BOOLEAN
   IS
   BEGIN
      RETURN cursor_variable_in%ISOPEN;
   EXCEPTION WHEN OTHERS THEN RETURN FALSE;
   END cursor_is_open;

   FUNCTION row_found (cursor_variable_in IN DEPARTMENT_TP.weak_refcur) RETURN BOOLEAN
   IS
   BEGIN
      RETURN cursor_variable_in%FOUND;
   EXCEPTION WHEN OTHERS THEN RETURN NULL;
   END row_found;

   FUNCTION row_notfound (cursor_variable_in IN DEPARTMENT_TP.weak_refcur) RETURN BOOLEAN
   IS
   BEGIN
      RETURN cursor_variable_in%NOTFOUND;
   EXCEPTION WHEN OTHERS THEN RETURN NULL;
   END row_notfound;

   FUNCTION cursor_rowcount (cursor_variable_in IN DEPARTMENT_TP.weak_refcur) RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN cursor_variable_in%ROWCOUNT;
   EXCEPTION WHEN OTHERS THEN RETURN 0;
   END cursor_rowcount;

   -- 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 DEPARTMENT_TP.DEPARTMENT_rc
    , limit_in IN PLS_INTEGER DEFAULT 100
    )
      RETURN DEPARTMENT_TP.DEPARTMENT_tc
   IS
      retval DEPARTMENT_TP.DEPARTMENT_tc;
   BEGIN
      FETCH cursor_variable_in BULK COLLECT INTO
         retval LIMIT limit_in;
      RETURN retval;
   END fetch_rows;

    -- Allrows for specified where clause (using dynamic SQL)
   FUNCTION allrows_by (where_clause_in IN VARCHAR2)
      RETURN DEPARTMENT_TP.DEPARTMENT_tc
   IS
      allrows_cur DEPARTMENT_TP.weak_refcur;
      retval DEPARTMENT_TP.DEPARTMENT_tc;
   BEGIN
      EXECUTE IMMEDIATE
         'SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM DEPARTMENT WHERE ' || where_clause_in
         BULK COLLECT INTO retval;
      RETURN retval;
   END allrows_by;

   PROCEDURE allrows (
      department_id_out OUT DEPARTMENT_TP.DEPARTMENT_ID_cc,
      name_out OUT DEPARTMENT_TP.NAME_cc,
      loc_id_out OUT DEPARTMENT_TP.LOC_ID_cc,
      created_by_out OUT DEPARTMENT_TP.CREATED_BY_cc,
      created_on_out OUT DEPARTMENT_TP.CREATED_ON_cc,
      changed_by_out OUT DEPARTMENT_TP.CHANGED_BY_cc,
      changed_on_out OUT DEPARTMENT_TP.CHANGED_ON_cc
   )
   IS
   BEGIN
      SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
        BULK COLLECT INTO
            department_id_out,
            name_out,
            loc_id_out,
            created_by_out,
            created_on_out,
            changed_by_out,
            changed_on_out
        FROM DEPARTMENT
      ;
   END allrows;

   -- Return collection of all rows for primary key column DEPARTMENT_ID
   FUNCTION for_DEPARTMENT_ID (
      department_id_in IN DEPARTMENT_TP.DEPARTMENT_ID_t
      )
      RETURN DEPARTMENT_TP.DEPARTMENT_tc
   IS
      CURSOR allrows_cur
      IS
         SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM DEPARTMENT
          WHERE DEPARTMENT_ID = for_DEPARTMENT_ID.department_id_in

          ;
      l_rows PLS_INTEGER;
      retval DEPARTMENT_TP.DEPARTMENT_tc;
   BEGIN
      OPEN allrows_cur;
      FETCH allrows_cur BULK COLLECT INTO retval;
      RETURN retval;
   END for_DEPARTMENT_ID;

   -- Return ref cursor to all rows for primary key column DEPARTMENT_ID
   FUNCTION for_DEPARTMENT_ID_cv (
      department_id_in IN DEPARTMENT_TP.DEPARTMENT_ID_t
      )
      RETURN DEPARTMENT_TP.DEPARTMENT_rc
   IS
      retval DEPARTMENT_TP.DEPARTMENT_rc;
   BEGIN
      OPEN retval FOR
         SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM DEPARTMENT
          WHERE DEPARTMENT_ID = department_id_in

             ;
      RETURN retval;
   END for_DEPARTMENT_ID_cv;

   FUNCTION in_DEPARTMENT_ID_cv (
      list_in IN VARCHAR2
      )
      RETURN DEPARTMENT_TP.weak_refcur
   IS
      retval DEPARTMENT_TP.weak_refcur;
   BEGIN
      OPEN retval FOR
         'SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM DEPARTMENT
          WHERE DEPARTMENT_ID IN (' || list_in || ')
             '
             ;
      RETURN retval;
   END in_DEPARTMENT_ID_cv;

   FUNCTION or_I_DEPARTMENT_NAME (
      name_in IN DEPARTMENT_TP.NAME_t
      )
      RETURN DEPARTMENT_TP.DEPARTMENT_rt
   IS
      retval DEPARTMENT_TP.DEPARTMENT_rt;
   BEGIN
      SELECT
            DEPARTMENT_ID,