程序博客网 > alias软件销售招聘

BULK COLLECT全解析

来源：互联网 发布：alias软件销售招聘 编辑：程序博客网 时间：2024/05/16 09:42
BULK COLLECT

BULK COLLECT子句会批量检索结果，并从SQL引擎发送到PL/SQL引擎。
(FORALL是从PL/SQL引擎发送到SQL引擎)
  1. declare
  2. cursor cur_student is select student_id,first_name,last_name from student;
  3. begin
  4. for i in cur_student loop
  5. DBMS_OUTPUT.PUT_LINE('student_id is '||i.student_id);
  6. DBMS_OUTPUT.PUT_LINE('first_name is '||i.first_name);
  7. DBMS_OUTPUT.PUT_LINE('last_name is '||i.last_name);
  8. end loop;
  9. end;
  10. 上面这个可以改写成BULK COLLECT子句。两者的差别在于，BULK COLLECT子句会立即从STUDENT表获取全部数据行。因为BULK COLLECT会检索多行数据，这些数据行存储在集合变量中。
  12. declare
  13. type student_id_type is table of student.student_id%type;
  14. type first_name_type is table of student.first_name%type;
  15. type last_name_type is table of student.last_name%type;
  16. student_id_tab student_id_type;
  17. first_name_tab first_name_type;
  18. last_name_tab last_name_type;
  19. begin
  20. select student_id,first_name,last_name
  21.   BULK COLLECT INTO student_id_tab,first_name_tab,last_name_tab
  22.   FROM student;
  24.   for i in student_id_tab.first..student_id_tab.last loop
  25.     DBMS_OUTPUT.PUT_LINE('student_id is '||student_id_tab(i));
  26. DBMS_OUTPUT.PUT_LINE('first_name is '||first_name_tab(i));
  27. DBMS_OUTPUT.PUT_LINE('last_name is '||last_name_tab(i));
  28.   end loop;
  29. end;
  30. /
嵌套表1没用构造器初始化2没EXTEND扩展？
当使用SELECT BULK COLLECT INTO语句田中嵌套表时，他们会自动被初始化，并自动扩展。回想一下，通常嵌套表必须在使用它之前进行初始化，调用与嵌套表类型同名的构造器函数。在初始化嵌套表之后，如要给它赋予下一个值，必须使用EXTEND方法进行扩展。

BULK COLLECT子句类似于游标循环，原因在于当SELECT语句不返回任何记录时，它不会抛出NO_DATA_FOUND异常。这样的话，很有必要检查返回的集合是否包含数据。
由于BULK COLLECT子句不会限制集合的尺寸，并能自动扩展，因此当select语句返回大量数据时，最好限制结果集。通过使用带有游标SELECT的BULK COLLECT以及添加LIMIT选项可以实现这个目标。
  1. declare
  2. cursor cur_student is select student_id,first_name,last_name from student;
  3. type student_id_type is table of student.student_id%type;
  4. type first_name_type is table of student.first_name%type;
  5. type last_name_type is table of student.last_name%type;
  6. student_id_tab student_id_type;
  7. first_name_tab first_name_type;
  8. last_name_tab last_name_type;
  9. v_limit pls_integer := 50;
  10. begin
  11. OPEN cur_student;
  12. loop
  13. fetch cur_student BULK COLLECT INTO student_id_tab,first_name_tab,last_name_tab
  14. LIMIT v_limit;
  16.  EXIT WHEN student_id_tab.count=0;
  17.   for i in student_id_tab.first..student_id_tab.last loop
  18.     DBMS_OUTPUT.PUT_LINE('student_id is '||student_id_tab(i));
  19. DBMS_OUTPUT.PUT_LINE('first_name is '||first_name_tab(i));
  20. DBMS_OUTPUT.PUT_LINE('last_name is '||last_name_tab(i));
  21.   end loop;
  22. end loop;
  23. close cur_student;
  24. end;
  25. /
该脚本使用带有LIMIT选项的BULK COLLECT子句，一次性从STUDENT表检索50行数据。也就是说每个集合最多包含50条记录。为完成这个目标，在游标循环中使用BULK COLLECT子句。注意，在这种情况下，该循环的推出条件基于集合中记录的数量，而不依赖于cur_student%NOTFOUND属性

用record
  1. declare
  2. cursor cur_student is select student_id,first_name,last_name from student;
  3. type rec_student is record(
  4. student_id student.student_id%type,
  5. first_name student.first_name%type,
  6. last_name student.last_name%type);
  7. type student_type is table of rec_student;
  8. student_tab student_type;
  9. v_limit pls_integer := 5;
  10. begin
  11. open cur_student;
  12. loop
  13. fetch cur_student BULK COLLECT INTO student_tab LIMIT v_limit;
  14. DBMS_OUTPUT.PUT_LINE(student_tab.count);
  15. EXIT WHEN student_tab.count=0;
  16. DBMS_OUTPUT.PUT_LINE(student_tab.count);
  17. for i in student_tab.first..student_tab.last loop
  18. DBMS_OUTPUT.PUT_LINE('student_id is '||student_tab(i).student_id);
  19. DBMS_OUTPUT.PUT_LINE('first_name is '||student_tab(i).first_name);
  20. DBMS_OUTPUT.PUT_LINE('last_name is '||student_tab(i).last_name);
  21. end loop;
  22. end loop;
  23. close cur_student;
  24. end;
  25. /
