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Êý¾Ý¿â×Ö·û¼¯Ïà¹ØµÄÎÊÌ⣬ÈçÔÚ²»Í¬Êý¾Ý¿â×öÊý¾ÝǨÒÆ¡¢Í¬ÆäËüϵͳ½»»»Êý¾ÝµÈ£¬³£³£ÒòΪ×Ö·û¼¯²»Í¬¶øµ¼ÖÂǨÒÆʧ°Ü»òÊý¾Ý¿âÄÚÊý¾Ý±ä³ÉÂÒÂë¡£ÏÖÔÚÎÒ½«oracle×Ö·û¼¯Ïà¹ØµÄһЩ֪ʶ×ö¸ö¼òµ¥×ܽᣬϣÍû¶Ô´ó¼Ò½ñºóµÄ¹¤×÷ÓÐËù°ïÖú¡£
¡¡¡¡Ò»¡¢Ê²Ã´ÊÇoracle×Ö·û¼¯
¡¡¡¡Oracle×Ö·û¼¯ÊÇÒ»¸ö×Ö½ÚÊý¾ÝµÄ½âÊÍ ......
²éѯ¼°É¾³ýÖظ´¼Ç¼µÄSQLÓï¾ä
1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ý ......
Stream ÊÇOracle µÄÏûÏ¢¶ÓÁÐ(Ò²½ÐOracle Advanced Queue)¼¼ÊõµÄÒ»ÖÖÀ©Õ¹Ó¦ÓᣠOracle µÄÏûÏ¢¶ÓÁÐÊÇͨ¹ý·¢²¼/¶©Ôĵķ½Ê½À´½â¾öʼþ¹ÜÀí¡£Á÷¸´ÖÆ(Stream replication)Ö»ÊÇ»ùÓÚËüµÄÒ»¸öÊý¾Ý¹²Ïí¼¼Êõ£¬Ò²¿ÉÒÔ±»ÓÃ×÷Ò»¸ö¿ÉÁé»î¶¨ÖƵĸ߿ÉÓÃÐÔ·½°¸¡£ Ëü¿ÉÒÔʵÏÖÁ½¸öÊý¾Ý¿âÖ®¼äÊý¾Ý¿â¼ ......
½ñÌìÔÚÍøÉÏÂҹ䣬¿´µ½Ò»Æª²»´íµÄÎÄÕ£¬¹ØÓÚORACLEÃÜÂëÎļþµÄ£¬ÀíÂÛѧϰ+¸öÈËʵ¼ù£¬ºÇºÇ£¬¸´ÖƹýÀ´£¬·½±ãÒÔºóѧϰ¡£
===============================================================================
ÀíÂÛ²¿·Ö
Oracle¹ØϵÊý¾Ý¿âϵͳÒÔÆä׿ԽµÄÐÔÄÜ»ñµÃÁ˹㷺µÄÓ¦Ó㬶ø±£Ö¤Êý¾Ý¿â°²È«ÐÔÊÇÊý¾Ý¿â¹ÜÀí¹¤×÷µÄÖØÒªÄÚÈÝ¡£±¾ ......
1. ´´½¨ÊÓͼ£º
CREATE OR REPLACE VIEW SM_V_UNIT_AUTH AS
SELECT T2.UNIT_ID,
T2.SUPER_UNIT_ID,
T1.AUTH_ID,
T1.AUTH_NAME,
T1.A ......