oracle批量绑定 forall bulk collect用法以及测试案例

合集下载

41批量绑定

批量绑定（Bulk Bind）用于在执行SQL操作时传递所有PL/SQL集合元素的的数据。

可以极快地大地加快批量数据的处理速度，从而提高应用程序的性能。

批量绑定是使用BULK COLLECT子句和forall语句来完成的。

BULK COLLECT适用于select，fetch，dml。

而forall 只是用于DML●传统循环和批量绑定传统处理大批量的数据，需要使用循环语句。

9i以后，使用批量绑定。

CREATE TABLE DEMO(ID NUMBER(6),NAME V ARCHAR2(10));⏹使用传统循环SQL> DECLARE2 TYPE ID_TABLE_TYPE IS TABLE OF NUMBER(6)3 INDEX BY BINARY_INTEGER;4 TYPE NAME_TABLE_TYPE IS TABLE OF VARCHAR2(10)5 INDEX BY BINARY_INTEGER;6 ID_TABLE ID_TABLE_TYPE;7 NAME_TABLE NAME_TABLE_TYPE;8 START_TIME NUMBER(20);9 END_TIME NUMBER(10);10 BEGIN11 FOR I IN 1..100000 LOOP12 ID_TABLE(I):=I;13 NAME_TABLE(I):='NAME'||TO_CHAR(I);14 END LOOP;15 START_TIME:=DBMS_UTILITY.get_time;16 FOR I IN 1..ID_TABLE.COUNT LOOP17 INSERT INTO DEMO VALUES(ID_TABLE(I),NAME_TABLE(I));18 END LOOP;19 END_TIME:=DBMS_UTILITY.get_time;20 DBMS_OUTPUT.put_line('总计时间：'||to_char((end_time-start_time)/100));21 end;22 /总计时间：10.47⏹使用批量绑定SQL> DECLARE2 TYPE ID_TABLE_TYPE IS TABLE OF NUMBER(6)3 INDEX BY BINARY_INTEGER;4 TYPE NAME_TABLE_TYPE IS TABLE OF VARCHAR2(10)5 INDEX BY BINARY_INTEGER;6 ID_TABLE ID_TABLE_TYPE;7 NAME_TABLE NAME_TABLE_TYPE;8 START_TIME NUMBER(20);9 END_TIME NUMBER(10);10 BEGIN11 FOR I IN 1..100000 LOOP12 ID_TABLE(I):=I;13 NAME_TABLE(I):='NAME'||TO_CHAR(I);14 END LOOP;15 START_TIME:=DBMS_UTILITY.get_time;16 FORALL I IN 1..ID_TABLE.COUNT17 INSERT INTO DEMO VALUES(ID_TABLE(I),NAME_TABLE(I));19 END_TIME:=DBMS_UTILITY.get_time;20 DBMS_OUTPUT.put_line('总计时间：'||to_char((end_time-start_time)/100));21 end;22 /总计时间：.75PL/SQL procedure successfully completed使用BULK COLLECT子句。

oracle中批量batchupdate的执行原理

oracle中批量batchupdate的执行原理在Oracle数据库中，批量更新（Batch Update）是指一次性执行多个更新操作，而不是逐条执行单个更新操作。

这可以显著提高性能，减少数据库通信开销。

Oracle数据库提供了多种方式来执行批量更新，其中一种常见的方式是使用PL/SQL语言的FORALL语句。

以下是一些关于Oracle中批量更新的执行原理的要点：FORALL 语句：在PL/SQL中，使用 FORALL 语句可以一次性提交一批 SQL 语句，而不是单独提交每个语句。

这可以通过减少与数据库服务器的通信次数来提高性能。

DECLARETYPE emp_id_array IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER;emp_ids emp_id_array;TYPE salary_array IS TABLE OF employees.salary%TYPE INDEX BY PLS_INTEGER;salaries salary_array;BEGIN-- 初始化数组 emp_ids 和 salaries-- ...FORALL i IN emp_ids.FIRST..emp_STUPDATE employeesSET salary = salaries(i)WHERE employee_id = emp_ids(i);END;Bulk Collect：在PL/SQL中，可以使用BULK COLLECT语句一次性从数据库中检索多行数据，然后将其存储在集合中。

这可以减少与数据库的通信次数，提高性能。

DECLARETYPE emp_id_array IS TABLE OF employees.employee_id%TYPE;emp_ids emp_id_array;TYPE salary_array IS TABLE OF employees.salary%TYPE;salaries salary_array;BEGIN-- 初始化数组 emp_ids 和 salaries-- ...SELECT employee_id, salaryBULK COLLECT INTO emp_ids, salariesFROM employeesWHERE department_id = 10;FORALL i IN 1..emp_ids.COUNTUPDATE employeesSET salary = salaries(i)WHERE employee_id = emp_ids(i);END;使用 Merge 语句： MERGE语句允许同时执行插入、更新和删除操作，可以用于批量更新。

FORALL与BULK COLLECT的使用方法