这个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子句一起使用
  1. declare
  2. type row_num_type is table of test.row_num%type index by binary_integer;
  3. type row_text_type is table of test.row_text%type index by binary_integer;
  4. row_num_tab row_num_type;
  5. row_text_tab row_text_type;
  7. begin
  8. delete from test 
  9. returning row_num,row_text
  10. BULK COLLECT INTO row_num_tab,row_text_tab;
  12. DBMS_OUTPUT.PUT_LINE(sql%rowcount||' rows deleted');
  13. for i in row_text_tab.first..row_text_tab.last loop
  14. DBMS_OUTPUT.PUT_LINE('row_num = '||row_num_tab(i)||' | row_text = '||row_text_tab(i));
  15. end loop;
  16. end;
  17. /
  18. 10 rows deleted
  19. row_num = 1 | row_text = row 1
  20. row_num = 2 | row_text = row 2
  21. row_num = 3 | row_text = row 3
  22. row_num = 4 | row_text = row 4
  23. row_num = 5 | row_text = row 5
  24. row_num = 6 | row_text = row 6
  25. row_num = 7 | row_text = row 7
  26. row_num = 8 | row_text = row 8
  27. row_num = 9 | row_text = row 9
  28. row_num = 10 | row_text = row 10
  30. PL/SQL procedure successfully completed.
结合FORALL和BULK COLLECT子句

我自己写的：
  1. declare
  2. type rec_zipcode is record(
  3. zip zipcode.zip%type,
  4. city zipcode.city%type,
  5. state zipcode.state%type,
  6. created_by zipcode.created_by%type,
  7. CREATED_DATE zipcode.CREATED_DATE%type,
  8. MODIFIED_BY zipcode.MODIFIED_BY%type,
  9. MODIFIED_DATE zipcode.MODIFIED_DATE%type);
  10. type zipcode_type is table of rec_zipcode;
  11. zipcode_tab zipcode_type;
  12. v_limit pls_integer := 50;
  13. cursor cur_zipcode is select * from zipcode;
  14. v_count number := 0;
  15. begin
  16. open cur_zipcode;
  17. loop
  18. fetch cur_zipcode bulk collect into zipcode_tab limit v_limit;
  19. exit when zipcode_tab.count=0;
  20. FORALL i in INDICES OF zipcode_tab
  21. insert into my_zipcode(zip,city,state,created_by,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE)
  22. 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);
  23. v_count := zipcode_tab.count + v_count;
  24. end loop;
  25. close cur_zipcode;
  26. DBMS_OUTPUT.PUT_LINE(v_count||' rows insterted');
  27. end;
  28. /
  29. 227 rows insterted
  31. PL/SQL procedure successfully completed.
  34. SQL> select count(*) from my_zipcode;
  36.   COUNT(*)
  37. ----------
  38.        227
  40. SQL> select count(*) from zipcode;
  42.   COUNT(*)
  43. ----------
  44.        227

或者不用record，一个%rowtype搞定
  1. declare
  2. type zipcode_type is table of zipcode%rowtype;
  3. zipcode_tab zipcode_type;
  4. v_limit pls_integer := 50;
  5. cursor cur_zipcode is select * from zipcode;
  6. v_count number := 0;
  7. begin
  8. open cur_zipcode;
  9. loop
  10. fetch cur_zipcode bulk collect into zipcode_tab limit v_limit;
  11. exit when zipcode_tab.count=0;
  12. FORALL i in INDICES OF zipcode_tab
  13. insert into my_zipcode(zip,city,state,created_by,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE)
  14. 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);
  15. v_count := zipcode_tab.count + v_count;
  16. end loop;
  17. close cur_zipcode;
  18. DBMS_OUTPUT.PUT_LINE(v_count||' rows insterted');
  19. end;
  20. /


