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;
Ïà¹ØÎĵµ£º
Ò»¡¢ÏµÍ³µÄĬÈÏÓû§
Java´úÂë
sys;//ϵͳ¹ÜÀíÔ±£¬ÓµÓÐ×î¸ßȨÏÞ
system;//±¾µØ¹ÜÀíÔ±£¬´Î¸ßȨÏÞ
scott;//ÆÕͨÓû§£¬ÃÜÂëĬÈÏΪtiger,ĬÈÏδ½âËø
¶þ¡¢µÇ½
Java´úÂë
sqlplus conn / as sysdba;//µÇ½sysÕÊ»§ ......
1£¬Ê²Ã´ÊÇÓαꣿ
¢Ù´Ó±íÖмìË÷³ö½á¹û¼¯£¬´ÓÖÐÿ´ÎÖ¸ÏòÒ»Ìõ¼Ç¼½øÐн»»¥µÄ»úÖÆ¡£
¢Ú¹ØÏµÊý¾Ý¿âÖеIJÙ×÷ÊÇÔÚÍêÕûµÄÐм¯ºÏÉÏÖ´Ðеġ£
ÓÉ SELECT Óï¾ä·µ»ØµÄÐм¯ºÏ°üÀ¨Âú×ã¸ÃÓï¾äµÄ WHERE ×Ó¾äËùÁÐÌõ¼þµÄËùÓÐÐС£ÓɸÃÓï¾ä·µ»ØÍêÕûµÄÐм¯ºÏ½Ð×ö½á¹û¼¯¡£
&n ......
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/ ......
×î½üÒ»¶Îʱ¼äһֱûд²©¿Í£¬²»ÊÇÀÁ£¬ÊÇѧÁËÌ«¶à¶«Î÷¡£ÒÔºóÂýÂý²¹ÉÏ¡£ 1. odbcÖÐÌí¼ÓoracleÊý¾ÝÔ´ odbcÖÐÌí¼ÓoracleÊý¾ÝÔ´Ê×ÒªÌõ¼þ£º°²×°oracle client¡£°²×°ÍêÖ®ºóÌí¼Ó£¬µ«ÊÇ»¹±ØÐëÔÚ°²×°Ä¿Â¼ÏÂͨ³£ÊÇC:\oracle\ora90\network\ADMIN\tnsnames.oraÖÐÌí¼ÓÒ»¸öÁ¬½Ó£¬Èçϸñʽ£º DXS =
(DESCRIPTION =
......
HWMÊÇoracleÖÐblockÓÐûÓÐʹÓõķֽçÏߣ¬Ëü»áËæ×ÅÊý¾ÝµÄinsert¶øÉÏÉý£¬µ«Ëü²¢²»»áËæÊý¾ÝµÄdelete¶øÏ½µ£¬Òò´ËÈ«±íɨÃèµÄʱ¼ä²¢²»ÒòÊý¾ÝµÄdelete¶ø¼õÉÙ£¬Ïà·´¿ÉÄÜÓÉÓÚ¿éÇå³ý·´¶øÈ«±íɨÃèʱ¼äÔö¼Ó£¬¿ÉÒÔÓÃÏÂÃæ·½·¨µÄÈÎÒ»Ò»ÖÖÀ´½µµÍHWM£º
µÚÒ»ÖÖ£ºshrink
ÊÊÓÃÓÚ10gÒÔºóµÄ°æ±¾£¬Ç°ÌáÊÇÕâ¸ö±í£¬Ë÷Òý£¬ÎﻯÊÓͼ»òÎﻯÊÓͼlogËùÔ ......