employee_cp.pkb

OReilly Oracle PL SQL Programming第4版源代码

CREATE OR REPLACE PACKAGE BODY EMPLOYEE_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 07:31:47 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 (
      employee_id_in IN EMPLOYEE_TP.EMPLOYEE_ID_t,
      last_name_in IN EMPLOYEE_TP.LAST_NAME_t DEFAULT NULL,
      first_name_in IN EMPLOYEE_TP.FIRST_NAME_t DEFAULT NULL,
      middle_initial_in IN EMPLOYEE_TP.MIDDLE_INITIAL_t DEFAULT NULL,
      job_id_in IN EMPLOYEE_TP.JOB_ID_t DEFAULT NULL,
      manager_id_in IN EMPLOYEE_TP.MANAGER_ID_t DEFAULT NULL,
      hire_date_in IN EMPLOYEE_TP.HIRE_DATE_t DEFAULT SYSDATE,
      salary_in IN EMPLOYEE_TP.SALARY_t DEFAULT NULL,
      commission_in IN EMPLOYEE_TP.COMMISSION_t DEFAULT NULL,
      department_id_in IN EMPLOYEE_TP.DEPARTMENT_ID_t DEFAULT NULL,
      empno_in IN EMPLOYEE_TP.EMPNO_t DEFAULT NULL,
      ename_in IN EMPLOYEE_TP.ENAME_t DEFAULT NULL,
      created_by_in IN EMPLOYEE_TP.CREATED_BY_t DEFAULT USER,
      created_on_in IN EMPLOYEE_TP.CREATED_ON_t DEFAULT SYSDATE,
      changed_by_in IN EMPLOYEE_TP.CHANGED_BY_t DEFAULT USER,
      changed_on_in IN EMPLOYEE_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 EMPLOYEE (
         EMPLOYEE_ID,
         LAST_NAME,
         FIRST_NAME,
         MIDDLE_INITIAL,
         JOB_ID,
         MANAGER_ID,
         HIRE_DATE,
         SALARY,
         COMMISSION,
         DEPARTMENT_ID,
         EMPNO,
         ENAME,
         CREATED_BY,
         CREATED_ON,
         CHANGED_BY,
         CHANGED_ON
         )
      VALUES (
         employee_id_in,
         last_name_in,
         first_name_in,
         middle_initial_in,
         job_id_in,
         manager_id_in,
         hire_date_in,
         salary_in,
         commission_in,
         department_id_in,
         empno_in,
         ename_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_EMPLOYEE_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_EMPLOYEE_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 => 'EMPLOYEE'
                ,validate_in => FALSE
                );
              qd_runtime.add_context (
                 err_instance_id_in => l_err_instance_id
                ,NAME_IN => 'LAST_NAME'
                ,value_in => last_name_in
                ,validate_in => FALSE
                );
              qd_runtime.add_context (
                 err_instance_id_in => l_err_instance_id
                ,NAME_IN => 'FIRST_NAME'
                ,value_in => first_name_in
                ,validate_in => FALSE
                );
              qd_runtime.add_context (
                 err_instance_id_in => l_err_instance_id
                ,NAME_IN => 'MIDDLE_INITIAL'
                ,value_in => middle_initial_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 => 'EMPLOYEE');
           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 => 'EMPLOYEE');
        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 => 'EMPLOYEE');
        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 => 'EMPLOYEE');
        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 EMPLOYEE_TP.EMPLOYEE_rt
     ,gen_pky_in IN BOOLEAN DEFAULT FALSE
     ,sequence_in IN VARCHAR2 := NULL
     ,handle_error_in IN BOOLEAN := TRUE
   )
   IS
      l_rec EMPLOYEE_TP.EMPLOYEE_rt := rec_in;
   BEGIN
      IF gen_pky_in THEN
         l_rec.EMPLOYEE_ID := next_key (sequence_in);
      END IF;
      ins (
         employee_id_in => l_rec.EMPLOYEE_ID,
         last_name_in => l_rec.LAST_NAME,
         first_name_in => l_rec.FIRST_NAME,
         middle_initial_in => l_rec.MIDDLE_INITIAL,
         job_id_in => l_rec.JOB_ID,
         manager_id_in => l_rec.MANAGER_ID,
         hire_date_in => l_rec.HIRE_DATE,
         salary_in => l_rec.SALARY,
         commission_in => l_rec.COMMISSION,
         department_id_in => l_rec.DEPARTMENT_ID,
         empno_in => l_rec.EMPNO,
         ename_in => l_rec.ENAME,
         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 EMPLOYEE_TP.EMPLOYEE_ID_t
   IS
      retval EMPLOYEE_TP.EMPLOYEE_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 employee_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'