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;
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
Ò»¡¢ÏµÍ³µÄĬÈÏÓû§
Java´úÂë
sys;//ϵͳ¹ÜÀíÔ±£¬ÓµÓÐ×î¸ßȨÏÞ
system;//±¾µØ¹ÜÀíÔ±£¬´Î¸ßȨÏÞ
scott;//ÆÕͨÓû§£¬ÃÜÂëĬÈÏΪtiger,ĬÈÏδ½âËø
¶þ¡¢µÇ½
Java´úÂë
sqlplus conn / as sysdba;//µÇ½sysÕÊ»§ ......
1. ²éѯÊý¾Ý¿âÏÖÔڵıí¿Õ¼ä
select tablespace_name, file_name, sum(bytes)/1024/1024 table_size from dba_data_files group by tablespace_name,file_name;
2. ½¨Á¢±í¿Õ¼ä
CREATE TABLESPACE data01 DATAFILE '/oracle/ ......
½ñÄêµÄOracleÈ«Çò´ó»áÓÚ10ÔÂ11ÈÕ£15ÈÕÔÚÃÀ¹ú¾É½ðɽµÄMosconeÖÐÐÄÒѾÀ¿ªÐòÄ»ÁË¡£
¡¡¡¡ Oracle¶ÔSun¹«Ë¾µÄ³¤Æڼƻ®ÎÞÒÉÊDZ¾´ÎOOW´ó»áµÄ½¹µã¡£µ«ÔÚ̸ÂÛÕâ¸ö»°Ìâ֮ǰ£¬ÎÒÃÇ×¼±¸ÁËÒ»×é´ó»áµÄÓÐȤÊý¾Ý£¬ÏÈÈôó¼Ò¶Ô±¾´Î´ó»áÓиöÕûÌåµÄÓ¡Ïó¡£
¡¡¡¡ÓйØÕû¸ö´ó»áµÄÊý×Ö£º
¡¡¡¡· ½ü4.3ÍòÈ˵½»á
¡¡¡¡· ΪÃÀ¹ ......