department_cp.pkb

OReilly Oracle PL SQL Programming第4版源代码

             ,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;

   PROCEDURE upd (
      rec_in IN DEPARTMENT_TP.DEPARTMENT_rt,
      rows_out OUT PLS_INTEGER,
      ignore_if_null_in IN BOOLEAN := FALSE
     ,handle_error_in IN BOOLEAN := TRUE
      )
   IS
   BEGIN
      upd (
         department_id_in => rec_in.DEPARTMENT_ID,
         name_in => rec_in.NAME,
         loc_id_in => rec_in.LOC_ID,
         created_by_in => rec_in.CREATED_BY,
         created_on_in => rec_in.CREATED_ON,
         changed_by_in => rec_in.CHANGED_BY,
         changed_on_in => rec_in.CHANGED_ON,
        rows_out => rows_out,
        ignore_if_null_in => ignore_if_null_in,
        handle_error_in => handle_error_in
      );
   END upd;

   PROCEDURE upd_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 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
      l_rows PLS_INTEGER;
   BEGIN
      upd (
         department_id_in => department_id_in,
         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,
          rows_out => l_rows
         ,ignore_if_null_in => ignore_if_null_in
         ,handle_error_in => handle_error_in
       );
      IF l_rows = 0
      THEN
         ins (
            department_id_in => department_id_in,
            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
         );
         l_rows := 1;
      END IF;
      rows_out := l_rows;
   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 upd_ins;
   FUNCTION dynupdstr (
      colname_in IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE,
      where_in IN VARCHAR2 := NULL)
   RETURN VARCHAR2
   IS
   BEGIN
      RETURN
         'UPDATE DEPARTMENT
             SET ' || colname_in || ' = :value
           WHERE ' || NVL (where_in, '1=1');
   END dynupdstr;
   PROCEDURE upd_onecol (
      colname_in IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE,
      new_value_in IN VARCHAR2,
      where_in IN VARCHAR2 := NULL,
      rows_out OUT PLS_INTEGER
     ,handle_error_in IN BOOLEAN := TRUE
   )
   IS
   BEGIN
      IF qd_runtime.trace_enabled THEN
         qd_runtime.start_execution(program_name_in => 'upd_onecol');
      END IF;
      EXECUTE IMMEDIATE dynupdstr (colname_in, where_in)
      USING new_value_in;
      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