BULK COLLECT，FORALL，fetch ....limit

最新推荐文章于 2020-10-23 11:38:53 发布

sqcwfiu

最新推荐文章于 2020-10-23 11:38:53 发布

阅读量336

点赞数

分类专栏：数据库文章标签： Oracle SQL 数据结构编程

数据库专栏收录该内容

60 篇文章 0 订阅

订阅专栏

BULK COLLECT支持EMPLOYEES%rowtype形式

FORALL插入时不支持EMPLOYEES%rowtype形式，如果有多个字段，要定义多个数组来存放．

　　　　如果插入前有条件判断，可以用VALUES OF

用fetch .... limit 系统会根据limit的行数向源库逐批获取数据

关于 limit 参数

　　你可以根据你的实际来调整 limit 参数的大小，来达到你最优的性能。limit 参数会影响到 pga 的使用率。而且也可以在 fetch bulk 中省略 limit 参数，写成

fetch all_contacts_cur bulk collect into v_contacts;

　　有些资料中是说，如果不写 limit 参数，将会以数据库的 arraysize　参数值作为默认值。在 sqlplus 中用 show arraysize　可以看到该值默认为 15，set arraysize 256 可以更改该值。而实际上我测试不带 limit 参数时，外层循环只执行了一轮，好像不是 limit 15，所以不写 limit 参数时，可以去除外层循环，begin-end 部分可写成：

declare


  type RecTyp is RECORD(


    t dbms_sql.varchar2_table/*bill.hxz1.t%type*/,


    b dbms_sql.varchar2_table/*bill.hxz1.b%type*/);


  l_rec RecTyp;


  cursor c is


    select * from bill.hxz1;


BEGIN


  open c;


  loop


    fetch c bulk collect into l_rec.t,l_rec.b limit 100;


    forall i in 1..l_rec.t.count


      insert into hxz2(t,b) values(l_rec.t(i),l_rec.b(i));


    exit when c%notfound;


  end loop;


  commit;


  close c;


end;

现在你的测试中出现新的错误ORA-06502: PL/SQL: 数字或值错误，这个问题出现最可能的原因是当最后一次fetch cur bulk collect into n limit 10000;的时候，找不到任何记录，所以n.first和n.last是空的，加一句控制就可以。

我之前是把exit when cur%notfound;放在for循环之前,但是后来发现问题,如果纪录总数不能整除limit参数,那么最后一部份纪录将不会被游标扫描到.

declare
type curtype is ref cursor;
cur curtype;
type ntype is table of test.col1%type;
n ntype;
i number;
begin
dbms_output.disable;
dbms_output.enable(1000000);
open cur for select col1 from test;
dbms_output.put_line('第1条记录插入开始：'||to_char(sysdate,'hh24:mi:ss'));
loop
fetch cur bulk collect into n limit 10000;
if n.count > 0 then -- 检查n中的纪录数
for i in n.first .. n.last loop
insert into test2 (col1,col2) values (n(i),n(i)*n(i));
end loop;
end if;
commit;
exit when cur%notfound;
end loop;
dbms_output.put_line('第'||cur%rowcount||'条记录插入结束：'||to_char(sysdate,'hh24:mi:ss'));
close cur;
end;
/

你成功执行的那段代码中exit when放在 for i in ...之前，和我加控制的效果是一样的，所以不出错。

常在SQL语句中给PL/SQL变量赋值叫做绑定（Binding）,一次绑定一个完整的集合称为批量绑定（Bulk Binding）。

批量绑定（Bulk binds）可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能.

批量绑定（Bulk binds）包括：
(i) Input collections, use the FORALL statement，一般用来改善DML（INSERT、UPDATE和DELETE) 操作的性能
(ii) Output collections, use BULK COLLECT clause，一般用来提高查询（SELECT）的性能

FORALL的语法如下：

FORALL index IN lower_bound..upper_bound sql_statement;

create or replace procedure cp_data2 as
type TYPE_EMPLOYEES is table of EMPLOYEES%rowtype;
  V_EMPLOYEES TYPE_EMPLOYEES;
begin
  select * bulk collect into V_EMPLOYEES from employees @DBLINK1 ;
   for i in 1 .. V_EMPLOYEES.count loop
    insert /*+ APPEND*/ into employee_cp
    (EMPLOYEE_ID,   
FIRST_NAME,   
LAST_NAME,     
EMAIL,         
PHONE_NUMBER,  
HIRE_DATE,     
JOB_ID,        
SALARY,        
COMMISSION_PCT,
MANAGER_ID,   
DEPARTMENT_ID,
BIRTHDAY)
    values
      (V_EMPLOYEES(i).EMPLOYEE_ID,
       V_EMPLOYEES(i).FIRST_NAME,
       V_EMPLOYEES(i).LAST_NAME,
       V_EMPLOYEES(i).EMAIL,
       V_EMPLOYEES(i).PHONE_NUMBER,
       V_EMPLOYEES(i).HIRE_DATE,
       V_EMPLOYEES(i).JOB_ID,
       V_EMPLOYEES(i).SALARY,
       V_EMPLOYEES(i).COMMISSION_PCT,
       V_EMPLOYEES(i).MANAGER_ID,
       V_EMPLOYEES(i).DEPARTMENT_ID,
       V_EMPLOYEES(i).BIRTHDAY);
       if mod(i,5000)=0 then commit; end if;
       end loop;
  commit;
