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. Óα
Ïà¹ØÎĵµ£º
´Ë²¿·ÖÄÚÈÝ´´½¨Ò»¸öÇáÁ¿¼¶T-SQL²âÊÔÌ×¼þ£¬×ܹ²ÓÐ3¸ö½Å±¾£º
ÓÃÓÚ´´½¨²âÊÔƽ̨Êý¾ÝºÍ´ý²â´æ´¢¹ý³ÌµÄ½Å±¾
--======================
--makeDbTestAndResults.sql
use master
go
if exists (select * from sysdatabases where name = 'DbTestAndResults')
drop database makeDbTes ......
In:µÈÖµÁ¬½Ó£¬ÓÃÀ´²éÕÒ¶à±íÏàͬ×ֶεļǼ
Not In:·ÇµÈÖµÁ¬½Ó£¬ÓÃÀ´²éÕÒ²»´æÔڵļǼ
Inner join:ÄÚÁ¬½Ó£¬Ö÷ÒªÓÃÀ´²éÕÒ¶¼·ûºÏÌõ¼þµÄ¼Ç¼
Left join:×óÁ¬½Ó£¬Ö÷ÒªÓÃÀ´²éÕÒ×ó±ßÓУ¬ÓÒ±ßûÓеÄÓÿÕÖµ±í´ï
Right join:ÓÒÁ¬½Ó£¬Ö÷ÒªÓÃÀ´²éÕÒÓÒ±ßÓУ¬×ó±ßûÓеÄÓÿÕÖµ±í´ï
Order By:ÉýÐò Asc ½µÐò Desc
Group By:·Ö×éÅÅÐò ......
¹¦ÄÜ£ºpl/sqlÖ´Ðб¾µØµÄsqlÎļþÖеÄsqlÓï¾ä
˵Ã÷£º±ÈÈ磺e:\zhaozhenlongÏÂÓÐcreate_table.sqlÎļþ£¬Ôò°´ÈçÏ·½·¨Ö´ÐУº
²½Ö裺
1¡¢ÔÚpl/sqlµÄcommand windowÏ£¬
»òÔÚwindowsµÄ¿ªÊ¼/'ÔËÐÐ'Ï£¬sqlplus /nolog; connect cs@orademo;
2¡¢Ö´ÐУº
@@e:\zhaozhenlong\drop_table ......
1. ËÀËøÔÀí
¸ù¾Ý²Ù×÷ϵͳÖеĶ¨Ò壺ËÀËøÊÇÖ¸ÔÚÒ»×é½ø³ÌÖеĸ÷¸ö½ø³Ì¾ùÕ¼Óв»»áÊͷŵÄ×ÊÔ´£¬µ«Òò»¥ÏàÉêÇë±»ÆäËû½ø³ÌËùÕ¾Óò»»áÊͷŵÄ×ÊÔ´¶ø´¦ÓÚµÄÒ»ÖÖÓÀ¾ÃµÈ´ý״̬¡£
ËÀËøµÄËĸö±ØÒªÌõ¼þ£º
»¥³âÌõ¼þ(Mutual exclusion)£º×ÊÔ´²»Äܱ»¹²Ïí£¬Ö»ÄÜÓÉÒ»¸ö½ø³ÌʹÓá£
ÇëÇóÓë±£³ÖÌõ¼þ(Ho ......