end;
3.使用BULK COLLECT和FORALL ，分批插入,多次提交,比较适用于大表; 前期数据字段定义比较烦锁(表各个字段必须分开定义)
-------------------
create or replace procedure cp_data as
type type_EMPLOYEE_ID is table of EMPLOYEES.EMPLOYEE_ID%type;
type type_FIRST_NAME is table of EMPLOYEES.FIRST_NAME%type;
type type_LAST_NAME is table of EMPLOYEES.LAST_NAME%type;
type type_EMAIL is table of EMPLOYEES.EMAIL%type;
type type_PHONE_NUMBER is table of EMPLOYEES.PHONE_NUMBER%type;
type type_HIRE_DATE is table of EMPLOYEES.HIRE_DATE%type;
type type_JOB_ID is table of EMPLOYEES.JOB_ID%type;
type type_SALARY is table of EMPLOYEES.SALARY%type;
type type_COMMISSION_PCT is table of EMPLOYEES.COMMISSION_PCT%type;
type type_MANAGER_ID is table of EMPLOYEES.MANAGER_ID%type;
type type_DEPARTMENT_ID is table of EMPLOYEES.DEPARTMENT_ID%type;
type type_BIRTHDAY is table of EMPLOYEES.BIRTHDAY%type;
V_EMPLOYEE_ID TYPE_EMPLOYEE_ID;
V_FIRST_NAME TYPE_FIRST_NAME;
V_LAST_NAME TYPE_LAST_NAME;
V_EMAIL TYPE_EMAIL;
V_PHONE_NUMBER TYPE_PHONE_NUMBER;
V_HIRE_DATE TYPE_HIRE_DATE;
V_JOB_ID TYPE_JOB_ID;
V_SALARY TYPE_SALARY;
V_COMMISSION_PCT TYPE_COMMISSION_PCT;
V_MANAGER_ID TYPE_MANAGER_ID;
V_DEPARTMENT_ID TYPE_DEPARTMENT_ID;
V_BIRTHDAY TYPE_BIRTHDAY;
type t_cur is ref cursor;
c_table t_cur;
v_table varchar2(30); --dest table
v_sql varchar2(300);
v_rows number := 50;
begin
v_table := 'EMPLOYEE_CP';
open c_table for
select * from employees; --sour table
v_sql := 'insert /*+ APPEND*/ into ' || v_table ||
' (EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID,
BIRTHDAY)
values (:1, :2,:3,:4,:5,:6, :7, :8,:9,:10, :11,:12)';
loop
fetch c_table --.EMPLOYEE_ID, c_table.FIRST_NAME, c_table.LAST_NAME, c_table.EMAIL, c_table.PHONE_NUMBER, c_table.HIRE_DATE, c_table.JOB_ID, c_table.SALARY, c_table.COMMISSION_PCT, c_table.MANAGER_ID, c_table.DEPARTMENT_ID, c_table.BIRTHDAY
bulk collect
into V_EMPLOYEE_ID, V_FIRST_NAME, V_LAST_NAME, V_EMAIL, V_PHONE_NUMBER, V_HIRE_DATE, V_JOB_ID, V_SALARY, V_COMMISSION_PCT, V_MANAGER_ID, V_DEPARTMENT_ID, V_BIRTHDAY limit v_rows; --分批
forall i in 1 .. V_EMPLOYEE_ID.count execute immediate v_sql using
V_EMPLOYEE_ID(i), V_FIRST_NAME(i), V_LAST_NAME(i),
V_EMAIL(i), V_PHONE_NUMBER(i), V_HIRE_DATE(i),
V_JOB_ID(i), V_SALARY(i), V_COMMISSION_PCT(i),
V_MANAGER_ID(i), V_DEPARTMENT_ID(i), V_BIRTHDAY(i)
;
commit;
exit when c_table%notfound;
end loop;
end;
---------------------------------------------------------
4相关附助SQL:
select 'type TYPE_' || column_name || ' is table of ' || table_name || '.' ||
column_name || '%type'
from dba_tab_columns
where table_name = 'EMPLOYEES'
and owner = 'HYF'
select 'V_' || column_name || ' TYPE_' || column_name ||';'
from dba_tab_columns
where table_name = 'EMPLOYEES'
and owner = 'HYF'
select 'V_' || column_name || ','
from dba_tab_columns
where table_name = 'EMPLOYEES'
and owner = 'HYF'
select 'V_' || column_name || '(i),'
from dba_tab_columns
where table_name = 'EMPLOYEES'
and owner = 'HYF'


