oracle ±Ê¼Ç VI Ö®Óαê (CURSOR)
Óαê(CURSOR),ºÜÖØÒª
Óαê:ÓÃÓÚ´¦Àí¶àÐмǼµÄÊÂÎñ
ÓαêÊÇÒ»¸öÖ¸ÏòÉÏÏÂÎĵľä±ú(handle)»òÖ¸Õë,¼òµ¥Ëµ£¬Óαê¾ÍÊÇÒ»¸öÖ¸Õë
1 ´¦ÀíÏÔʽÓαê
ÏÔʽÓα괦ÀíÐè 4¸ö PL/SQL ²½Öè,ÏÔʾÓαêÖ÷ÒªÓÃÓÚ´¦Àí²éѯÓï¾ä
(1) ¶¨ÒåÓαê
¸ñʽ: CURSOR cursor_name [(partment[,parameter]...)] IS select_statement;
¶¨ÒåµÄÓα겻ÄÜÓÐ INTO ×Ó¾ä
(2) ´ò¿ªÓαê
OPEN cursor_name[...];
PL/SQL ³ÌÐò²»ÄÜÓà OPEN Óï¾äÖظ´´ò¿ªÒ»¸öÓαê
(3)ÌáÈ¡ÓαêÊý¾Ý
FETCH cursor_name INTO {variable_list | record_variable};
(4) ¹Ø±ÕÓαê
CLOSE cursor_name;
Àý 1 ²éѯǰ 10 ÃûÔ±¹¤µÄÐÅÏ¢
declare
--¶¨ÒåÓαê
cursor c_cursor is select last_name,salary from employees where rownum < 11 order by salary;
v_name employees.last_name%type;
V_sal employees.salary%type;
begin
--´ò¿ªÓαê
open c_cursor;
-- ÌáÈ¡ÓαêÊý¾Ý
fetch c_cursor into v_name,v_sal;
while c_cursor %found loop
dbms_output.put_line(v_name || ':' || v_sal);
fetch c_cursor into v_name,v_sal;
end loop;
--¹Ø±ÕÓαê
close c_cursor;
end;
----------------------------------
Á·Ï°: ÊäÈ벿ÃźŠdep_id,²éѯ¸Ã²¿ÃŵÄƽ¾ù¹¤×Ê : avg_sal,Ô±¹¤¹¤×ÊΪ salary
Èô salary < avg_sal - 500 ¹¤×ÊÕÇ 500
Èô avg_sal - 500 <= salary < avg_sal + 500 ¹¤×ÊÕÇ 300
Èô
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
¡¡Ò»¡¢ÉèÖóõʼ»¯²ÎÊý job_queue_processes
¡¡¡¡sql> alter system set job_queue_processes=n;£¨n>0£©
¡¡¡¡job_queue_processes×î´óֵΪ1000
¡¡¡¡
¡¡¡¡²é¿´job queue ºǫ́½ø³Ì
¡¡¡¡sql>select name,description from v$bgprocess;
¡¡¡¡
¡¡¡¡¶þ£¬dbms_job package Ó÷¨½éÉÜ
¡¡¡¡°üº¬ÒÔÏÂ×Ó¹ý³Ì£º
......
DML(Manipulation):Êý¾Ý²Ù×÷ÓïÑÔ
CRUD
DDL(Definition): Êý¾Ý¶¨ÒåÓïÑÔ,Óë±í£¬Ë÷Òý£¬Í¬Òå´ÊÓйØ
create,alter,drop,rename,truncate(Çå¿Õ)
DCL(Control): Êý¾Ý¿ØÖÆÓïÑÔ£¬ÓëȨÏÞÓйØ
grant,revoke
TCL(Transaction Control): ÊÂÎñ¿ØÖÆÓïÑÔ,ÓëÊÂÎñÓйØ
commit,rollback,savepoint
==========================
´æ´¢ ......
1. flashback table table_test to timestamp to_timestamp('20091103000000','yyyymmddhh24miss');
2.Èç¹û報錯ORA-08189: cannot flashback the table because row movement is not enabled
3.alter table table_test enable row movement;
4.OK ......
´Ó10g¿ªÊ¼£¬oracle¿ªÊ¼ÌṩShrinkµÄÃüÁ¼ÙÈçÎÒÃǵıí¿Õ¼äÖÐÖ§³Ö×Ô¶¯¶Î¿Õ¼ä¹ÜÀí (ASSM),¾Í¿ÉÒÔʹÓÃÕâ¸öÌØÐÔËõС¶Î£¬¼´½µµÍHWM¡£ÕâÀïÐèҪǿµ÷Ò»µã£¬10gµÄÕâ¸öÐÂÌØÐÔ£¬½ö¶ÔASSM±í¿Õ¼äÓÐЧ£¬·ñÔò»á±¨ ORA-10635: Invalid segment or tablespace type¡£
Èç¹û¾³£ÔÚ±íÉÏÖ´ÐÐDML²Ù× ......