书上的例子:
  1. DECLARE
  2.   -- Declare collection types
  3.   TYPE string_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  4.   TYPE date_type IS TABLE OF DATE INDEX BY PLS_INTEGER;
  5.   -- Declare collection variables to be used by the FORALL statement
  6.   zip_tab      string_type;
  7.   city_tab     string_type;
  8.   state_tab    string_type;
  9.   cr_by_tab    string_type;
  10.   cr_date_tab  date_type;
  11.   mod_by_tab   string_type;
  12.   mod_date_tab date_type;
  13.   v_counter    PLS_INTEGER := 0;
  14.   v_total      INTEGER := 0;
  15. BEGIN
  16.   -- Populate individual collections
  17.   SELECT * BULK COLLECT
  18.     INTO zip_tab,
  19.          city_tab,
  20.          state_tab,
  21.          cr_by_tab,
  22.          cr_date_tab,
  23.          mod_by_tab,
  24.          mod_date_tab
  25.     FROM zipcode
  26.    WHERE state = 'CT';
  27.   -- Populate MY_ZIPCODE table
  28.   FORALL i in 1 .. zip_tab.COUNT
  29.     INSERT INTO my_zipcode
  30.       (zip,
  31.        city,
  32.        state,
  33.        created_by,
  34.        created_date,
  35.        modified_by,
  36.        modified_date)
  37.     VALUES
  38.       (zip_tab(i),
  39.        city_tab(i),
  40.        state_tab(i),
  41.        cr_by_tab(i),
  42.        cr_date_tab(i),
  43.        mod_by_tab(i),
  44.        mod_date_tab(i));
  45.   COMMIT;
  46.   -- Check how many records were added to MY_ZIPCODE table
  47.   SELECT COUNT(*) INTO v_total FROM my_zipcode WHERE state = 'CT';
  48.   DBMS_OUTPUT.PUT_LINE(v_total ||
  49.                        ' records were added to MY_ZIPCODE table');
  50. END;

  1. declare
  2. type rec_instructor is record(
  3. instructor_id my_instructor.instructor_id%type,
  4. first_name my_instructor.first_name%type,
  5. last_name my_instructor.last_name%type);
  6. type instructor_type is table of rec_instructor;
  7. instructor_tab instructor_type;
  8. begin
  9. select instructor_id,first_name,last_name
  10. BULK COLLECT into instructor_tab from my_instructor;
  12. for i in instructor_tab.first..instructor_tab.last loop
  13. DBMS_OUTPUT.PUT_LINE(instructor_tab(i).instructor_id||' '||instructor_tab(i).first_name||' '||instructor_tab(i).last_name);
  14. end loop;
  15. end;
  16. /
  17. 如果表my_instructor是空的，会报错
  18. declare
  19. *
  20. ERROR at line 1:
  21. ORA-06502: PL/SQL: numeric or value error
  22. ORA-06512: at line 12
  23. 这时候加一个控制
  1. declare
  2. type rec_instructor is record(
  3. instructor_id my_instructor.instructor_id%type,
  4. first_name my_instructor.first_name%type,
  5. last_name my_instructor.last_name%type);
  6. type instructor_type is table of rec_instructor;
  7. instructor_tab instructor_type;
  8. begin
  9. select instructor_id,first_name,last_name
  10. BULK COLLECT into instructor_tab from my_instructor;
  11. if sql%rowcount > 0 then
  12. for i in instructor_tab.first..instructor_tab.last loop
  13. DBMS_OUTPUT.PUT_LINE(instructor_tab(i).instructor_id||' '||instructor_tab(i).first_name||' '||instructor_tab(i).last_name);
  14. end loop;
  15. end if;
  16. end;
  17. /
或者if instructor_tab.count > 0

  1. declare
  2. type rec_instructor is record(
  3. instructor_id my_instructor.instructor_id%type,
  4. first_name my_instructor.first_name%type,
  5. last_name my_instructor.last_name%type);
  6. type instructor_type is table of rec_instructor;
  7. instructor_tab instructor_type;
  8. begin
  9. delete from my_instructor 
  10. returning instructor_id,first_name,last_name
  11. BULK COLLECT into instructor_tab;
  12. if instructor_tab.count > 0 then
  13. for i in instructor_tab.first..instructor_tab.last loop
  14. DBMS_OUTPUT.PUT_LINE(instructor_tab(i).instructor_id||' '||instructor_tab(i).first_name||' '||instructor_tab(i).last_name);
  15. end loop;
  16. end if;
  17. end;
  18. /










0 0
alias软件销售招聘 alias软件销售招聘
原创粉丝点击
热门IT博客
mac怎么强制卸载软件mac怎么强制卸载软件
傣族语言翻译软件傣族语言翻译软件
联邦利率期货实时数据
qq业务宣传图ps源码
淘宝买家评价
递归迷宫生成算法
stc15系列单片机
淘宝优惠券名字
mac怎么删掉客人用户
mac怎么去资源库
万科 万达 知乎
java实现snmp trap
平板电脑装windows系统
知天下资源吧解压密码
淘宝优惠券的使用规则
ps4三国志12网络对战版
公务用车软件开发
linux on android下载
写故事 知乎
引力的本质 知乎
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 都市少帅 花都少帅 东北少帅 景少帅炸天 少帅txt 少帅图片 少帅全集 少帅剧照 少帅剧情 冰冷少帅荒唐 少帅你老婆又跑顾轻舟 顾轻舟司少帅免费阅读 少帅夫人要退婚 少帅每天都吃醋免费阅读 少帅每天都在吃醋免费阅读 明药冰冷少帅荒唐妻 冰冷少帅荒唐妻全文免费阅读 少帅你老婆跑免费阅读 都市绝品少帅 少帅老婆跑免费阅读 冰冷少帅荒唐妻明药 铁血东北少帅 顾轻舟司少帅 少帅剧情介绍 少帅经典台词 少帅 拜月楼主 少帅txt全集下载 少年说 少年 逃跑少年 少年大将军 少年时代 巨蟒少年 中国少年 少年班 少年梦 江苏少年网 狼少年 少年阿 少年说歌曲 少年说歌词

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里