bulk collect 用法记事本_我看世界 2010-08-29 21:21:14 阅读175 评论0    字号：大中小 订阅
通过bulk collect减少loop处理的开销
发表人:logzgh | 发表时间: 2006年五月19日, 10:56
采用bulk collect可以将查询结果一次性地加载到collections中。
而不是通过cursor一条一条地处理。
可以在select into,fetch into,returning into语句使用bulk collect。
注意在使用bulk collect时，所有的into变量都必须是collections.
举几个简单的例子：
--在select into语句中使用bulk collect
DECLARE
    TYPE sallist IS TABLE OF emp.sal%TYPE;
    sals    sallist;
BEGIN
-- Limit the number of rows to 100.
    SELECT sal
    BULK COLLECT INTO sals
      FROM emp
     WHERE ROWNUM <= 100;
-- Retrieve 10% (approximately) of the rows in the table.
    SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;
END;
/
--在fetch into中使用bulk collect
DECLARE
    TYPE deptrectab IS TABLE OF dept%ROWTYPE;
    dept_recs    deptrectab;
    CURSOR c1
    IS
       SELECT deptno, dname, loc
         FROM dept
        WHERE deptno > 10;
BEGIN
    OPEN c1;
    FETCH c1
    BULK COLLECT INTO dept_recs;
END;
/
--在returning into中使用bulk collect
CREATE TABLE emp2 AS SELECT * FROM employees;
DECLARE
    TYPE numlist IS TABLE OF employees.employee_id%TYPE;
    enums    numlist;
    TYPE namelist IS TABLE OF employees.last_name%TYPE;
    names    namelist;
BEGIN
    DELETE FROM emp2
          WHERE department_id = 30
      RETURNING        employee_id, last_name
    BULK COLLECT INTO enums, names;
    DBMS_OUTPUT.put_line ('Deleted ' || SQL%ROWCOUNT || ' rows:');
    FOR i IN enums.FIRST .. enums.LAST
    LOOP
       DBMS_OUTPUT.put_line ('Employee #' || enums (i) || ': ' || names (i));
    END LOOP;
END;
/
DROP TABLE emp2;
这里有一个很好的例子
CREATE OR REPLACE PACKAGE comp_analysis
IS
    FUNCTION is_eligible (id_in IN lots_of_employees.employee_id%TYPE)
       RETURN BOOLEAN;
END comp_analysis;
/    
CREATE OR REPLACE PACKAGE BODY comp_analysis
IS
    FUNCTION is_eligible (id_in IN lots_of_employees.employee_id%TYPE)
       RETURN BOOLEAN


    IS
    BEGIN
       RETURN MOD (id_in, 2) = 0;
    END;
END comp_analysis;
/    
CREATE OR REPLACE PROCEDURE give_raises_in_department1 (
    dept_in IN lots_of_employees.department_id%TYPE
, newsal IN lots_of_employees.salary%TYPE
)
IS
    CURSOR emp_cur
    IS
       SELECT employee_id, salary, hire_date
         FROM lots_of_employees
        WHERE (department_id = dept_in OR dept_IN IS NULL);
    emp_rec emp_cur%ROWTYPE;
BEGIN
    OPEN emp_cur;
    LOOP
       FETCH emp_cur
        INTO emp_rec;
       EXIT WHEN emp_cur%NOTFOUND;
       IF comp_analysis.is_eligible (emp_rec.employee_id)
       THEN
          UPDATE lots_of_employees
             SET salary = newsal
           WHERE employee_id = emp_rec.employee_id;
       ELSE
          INSERT INTO employee_history
                      (employee_id, salary
                     , hire_date, activity
                      )
               VALUES (emp_rec.employee_id, emp_rec.salary
                     , emp_rec.hire_date, 'RAISE DENIED'
                      );
       END IF;
    END LOOP;