enums IdList;
TYPE NameList IS TABLE OF test_er_name%TYPE;
names NameList;
BEGIN
DELETE FROM test_forall2 WHERE user_id = 10100
INSERT INTO test_forall VALUES v_table(idx);
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
END ;
在returning into中使用bulk collect
d. If the failure of a DML operation on a particular row is not a serious problem,Include the keywords SAVE EXCEPTIONS in the FORALL statement and report Or clean up the errors in a subsequent loop using the %BULK_EXCEPTIONS Attribute.
depts NumList := NumList(10, 30, 70, ...);
-- department numbers
BEGIN
...
FOR i IN depts. FIRST ..depts. LAST
LOOP
dbms_output.put_line( 'User #' || enums(i) || ': ' || names(i));
END LOOP;
COMMIT ;

批量绑定— BULK COLLECT

批量绑定—BULK COLLECT 在PL/SQL 编码中，经常会从表中获取结果集，然后进行一些逻辑处理，再生成新的数据。

我们想到的最直接的方法就是使用游标。

从Oracle8i 开始，出现了一个新的子句：BULK COLLECT。

可以降低SQL 引擎到PL/SQL 引擎的上下文交换（context switch）次数，从而实现数据的高速检索。

1. 速度比较从表中获取结果集，我们常用的方法就是使用游标循环，我们看看它的执行速度：我们再看一下完成同样的功能，BULK COLLECT 的执行速度：BULK COLLECT 要比游标循环快得多。

如果你的代码中有游标循环，并且你也正为这里执行缓慢而发愁，不妨试试BULK COLLECT。

2. 没有NO_DATA_FOUND 异常不过要记得，SELECT ... BULK COLLECT INTO ... 不会引起NO_DATA_FOUND 异常，虽然它的语法看起来和SELECT ... INTO ... 类似。

要判断BULK COLLECT 是否获得数据，可以通过COUNT 方法：3. 其他使用除了SELECT INTO 外，还可以在FETCH INTO 和RETURNING INTO 子句中使用BULK COLLECT：4. 联合数组中使用BULK COLLECT前面我们举的例子，都是用的嵌套表，其实也可以使用联合数组：BULK COLLECT 生成的集合，下表是默认从1开始的数字，步进为1，所以联合数组的索引，不能使用varchar2 类型。

如果你不小心使用了varchar2 类型作为联合数组的索引，就会出现下面的错误：5. V ARRAY 数组中使用BULK COLLECT而对于V ARRAY 数组，BULK COLLECT 也适用，不过定义时，其长度必须得大于或等于结果集的长度：如果V ARRAY 数组长度定义得过小，则会提示错误：如果V ARRAY 数组长度定义得过大，没什么影响，而且也不会占用多余的内存空间：不过推荐首选联合数组，或是嵌套表，在这里虽然可以使用V ARRAY 数组，但显然是不太合适的，就像steven 所介绍的那样。

forall+用法小结

FORALL 用法小结：作者：sonic本文主要翻译、整理了ORACLE官方文档上有关FORALL的部份内容，不妥之处，还希望多和大家交流。

在发送语句到SQL引擎前，FORALL语句告知PL/SQL 引擎批挷定输入集合。

尽管FORALL语句包含一个迭代(iteration)模式，它并不一是个FOR循环。

其语法为：FORALL index IN lower_bound..upper_boundsql_statement;一、如何使用批挷定提高性能（How Do Bulk Binds Improve Performance）在PL/SQL 和SQL引擎(engines)中，太多的上下文切换(context switches)会影响性能。

这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。

而使用批挷定能显著提高性能。

下图显示PL/SQL引擎和SQL引擎之间的context switches：（PL/SQL引擎执行存过语句仅发送SQL语句到SQL引擎，SQL引擎执行语句后返回数据给PL/SQL引擎）PL/SQL引擎发送一次SQL语句给SQL引擎，在SQL引擎中则为范围中每个index数字执行一次SQL语句。

PL/SQL挷定操作包含以下三类：in-bind：When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.out-bind：When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement. define： When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.在SQL语句中，为PL/SQL变量指定值称为挷定(binding)，DML语句能传递所有集合元素到一个单个操作中，这过程称为批挷定（bulk binding）。

ORACLE FORALL介绍

ORACLE FORALL介绍ORACLE 10G OFFICIAL DOCUMNET-------------------------------------------------------------------------一介绍：1、语法for all statement ::=bounds_clause ::=2、关键字与参数介绍：==index_name：一个无需声明的标识符，作为集合下标使用；==sql_statement：静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE IMMEDIATE）DML 语句。

==SAVE EXCEPTIONS：可选关键字，表示即使一些DML语句失败，直到FORALL loop执行完毕才抛出异常。

可以使用SQL%BULK_EXCEPTIONS 查看异常信息。

==lower_bound .. upper_bound：数字表达式，来指定一组连续有效的索引数字。

该表达式只需解析一次。

