employee_cp.pkb

OReilly Oracle PL SQL Programming第4版源代码

           ,value1_in => NVL (sequence_in, 'employee_seq')
           );
   END next_key;

   PROCEDURE ins (
      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,
      employee_id_out IN OUT EMPLOYEE_TP.EMPLOYEE_ID_t,
      handle_error_in IN BOOLEAN := TRUE
   )
   IS
      l_pky EMPLOYEE_TP.EMPLOYEE_ID_t := next_key;
   BEGIN
      ins (
         employee_id_in => l_pky,
         last_name_in => last_name_in,
         first_name_in => first_name_in,
         middle_initial_in => middle_initial_in,
         job_id_in => job_id_in,
         manager_id_in => manager_id_in,
         hire_date_in => hire_date_in,
         salary_in => salary_in,
         commission_in => commission_in,
         department_id_in => department_id_in,
         empno_in => empno_in,
         ename_in => ename_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
         );
      employee_id_out := l_pky;
   END ins;

   FUNCTION ins (
      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
   )
      RETURN
         EMPLOYEE_TP.EMPLOYEE_ID_t
   IS
      l_pky EMPLOYEE_TP.EMPLOYEE_ID_t := next_key;
   BEGIN
      ins (
         employee_id_in => l_pky,
         last_name_in => last_name_in,
         first_name_in => first_name_in,
         middle_initial_in => middle_initial_in,
         job_id_in => job_id_in,
         manager_id_in => manager_id_in,
         hire_date_in => hire_date_in,
         salary_in => salary_in,
         commission_in => commission_in,
         department_id_in => department_id_in,
         empno_in => empno_in,
         ename_in => ename_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
         );
      RETURN l_pky;
   END ins;

    PROCEDURE ins (
      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
      l_pky EMPLOYEE_TP.EMPLOYEE_ID_t := next_key;
   BEGIN
      ins (
         employee_id_in => l_pky,
         last_name_in => last_name_in,
         first_name_in => first_name_in,
         middle_initial_in => middle_initial_in,
         job_id_in => job_id_in,
         manager_id_in => manager_id_in,
         hire_date_in => hire_date_in,
         salary_in => salary_in,
         commission_in => commission_in,
         department_id_in => department_id_in,
         empno_in => empno_in,
         ename_in => ename_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 EMPLOYEE_TP.EMPLOYEE_tc
     ,rows_out OUT PLS_INTEGER
     ,handle_error_in IN BOOLEAN := TRUE
   )
   IS
      l_employee_id EMPLOYEE_TP.EMPLOYEE_ID_cc;
      l_last_name EMPLOYEE_TP.LAST_NAME_cc;
      l_first_name EMPLOYEE_TP.FIRST_NAME_cc;
      l_middle_initial EMPLOYEE_TP.MIDDLE_INITIAL_cc;
      l_job_id EMPLOYEE_TP.JOB_ID_cc;
      l_manager_id EMPLOYEE_TP.MANAGER_ID_cc;
      l_hire_date EMPLOYEE_TP.HIRE_DATE_cc;
      l_salary EMPLOYEE_TP.SALARY_cc;
      l_commission EMPLOYEE_TP.COMMISSION_cc;
      l_department_id EMPLOYEE_TP.DEPARTMENT_ID_cc;
      l_empno EMPLOYEE_TP.EMPNO_cc;
      l_ename EMPLOYEE_TP.ENAME_cc;
      l_created_by EMPLOYEE_TP.CREATED_BY_cc;
      l_created_on EMPLOYEE_TP.CREATED_ON_cc;
      l_changed_by EMPLOYEE_TP.CHANGED_BY_cc;
      l_changed_on EMPLOYEE_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_employee_id collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying EMPLOYEE_ID to column collection row ' || indx;
            l_employee_id(indx) := rows_in (indx).EMPLOYEE_ID;
         END LOOP;
         l_progress := 'Populate l_last_name collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying LAST_NAME to column collection row ' || indx;
            l_last_name(indx) := rows_in (indx).LAST_NAME;
         END LOOP;
         l_progress := 'Populate l_first_name collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying FIRST_NAME to column collection row ' || indx;
            l_first_name(indx) := rows_in (indx).FIRST_NAME;
         END LOOP;
         l_progress := 'Populate l_middle_initial collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying MIDDLE_INITIAL to column collection row ' || indx;
            l_middle_initial(indx) := rows_in (indx).MIDDLE_INITIAL;
         END LOOP;
         l_progress := 'Populate l_job_id collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying JOB_ID to column collection row ' || indx;
            l_job_id(indx) := rows_in (indx).JOB_ID;
         END LOOP;
         l_progress := 'Populate l_manager_id collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying MANAGER_ID to column collection row ' || indx;
            l_manager_id(indx) := rows_in (indx).MANAGER_ID;
         END LOOP;
         l_progress := 'Populate l_hire_date collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying HIRE_DATE to column collection row ' || indx;
            l_hire_date(indx) := rows_in (indx).HIRE_DATE;
         END LOOP;
         l_progress := 'Populate l_salary collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying SALARY to column collection row ' || indx;
            l_salary(indx) := rows_in (indx).SALARY;
         END LOOP;
         l_progress := 'Populate l_commission collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying COMMISSION to column collection row ' || indx;
            l_commission(indx) := rows_in (indx).COMMISSION;
         END LOOP;
         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_empno collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying EMPNO to column collection row ' || indx;
            l_empno(indx) := rows_in (indx).EMPNO;
         END LOOP;
         l_progress := 'Populate l_ename collection';
         FOR indx IN rows_in.FIRST .. rows_in.LAST
         LOOP
            l_progress := 'Copying ENAME to column collection row ' || indx;
            l_ename(indx) := rows_in (indx).ENAME;
         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 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 (
               l_employee_id(indx),
               l_last_name(indx),
               l_first_name(indx),
               l_middle_initial(indx),
               l_job_id(indx),
               l_manager_id(indx),
               l_hire_date(indx),
               l_salary(indx),
               l_commission(indx),
               l_department_id(indx),
               l_empno(indx),
               l_ename(indx),
               l_created_by(indx),
               l_created_on(indx),
               l_changed_by(indx),
               l_changed_on(indx)
               );
         l_employee_id.DELETE;
         l_last_name.DELETE;
         l_first_name.DELETE;
         l_middle_initial.DELETE;
         l_job_id.DELETE;
         l_manager_id.DELETE;
         l_hire_date.DELETE;
         l_salary.DELETE;
         l_commission.DELETE;
         l_department_id.DELETE;
         l_empno.DELETE;
         l_ename.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 (