department_cp.pkb

OReilly Oracle PL SQL Programming第4版源代码

   )
   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
         );
   END ins;

    PROCEDURE ins (
      rows_in IN DEPARTMENT_TP.DEPARTMENT_tc
     ,rows_out OUT PLS_INTEGER
     ,handle_error_in IN BOOLEAN := TRUE
   )
   IS
      l_department_id DEPARTMENT_TP.DEPARTMENT_ID_cc;
      l_name DEPARTMENT_TP.NAME_cc;
      l_loc_id DEPARTMENT_TP.LOC_ID_cc;
      l_created_by DEPARTMENT_TP.CREATED_BY_cc;
      l_created_on DEPARTMENT_TP.CREATED_ON_cc;
      l_changed_by DEPARTMENT_TP.CHANGED_BY_cc;
      l_changed_on DEPARTMENT_TP.CHANGED_ON_cc;
      l_progress VARCHAR2(1000);
   BEGIN
      IF qd_runtime.trace_enabled THEN
         qd_runtime.start_execution(program_name_in => 'ins');
      END IF;
      IF rows_in.COUNT = 0
      THEN
         rows_out := 0;
      ELSE
         l_progress := 'Populate l_department_id collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying DEPARTMENT_ID to column collection row ' || indx;
            l_department_id(indx) := rows_in (indx).DEPARTMENT_ID;
         END LOOP;
         l_progress := 'Populate l_name collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying NAME to column collection row ' || indx;
            l_name(indx) := rows_in (indx).NAME;
         END LOOP;
         l_progress := 'Populate l_loc_id collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying LOC_ID to column collection row ' || indx;
            l_loc_id(indx) := rows_in (indx).LOC_ID;
         END LOOP;
         l_progress := 'Populate l_created_by collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying CREATED_BY to column collection row ' || indx;
            l_created_by(indx) := rows_in (indx).CREATED_BY;
         END LOOP;
         l_progress := 'Populate l_created_on collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying CREATED_ON to column collection row ' || indx;
            l_created_on(indx) := rows_in (indx).CREATED_ON;
         END LOOP;
         l_progress := 'Populate l_changed_by collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying CHANGED_BY to column collection row ' || indx;
            l_changed_by(indx) := rows_in (indx).CHANGED_BY;
         END LOOP;
         l_progress := 'Populate l_changed_on collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying CHANGED_ON to column collection row ' || indx;
            l_changed_on(indx) := rows_in (indx).CHANGED_ON;
         END LOOP;
      l_progress := 'Execute FORALL for ' || rows_in.COUNT || ' rows';
         FORALL indx IN rows_in.FIRST .. rows_in.LAST
            SAVE EXCEPTIONS
            INSERT INTO DEPARTMENT (
               DEPARTMENT_ID,
               NAME,
               LOC_ID,
               CREATED_BY,
               CREATED_ON,
               CHANGED_BY,
               CHANGED_ON
               )
            VALUES (
               l_department_id(indx),
               l_name(indx),
               l_loc_id(indx),
               l_created_by(indx),
               l_created_on(indx),
               l_changed_by(indx),
               l_changed_on(indx)
               );
         l_department_id.DELETE;
         l_name.DELETE;
         l_loc_id.DELETE;
         l_created_by.DELETE;
         l_created_on.DELETE;
         l_changed_by.DELETE;
         l_changed_on.DELETE;
         rows_out := SQL%ROWCOUNT;
      END IF;
      IF qd_runtime.trace_enabled THEN
         qd_runtime.end_execution;
      END IF;
   EXCEPTION
     WHEN e_forall_error
     THEN
        -- In Oracle9i and above, SAVE EXCEPTIONS will direct control
        -- here if any error occurs. We can then save all the error
        -- information out to the error instance.
       qd_runtime.start_exception_handling;
       IF NOT handle_error_in THEN RAISE;
       ELSE
          <<bulk_handler>>
          DECLARE
             l_err_instance_id qd_err_instance_tp.id_t;
          BEGIN
             -- For each error, write to the log.
             FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
             LOOP
                qd_runtime.register_error (
                    error_name_in => 'FORALL-INSERT-FAILURE'
                   ,err_instance_id_out => l_err_instance_id
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'BINDING_ROW_' || indx
                  ,value_in => SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   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 => '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 => 'NAME'
                  ,value_in => l_name(indx)
                  ,validate_in => FALSE
                );
                qd_runtime.add_context (
                   err_instance_id_in => l_err_instance_id
                  ,NAME_IN => 'LOC_ID'
                  ,value_in => l_loc_id(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 => 'DEPARTMENT'
          ,name2_in => 'ROW_COUNT'
          ,value2_in => rows_in.COUNT
          ,name3_in => 'PROGRESS_INDICATOR'
          ,value3_in => l_progress
           );
       END IF;
   END ins;
   PROCEDURE upd (
      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 NULL,
      created_on_in IN DEPARTMENT_TP.CREATED_ON_t DEFAULT NULL,
      changed_by_in IN DEPARTMENT_TP.CHANGED_BY_t DEFAULT NULL,
      changed_on_in IN DEPARTMENT_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 DEPARTMENT SET
            NAME = NVL (name_in, NAME),
            LOC_ID = NVL (loc_id_in, LOC_ID),
            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
             DEPARTMENT_ID = department_id_in
         ;
      ELSE
         UPDATE DEPARTMENT SET
            NAME = name_in,
            LOC_ID = loc_id_in,
            CREATED_BY = created_by_in,
            CREATED_ON = created_on_in,
            CHANGED_BY = changed_by_in,
            CHANGED_ON = changed_on_in
          WHERE
             DEPARTMENT_ID = department_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_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