==INDICES OF collection_name：用于指向稀疏数组的实际下标==VALUES OF index_collection_name：用于指向集合的一个子集的下标数组二使用FORALL：1、循环中声明删除语句（Issuing DELETE Statements in a Loop1CREATE TABLE employees_temp AS SELECT * FROM employees;2DECLARE3 TYPE NumList IS VARRAY(20) OF NUMBER;4 depts NumList := NumList(10, 30, 70); -- department numbers5BEGIN6 FORALL i IN ST7DELETE FROM employees_temp WHERE department_id = depts(i);8COMMIT;9END;10/2、循环中声明插入语句（Issuing INSERT Statements in a Loop）11CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));12CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));13DECLARE14 TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;15 TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;16 pnums NumTab;17 pnames NameTab;18 iterations CONSTANT PLS_INTEGER := 50000;19 t1 INTEGER;20 t2 INTEGER;21 t3 INTEGER;22BEGIN23FOR j IN 1..iterations LOOP -- load index-by tables24 pnums(j) := j;25 pnames(j) := 'Part No. ' || TO_CHAR(j);26END LOOP;27 t1 := DBMS_UTILITY.get_time;28FOR i IN 1..iterations LOOP -- use FOR loop29INSERT INTO parts1 VALUES (pnums(i), pnames(i));30END LOOP;31 t2 := DBMS_UTILITY.get_time;32 FORALL i IN 1..iterations -- use FORALL statement33INSERT INTO parts2 VALUES (pnums(i), pnames(i));34 t3 := DBMS_UTILITY.get_time;35 DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');36 DBMS_OUTPUT.PUT_LINE('---------------------');37 DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((t2 - t1)/100));38 DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR((t3 - t2)/100));39COMMIT;40END;41/FORALL要明显快于FOR..LOOP结构：42Execution Time (secs)43---------------------44FOR loop: 5.1445FORALL: .564647PL/SQL 过程已成功完成。

oracle bulk collect into 顺序

oracle bulk collect into 顺序关于"Oracle bulk collect into 顺序"的主题，我将为您编写一篇1500-2000字的文章，详细回答您的问题。

标题：Oracle bulk collect into 顺序- 优化数据集处理的有效方法引言：在Oracle数据库中，处理大数据集时，往往需要使用bulk collect into子句来提高性能和效率。

本文将分步骤讨论bulk collect into 的顺序，并介绍如何在处理大数据集时正确使用它。

一、什么是Oracle bulk collect into？Oracle bulk collect into是一种检索和插入数据的特殊方法，它允许将多行数据一次性读取到结果集中或者一次性插入到表或变量中。

相比使用逐行处理，bulk collect into可以提供更高的性能和效率。

二、bulk collect into的顺序是什么？在使用bulk collect into时，有一定的顺序需要遵守。

以下是正确的顺序：1. 定义一个可以存储数据的集合变量（collection variable），例如一个PL/SQL表变量或一个记录类型变量。

2. 编写一个SELECT语句来检索想要的数据，并将结果赋值给集合变量。

3. 使用bulk collect into子句将结果集一次性读取到集合变量中。

4. 在需要的地方使用集合变量来操作数据。

请注意，集合变量的定义和使用必须放在同一个PL/SQL块中。

定义集合变量的代码必须位于使用bulk collect into之前。

这样可以确保集合变量在bulk collect操作之前被正确初始化。

三、如何正确使用bulk collect into？1. 初始化集合变量：在使用bulk collect into之前，需要对集合变量进行初始化操作。

通常，可以使用集合类型的构造函数或者ALLOCATE语句来实现。

oracleforall用法

oracleforall用法Oracle FORALL 是一种在Oracle数据库中处理多行数据的高效方法，可以减少CPU和I/O资源的消耗。

它可以替代常规的SQL分行操作，从而在某些情况下大幅提高性能。

FORALL 语句是在PL/SQL中使用的，用于批量地执行DML（Data Manipulation Language）语句，如 INSERT、UPDATE 或 DELETE。

在FORALL 语句中，我们可以指定一个操作的数组范围，并使用BULK COLLECT 子句读取数组或使用SAVE EXCEPTIONS 子句来捕获异常。

FORALL语句的语法如下：FORALL inde某 IN lower_bound..upper_boundDML statement下面是一些使用FORALL语句的示例：1.使用BULKCOLLECT子句读取数据：DECLARETYPE prod_name_tab IS TABLE OF products.product_name%TYPE;prod_names prod_name_tab;BEGINSELECT product_name BULK COLLECT INTO prod_names FROM products;END;2.使用FORALL更新多行数据：DECLARETYPE prod_name_tab IS TABLE OF products.product_name%TYPE INDEX BY PLS_INTEGER;prod_names prod_name_tab;BEGINSELECT product_name BULK COLLECT INTO prod_names FROM products;FORALL i IN prod_names.FIRST..prod_STUPDATE products SET product_name = prod_names(i) WHERE product_id = i;END;3.使用SAVEEXCEPTIONS子句捕获异常：DECLARETYPE prod_name_tab IS TABLE OF products.product_name%TYPE INDEX BY PLS_INTEGER;prod_names prod_name_tab;err_inde某 NUMBER;err_code NUMBER;err_msg VARCHAR2(100);BEGINSELECT product_name BULK COLLECT INTO prod_names FROM products;FORALL i IN prod_names.FIRST..prod_STUPDATE products SET product_name = prod_names(i) WHERE product_id = iSAVE EXCEPTIONS INTO err_inde某;COMMIT;EXCEPTIONWHENOTHERSTHENerr_code := SQL%BULK_EXCEPTIONS(i).ERROR_CODE;err_msg := SQL%BULK_EXCEPTIONS(i).ERROR_MESSAGE;DBMS_OUTPUT.PUT_LINE('Error at inde某 ' ， err_inde某， ': Code=' ， err_code ， ', Msg=' ， err_msg);。

