department_cp.pkb

OReilly Oracle PL SQL Programming第4版源代码

               ,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 upd_onecol;
   PROCEDURE upd_onecol_pky (
      colname_in IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE,
      new_value_in IN VARCHAR2,
      department_id_in IN DEPARTMENT_TP.DEPARTMENT_ID_t,
      rows_out OUT PLS_INTEGER
     ,handle_error_in IN BOOLEAN := TRUE
   )
   IS
      l_colname ALL_TAB_COLUMNS.COLUMN_NAME%TYPE := UPPER (colname_in);
   BEGIN
      IF qd_runtime.trace_enabled THEN
         qd_runtime.start_execution(program_name_in => 'upd_onecol_pky');
      END IF;
      IF l_colname = 'NAME' THEN
         UPDATE DEPARTMENT
            SET NAME = new_value_in
          WHERE
             DEPARTMENT_ID = department_id_in
              ;
         -- Perfect time for a GOTO!
         GOTO update_complete;
      END IF;
      IF l_colname = 'CREATED_BY' THEN
         UPDATE DEPARTMENT
            SET CREATED_BY = new_value_in
          WHERE
             DEPARTMENT_ID = department_id_in
              ;
         -- Perfect time for a GOTO!
         GOTO update_complete;
      END IF;
      IF l_colname = 'CHANGED_BY' THEN
         UPDATE DEPARTMENT
            SET CHANGED_BY = new_value_in
          WHERE
             DEPARTMENT_ID = department_id_in
              ;
         -- Perfect time for a GOTO!
         GOTO update_complete;
      END IF;
      <<update_complete>>
      rows_out := SQL%ROWCOUNT;
      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);
           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;
        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 upd_onecol_pky;
   PROCEDURE upd_onecol_pky (
      colname_in IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE,
      new_value_in IN DATE,
      department_id_in IN DEPARTMENT_TP.DEPARTMENT_ID_t,
      rows_out OUT PLS_INTEGER
     ,handle_error_in IN BOOLEAN := TRUE
   )
   IS
      l_colname ALL_TAB_COLUMNS.COLUMN_NAME%TYPE := UPPER (colname_in);
   BEGIN
      IF qd_runtime.trace_enabled THEN
         qd_runtime.start_execution(program_name_in => 'upd_onecol_pky');
      END IF;
      IF l_colname = 'CREATED_ON' THEN
         UPDATE DEPARTMENT
            SET CREATED_ON = new_value_in
          WHERE
             DEPARTMENT_ID = department_id_in
              ;
         -- Perfect time for a GOTO!
         GOTO update_complete;
      END IF;
      IF l_colname = 'CHANGED_ON' THEN
         UPDATE DEPARTMENT
            SET CHANGED_ON = new_value_in
          WHERE
             DEPARTMENT_ID = department_id_in
              ;
         -- Perfect time for a GOTO!
         GOTO update_complete;
      END IF;
      <<update_complete>>
      rows_out := SQL%ROWCOUNT;
      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);
           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;
        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