end;

FORALL语句的一个关键性改进，它可大大简化代码，并且对于那些要在PL/SQL程序中更新很多行数据的程序来说，它可显著提高其性能。

1:
用 FORALL来增强DML的处理能力
Oracle为Oracle8i中的PL/SQL引入了两个新的数据操纵语言（DML）语句：BULK COLLECT和FORALL。这两个语句在PL/SQL内部进行一种数组处理

；BULK COLLECT提供对数据的高速检索，FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。Oracle数据库使用这些语句大大减少了

PL/SQL与SQL语句执行引擎的环境切换次数，从而使其性能有了显著提高。

使用BULK COLLECT，你可以将多个行引入一个或多个集合中，而不是单独变量或记录中。下面这个BULK COLLECT的实例是将标题中包含

有"PL/SQL" 的所有书籍检索出来并置于记录的一个关联数组中，它们都位于通向该数据库的单一通道中。

DECLARE
   TYPE books_aat

      IS TABLE OF book%ROWTYPE
      INDEX BY PLS_INTEGER;
   books books_aat;
BEGIN
   SELECT *
     BULK COLLECT INTO book
     FROM books
    WHERE title LIKE '%PL/SQL%';
   ...
END;

类似地，FORALL将数据从一个PL/SQL集合传送给指定的使用集合的表。下面的代码实例给出一个过程，即接收书籍信息的一个嵌套表，并将该

集合（绑定数组）的全部内容插入该书籍表中。注意，这个例子还利用了Oracle9i的FORALL的增强功能，可以将一条记录直接插入到表中。

BULK COLLECT和FORALL都非常有用，它们不仅提高了性能，而且还简化了为PL/SQL中的SQL操作所编写的代码。下面的多行FORALL INSERT相当

清楚地说明了为什么PL/SQL被认为是Oracle数据库的最佳编程语言。

CREATE TYPE books_nt
IS TABLE OF book%ROWTYPE;
/
CREATE OR REPLACE PROCEDURE add_books (

books_in IN books_nt)
IS
BEGIN
FORALL book_index
    IN books_in.FIRST .. books_in.LAST
    INSERT INTO book
       VALUES books_in(book_index);
   ...
END;

不过在Oracle数据库10g之前，以FORAll方式使用集合有一个重要的限制：该数据库从IN范围子句中的第一行到最后一行，依次读取集合的内容

。如果在该范围内遇到一个未定义的行，Oracle数据库将引发ORA-22160异常事件：

ORA-22160: element at index [N] does not exist

对于FORALL的简单应用，这一规则不会引起任何麻烦。但是，如果想尽可能地充分利用FORALL，那么要求任意FORALL驱动数组都要依次填充可

能会增加程序的复杂性并降低性能。

在 Oracle数据库10g中，PL/SQL现在在FORALL语句中提供了两个新子句：INDICES OF与VALUES OF，它们使你能够仔细选择驱动数组中该由扩展

DML语句来处理的行。
当绑定数组为稀疏数组或者包含有间隙时，INDICES OF会非常有用。该语句的语法结构为：
FORALL indx IN INDICES

OF sparse_collection
INSERT INTO my_table
VALUES sparse_collection (indx);

VALUES OF用于一种不同的情况：绑定数组可以是稀疏数组，也可以不是，但我只想使用该数组中元素的一个子集。那么我就可以使用VALUES

OF 来指向我希望在DML操作中使用的值。该语句的语法结构为：
FORALL indx IN VALUES OF pointer_array
INSERT INTO my_table
VALUES binding_array (indx);

不用FOR循环而改用FORALL
假定我需要编写一个程序，对合格员工（由comp_analysis.is_eligible函数确定）加薪，编写关于不符合加薪条件的员工的报告并写入

employee_history 表。我在一个非常大的公司工作；我们的员工非常非常多。

对于一位PL/SQL开发人员来说，这并不是一项十分困难的工作。我甚至不需要使用BULK COLLECT或FORALL就可以完成这项工作，如清单 1所示

，我使用一个CURSOR FOR循环和单独的INSERT及UPDATE语句。这样的代码简洁明了；不幸地是，我花了10分钟来运行此代码，我的"老式"方法