oraclefetchinto语法oracle下巧用bulkcollect实现curso

oraclefetchinto语法oracle下巧用bulkcollect实现cursoFETCH INTO语法是用于从游标中将结果集的数据存储到变量中的Oracle SQL语句。

它的一般语法格式如下所示：```sqlFETCH cursor_name INTO variable1 [, variable2, ...];```其中，`cursor_name`是已经声明的游标变量的名称，`variable1, variable2, ...`是要接收结果集数据的变量名称。

Oracle SQL中的`BULK COLLECT`关键字用于一次性获取多个结果集条目，并将其存储到集合类型的变量中。

通过结合`BULK COLLECT`和`FETCH INTO`语法，我们可以实现将游标的结果集存储到集合变量中。

下面是一个示例代码，演示了如何使用`BULKCOLLECT`和`FETCHINTO`语法来实现将游标的结果集存储到集合变量中：```sqlDECLARETYPE emp_tab_type IS TABLE OF employees%ROWTYPE;emp_tab emp_tab_type;CURSOR emp_cursor ISSELECT * FROM employees;BEGINOPEN emp_cursor;FETCH emp_cursor BULK COLLECT INTO emp_tab;CLOSE emp_cursor;-- 现在我们可以使用 emp_tab 变量来访问结果集数据FOR i IN 1..emp_tab.COUNT LOOPDBMS_OUTPUT.PUT_LINE('Employee ID: ' ，emp_tab(i).employee_id', Employee Name: ' ， emp_tab(i).first_name' ' ， emp_tab(i).last_name);ENDLOOP;END;```在以上代码中，我们首先定义了一个与`employees`表中行的结构相匹配的类型`emp_tab_type`，然后声明了一个以`emp_cursor`为源的游标变量。

oracle collect by 用法

Oracle中的COLLECT函数是一种非常有用的聚合函数，它可以将一列值聚合成单个集合值。

在本篇文章中，我将探讨COLLECT函数的用法，并结合具体示例，向您展示如何在实际应用中使用这个功能。

1. COLLECT函数概述COLLECT函数是Oracle提供的一种集合函数，它可以用于将一列值转换成一个集合（也称为多行数组）。

它通常与GROUP BY子句一起使用，用来聚合行数据，返回一个包含指定列值的集合。

2. COLLECT函数的基本语法下面是COLLECT函数的基本语法：```SELECTcolumn_name,COLLECT (column_name2)FROMtable_nameGROUP BYcolumn_name;```3. COLLECT函数的应用示例假设我们有一个包含员工尊称和所属部门的表格employee_table，我们现在想要统计每个部门中的员工尊称。

我们可以使用COLLECT函数来实现这一需求。

示例代码如下：```SELECTdepartment_name,COLLECT (employee_name) AS employee_listFROMemployee_tableGROUP BYdepartment_name;```通过这个示例，我们可以得到每个部门的员工尊称集合，方便后续对员工信息进行分析和处理。

4. COLLECT函数的灵活应用除了基本的用法外，COLLECT函数还可以结合其他的函数和条件，实现更加灵活的聚合操作。

我们可以结合WHERE子句来对要聚合的数据进行筛选；还可以将COLLECT函数嵌套在其他查询中，实现更加复杂的数据处理操作。

5. 个人观点和理解在实际工作中，我发现COLLECT函数非常适用于需要对大量数据进行聚合分析的场景。

它可以简化复杂的数据处理过程，提高了数据处理的效率和可读性。

与此COLLECT函数的灵活性也使得我们可以根据具体的需求来对数据进行个性化的处理，满足了不同业务场景下的数据分析要求。

oracle bulk collect into 顺序 -回复

oracle bulk collect into 顺序-回复题目：Oracle中的Bulk Collect Into语句的执行顺序详解引言：在Oracle数据库中，Bulk Collect Into语句是一种非常强大且高效的数据获取方式。

它能够将查询结果集一次性的取回，并存储在集合中，避免了逐行处理结果集的性能瓶颈。

本文将深入探讨Bulk Collect Into语句的执行顺序，帮助读者更好地理解和应用这个功能。

第一部分：Bulk Collect Into语句概述Bulk Collect Into是Oracle PL/SQL语言中的一种特殊的查询和存储机制。

它允许将一个或多个表的查询结果集一次性存储在一个集合中，并可以通过循环操作集合来对结果进行处理。

这种方式相比传统的逐行处理结果集的方法更加高效，尤其是在处理大数据量时。

第二部分：Bulk Collect Into语句的关键语法Bulk Collect Into语句的语法结构如下：BULK COLLECT INTO <collection_name>FROM <table_name>[WHERE <condition>];其中，<collection_name>是存储查询结果的集合变量名，<table_name>是需要查询的表名，<condition>是可选的查询条件。

