BULK COLLECT全解析

BULK COLLECT

 

BULK COLLECT子句会批量检索结果，并从SQL引擎发送到PL/SQL引擎。

(FORALL是从PL/SQL引擎发送到SQL引擎)

declare
cursor cur_student is select student_id,first_name,last_name from student;
begin
for i in cur_student loop
DBMS_OUTPUT.PUT_LINE('student_id is '||i.student_id);
DBMS_OUTPUT.PUT_LINE('first_name is '||i.first_name);
DBMS_OUTPUT.PUT_LINE('last_name is '||i.last_name);
end loop;
end;
上面这个可以改写成BULK COLLECT子句。两者的差别在于，BULK COLLECT子句会立即从STUDENT表获取全部数据行。因为BULK COLLECT会检索多行数据，这些数据行存储在集合变量中。

declare
type student_id_type is table of student.student_id%type;
type first_name_type is table of student.first_name%type;
type last_name_type is table of student.last_name%type;
student_id_tab student_id_type;
first_name_tab first_name_type;
last_name_tab last_name_type;
begin
select student_id,first_name,last_name
 BULK COLLECT INTO student_id_tab,first_name_tab,last_name_tab
 FROM student;

 for i in student_id_tab.first..student_id_tab.last loop
   DBMS_OUTPUT.PUT_LINE('student_id is '||student_id_tab(i));
DBMS_OUTPUT.PUT_LINE('first_name is '||first_name_tab(i));
DBMS_OUTPUT.PUT_LINE('last_name is '||last_name_tab(i));
 end loop;
end;
/

嵌套表1没用构造器初始化2没EXTEND扩展？

当使用SELECT BULK COLLECT INTO语句田中嵌套表时，他们会自动被初始化，并自动扩展。回想一下，通常嵌套表必须在使用它之前进行初始化，调用与嵌套表类型同名的构造器函数。在初始化嵌套表之后，如要给它赋予下一个值，必须使用EXTEND方法进行扩展。

 

BULK COLLECT子句类似于游标循环，原因在于当SELECT语句不返回任何记录时，它不会抛出NO_DATA_FOUND异常。这样的话，很有必要检查返回的集合是否包含数据。

由于BULK COLLECT子句不会限制集合的尺寸，并能自动扩展，因此当select语句返回大量数据时，最好限制结果集。通过使用带有游标SELECT的BULK COLLECT以及添加LIMIT选项可以实现这个目标。

declare
cursor cur_student is select student_id,first_name,last_name from student;
type student_id_type is table of student.student_id%type;
type first_name_type is table of student.first_name%type;
type last_name_type is table of student.last_name%type;
student_id_tab student_id_type;
first_name_tab first_name_type;
last_name_tab last_name_type;
v_limit pls_integer := 50;
begin
OPEN cur_student;
loop
fetch cur_student BULK COLLECT INTO student_id_tab,first_name_tab,last_name_tab
LIMIT v_limit;

EXIT WHEN student_id_tab.count=0;
 for i in student_id_tab.first..student_id_tab.last loop
   DBMS_OUTPUT.PUT_LINE('student_id is '||student_id_tab(i));
DBMS_OUTPUT.PUT_LINE('first_name is '||first_name_tab(i));
DBMS_OUTPUT.PUT_LINE('last_name is '||last_name_tab(i));
 end loop;
end loop;
close cur_student;
end;
/

该脚本使用带有LIMIT选项的BULK COLLECT子句，一次性从STUDENT表检索50行数据。也就是说每个集合最多包含50条记录。为完成这个目标，在游标循环中使用BULK COLLECT子句。注意，在这种情况下，该循环的推出条件基于集合中记录的数量，而不依赖于cur_student%NOTFOUND属性

 

用record

declare
cursor cur_student is select student_id,first_name,last_name from student;
type rec_student is record(
student_id student.student_id%type,
first_name student.first_name%type,
last_name student.last_name%type);
type student_type is table of rec_student;
student_tab student_type;
v_limit pls_integer := 5;
begin
open cur_student;
loop
fetch cur_student BULK COLLECT INTO student_tab LIMIT v_limit;
DBMS_OUTPUT.PUT_LINE(student_tab.count);
EXIT WHEN student_tab.count=0;
DBMS_OUTPUT.PUT_LINE(student_tab.count);
for i in student_tab.first..student_tab.last loop
DBMS_OUTPUT.PUT_LINE('student_id is '||student_tab(i).student_id);
DBMS_OUTPUT.PUT_LINE('first_name is '||student_tab(i).first_name);
DBMS_OUTPUT.PUT_LINE('last_name is '||student_tab(i).last_name);
end loop;
end loop;
close cur_student;
end;
/

