employee_cp.pkb

OReilly Oracle PL SQL Programming第4版源代码

                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'ERROR_AT_ROW_' || indx
                  ,value_in => SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'EMPLOYEE_ID'
                  ,value_in => l_employee_id(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'LAST_NAME'
                  ,value_in => l_last_name(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'FIRST_NAME'
                  ,value_in => l_first_name(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'MIDDLE_INITIAL'
                  ,value_in => l_middle_initial(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'JOB_ID'
                  ,value_in => l_job_id(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'MANAGER_ID'
                  ,value_in => l_manager_id(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'HIRE_DATE'
                  ,value_in => l_hire_date(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'SALARY'
                  ,value_in => l_salary(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'COMMISSION'
                  ,value_in => l_commission(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'DEPARTMENT_ID'
                  ,value_in => l_department_id(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'EMPNO'
                  ,value_in => l_empno(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'ENAME'
                  ,value_in => l_ename(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'CREATED_BY'
                  ,value_in => l_created_by(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'CREATED_ON'
                  ,value_in => l_created_on(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'CHANGED_BY'
                  ,value_in => l_changed_by(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'CHANGED_ON'
                  ,value_in => l_changed_on(indx)
                  ,validate_in => FALSE
                );
             END LOOP;
          END bulk_handler;
        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 => 'FORALL-INSERT-FAILURE'
          ,name1_in => 'TABLE_NAME'
          ,value1_in => 'EMPLOYEE'
          ,name2_in => 'ROW_COUNT'
          ,value2_in => rows_in.COUNT
          ,name3_in => 'PROGRESS_INDICATOR'
          ,value3_in => l_progress
           );
       END IF;
   END ins;
   PROCEDURE upd (
      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 NULL,
      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 NULL,
      created_on_in IN EMPLOYEE_TP.CREATED_ON_t DEFAULT NULL,
      changed_by_in IN EMPLOYEE_TP.CHANGED_BY_t DEFAULT NULL,
      changed_on_in IN EMPLOYEE_TP.CHANGED_ON_t DEFAULT NULL,
      rows_out OUT PLS_INTEGER,
      ignore_if_null_in IN BOOLEAN := FALSE
     ,handle_error_in IN BOOLEAN := TRUE
      )
   IS
   BEGIN
      IF qd_runtime.trace_enabled THEN
         qd_runtime.start_execution(program_name_in => 'upd');
      END IF;
      IF NVL (ignore_if_null_in, FALSE)
      THEN
         -- Set any columns to their current values
         -- if incoming value is NULL.
         -- Put WHEN clause on column-level triggers!
         UPDATE EMPLOYEE SET
            LAST_NAME = NVL (last_name_in, LAST_NAME),
            FIRST_NAME = NVL (first_name_in, FIRST_NAME),
            MIDDLE_INITIAL = NVL (middle_initial_in, MIDDLE_INITIAL),
            JOB_ID = NVL (job_id_in, JOB_ID),
            MANAGER_ID = NVL (manager_id_in, MANAGER_ID),
            HIRE_DATE = NVL (hire_date_in, HIRE_DATE),
            SALARY = NVL (salary_in, SALARY),
            COMMISSION = NVL (commission_in, COMMISSION),
            DEPARTMENT_ID = NVL (department_id_in, DEPARTMENT_ID),
            EMPNO = NVL (empno_in, EMPNO),
            ENAME = NVL (ename_in, ENAME),
            CREATED_BY = NVL (created_by_in, CREATED_BY),
            CREATED_ON = NVL (created_on_in, CREATED_ON),
            CHANGED_BY = NVL (changed_by_in, CHANGED_BY),
            CHANGED_ON = NVL (changed_on_in, CHANGED_ON)
          WHERE
             EMPLOYEE_ID = employee_id_in
         ;
      ELSE
         UPDATE EMPLOYEE SET
            LAST_NAME = last_name_in,
            FIRST_NAME = first_name_in,
            MIDDLE_INITIAL = middle_initial_in,
            JOB_ID = job_id_in,
            MANAGER_ID = manager_id_in,
            HIRE_DATE = hire_date_in,
            SALARY = salary_in,
            COMMISSION = commission_in,
            DEPARTMENT_ID = department_id_in,
            EMPNO = empno_in,
            ENAME = ename_in,
            CREATED_BY = created_by_in,
            CREATED_ON = created_on_in,
            CHANGED_BY = changed_by_in,
            CHANGED_ON = changed_on_in
          WHERE
             EMPLOYEE_ID = employee_id_in
         ;
      END IF;
      rows_out := SQL%ROWCOUNT;
   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'