OracleÀ©Õ¹PL/SQL¼ò½é(Áù)
8. bulk collect /forall
ʹÓÃbulk collect¿ÉÒÔ³É¿éµØ¶ÁÈ¡Êý¾Ý£¬Ëü¿ÉʹSQLÒýÇæÔÚ·µ»ØÊä³ö½á¹û¸øPL/SQLÒýÇæÖ®Ç°´óÅú°ó¶¨Êä³ö¼¯ºÏ¡£ÕâÑù¿ÉÒÔÒ»´ÎÐԵذÑÊý¾Ý¶¯Ì¬µØ×°Ôص½¼¯ºÏÖУ¬µ«bulk collectÐèÒª´óÁ¿ÄÚ´æ¡£bulk collect¿ÉÓÃÓÚselect into¡¢fetch intoºÍreturning intoÓï¾äÖС£
¡ñ select intoÖÐʹÓÃbulk collect
declare
type t_email is table of employees.email%type;
v_email_list t_email;
begin
select email bulk collect
into v_email_list
from employees
where department_id = 50;
dbms_output.put_line('»ñÈ¡emailµØÖ·Êý£º' || v_email_list.count);
end;
/
»ñÈ¡emailµØÖ·Êý£º45
¡ñ fetch into ÖÐʹÓÃbulk collect
declare
type t_emp is table of employees%rowtype;
v_emp_list t_emp;
cursor c_emp is
select * from employees where department_id = 50;
begin
open c_emp;
--ÔÚfetch intoÖÐʹÓÃbulk collect
fetch c_emp bulk collect
into v_emp_list;
dbms_output.put_line('»ñÈ¡¹ÍÔ±×ÜÊý£º' || v_emp_list.count);
end;
/
»ñÈ¡¹ÍÔ±×ÜÊý£º45
¡ñ ÔÚreturning intoÖÐʹÓÃbulk collect
ͨ¹ýʹÓÃreturning×Ó¾äbulk collect¿É·µ»ØÖµ¸øµ÷Óùý³Ì£¬²»ÐèÒª¶îÍâ»ñÈ¡fetchÓï¾ä¡£
--´´½¨²âÊÔ±í
create table emptemp as select * from employees where department_id=50;
--Ö´Ðйý³Ì
declare
type t_id_list is table of number;
type t_name_list is table of varchar2(32);
ids t_id_list;
names t_name_list;
begin
delete from emptemp
where commission_pct is null
returning employee_id, first_name bulk collect into ids, names;
dbms_output.put_line('Deleted ' || sql%rowcount || ' rows:');
for i in ids.first .. ids.last loop
dbms_output.put_line('Employees #' || ids(i) || ': ' || names(i));
end loop;
commit;
exception
when others then
rollback;
end;
Ïà¹ØÎĵµ£º
1£¬Ê²Ã´ÊÇÓαꣿ
¢Ù´Ó±íÖмìË÷³ö½á¹û¼¯£¬´ÓÖÐÿ´ÎÖ¸ÏòÒ»Ìõ¼Ç¼½øÐн»»¥µÄ»úÖÆ¡£
¢Ú¹ØÏµÊý¾Ý¿âÖеIJÙ×÷ÊÇÔÚÍêÕûµÄÐм¯ºÏÉÏÖ´Ðеġ£
ÓÉ SELECT Óï¾ä·µ»ØµÄÐм¯ºÏ°üÀ¨Âú×ã¸ÃÓï¾äµÄ WHERE ×Ó¾äËùÁÐÌõ¼þµÄËùÓÐÐС£ÓɸÃÓï¾ä·µ»ØÍêÕûµÄÐм¯ºÏ½Ð×ö½á¹û¼¯¡£
&n ......
Oracle ÈýÖÖ¼¯ºÏÊý¾ÝÀàÐ͵ıȽÏ:
PL/SQLÖÐûÓÐÊý×éµÄ¸ÅÄËûµÄ¼¯ºÏÊý¾ÝÀàÐͺÍÊý×éÊÇÏàËÆµÄ¡£ÔÚ7.3ÒÔǰµÄ°æ±¾ÖÐÖ»ÓÐÒ»ÖÖ¼¯ºÏ£¬³ÆÎªPL/SQL±í£¬ÔÚÕâÖ®ºóÓÖÓÐÁ½ÖÖ¼¯ºÏÊý¾ÝÀàÐÍ:ǶÌ×±íºÍvarray¡£ÆäÖÐvarray¼¯ºÏÖеÄÔªËØÊÇÓÐÊýÁ¿ÏÞÖÆµÄ£¬index_by±íºÍǶÌ×±íÊÇûÓÐÕâ¸öÏÞÖÆµÄ¡£index-by±íÊÇÏ¡ÊèµÄ£¬Ò²¾ÍÊÇ˵ϱê¿ÉÒÔ²»Á¬Ðø ......
ǰ¼¸ÌìÃæÊÔµÄʱºòÃæÊÔ¹Ù²ÅÎʹýÎÒORACLEµÄÌåϵ½á¹¹£¬ÈÃÎÒÔÚÒ»ÕŰ×Ö½ÉÏ»³öÀ´¡£»ØÍ·ÏëÏ뵱ʱ´ðµÃ»¹²»´í£¬´ó²¿·ÖÄÚÈݶ¼ÃèÊö³öÀ´ÁË£¬ºÇºÇ£¬¸Õ²ÅÔÚÍøÉÏ¿´µ½Ò»Æª½²½âORACLEÌåϵ½á¹¹µÄÎÄÕ£¬¾õµÃ²»´í£¬×ª¹ýÀ´´æ×Å£¬½«À´¿ÉÄÜ»áÓõ½¡£
=================================================================================
ÔÚ±¾ÎÄÀ ......