集合的批量绑定(二)bulkcollect-优快云博客

本文介绍了在Oracle数据库中使用BULK COLLECT进行批量操作的方法，包括如何在SELECT INTO语句、DML RETURNING子句及UPDATE语句中使用该功能来提高处理效率。

1、在select into 中使用BULK COLLECT，批量绑定到集合变量中

 1 DECLARE
 2   TYPE list_of_emp_type IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
 3   list_emp list_of_emp_type;
 4 BEGIN
 5   SELECT * BULK COLLECT INTO list_emp FROM emp WHERE deptno = &deptno;
 6   FOR i IN 1 .. list_emp.count LOOP
 7     dbms_output.put_line('工号: ' ||list_emp(i).empno||',姓名: '||list_emp(i).ename ||
 8      ',的薪水: ' || list_emp(i).sal || '$');
 9   END LOOP;
10 END;
11 /

2、在DML返回子句中使用BULK COLLECT

 1 declare
 2 type list_name_type is table of emp.ename%type index by pls_integer;
 3 type list_sal_type  is table of emp.sal%type index by pls_integer;
 4 list_name           list_name_type;
 5 list_sal            list_sal_type;
 6 BEGIN
 7   update emp set sal=sal*1.1 where deptno=&deptno returning ename,sal bulk collect into
 8 list_name,list_sal;
 9   for i in 1..list_name.count loop
10     DBMS_OUTPUT.PUT_LINE('姓名 :'||list_name(i)||'new sal: '||list_sal(i));
11     end loop;
12 END;
13 /

3、在update中使用BULK COLLECT

declare
type list_name_type is table of emp.ename%type index by pls_integer;
type list_sal_type  is table of emp.sal%type index by pls_integer;
list_name           list_name_type;
list_sal            list_sal_type;
list_name1          list_name_type;
list_sal1           list_sal_type;
BEGIN
  update emp set sal=sal*1.2 where deptno=&deptno returning ename,sal bulk collect into
list_name,list_sal;
  for i in 1..list_name.count loop
    DBMS_OUTPUT.PUT_LINE('name :'||list_name(i)||'new sal: '||list_sal(i));
    end loop;
END;
/