PL/SQL³ÌÐòÉè¼Æ£¨ÓαêµÄʹÓã©
ΪÁË´¦Àí SQL Óï¾ä£¬ORACLE ±ØÐë·ÖÅäһƬ½ÐÉÏÏÂÎÄ( context area )µÄÇøÓòÀ´´¦ÀíËù±ØÐèµÄÐÅÏ¢£¬ÆäÖаüÀ¨Òª´¦ÀíµÄÐеÄÊýÄ¿£¬Ò»¸öÖ¸ÏòÓï¾ä±»·ÖÎöÒÔºóµÄ±íʾÐÎʽµÄÖ¸ÕëÒÔ¼°²éѯµÄ»î¶¯¼¯(active set)¡£
ÓαêÊÇÒ»¸öÖ¸ÏòÉÏÏÂÎĵľä±ú( handle)»òÖ¸Õ롣ͨ¹ýÓα꣬PL/SQL¿ÉÒÔ¿ØÖÆÉÏÏÂÎÄÇøºÍ´¦ÀíÓï¾äʱÉÏÏÂÎÄÇø»á·¢ÉúЩʲôÊÂÇé¡£
¶ÔÓÚ²»Í¬µÄSQLÓï¾ä£¬ÓαêµÄʹÓÃÇé¿ö²»Í¬£º
SQLÓï¾ä
Óαê
·Ç²éѯÓï¾ä
ÒþʽµÄ
½á¹ûÊǵ¥ÐеIJéѯÓï¾ä
ÒþʽµÄ»òÏÔʾµÄ
½á¹ûÊǶàÐеIJéѯÓï¾ä
ÏÔʾµÄ
§4.1.1 ´¦ÀíÏÔʽÓαê
1. ÏÔʽÓα괦Àí
ÏÔʽÓα괦ÀíÐèËĸö PL/SQL²½Öè:
l ¶¨ÒåÓα꣺¾ÍÊǶ¨ÒåÒ»¸öÓαêÃû£¬ÒÔ¼°ÓëÆäÏà¶ÔÓ¦µÄSELECT Óï¾ä¡£
¸ñʽ£º
CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement;
Óαê²ÎÊýÖ»ÄÜΪÊäÈë²ÎÊý£¬Æä¸ñʽΪ£º
parameter_name [IN] datatype [{:= | DEFAULT} expression]
ÔÚÖ¸¶¨Êý¾ÝÀàÐÍʱ£¬²»ÄÜʹÓó¤¶ÈÔ¼Êø¡£ÈçNUMBER(4)¡¢CHAR(10) µÈ¶¼ÊÇ´íÎóµÄ¡£
l ´ò¿ªÓα꣺¾ÍÊÇÖ´ÐÐÓαêËù¶ÔÓ¦µÄSELECT Óï¾ä£¬½«Æä²éѯ½á¹û·ÅÈ빤×÷Çø£¬²¢ÇÒÖ¸ÕëÖ¸Ïò¹¤×÷ÇøµÄÊײ¿£¬±êʶÓαê½á¹û¼¯ºÏ¡£Èç¹ûÓαê²éѯÓï¾äÖдøÓÐFOR UPDATEÑ¡ÏOPEN Óï¾ä»¹½«Ëø¶¨Êý¾Ý¿â±íÖÐÓαê½á¹û¼¯ºÏ¶ÔÓ¦µÄÊý¾ÝÐС£
¸ñʽ£º
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
ÔÚÏòÓα괫µÝ²ÎÊýʱ£¬¿ÉÒÔʹÓÃÓ뺯Êý²ÎÊýÏàͬµÄ´«Öµ·½·¨£¬¼´Î»Öñíʾ·¨ºÍÃû³Æ±íʾ ·¨¡£PL/SQL ³ÌÐò²»ÄÜÓÃOPEN Óï¾äÖظ´´ò¿ªÒ»¸öÓαꡣ
l ÌáÈ¡ÓαêÊý¾Ý£º¾ÍÊǼìË÷½á¹û¼¯ºÏÖеÄÊý¾ÝÐУ¬·ÅÈëÖ¸¶¨µÄÊä³ö±äÁ¿ÖС£
¸ñʽ£º
FETCH cursor_name INTO {variable_list | record_variable };
l ¶Ô¸Ã¼Ç¼½øÐд¦Àí£»
l ¼ÌÐø´¦Àí£¬Ö±µ½»î¶¯¼¯ºÏÖÐûÓмǼ£»
l ¹Ø±ÕÓα꣺µ±ÌáÈ¡ºÍ´¦ÀíÍêÓαê½á¹û¼¯ºÏÊý¾Ýºó£¬Ó¦¼°Ê±¹Ø±ÕÓα꣬ÒÔÊͷŸÃÓαêËùÕ¼ÓõÄϵͳ×ÊÔ´£¬²¢Ê¹¸ÃÓαêµÄ¹¤×÷Çø±ä³ÉÎÞЧ£¬²»ÄÜÔÙʹÓÃFETCH Óï¾äÈ¡ÆäÖÐÊý¾Ý¡£¹Ø±ÕºóµÄÓαê¿ÉÒÔʹÓÃOPEN Óï¾äÖØдò¿ª¡£
¸ñʽ£º
CLOSE cursor_name;
×¢£º¶¨ÒåµÄÓα겻ÄÜÓÐINTO ×Ӿ䡣
Àý1. Óα
Ïà¹ØÎĵµ£º
select gztzid,
gztztt,
gztzbt,
gztznr,
fslxmc,
decode(fsfs, '0', 'ÎÞÐè»Ø¸´', '1', 'ÐèÒª»Ø¸´') fsfs,
&nb ......
ιʶø֪У¬¹ûÈ»Èç´Ëѽ£¬µÚ¶þ´ÎÔÙ·¿ªÍ¬ÑùµÄÄÚÈݹûÈ»Óв»Í¬µÄÊÕ»ñ£¬ÓÐЩÊǵÚÒ»´Î¿´µÄʱºòûÓÐ×ÐϸÀí½âµÄ£¬»¹ÓÐЩ¿ÉÄÜÊÇÔÚµÚÒ»´Î¿´´Ò´Ò¾ÍÌø¹ýµÄ£¬µ±È»£¬¿ÉÄÜ»¹Óв¿·ÖÊÇ×Ô¼ºµ±Ê±¼ÇסÁËÍêÁËÓÖ¸øÍü¼ÇÁË¡£½ñÌìµÚ¶þ´Î¿´µ½×Ó³ÌÐòÕâÒ»Õ½ڣ¬·¢ÏÖÁËЩеÄÄÚÈÝ£¬ºÇºÇ¡£ÔÚÕâÀïÎÒ¾ÍдÏÂһЩ»ù±¾ÄÚÈݺÍÈÝÒ×Íü¼ÇµÄ£¬ÃâµÃÏ´ÎÓÖ¸øÍüÁË¡£ÄÚ ......
In:µÈÖµÁ¬½Ó£¬ÓÃÀ´²éÕÒ¶à±íÏàͬ×ֶεļǼ
Not In:·ÇµÈÖµÁ¬½Ó£¬ÓÃÀ´²éÕÒ²»´æÔڵļǼ
Inner join:ÄÚÁ¬½Ó£¬Ö÷ÒªÓÃÀ´²éÕÒ¶¼·ûºÏÌõ¼þµÄ¼Ç¼
Left join:×óÁ¬½Ó£¬Ö÷ÒªÓÃÀ´²éÕÒ×ó±ßÓУ¬ÓÒ±ßûÓеÄÓÿÕÖµ±í´ï
Right join:ÓÒÁ¬½Ó£¬Ö÷ÒªÓÃÀ´²éÕÒÓÒ±ßÓУ¬×ó±ßûÓеÄÓÿÕÖµ±í´ï
Order By:ÉýÐò Asc ½µÐò Desc
Group By:·Ö×éÅÅÐò ......
select *
from (select row_number() over(partition by t.type order by date desc) rn,
t.*
from ±íÃû t)
where rn <= 2;
typeÒª·ÖµÄÀà
date ÅÅÐò ......
Ò»£¬PL/SQL¿éµÄ½á¹¹ºÍ×é³ÉÔªËØ
PL/SQL³ÌÐòÓÉÉùÃ÷²¿·Ö£¬Ö´Ðв¿·Ö£¬Òì³£´¦Àí²¿·ÖÈý¸ö²¿·Ö×é³É¡£½á¹¹ÈçÏ£º
DECLARE
/*ÉùÃ÷²¿·Ö£ºÔÚ´ËÉùÃ÷PL/SQL±äÁ¿£¬ÀàÐͼ°Óα꣬ÒÔ¼°¾Ö²¿µÄ´æ´¢¹ý³ÌºÍº¯Êý*/
BEGIN
/*Ö´Ðв¿·Ö£º¹ý³Ì¼°sqlÓï¾ä£¬³ÌÐòÖ÷Òª²¿·Ö£¬ÊDZØÐëµÄ*/
EXCEPTION
/*Òì³£´¦Àí²¿·Ö£º´íÎó´¦Àí*/
END
PL/SQL¿ ......