这个limit是限制bulk collect一次取多少行into进去，好比说一共select9行，limit是5

那么第一次取5行，第二次4行，第三次0.如果没有EXIT WHEN判断会报错 PL/SQL: numeric or value error

last_name is Ocampo
5
5
student_id is 282
first_name is Jonathan
last_name is Jaele
student_id is 283
first_name is Benita
last_name is Perkins
student_id is 284
first_name is Salewa
last_name is Lindeman
student_id is 285
first_name is Paul
last_name is Sikinger
student_id is 286
first_name is Robin
last_name is Kelly
4 第一个output
4 第二个output
student_id is 288
first_name is Rosemary
last_name is Ellman
student_id is 289
first_name is Shirley
last_name is Murray
student_id is 290
first_name is Brian
last_name is Robles
student_id is 291
first_name is D.
last_name is Dewitt
0 第一个output，0行EXIT
  第二个output就不会有了

 

现在你的测试中出现新的错误

ORA-06502: PL/SQL: numeric or value erro，这个问题出现最可能的原因是当最后一次fetch cur bulk collect into n limit 50;的时候，找不到任何记录，所以n.first和n.last是空的，加一句控制就可以。  

 

 

通常BULK COLLECT子句也会与INSERT、UPDATE和DELETE一起使用。在下列情况下，BULK COLLECT子句与RETURNING子句一起使用

declare
type row_num_type is table of test.row_num%type index by binary_integer;
type row_text_type is table of test.row_text%type index by binary_integer;
row_num_tab row_num_type;
row_text_tab row_text_type;

begin
delete from test
returning row_num,row_text
BULK COLLECT INTO row_num_tab,row_text_tab;

DBMS_OUTPUT.PUT_LINE(sql%rowcount||' rows deleted');
for i in row_text_tab.first..row_text_tab.last loop
DBMS_OUTPUT.PUT_LINE('row_num = '||row_num_tab(i)||' | row_text = '||row_text_tab(i));
end loop;
end;
/
10 rows deleted
row_num = 1 | row_text = row 1
row_num = 2 | row_text = row 2
row_num = 3 | row_text = row 3
row_num = 4 | row_text = row 4
row_num = 5 | row_text = row 5
row_num = 6 | row_text = row 6
row_num = 7 | row_text = row 7
row_num = 8 | row_text = row 8
row_num = 9 | row_text = row 9
row_num = 10 | row_text = row 10

PL/SQL procedure successfully completed.

结合FORALL和BULK COLLECT子句

 

我自己写的：

declare
type rec_zipcode is record(
zip zipcode.zip%type,
city zipcode.city%type,
state zipcode.state%type,
created_by zipcode.created_by%type,
CREATED_DATE zipcode.CREATED_DATE%type,
MODIFIED_BY zipcode.MODIFIED_BY%type,
MODIFIED_DATE zipcode.MODIFIED_DATE%type);
type zipcode_type is table of rec_zipcode;
zipcode_tab zipcode_type;
v_limit pls_integer := 50;
cursor cur_zipcode is select * from zipcode;
v_count number := 0;
begin
open cur_zipcode;
loop
fetch cur_zipcode bulk collect into zipcode_tab limit v_limit;
exit when zipcode_tab.count=0;
FORALL i in INDICES OF zipcode_tab
insert into my_zipcode(zip,city,state,created_by,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE)
values(zipcode_tab(i).zip,zipcode_tab(i).city,zipcode_tab(i).state,zipcode_tab(i).created_by,zipcode_tab(i).CREATED_DATE,zipcode_tab(i).MODIFIED_BY,zipcode_tab(i).MODIFIED_DATE);
v_count := zipcode_tab.count + v_count;
end loop;
close cur_zipcode;
DBMS_OUTPUT.PUT_LINE(v_count||' rows insterted');
end;
/
227 rows insterted

PL/SQL procedure successfully completed.


SQL> select count(*) from my_zipcode;

 COUNT(*)
----------
      227

SQL> select count(*) from zipcode;

 COUNT(*)
----------
      227

或者不用record，一个%rowtype搞定

