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;
/