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;
Ïà¹ØÎĵµ£º
¡¾IT168¼¼ÊõÎĵµ¡¿
DATA GUARDµÄ×îÖ÷ÒªµÄ¹¦ÄÜÊÇÈßÔÖ¡£µ±È»¸ù¾ÝÅäÖõIJ»Í¬£¬DATA GUARD»¹¿ÉÒԾ߱¸ÒÔÏÂÌØµã£º¸ß¿ÉÓá¢ÐÔÄÜÌáÉý¡¢Êý¾Ý±£»¤ÒÔ¼°¹ÊÕϻָ´µÈ¡£
DATA GUARD¿ÉÒÔ·ÖΪÎïÀíSTANDBYºÍÂß¼STANDBYÁ½ÖÖ¡£¶þÕßµÄ×î´ó²î±ðÔÚÓÚ£¬ÎïÀíSTANDBYÓ¦ÓõÄÊÇÖ÷¿âµÄ¹éµµÈÕÖ¾£¬¶øÂ ......
OracleϵÁУºÍ¼Æ¬µÄ´æ´¢
Ò»£ºÊ²Ã´ÊÇ´ó¶ÔÏ󣬴ó¶ÔÏó»ù±¾²Ù×÷£¿
²Î¼ûÎÒµÄBLOG£ºOracleϵÁУºLOB´ó¶ÔÏó´¦Àí
http://blog.csdn.net/qfs_v/archive/2008/05/21/2464599.aspx
¶þ£¬Í¼Æ¬µÄ´æ´¢»ò¶þ½øÖÆÎļþµÄ´æ´¢
1£¬ÏȲåÈëÆÕͨÊý¾Ý£¬Óöµ½´ó¶ÔÏóÁÐʹÓÃempty_blob()¹¹Ôì¿ÕµÄÖ¸Õë¡£
Àý× ......
Ô´´ÓÚ2007Äê04ÔÂ12ÈÕ£¬2009Äê10ÔÂ15ÈÕÇ¨ÒÆÖÁ´Ë¡£
windows xp£¬Êý¾Ý¿âoracle 10.2.0¡£1
ûÓб¸·Ý£¬»ù±¾ÉÏÊÇĬÈϰ²×°£¬ºÃÏñ»¹²»Êǹ鵵ģʽ
Ö¢×´£ºsqlplusÖ»ÓÐsysdbaÓû§ÄܽøÈ¥£¬ÆäËûÓû§½øÈ¥Ò»¸Å±¨£ºora-01033£ºoracleÕýÔÚ³õʼ»¯»ò¹Ø±Õ
¶øÇÒsysdbaÓû§½øÈ¥Ö®ºóÄÜÖ´ÐÐselect sysdate from dual£¬µ«ÊÇÖ´ÐÐselect use ......
HWMÊÇoracleÖÐblockÓÐûÓÐʹÓõķֽçÏߣ¬Ëü»áËæ×ÅÊý¾ÝµÄinsert¶øÉÏÉý£¬µ«Ëü²¢²»»áËæÊý¾ÝµÄdelete¶øÏ½µ£¬Òò´ËÈ«±íɨÃèµÄʱ¼ä²¢²»ÒòÊý¾ÝµÄdelete¶ø¼õÉÙ£¬Ïà·´¿ÉÄÜÓÉÓÚ¿éÇå³ý·´¶øÈ«±íɨÃèʱ¼äÔö¼Ó£¬¿ÉÒÔÓÃÏÂÃæ·½·¨µÄÈÎÒ»Ò»ÖÖÀ´½µµÍHWM£º
µÚÒ»ÖÖ£ºshrink
ÊÊÓÃÓÚ10gÒÔºóµÄ°æ±¾£¬Ç°ÌáÊÇÕâ¸ö±í£¬Ë÷Òý£¬ÎﻯÊÓͼ»òÎﻯÊÓͼlogËùÔ ......