declare
type zipcode_type is table of zipcode%rowtype;
zipcode_tab zipcode_type;
v_limit pls_integer := 50;
cursor cur_zipcode is select * from zipcode;
v_count number := 0;
begin
open cur_zipcode;
loop
fetch cur_zipcode bulk collect into zipcode_tab limit v_limit;
exit when zipcode_tab.count=0;
FORALL i in INDICES OF zipcode_tab
insert into my_zipcode(zip,city,state,created_by,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE)
values(zipcode_tab(i).zip,zipcode_tab(i).city,zipcode_tab(i).state,zipcode_tab(i).created_by,zipcode_tab(i).CREATED_DATE,zipcode_tab(i).MODIFIED_BY,zipcode_tab(i).MODIFIED_DATE);
v_count := zipcode_tab.count + v_count;
end loop;
close cur_zipcode;
DBMS_OUTPUT.PUT_LINE(v_count||' rows insterted');
end;
/

 

 

书上的例子:

DECLARE
 -- Declare collection types
 TYPE string_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
 TYPE date_type IS TABLE OF DATE INDEX BY PLS_INTEGER;
 -- Declare collection variables to be used by the FORALL statement
 zip_tab      string_type;
 city_tab     string_type;
 state_tab    string_type;
 cr_by_tab    string_type;
 cr_date_tab  date_type;
 mod_by_tab   string_type;
 mod_date_tab date_type;
 v_counter    PLS_INTEGER := 0;
 v_total      INTEGER := 0;
BEGIN
 -- Populate individual collections
 SELECT * BULK COLLECT
   INTO zip_tab,
        city_tab,
        state_tab,
        cr_by_tab,
        cr_date_tab,
        mod_by_tab,
        mod_date_tab
   FROM zipcode
  WHERE state = 'CT';
 -- Populate MY_ZIPCODE table
 FORALL i in 1 .. zip_tab.COUNT
   INSERT INTO my_zipcode
     (zip,
      city,
      state,
      created_by,
      created_date,
      modified_by,
      modified_date)
   VALUES
     (zip_tab(i),
      city_tab(i),
      state_tab(i),
      cr_by_tab(i),
      cr_date_tab(i),
      mod_by_tab(i),
      mod_date_tab(i));
 COMMIT;
 -- Check how many records were added to MY_ZIPCODE table
 SELECT COUNT(*) INTO v_total FROM my_zipcode WHERE state = 'CT';
 DBMS_OUTPUT.PUT_LINE(v_total ||
                      ' records were added to MY_ZIPCODE table');
END;

​​​​

declare
type rec_instructor is record(
instructor_id my_instructor.instructor_id%type,
first_name my_instructor.first_name%type,
last_name my_instructor.last_name%type);
type instructor_type is table of rec_instructor;
instructor_tab instructor_type;
begin
select instructor_id,first_name,last_name
BULK COLLECT into instructor_tab from my_instructor;

for i in instructor_tab.first..instructor_tab.last loop
DBMS_OUTPUT.PUT_LINE(instructor_tab(i).instructor_id||' '||instructor_tab(i).first_name||' '||instructor_tab(i).last_name);
end loop;
end;
/
如果表my_instructor是空的，会报错
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 12
这时候加一个控制

declare
type rec_instructor is record(
instructor_id my_instructor.instructor_id%type,
first_name my_instructor.first_name%type,
last_name my_instructor.last_name%type);
type instructor_type is table of rec_instructor;
instructor_tab instructor_type;
begin
select instructor_id,first_name,last_name
BULK COLLECT into instructor_tab from my_instructor;
if sql%rowcount > 0 then
for i in instructor_tab.first..instructor_tab.last loop
DBMS_OUTPUT.PUT_LINE(instructor_tab(i).instructor_id||' '||instructor_tab(i).first_name||' '||instructor_tab(i).last_name);
end loop;
end if;
end;
/

或者if instructor_tab.count > 0

declare
type rec_instructor is record(
instructor_id my_instructor.instructor_id%type,
first_name my_instructor.first_name%type,
last_name my_instructor.last_name%type);
type instructor_type is table of rec_instructor;
instructor_tab instructor_type;
begin
delete from my_instructor
returning instructor_id,first_name,last_name
BULK COLLECT into instructor_tab;
if instructor_tab.count > 0 then
for i in instructor_tab.first..instructor_tab.last loop
DBMS_OUTPUT.PUT_LINE(instructor_tab(i).instructor_id||' '||instructor_tab(i).first_name||' '||instructor_tab(i).last_name);
end loop;
end if;
end;
/