第三部分：Bulk Collect Into语句的执行过程Bulk Collect Into语句的执行过程可以分为以下步骤：1. 解析语句：首先，Oracle数据库会解析Bulk Collect Into语句的语法和语义，检查集合变量和表名的合法性，并对查询语句进行语义分析。

2. 优化查询计划：接下来，Oracle会根据查询语句的复杂程度和查询表的大小等因素，生成一个最优的查询计划。

这个查询计划将决定如何通过索引或全表扫描来获取数据。

oracle bulk collect into 语法

oracle bulk collect into 语法Oracle中的BULK COLLECT INTO语法用于从一个或多个表或查询结果集中，将数据一次性存储到一个集合中。

以下是BULK COLLECT INTO语法的示例：```sqlDECLARETYPE emp_tabtype IS TABLE OF employees%ROWTYPE;emp_tab emp_tabtype;BEGINSELECT * BULK COLLECT INTO emp_tab FROM employees; -- 执行操作...END;```上面的示例中，我们首先定义了一个包含EMPLOYEES表的行类型的集合类型emp_tabtype。

然后，我们声明了一个名为emp_tab的emp_tabtype类型的变量。

接下来，我们使用SELECT语句查询EMPLOYEES表，并通过BULK COLLECT INTO语句将结果集存储到emp_tab变量中。

一旦数据存储到emp_tab变量中，我们可以对其进行操作，例如对每条记录进行迭代或执行其他操作。

需要注意的是，在使用BULK COLLECT INTO语句时，需要确保变量类型与查询的结果集的列类型匹配。

否则，将会引发类型不匹配的错误。

此外，BULK COLLECT INTO还可以与LIMIT子句一起使用，以限制要存储的记录数。

例如：```sqlDECLARETYPE emp_tabtype IS TABLE OF employees%ROWTYPE;emp_tab emp_tabtype;BEGINSELECT * BULK COLLECT INTO emp_tab FROM employees WHERE ROWNUM <= 100;-- 执行操作...END;```上面的示例中，我们使用LIMIT子句限制存储到emp_tab变量中的记录数最多为100条。

fetch bulk collect into 批量效率的读取游标数据

Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据[网站建设之]Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据通常我们获取游标数据是用 fetch some_cursor into var1, var2 的形式，当游标中的记录数不多时不打紧。

然而自Oracle 8i 起，Oracle 为我们提供了 fetch bulk collect 来批量取游标中的数据，存中即是合理的。

它能在读取游标中大量数据的时候提升效率，就像 SNMP 协议中，V2 版比 V1 版新加了GET-BULK PDU 一样，也是用来更高效的批量取设备上的节点值（原来做过网管软件开发，故联想到此）。

fetch bulk collect into 的使用格式是：fetch some_cursor collect into col1, col2 limit xxx。

col1、col2 是声明的集合类型变量，xxx 为每次取数据块的大小（记录数），相当于缓冲区的大小，可以不指定 limit xxx 大小。

下面以实际的例子来说明它的使用，并与逐条取记录的 fetch into 执行效率上进行比较。

测试环境是 Oracle 10g 10.2.1.0，查询的联系人表 sr_contacts 中有记录数 1802983 条，游标中以rownum 限定返回的记录数。

