department_cp.pkb

OReilly Oracle PL SQL Programming第4版源代码

CREATE OR REPLACE PACKAGE BODY DEPARTMENT_CP
/*
| Generated by or retrieved from Qnxo - DO NOT MODIFY!
| Qnxo - "Get it right, do it fast" - www.qnxo.com
| Qnxo Universal ID: 657bd35e-2e48-4cd4-b14e-41420b8433b3
| Created On: April     04, 2005 08:01:06 Created By: QNXO_DEMO
*/
IS

   e_null_column_value EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_null_column_value, -1400);
   e_existing_fky_reference EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_existing_fky_reference, -2266);
   e_check_constraint_failure EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_check_constraint_failure, -2290);
   e_no_parent_key EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_no_parent_key, -2291);
   e_child_record_found EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_child_record_found, -2292);
   e_forall_error EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_forall_error, -24381);
   --
   -- Defined for backward compatibilty.
   e_integ_constraint_failure EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_integ_constraint_failure, -2291);


    -- Private utilities
   PROCEDURE get_constraint_info (
      owner_out OUT ALL_CONSTRAINTS.OWNER%TYPE
     ,name_out OUT ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE)
   IS
      l_errm VARCHAR2(2000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
      dotloc INTEGER;
      leftloc INTEGER;
      l_owner ALL_CONSTRAINTS.OWNER%TYPE;
      l_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
   BEGIN
      dotloc  := INSTR (l_errm,'.');
      leftloc := INSTR (l_errm,'(');
      owner_out := SUBSTR (l_errm, leftloc+1, dotloc-leftloc-1);
      name_out  := SUBSTR (l_errm, dotloc+1, INSTR (l_errm,')')-dotloc-1);
   END get_constraint_info;

   -- Public programs

   PROCEDURE ins (
      department_id_in IN DEPARTMENT_TP.DEPARTMENT_ID_t,
      name_in IN DEPARTMENT_TP.NAME_t DEFAULT NULL,
      loc_id_in IN DEPARTMENT_TP.LOC_ID_t DEFAULT NULL,
      created_by_in IN DEPARTMENT_TP.CREATED_BY_t DEFAULT USER,
      created_on_in IN DEPARTMENT_TP.CREATED_ON_t DEFAULT SYSDATE,
      changed_by_in IN DEPARTMENT_TP.CHANGED_BY_t DEFAULT USER,
      changed_on_in IN DEPARTMENT_TP.CHANGED_ON_t DEFAULT SYSDATE,
      handle_error_in IN BOOLEAN := TRUE
      )
   IS
   BEGIN
      IF qd_runtime.trace_enabled THEN
         qd_runtime.start_execution (program_name_in => 'ins');
      END IF;
      INSERT INTO DEPARTMENT (
         DEPARTMENT_ID,
         NAME,
         LOC_ID,
         CREATED_BY,
         CREATED_ON,
         CHANGED_BY,
         CHANGED_ON
         )
      VALUES (
         department_id_in,
         name_in,
         loc_id_in,
         created_by_in,
         created_on_in,
         changed_by_in,
         changed_on_in
         );
      IF qd_runtime.trace_enabled THEN
        qd_runtime.end_execution;
      END IF;
   EXCEPTION

      WHEN DUP_VAL_ON_INDEX
      THEN
        qd_runtime.start_exception_handling;
        IF NOT handle_error_in THEN RAISE;
        ELSE
        DECLARE
           l_owner ALL_CONSTRAINTS.OWNER%TYPE;
           l_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
           l_err_instance_id qd_err_instance_tp.id_t;
        BEGIN
           get_constraint_info (l_owner, l_name);
           IF FALSE THEN NULL; -- Placeholder in case no unique indexes
           ELSIF l_name = 'I_DEPARTMENT_NAME'
           THEN
              qd_runtime.register_error (
                 error_name_in => 'DUPLICATE-VALUE'
                ,err_instance_id_out => l_err_instance_id
                ,text_in => 'Violation of unique index "I_DEPARTMENT_NAME"'
                );
              qd_runtime.add_context (
                 err_instance_id_in => l_err_instance_id
                ,NAME_IN => 'OWNER'
                ,value_in => l_owner
                ,validate_in => FALSE
                );
              qd_runtime.add_context (
                 err_instance_id_in => l_err_instance_id
                ,NAME_IN => 'CONSTRAINT_NAME'
                ,value_in => l_name
                ,validate_in => FALSE
                );
              qd_runtime.add_context (
                 err_instance_id_in => l_err_instance_id
                ,NAME_IN => 'TABLE_NAME'
                ,value_in => 'DEPARTMENT'
                ,validate_in => FALSE
                );
              qd_runtime.add_context (
                 err_instance_id_in => l_err_instance_id
                ,NAME_IN => 'NAME'
                ,value_in => name_in
                ,validate_in => FALSE
                );
              qd_runtime.raise_error_instance (
                    err_instance_id_in => l_err_instance_id);
           ELSE
              qd_runtime.raise_error (
                    error_name_in => 'DUPLICATE-VALUE'
                    ,name1_in => 'OWNER'
                    ,value1_in => l_owner
                    ,name2_in => 'CONSTRAINT_NAME'
                    ,value2_in => l_name
                    ,name3_in => 'TABLE_NAME'
                    ,value3_in => 'DEPARTMENT');
           END IF;
        END;
        END IF;
      WHEN e_check_constraint_failure
      THEN
        qd_runtime.start_exception_handling;
        IF NOT handle_error_in THEN RAISE;
        ELSE
        DECLARE
           l_owner ALL_CONSTRAINTS.OWNER%TYPE;
           l_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
        BEGIN
           get_constraint_info (l_owner, l_name);
           qd_runtime.raise_error (
              error_name_in => 'CHECK-CONSTRAINT-FAILURE'
             ,name1_in => 'OWNER'
             ,value1_in => l_owner
             ,name2_in => 'CONSTRAINT_NAME'
             ,value2_in => l_name
             ,name3_in => 'TABLE_NAME'
             ,value3_in => 'DEPARTMENT');
        END;
        END IF;
      WHEN e_integ_constraint_failure OR e_no_parent_key
      THEN
        qd_runtime.start_exception_handling;
        IF NOT handle_error_in THEN RAISE;
        ELSE
        DECLARE
           l_owner ALL_CONSTRAINTS.OWNER%TYPE;
           l_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
        BEGIN
           get_constraint_info (l_owner, l_name);
           qd_runtime.raise_error (
              error_name_in => 'NO-PARENT-KEY-FOUND'
             ,name1_in => 'OWNER'
             ,value1_in => l_owner
             ,name2_in => 'CONSTRAINT_NAME'
             ,value2_in => l_name
             ,name3_in => 'TABLE_NAME'
             ,value3_in => 'DEPARTMENT');
        END;
        END IF;
      WHEN e_child_record_found
      THEN
        qd_runtime.start_exception_handling;
        IF NOT handle_error_in THEN RAISE;
        ELSE
        DECLARE
           l_owner ALL_CONSTRAINTS.OWNER%TYPE;
           l_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
        BEGIN
           get_constraint_info (l_owner, l_name);
           qd_runtime.raise_error (
              error_name_in => 'CHILD-RECORD-FOUND'
             ,name1_in => 'OWNER'
             ,value1_in => l_owner
             ,name2_in => 'CONSTRAINT_NAME'
             ,value2_in => l_name
             ,name3_in => 'TABLE_NAME'
             ,value3_in => 'DEPARTMENT');
        END;
        END IF;
      WHEN e_null_column_value
      THEN
        qd_runtime.start_exception_handling;
        IF NOT handle_error_in THEN RAISE;
        ELSE
        DECLARE
           v_errm VARCHAR2(2000) := DBMS_UTILITY.FORMAT_ERROR_STACK;
           dot1loc INTEGER;
           dot2loc INTEGER;
           parenloc INTEGER;
           c_owner ALL_CONSTRAINTS.OWNER%TYPE;
           c_tabname ALL_TABLES.TABLE_NAME%TYPE;
           c_colname ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
        BEGIN
           dot1loc := INSTR (v_errm, '.', 1, 1);
           dot2loc := INSTR (v_errm, '.', 1, 2);
           parenloc := INSTR (v_errm, '(');
           c_owner :=SUBSTR (v_errm, parenloc+1, dot1loc-parenloc-1);
           c_tabname := SUBSTR (v_errm, dot1loc+1, dot2loc-dot1loc-1);
           c_colname := SUBSTR (v_errm, dot2loc+1, INSTR (v_errm,')')-dot2loc-1);

           qd_runtime.raise_error (
                error_name_in => 'COLUMN-CANNOT-BE-NULL'
               ,name1_in => 'OWNER'
               ,value1_in => c_owner
               ,name2_in => 'TABLE_NAME'
               ,value2_in => c_tabname
               ,name3_in => 'COLUMN_NAME'
               ,value3_in => c_colname);
        END;
        END IF;
      WHEN OTHERS THEN
         qd_runtime.start_exception_handling;
         IF NOT handle_error_in THEN RAISE;
         ELSE
           qd_runtime.raise_error (
              error_name_in => 'UNANTICIPATED-ERROR'
             ,name1_in => NULL
             ,value1_in => NULL
             ,name2_in => NULL
             ,value2_in => NULL
             ,name3_in => NULL
             ,value3_in => NULL
             ,name4_in => NULL
             ,value4_in => NULL
             ,name5_in => NULL
             ,value5_in => NULL
           );
         END IF;
   END ins;

   PROCEDURE ins (
      rec_in IN DEPARTMENT_TP.DEPARTMENT_rt
     ,gen_pky_in IN BOOLEAN DEFAULT FALSE
     ,sequence_in IN VARCHAR2 := NULL
     ,handle_error_in IN BOOLEAN := TRUE
   )
   IS
      l_rec DEPARTMENT_TP.DEPARTMENT_rt := rec_in;
   BEGIN
      IF gen_pky_in THEN
         l_rec.DEPARTMENT_ID := next_key (sequence_in);
      END IF;
      ins (
         department_id_in => l_rec.DEPARTMENT_ID,
         name_in => l_rec.NAME,
         loc_id_in => l_rec.LOC_ID,
         created_by_in => l_rec.CREATED_BY,
         created_on_in => l_rec.CREATED_ON,
         changed_by_in => l_rec.CHANGED_BY,
         changed_on_in => l_rec.CHANGED_ON,
         handle_error_in => handle_error_in
         );
   END ins;

   FUNCTION next_key (sequence_in IN VARCHAR2 := NULL) RETURN DEPARTMENT_TP.DEPARTMENT_ID_t
   IS
      retval DEPARTMENT_TP.DEPARTMENT_ID_t;
   BEGIN
      IF qd_runtime.trace_enabled THEN
         qd_runtime.start_execution (program_name_in => 'next_key');
      END IF;
      IF sequence_in IS NULL
      THEN
         SELECT department_seq.NEXTVAL INTO retval FROM dual;
      ELSE
         EXECUTE IMMEDIATE
            'SELECT ' || sequence_in || '.NEXTVAL FROM dual'
            INTO retval;
      END IF;
      IF qd_runtime.trace_enabled THEN
         qd_runtime.end_execution;
      END IF;
      RETURN retval;
   EXCEPTION
      WHEN OTHERS THEN
        qd_runtime.start_exception_handling;
        qd_runtime.raise_error (
           error_name_in => 'SEQUENCE-GENERATION-FAILURE'
           ,name1_in => 'SEQUENCE'
           ,value1_in => NVL (sequence_in, 'department_seq')
           );
   END next_key;

   PROCEDURE ins (
      name_in IN DEPARTMENT_TP.NAME_t DEFAULT NULL,
      loc_id_in IN DEPARTMENT_TP.LOC_ID_t DEFAULT NULL,
      created_by_in IN DEPARTMENT_TP.CREATED_BY_t DEFAULT USER,
      created_on_in IN DEPARTMENT_TP.CREATED_ON_t DEFAULT SYSDATE,
      changed_by_in IN DEPARTMENT_TP.CHANGED_BY_t DEFAULT USER,
      changed_on_in IN DEPARTMENT_TP.CHANGED_ON_t DEFAULT SYSDATE,
      department_id_out IN OUT DEPARTMENT_TP.DEPARTMENT_ID_t,
      handle_error_in IN BOOLEAN := TRUE
   )
   IS
      l_pky DEPARTMENT_TP.DEPARTMENT_ID_t := next_key;
   BEGIN
      ins (
         department_id_in => l_pky,
         name_in => name_in,
         loc_id_in => loc_id_in,
         created_by_in => created_by_in,
         created_on_in => created_on_in,
         changed_by_in => changed_by_in,
         changed_on_in => changed_on_in,
         handle_error_in => handle_error_in
         );
      department_id_out := l_pky;
   END ins;

   FUNCTION ins (
      name_in IN DEPARTMENT_TP.NAME_t DEFAULT NULL,
      loc_id_in IN DEPARTMENT_TP.LOC_ID_t DEFAULT NULL,
      created_by_in IN DEPARTMENT_TP.CREATED_BY_t DEFAULT USER,
      created_on_in IN DEPARTMENT_TP.CREATED_ON_t DEFAULT SYSDATE,
      changed_by_in IN DEPARTMENT_TP.CHANGED_BY_t DEFAULT USER,
      changed_on_in IN DEPARTMENT_TP.CHANGED_ON_t DEFAULT SYSDATE,
      handle_error_in IN BOOLEAN := TRUE
   )
      RETURN
         DEPARTMENT_TP.DEPARTMENT_ID_t
   IS
      l_pky DEPARTMENT_TP.DEPARTMENT_ID_t := next_key;
   BEGIN
      ins (
         department_id_in => l_pky,
         name_in => name_in,
         loc_id_in => loc_id_in,
         created_by_in => created_by_in,
         created_on_in => created_on_in,
         changed_by_in => changed_by_in,
         changed_on_in => changed_on_in,
         handle_error_in => handle_error_in
         );
      RETURN l_pky;
   END ins;

    PROCEDURE ins (
      name_in IN DEPARTMENT_TP.NAME_t DEFAULT NULL,
      loc_id_in IN DEPARTMENT_TP.LOC_ID_t DEFAULT NULL,
      created_by_in IN DEPARTMENT_TP.CREATED_BY_t DEFAULT USER,
      created_on_in IN DEPARTMENT_TP.CREATED_ON_t DEFAULT SYSDATE,
      changed_by_in IN DEPARTMENT_TP.CHANGED_BY_t DEFAULT USER,
      changed_on_in IN DEPARTMENT_TP.CHANGED_ON_t DEFAULT SYSDATE,
      handle_error_in IN BOOLEAN := TRUE