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ϵÁУºLOB´ó¶ÔÏó´¦Àí
Ö÷ÒªÊÇÓÃÀ´´æ´¢´óÁ¿Êý¾ÝµÄÊý¾Ý¿â×ֶΣ¬×î´ó¿ÉÒÔ´æ´¢4G×ֽڵķǽṹ»¯Êý¾Ý¡£
Ö÷Òª½éÉÜ×Ö·ûÀàÐͺͶþ½øÖÆÎļþÀàÐÍLOBÊý¾ÝµÄ´æ´¢£¬µ¥¶À½éÉܶþ½øÖÆÀàÐÍLOBÊý¾ÝµÄ´æ´¢¡£
Ò»£¬OracleÖеÄLOBÊý¾ÝÀàÐÍ·ÖÀà
1£¬°´´æ´¢Êý¾ÝµÄÀàÐÍ·Ö£º
¢Ù×Ö·ûÀàÐÍ£º
&nbs ......
Ò»£¬OracleÊý¾Ý¿âÓû§¼ò½é
ÔÚOracleÊý¾Ý¿âÖÐÈκζÔÏó¶¼ÊôÓÚÒ»¸öÌØ¶¨Óû§£¬»òÕß˵һ¸öÓû§ÓëͬÃûµÄģʽÏà¹ØÁª¡£
ÒªÁ¬½Óµ½OracleÊý¾Ý¿âÐèÒªÒ»¸öÓû§ÕÊ»§£¬¸ù¾ÝÐèÒªÊÚÓèµÄ²Ù×÷ȨÏÞ¡£
1£¬Ä¬ÈÏÊý¾Ý¿âÓû§Ä£Ê½£º
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 Database 10g ÌṩÁËÒ»¸öÏÔÖø¸Ä½øµÄ¹¤¾ß£º×Ô¶¯¹¤×÷¸ºÔØÐÅÏ¢¿â (AWR:Automatic Workload Repository)¡£Oracle ½¨ÒéÓû§ÓÃÕâ¸öÈ¡´ú Statspack¡£AWR ʵÖÊÉÏÊÇÒ»¸ö Oracle µÄÄÚÖù¤¾ß£¬Ëü²É¼¯ÓëÐÔÄÜÏà¹ØµÄͳ¼ÆÊý¾Ý£¬²¢´ÓÄÇЩͳ¼ÆÊý¾ÝÖе¼³öÐÔÄÜÁ¿¶È£¬ÒÔ¸ú×ÙDZÔÚµÄÎÊÌâ¡£Óë Statspack ²»Í¬£¬¿ìÕÕÓÉÒ»¸ö³ÆÎª MMON µÄеĺó ......
RMAN> startup nomount;
RMAN> sql 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''";
--ÒòΪrmanĬÈÏÒÔ»·¾³±äÁ¿À´¶Áȡʱ¼ä¸ñʽ£¬ÓësqlplusµÄ¹Ì¶¨¸ñʽ²»Í¬£¬ËùÒÔ£¬´Ë´¦ÒªÉ趨ʱ¼ä¸ñʽ±äÁ¿¡£
RMAN> restore controlfile from autobackup until time '2009-03-10 18:15:00';
×¢Ò⣬ÈÔÈ»ÐèÒªÊʵ±µ ......