使用 fetch bulk collect into 获取游标数据declare--声明需要集合类型及变量，参照字段的 type 来声明类型type id_type is table of sr_contacts.sr_contact_id%type;v_id id_type;type phone_type is table of sr_contacts.contact_phone%type;v_phone phone_type;type remark_type is table of sr_contacts.remark%type;v_remark remark_type;cursor all_contacts_cur is --用 rownum 来限定取出的记录数来测试select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;beginopen all_contacts_cur;loopfetch all_contacts_cur bulk collect into v_id,v_phone,v_remark limit 256;for i in 1..v_id.count loop --遍历集合--用 v_id(i)/v_phone(i)/v_remark(i) 取出字段值来执行你的业务逻辑null; --这里只放置一个空操作，只为测试循环取数的效率end loop;exit when all_contacts_cur%notfound; --exit 不能紧接 fetch 了，不然会漏记录 end loop;close all_contacts_cur;end;declare--声明需要集合类型及变量，参照字段的 type 来声明类型type id_type is table of sr_contacts.sr_contact_id%type;v_id id_type;type phone_type is table of sr_contacts.contact_phone%type;v_phone phone_type;type remark_type is table of sr_contacts.remark%type;v_remark remark_type;cursor all_contacts_cur is --用 rownum 来限定取出的记录数来测试select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000; beginopen all_contacts_cur;loopfetch all_contacts_cur bulk collect into v_id,v_phone,v_remark limit 256;for i in 1..v_id.count loop --遍历集合--用 v_id(i)/v_phone(i)/v_remark(i) 取出字段值来执行你的业务逻辑null; --这里只放置一个空操作，只为测试循环取数的效率end loop;exit when all_contacts_cur%notfound; --exit 不能紧接 fetch 了，不然会漏记录 end loop;close all_contacts_cur;end;使用 fetch into 逐行获取游标数据declare--声明变量，参照字段的 type 来声明类型v_id sr_contacts.sr_contact_id%type;v_phone sr_contacts.contact_phone%type;v_remark sr_contacts.remark%type;cursor all_contacts_cur is --用 rownum 来限定取出的记录数来测试select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000; beginopen all_contacts_cur;loopfetch all_contacts_cur into v_id,v_phone,v_remark;exit when all_contacts_cur%notfound;--用 v_id/v_phone/v_remark 取出字段值来执行你的业务逻辑null; --这里只放置一个空操作，只为测试循环取数的效率end loop;close all_contacts_cur;end;declare--声明变量，参照字段的 type 来声明类型v_id sr_contacts.sr_contact_id%type;v_phone sr_contacts.contact_phone%type;v_remark sr_contacts.remark%type;cursor all_contacts_cur is --用 rownum 来限定取出的记录数来测试select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;begin open all_contacts_cur;loopfetch all_contacts_cur into v_id,v_phone,v_remark;exit when all_contacts_cur%notfound;--用 v_id/v_phone/v_remark 取出字段值来执行你的业务逻辑null; --这里只放置一个空操作，只为测试循环取数的效率end loop;close all_contacts_cur;end;执行性能比较看看测试的结果，分别执行五次所耗费的秒数：当 rownum <= 100000 时：fetch bulk collect into 耗时：0.125秒, 0.125秒, 0.125秒, 0.125秒, 0.141秒fetch into 耗时： 1.266秒, 1.250秒, 1.250秒, 1.250秒, 1.250秒当 rownum <= 1000000 时：fetch bulk collect into 耗时：1.157秒, 1.157秒, 1.156秒, 1.156秒, 1.171秒fetch into 耗时： 12.128秒, 12.125秒, 12.125秒, 12.109秒, 12.141秒当 rownum <= 10000 时：fetch bulk collect into 耗时：0.031秒, 0.031秒, 0.016秒, 0.015秒, 0.015秒fetch into 耗时： 0.141秒, 0.140秒, 0.125秒, 0.141秒, 0.125秒当 rownum <= 1000 时：fetch bulk collect into 耗时：0.016秒, 0.015秒, 0.016秒, 0.016秒, 0.015秒fetch into 耗时： 0.016秒, 0.031秒, 0.031秒, 0.032秒, 0.015秒从测试结果来看游标的记录数越大时，用 fetch bulk collect into 的效率很明显示，趋于很小时就差不多了。

OraclePLSQL开发基础（第二十弹：批量绑定和BULKCOLLECT）

OraclePLSQL开发基础（第二十弹：批量绑定和BULKCOLLECT）批量绑定在编写PL/SQL代码时，PL/SQL引擎通常会与SQL引擎进行交互，比如将SQL语句发送到PL/SQL引擎，SQL引擎在执行了SQL语句后，会返回一些数据给PL/SQL引擎。

频繁的交互会大大降低效率，例如下面的示例将在一个循环中向SQL引擎发送多条DELETE指令，会导致效率非常低下。

DECLARETYPE dept_type IS VARRAY (20) OF NUMBER; --定义嵌套表变量depts dept_type:=dept_type (10, 30, 70); --实例化嵌套表，分配3个元素BEGINFOR i IN ST --循环嵌套表元素LOOPDELETE FROM empWHERE deptno = depts (i); --向SQL引擎发送SQL命令执行SQL操作END LOOP;END;•1•2•3•4•5•6•7•8•9•10可以看到要删除emp表中特定部门编号的记录，代码通过循环依次向SQL引擎发送SQL语句，这样的操作方式会降低执行的性能，特别是当元素个数比较多的时候。

如果使用PL/SQL的批量绑定，将一次性向SQL引擎发送所有的SQL语句，会显著的提高执行的性能。

在示例中，一次一个depts集合元素的传递是造成性能降低的关键点，可以批量传递集合中的元素来执行，这个过程称为批量绑定。

要使用批量绑定，可以使用FORALL语句，该语句将输入的集合送到SQL引擎之前，通知PL/SQL引擎将集合中的所有元素进行批量绑定。

FORALL语句并不是一个FOR循环，它仅包含了一个重复的步骤，用来通知PL/SQL引擎在将SQL语句发送给SQL引擎之前，将集合中的所有元素批量绑定，以便一次性将多个绑定到SQL语句的变量一次性发送给SQL引擎。

FORALL使用如下：FORALL index IN lower_bound..upper_boundsql_statement;•1•2语法的含义如下：- index：只能在FORALL语句块内作为集合下标使用。

Mybatis对oracle数据库进行foreach批量插入操作

Mybatis对oracle数据库进⾏foreach批量插⼊操作MySQL⽀持的语法INSERT INTO `tableX` (`a`,`b`,`c`,`d`,`e`) VALUES<foreach collection ="list" item="param" index= "index" separator =",">(param.a,param.b,param.c,param.d,param.e)</foreach>oracle语法insert into tableX(a,b,c)select*from (select1,2,3from dualunionselect4,5,6from dual) t在使⽤mybatis时，oracle需要写成下⾯格式<foreach collection="list" item="file" index="index" separator="UNION">最近做⼀个批量导⼊的需求，将多条记录批量插⼊数据库中。

解决思路：在程序中封装⼀个List集合对象，然后把该集合中的实体插⼊到数据库中，因为项⽬使⽤了MyBatis，所以打算使⽤MyBatis的foreach功能进⾏批量插⼊。