END give_raises_in_department1;
/
SHO ERR
REM
REM Pre-10g create multiple copies of collection
REM for different purposes.
REM
CREATE OR REPLACE PROCEDURE give_raises_in_department2 (
    dept_in IN lots_of_employees.department_id%TYPE
, newsal IN lots_of_employees.salary%TYPE
)
IS
    TYPE employee_aat IS TABLE OF lots_of_employees.employee_id%TYPE
       INDEX BY PLS_INTEGER;
    TYPE salary_aat IS TABLE OF lots_of_employees.salary%TYPE
       INDEX BY PLS_INTEGER;


    TYPE hire_date_aat IS TABLE OF lots_of_employees.hire_date%TYPE
       INDEX BY PLS_INTEGER;
      
    employee_ids employee_aat;
    salaries salary_aat;
    hire_dates hire_date_aat;
    approved_employee_ids employee_aat;
   
    denied_employee_ids employee_aat;
    denied_salaries salary_aat;
    denied_hire_dates hire_date_aat;
    PROCEDURE retrieve_employee_info
    IS
    BEGIN
       SELECT employee_id, salary, hire_date
       BULK COLLECT INTO employee_ids, salaries, hire_dates
         FROM lots_of_employees
        WHERE (department_id = dept_in OR dept_IN IS NULL);
    END;
    PROCEDURE partition_by_eligibility
    IS
    BEGIN
       FOR indx IN employee_ids.FIRST .. employee_ids.LAST
       LOOP
          IF comp_analysis.is_eligible (employees (indx))
          THEN
             approved_employee_ids (indx) := employees (indx);
          ELSE
             denied_employee_ids (indx) := employees (indx);
             denied_salaries (indx) := salaries (indx);
             denied_hire_dates (indx) := hire_dates (indx);
          END IF;
       END LOOP;
    END;
    PROCEDURE add_to_history
    IS
    BEGIN
       FORALL indx IN denied_employee_ids.FIRST .. denied_employee_ids.LAST
          INSERT INTO employee_history
                      (employee_id
                     , salary
                     , hire_date, activity
                      )
               VALUES (denied_employee_ids (indx)


                     , denied_salaries (indx)
                     , denied_hire_dates (indx), 'RAISE DENIED'
                      );
    END;
    PROCEDURE give_the_raise
    IS
    BEGIN
       FORALL indx IN approved_employee_ids.FIRST .. approved_employee_ids.LAST
          UPDATE lots_of_employees
             SET salary = newsal
           WHERE employee_id = approved_employee_ids (indx);
    END;
BEGIN
    retrieve_employee_info;
    partition_by_eligibility;
    add_to_history;
    give_the_raise;
