oracle批绑定

采用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;
 
 

oracle批量绑定forall bulk collect

oracle批量绑定forall bulk collect http://otn.oracle.com/oramag/oracle...tech_plsql.html
                                                           http://otn.oracle.com/docs/products...colls.htm#23723
关于Bulk Binds中LIMIT的使用,请看TOM的解说
                                                           http://asktom.oracle.com/pls/ask/f?...5918938803188,Y
Oracle10g中对于forall的增强
                                                           http://www.itpub.net/showthread.php?s=&threadid=184794
批量绑定(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..up
       i 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   --把屏幕显示开关置上
DECLARE
     TYPE 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);
BEGIN
     FOR j IN 1..iterations LOOP -- load index-by tables
          pnums(j) := j;
          pnames(j) := 'Part No. ' || TO_CHAR(j);
     END LOOP;
    
     t1 := dbms_utility.get_time;
     FOR i IN 1..iterations LOOP -- use FOR loop
          INSERT INTO parts1 VALUES (pnums(i), pnames(i));
     END LOOP;
    
     t2 := dbms_utility.get_time;
     FORALL i IN 1..iterations -- use FORALL statement
          INSERT 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 statement
          EXECUTE 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 statement
          EXECUTE 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));
    
     COMMIT;
END;
/

DROP TABLE parts1;
DROP TABLE parts2;
DROP TABLE parts3;
DROP TABLE parts4;

使用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))
/

begin
for i in 3000..102999 loop
insert 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
------------------
            100000
 
Executed in 0.36 seconds

我们可以看到,常规方法需要0.36秒查出该表中有100000个不重复的Last_name值。

2.2 使用游标来实现

我们执行下面语句来统计Last_name字段的不重复值个数:

declare
  all_rows number(10);
  temp_last_name empl_tbl.last_name%type;
begin
  all_rows:=0;
  temp_last_name:=' ';
  for cur in (select last_name from empl_tbl order by last_name) loop
     
      if cur.last_name!=temp_last_name then
       all_rows:=all_rows+1;
      end if;
      temp_last_name:=cur.last_name;
     
  end loop;
  dbms_output.put_line('all_rows are '||all_rows);
end;

请注意上面代码中的黑体部分使用了一个For Loop游标,为了提高程序可读性,我们没有显示定义游标变量。

执行结果:
all_rows are 100000
PL/SQL procedure successfully completed
Executed in 1.402 seconds

游标需要1.4秒才能查出该表中有100000个不重复的Last_name值,所耗时间是Distinct查询的3倍多。

2.3 使用Bulk Collect批查询来实现

示例代码如下:
declare
  all_rows number(10);
  --首先,定义一个Index-by表数据类型
  type last_name_tab is table of empl_tbl.last_name%type index by binary_integer;
  last_name_arr last_name_tab;
  --定义一个Index-by表集合变量
  temp_last_name empl_tbl.last_name%type;
 
begin
  all_rows:=0;
  temp_last_name:=' ';
  --使用Bulk Collect批查询来充填集合变量
  select last_name bulk collect into last_name_arr from empl_tbl;
 
  for i in 1..last_name_arr.count loop
      if temp_last_name!=last_name_arr(i) then
       all_rows:=all_rows+1;
      end if;
      temp_last_name:=last_name_arr(i);
  end loop;
 dbms_output.put_line('all_rows are '||all_rows);
end;

请注意上面代码中,我们首先定义了一个Index-by表数据类型last_name_tab,然后定义了一个该集合数据类型的变量last_name_arr,最后我们使用Bulk Collect批查询来充填last_name_arr,请注意它的使用语法。

执行结果:
all_rows are 100000
PL/SQL procedure successfully completed
Executed in 0.28 seconds
从上面执行结果,我们可以看到,Bulk Collect批查询只需要0.28秒就能查出该表中有100000个不重复的Last_name值,所耗时间只有游标查询的1/5,同时它比Distinct常规查询的速度也要快。

posted on 2008-03-24 15:13 白露 阅读(299) 评论(0)  编辑  收藏 所属分类: oracle

<2025年7月>
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

导航

统计

留言簿(1)

文章分类

文章档案

搜索

最新评论