期间遇到了“SQL 命令未正确结束 ”的错误，最终解决，记录下来供以后查阅和学习。

⾸先，在⽹上参考了有关Mybatis的foreach insert的，具体如下：foreach的主要⽤在构建in条件中，它可以在SQL语句中进⾏迭代⼀个集合。

foreach元素的属性主要有 item，index，collection，open，separator，close。

item表⽰集合中每⼀个元素进⾏迭代时的别名，index指定⼀个名字，⽤于表⽰在迭代过程中，每次迭代到的位置，open表⽰该语句以什么开始，separator表⽰在每次进⾏迭代之间以什么符号作为分隔符，close表⽰以什么结束，在使⽤foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不⼀样的，主要有⼀下3种情况：1.如果传⼊的是单参数且参数类型是⼀个List的时候，collection属性值为list2.如果传⼊的是单参数且参数类型是⼀个array数组的时候，collection的属性值为array3.如果传⼊的参数是多个的时候，我们就需要把它们封装成⼀个Map了，当然单参数也可以封装成map然后，照葫芦画瓢写了如下的xml⽂件，xxxMapper.xml⽂件：<insert id="addSupCity" parameterType="java.util.List"><selectKey keyProperty="cityId" order="BEFORE" resultType="String"><![CDATA[SELECT SEQ_OCL_SUPCITY.NEXTVAL FROM dual]]></selectKey>INSERT INTO T_OCL_SUPCITY(CITY_ID,CITY_CODE, CITY_NAME, AREA_DESC, SUP_ID, STAT)VALUES<foreach collection="list" item="item" index="index" separator=",">(#{item.cityId,jdbcType=VARCHAR},#{item.cityCode,jdbcType=VARCHAR},#{item.cityName,jdbcType=VARCHAR},#{item.areaDesc,jdbcType=VARCHAR},#{item.supId,jdbcType=VARCHAR},#{item.stat,jdbcType=VARCHAR})</foreach></insert>但是运⾏起来后就⼀直报错，报错信息如下：### SQL: INSERT INTO T_OCL_SUPCITY(CITY_ID,CITY_CODE, CITY_NAME, AREA_DESC, SUP_ID, STAT) VALUES (?,?,?,?,?),(?,?,?,?,?)### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束把SQL复制出来在PL/SQL中运⾏也是报同样的错，如上也可以看出，使⽤批量插⼊执⾏的SQL语句等价于： INSERT INTOT_OCL_SUPCITY (CITY_ID,CITY_CODE, CITY_NAME, AREA_DESC, SUP_ID, STAT) VALUES (?,?,?,?,?),(?,?,?,?,?)，⽽在oracle中⽤insert into xxx values (xxx,xxx),(xxx,xxx) 这种语法是通不过的。

aql collect 用法

aql collect 用法AQL(ArangoDB Query Language) collect用于通过指定的属性或表达式获取文档的数据，并将其汇总为一个数组。

collect语句可以在查询语句的末尾使用。

以下是collect的用法示例：1. 收集文档属性：```FOR doc IN collectionCOLLECT attribute = doc.attributeRETURN attribute```这将收集指定集合中所有文档的属性值，并作为一个数组返回。

2. 使用计数器进行收集：```FOR doc IN collectionCOLLECT attribute = doc.attribute WITH COUNT INTO counterRETURN { attribute, count: counter }```这将收集指定集合中所有文档的属性值，并将每个属性的数量计数，并将属性和计数值作为对象返回。

3. 使用筛选条件进行收集：```FOR doc IN collectionFILTER doc.attribute == valueCOLLECT attribute = doc.attributeRETURN attribute```这将收集满足筛选条件的文档的属性值，并作为一个数组返回。

4. 使用表达式进行收集：```FOR doc IN collectionCOLLECT attr = doc.attribute1 + doc.attribute2RETURN attr```这将对每个文档的两个属性进行表达式计算，然后将结果收集并返回。

在使用collect语句时，还可以使用其他关键字，如SORT对结果进行排序，LIMIT对结果进行限制等。

请注意，collect可能会占用大量的内存，因为它需要将所有收集的数据保留在内存中。

如果收集的数据过大，可能会导致性能问题，因此请谨慎使用collect语句。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

Oracle 2009-10-11 13:13:22 阅读118 评论0 字号：大中小oracle批量绑定 forall bulk collect用法以及测试案例采用bulk collect可以将查询结果一次性地加载到collections中。

而不是通过cursor 一条一条地处理。

可以在select into,fetch into,returning into语句使用bulk collect。

注意在使用bulk collect时，所有的into变量都必须是collections.--在select into语句中使用bulk collectDECLARETYPE SalList IS TABLE OF emp.sal%TYPE;sals SalList;BEGIN-- Limit the number of rows to 100.SELECT sal BULK COLLECT INTO sals FROM empWHERE ROWNUM <= 100;-- Retrieve 10% (approximately) of the rows in the table.SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;--在fetch into中使用bulk collectDECLARETYPE DeptRecTab IS TABLE OF dept%ROWTYPE;dept_recs DeptRecTab;CURSOR c1 ISSELECT deptno, dname, loc FROM dept WHERE deptno > 10;BEGINOPEN c1;FETCH c1 BULK COLLECT INTO dept_recs;END;/--在returning into中使用bulk collectCREATE TABLE emp2 AS SELECT * FROM employees;DECLARETYPE NumList IS TABLE OF employees.employee_id%TYPE;enums NumList;TYPE NameList IS TABLE OF st_name%TYPE;names NameList;BEGINDELETE FROM emp2 WHERE department_id = 30RETURNING employee_id, last_name BULK COLLECT INTO enums, names;dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');FOR i IN enums.FIRST .. STLOOPdbms_output.put_line('Employee #' || enums(i) || ': ' || names(i));END LOOP;END;/DROP TABLE emp2;oracle批量绑定forall bulk collect批量绑定（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）的性能10g开始forall语句可以使用三种方式：i in low..upi in indices of collection 取得集合元素下标的值i in values of collection 取得集合元素的值forall语句还可以使用部分集合元素sql%bulk_rowcount(i)表示forall语句第i元素所作用的行数CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));CREATE TABLE parts3 (pnum INTEGER, pname VARCHAR2(15));CREATE TABLE parts4 (pnum INTEGER, pname VARCHAR2(15));set serveroutput on --把屏幕显示开关置上DECLARETYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;pnums NumTab;pnames NameTab;iterations CONSTANT PLS_INTEGER := 50000;t1 INTEGER; t2 INTEGER; t3 INTEGER; t4 INTEGER; t5 INTEGER;stmt_str varchar2(255);table_name varchar2(255);BEGINFOR j IN 1..iterations LOOP -- load index-by tablespnums(j) := j;pnames(j) := 'Part No. ' || TO_CHAR(j);END LOOP;FOR i IN 1..iterations LOOP -- use FOR loopINSERT INTO parts1 VALUES (pnums(i), pnames(i));END LOOP;FORALL i IN 1..iterations -- use FORALL statementINSERT INTO parts2 VALUES (pnums(i), pnames(i));t3 := dbms_utility.get_time;table_name:='parts3';stmt_str := 'INSERT INTO ' || table_name || ' values (:num, :pname)'; FOR i IN 1..iterations LOOP -- use FORALL statementEXECUTE IMMEDIATE stmt_str USING pnums(i), pnames(i);END LOOP;t4 := dbms_utility.get_time;table_name:='parts4';stmt_str := 'INSERT INTO ' || table_name || ' values (:num, :pname)';FORALL i IN 1..iterations-- use FORALL statementEXECUTE IMMEDIATE stmt_str USING pnums(i), pnames(i);t5 := dbms_utility.get_time;dbms_output.put_line('Execution Time (secs)');dbms_output.put_line('---------------------');dbms_output.put_line('FOR loop: ' || TO_CHAR((t2 - t1)/100));dbms_output.put_line('FORALL: ' || TO_CHAR((t3 - t2)/100));dbms_output.put_line('FOR loop: ' || TO_CHAR((t4 - t3)/100));dbms_output.put_line('FORALL: ' || TO_CHAR((t5 - t4)/100));END;/DROP TABLE parts1;DROP TABLE parts2;bulk collect 语句：用于取得批量数据，只适用于select into ,fetch into 及DML语句的返回子句TYPE type_emp IS TABLE OF scott.emp%ROWTYPE INDEX BY BINARY_INTEGER;tab_emp type_emp;TYPE type_ename IS TABLE OF scott.emp.ename%TYPE INDEX BY BINARY_INTEGER; tab_ename type_ename;CURSOR c ISSELECT *SELECT * BULK COLLECTINTO tab_empFROM scott.emp;dbms_output.put_line(tab_emp(i).ename);DELETE scott.emp RETURNING ename BULK COLLECT INTO tab_ename;FOR i IN 1 .. tab_emp.COUNT LOOPdbms_output.put_line(tab_emp(i).ename);END LOOP;FETCH c BULK COLLECTINTO tab_emp;dbms_output.new_line;FOR i IN 1 .. tab_emp.COUNT LOOPdbms_output.put_line(tab_emp(i).sal);*/使用Bulk Collect提高Oracle查询效率Oracle8i中首次引入了Bulk Collect特性，该特性可以让我们在PL/SQL中能使用批查询，批查询在某些情况下能显著提高查询效率。

现在，我们对该特性进行一些简单的测试和分析。

1．首先，我们创建一个表，并插入100000条记录在SQL/Plus中执行下列脚本：drop table empl_tbl/create table empl_tbl(last_name varchar2(20),first_name varchar2(10),salary number(10))for i in 3000..102999 loopinsert into empl_tbl(last_name,first_name,salary) values('carl'||(i-3000),'wu'||(103000-i),i);end loop;end;/commit/select count(*) from empl_tbl;/2．使用三种方法计算表中某一字段含有多少个不重复值2.1 使用常规的Distinct来实现SQL> select count(distinct last_name) "Distinct Last Name" from empl_tbl; Distinct Last Name-----------------100000Executed in 0.36 seconds我们可以看到，常规方法需要0.36秒查出该表中有100000个不重复的Last_name值。