END give_raises_in_department2;
/
SHO ERR
REM
REM 10g usage of INDICES OF
REM
CREATE OR REPLACE PROCEDURE give_raises_in_department3 (
    dept_in IN lots_of_employees.department_id%TYPE
, newsal IN lots_of_employees.salary%TYPE
)
IS
    TYPE employee_aat IS TABLE OF lots_of_employees.employee_id%TYPE
       INDEX BY PLS_INTEGER;
    TYPE salary_aat IS TABLE OF lots_of_employees.salary%TYPE
       INDEX BY PLS_INTEGER;
    TYPE hire_date_aat IS TABLE OF lots_of_employees.hire_date%TYPE
       INDEX BY PLS_INTEGER;
      
    employee_ids employee_aat;
    salaries salary_aat;
    hire_dates hire_date_aat;
    TYPE guide_aat IS TABLE OF BOOLEAN
       INDEX BY PLS_INTEGER;
    approved_list guide_aat;
    denied_list guide_aat;
    PROCEDURE retrieve_employee_info
    IS
    BEGIN
       SELECT employee_id, salary, hire_date
       BULK COLLECT INTO employee_ids, salaries, hire_dates
         FROM lots_of_employees
        WHERE (department_id = dept_in OR dept_IN IS NULL);
    END;
    PROCEDURE partition_by_eligibility
    IS
    BEGIN
       FOR indx IN employee_ids.FIRST .. employee_ids.LAST
       LOOP
          IF comp_analysis.is_eligible (employees(indx))


          THEN
             approved_list (indx) := TRUE;
          ELSE
             denied_list (indx) := TRUE;
          END IF;
       END LOOP;
    END;
    PROCEDURE add_to_history
    IS
    BEGIN
       FORALL indx IN INDICES OF denied_list
          INSERT INTO employee_history
                      (employee_id
                     , salary
                     , hire_date, activity
                      )
               VALUES (employees (indx)
                     , salaries (indx)
                     , hire_dates (indx)
                     , 'RAISE DENIED'
                      );
    END;
    PROCEDURE give_the_raise
    IS
    BEGIN
       FORALL indx IN INDICES OF approved_list
          UPDATE lots_of_employees
             SET salary = newsal
               , hire_date = hire_dates(indx)
           WHERE employee_id = employees(indx);
    END;
BEGIN
    retrieve_employee_info;
    partition_by_eligibility;
    add_to_history;
    give_the_raise;
END give_raises_in_department3;
/
SHO ERR
REM
REM 10g usage of VALUES OF
REM
CREATE OR REPLACE PROCEDURE give_raises_in_department4 (
    dept_in IN lots_of_employees.department_id%TYPE
, newsal IN lots_of_employees.salary%TYPE
)
IS


    TYPE employee_aat IS TABLE OF lots_of_employees.employee_id%TYPE
       INDEX BY PLS_INTEGER;
    TYPE salary_aat IS TABLE OF lots_of_employees.salary%TYPE
       INDEX BY PLS_INTEGER;
    TYPE hire_date_aat IS TABLE OF lots_of_employees.hire_date%TYPE
       INDEX BY PLS_INTEGER;
      
    employee_ids employee_aat;
    salaries salary_aat;
    hire_dates hire_date_aat;
    TYPE guide_aat IS TABLE OF PLS_INTEGER
       INDEX BY PLS_INTEGER;
    approved_list guide_aat;
    denied_list guide_aat;
    PROCEDURE retrieve_employee_info
    IS
    BEGIN
       SELECT employee_id, salary, hire_date
       BULK COLLECT INTO employee_ids, salaries, hire_dates
         FROM lots_of_employees
        WHERE (department_id = dept_in OR dept_IN IS NULL);
    END;
    PROCEDURE partition_by_eligibility
    IS
    BEGIN
       FOR indx IN employee_ids.FIRST .. employee_ids.LAST
       LOOP
          IF comp_analysis.is_eligible (employees(indx))
          THEN
             approved_list (indx) := indx;
          ELSE
             denied_list (indx) := indx;
          END IF;
       END LOOP;
    END;
    PROCEDURE add_to_history
    IS
    BEGIN
       FORALL indx IN VALUES OF denied_list
          INSERT INTO employee_history
                      (employee_id
                     , salary
                     , hire_date, activity
                      )
               VALUES (employees (indx)
                     , salaries (indx)
                     , hire_dates (indx)
                     , 'RAISE DENIED'
                      );
    END;
    PROCEDURE give_the_raise
    IS
    BEGIN
       FORALL indx IN VALUES OF approved_list
          UPDATE lots_of_employees
             SET salary = newsal
               , hire_date = hire_dates(indx)
           WHERE employee_id = employees(indx);
    END;
BEGIN
    retrieve_employee_info;
    partition_by_eligibility;
    add_to_history;
    give_the_raise;
END give_raises_in_department4;
/
SHO ERR
SET TIMING ON
BEGIN
    give_raises_in_department1 (NULL, 1000);
END;
/
BEGIN
    give_raises_in_department2 (NULL, 1000);
END;
/
BEGIN
    give_raises_in_department3 (NULL, 1000);
END;
/