要运行30分钟或更长时间。
清单 1:

CREATE OR REPLACE PROCEDURE give_raises_in_department (
        dept_in IN employee.department_id%TYPE
      , newsal IN employee.salary%TYPE
     )
     IS
        CURSOR emp_cur
        IS
           SELECT employee_id, salary, hire_date
             FROM employee
            WHERE department_id = dept_in;
     BEGIN
        FOR emp_rec IN emp_cur
        LOOP
           IF comp_analysis.is_eligible (emp_rec.employee_id)
           THEN
              UPDATE employee
                 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_department;

好在我公司的数据库升级到了Oracle9i，而且更幸运的是，在最近的Oracle研讨会上（以及Oracle技术网站提供的非常不错的演示中）我了解

到了批量处理方法。所以我决定使用集合与批量处理方法重新编写程序。写好的程序如清单 2所示。
清单 2:

CREATE OR REPLACE PROCEDURE give_raises_in_department (
    dept_in IN employee.department_id%TYPE
  , newsal IN employee.salary%TYPE
)
IS
    TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
       INDEX BY PLS_INTEGER;
    TYPE salary_aat IS TABLE OF employee.salary%TYPE
       INDEX BY PLS_INTEGER;
     TYPE hire_date_aat IS TABLE OF employee.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 employee
         WHERE department_id = dept_in;
     END;

     PROCEDURE partition_by_eligibility
     IS
     BEGIN
        FOR indx IN employee_ids.FIRST .. employee_ids.LAST
        LOOP
           IF comp_analysis.is_eligible (employee_ids (indx))
           THEN
              approved_employee_ids (indx) := employee_ids (indx);
           ELSE
              denied_employee_ids (indx) := employee_ids (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 employee
              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_department;

扫一眼清单1 和清单2 就会清楚地认识到：改用集合和批量处理方法将增加代码量和复杂性。但是，如果你需要大幅度提升性能，这还是值得

的。下面，我们不看这些代码，我们来看一看当使用FORALL时，用什么来处理CURSOR FOR循环内的条件逻辑。

定义集合类型与集合

在清单 2中，声明段的第一部分（第6行至第11行）定义了几种不同的集合类型，与我将从员工表检索出的列相对应。我更喜欢基于employee%

ROWTYPE 来声明一个集合类型，但是FORALL还不支持对某些记录集合的操作，在这样的记录中，我将引用个别字段。所以，我还必须为员工ID、

薪金和雇用日期分别声明其各自的集合。

接下来为每一列声明所需的集合（第13行至第21行）。首先定义与所查询列相对应的集合（第13行至第15行）：

employee_ids employee_aat;
salaries salary_aat;
hire_dates hire_date_aat;

然后我需要一个新的集合，用于存放已被批准加薪的员工的ID（第17行）：

approved_employee_ids employee_aat;

最后，我再为每一列声明一个集合（第19行至第21行），用于记录没有加薪资格的员工：

denied_employee_ids employee_aat;
denied_salaries salary_aat;
denied_hire_dates hire_date_aat;

深入了解代码

数据结构确定后，我们现在跳过该程序的执行部分（第72行至第75行），了解如何使用这些集合来加速进程。

retrieve_employee_info;
partition_by_eligibility;
add_to_history;
give_the_raise;

我编写此程序使用了逐步细化法（也被称为"自顶向下设计"）。所以执行部分不是很长，也不难理解，只有四行，按名称对过程中的每一步进

行了描述。首先检索员工信息（指定部门的所有员工）。然后进行划分，将要加薪和不予加薪的员工区分出来。完成之后，我就可以将那些不

予加薪的员工添加至员工历史表中，对其他员工进行加薪。

以这种方式编写代码使最终结果的可读性大大增强。因而我可以深入到该程序中对我有意义的任何部分。

有了已声明的集合，我现在就可以使用BULK COLLECT来检索员工信息（第23行至第30行）。这一部分有效地替代了CURSOR FOR循环。至此，数

据被加载到集合中。

划分逻辑（第32行至第46行）要求对刚刚填充的集合中的每一行进行检查，看其是否符合加薪条件。如果符合，我就将该员工ID从查询填充的

集合复制到符合条件的员工的集合。如果不符合，则复制该员工ID、薪金和雇用日期，因为这些都需要插入到employee_history表中。

初始数据现在已被分为两个集合，可以将其分别用作两个不同的FORALL语句（分别从第51行和第66行开始）的驱动器。我将不合格员工的集合

中的数据批量插入到employee_history（add_to_history）表中，并通过give_the_raise过程，在employee 表中批量更新合格员工的信息。

最后再仔细地看一看add_to_history（第48行至第61行），以此来结束对这个重新编写的程序的分析。FORALL语句（第51行）包含一个IN子句

，它指定了要用于批量INSERT的行号范围。在对程序进行第二次重写的说明中，我将把用于定义范围的集合称为"驱动集合"。但在

add_to_history的这一版本中，我简单地假定：使用在denied_employee_ids中定义的所有行。在INSERT自身内部，关于不合格员工的三个集

合都会被用到；我将把这些集合称为"数据集合"。可以看到，驱动集合与数据集合无需匹配。在学习Oracle数据库10g的新特性时，这是一个关

键点。

结果，清单 2 的行数大约是清单 1行数的2倍，但是清单 2 中的代码会在要求的时间内运行。在使用Oracle数据库10g之前，在这种情况下，

我只会对能够在这一时间内运行代码并开始下一个任务这一点感到高兴。

不过，有了Oracle数据库10g中最新版的PL/SQL，现在我就可以在性能、可读性和代码量方面作出更多的改进。
将VALUES OF用于此过程

在Oracle数据库10g中，可以指定 FORALL语句使用的驱动集合中的行的子集。可以使用以下两种方法之一来定义该子集：

将数据集合中的行号与驱动集合中的行号进行匹配。你需要使用INDICES OF子句。
将数据集合中的行号与驱动集合中所定义行中找到的值进行匹配。这需要使用VALUES OF子句。
在对give_raises_in_department进行第二次和最后一次改写中我将使用VALUES OF子句。清单 3 包含这个版本的全部代码。我将略过这一程序

中与前一版本相同的部分。

从声明集合开始，请注意我不再另外定义集合来存放合格的和不合格的员工信息，而是在清单 3 （第17行至第21行）中声明两个"引导"集合：

一个用于符合加薪要求的员工，另一个用于不符合加薪要求的员工。这两个集合的数据类型都是布尔型；不久将会看到，这些集合的数据类型

与FORALL语句毫无关系。FORALL语句只关心定义了哪些行。在员工表中拥有50 000行信息的give_raises_in_department的三种执行方法的占

用时间执行方法用时
CURSOR FOR循环 00:00:38.01
Oracle数据库10g之前的批量处理 00:00:06.09
Oracle 数据库10g的批量处理 00:00:02.06

在员工表中拥有100,000行数据的 give_raises_in_department的三种执行方法的占用时间执行方法用时
CURSOR FOR循环 00:00:58.01
Oracle数据库10g之前的批量处理 00:00:12.00
Oracle数据库10g的批量处理 00:00:05.05

表1：处理50,000行和100,000行数据的用时测试结果

retrieve_employee_info 子程序与前面的相同，但是对数据进行划分的方式完全不同（第32行至第44行）。我没有将记录从一个集合复制到另

一个集合（这个操作相对较慢），而只是确定与员工ID集合中的行号相匹配的相应引导集合中的行（通过为其指定一个TRUE值）。

现在可以在两个不同FORALL 语句（由第49行和第65行开始）中，将approved_list和denied_list集合用作驱动集合。

为了插入到 employee_history表中，我使用了如下语句：

FORALL indx IN VALUES OF denied_list

为了进行更新（给员工进行加薪），我使用这一格式：

FORALL indx IN VALUES OF approved_list

在这两个DML语句中，数据集合是在BULK COLLECT 检索步骤中填充的最初的集合；没有进行过复制。利用VALUES OF，Oracle数据库在这些数据

集合的行中进行筛选，仅使用行号与驱动集合中行号相匹配的行
利用本程序中的VALUES OF，可以避免复制对全部记录进行复制，而是用行号的一个简单列表来替换它们。对于大型数组，进行这些复制的开销

是非常可观的。为了测试 Oracle数据库10g的优越性，我装入employee表并对50,000行和100,000行的数据运行测试。为了模拟更多的现实情况

，我将Oracle数据库10g之前的批量处理的执行方法作了修改以进行集合内容的多次复制。然后我使用SQL*Plus SET TIMING ON来显示运行各个

不同的执行方法所用的时间。表 1 给出了结果。

从这些时间测定得到的结论非常清楚：由单个DML语句变为批量处理将大幅缩短耗用时间，数据为50,000行时的用时由38秒减为6秒，数据为

100,000行时的用时由58秒减为12 秒。而且，通过使用VALUES OF来避免复制数据，我可以将用时缩短一半左右。

sqcwfiu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
BULK COLLECT，FORALL，fetch ....limit

BULK COLLECT支持EMPLOYEES%rowtype形式FORALL插入时不支持EMPLOYEES%rowtype形式，如果有多个字段，要定义多个数组来存放．　　　　如果插入前有条件判断，可以用VALUES OF 用fetch .... limit 系统会根据limit的行数向源库逐批获取数据关于 limit 参数　　你可以根据你的实际来调整 limit ...
复制链接

扫一扫