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. Óα
Ïà¹ØÎĵµ£º
USE AdventureWorks
GO
CREATE PROC spEmployee
AS
SELECT * from Humanresources.Employee
EXEC spEmployee
ALTER PROC spEmployee
AS SELECT EmployeeID from Humanresources.Employee
drop proc spEmployee
ALTER PROC spEmployee
@LastName nvarchar(50) = NULL
AS
IF @LastName IS NULL
SELECT * f ......
Ñ¡Ôñ10gÊÇÒòΪ¶ÔÍø¸ñ¼¼Êõ±È½ÏºÃÆ棬ºÃÏñ»¹Ã»µÃµ½¹ã·ºÓ¦Óã¬Ò²¾ÍÎÞËùνÓëÇ°Ãæ°æ±¾ÓкܴóÇø±ðÁË¡£
ÔÚѸÀ×ÉÏÏÂÁ˸öÈí¼þ£¬ÔËÐÐsqlplusw£¬È»ºóÕÕ×ÅÊ飬¿ñÇÃÁËÒ»·£¬ÓÐÔÚÍøÉÏÏÂÁ˸öÊÓƵ½Ì³Ì£¨MLDNħÀֿƼ¼_Oracle¿ÎÌã©£¬½²µÃͦºÃµÄ£¬¾ÍÊÇʱ¼ä³¤Á˵㣬²»Èç¿´ÊéÀ´µÃ¿ì¡£·´ÕýÏÖÔÚÖ»ÊÇÏëÊìϤһÏ»ù±¾Óï¾ä¡£
¿´ÁËÁ½ÌìÊéÁË¡£Á˽âÁËÒ» ......
´Ë²¿·ÖÄÚÈÝ´´½¨Ò»¸öÇáÁ¿¼¶T-SQL²âÊÔÌ×¼þ£¬×ܹ²ÓÐ3¸ö½Å±¾£º
ÓÃÓÚ´´½¨²âÊÔƽ̨Êý¾ÝºÍ´ý²â´æ´¢¹ý³ÌµÄ½Å±¾
--======================
--makeDbTestAndResults.sql
use master
go
if exists (select * from sysdatabases where name = 'DbTestAndResults')
drop database makeDbTes ......
´ÓÕâÒ»½Ú¿ªÊ¼ÄØ£¬ÎÒÃǾÍÒª¿ªÊ¼CLRµÄ±à³ÌÖ®ÂÃÁË¡£ÔÚÕâ֮ǰ£¬ÎÒÏȰѱ¾½ÚÖÐÐèÒªÁ˽âµÄÁ½¸öÐÂÀàSqlDataRecordºÍSqlMetaData£¬¼°Îå¸öз½·¨SqlContext.Pipe.SendResultsStart£¬SqlContext.Pipe.SendResultsRow£¬SqlContext.Pipe.SendResultsEnd£¬SqlContext.Pipe.SendºÍSqlContext.Pipe.ExecuteAndSend½øÐÐһϱØÒªµÄ˵Ã÷£¬·½±ã´ ......
SQL SelectÓï¾äÍêÕûµÄÖ´ÐÐ˳Ðò
2009-06-02 15:57
SQL SelectÓï¾äÍêÕûµÄÖ´ÐÐ˳Ðò£º1¡¢from×Ó¾ä×é×°À´×Ô²»Í¬Êý¾ÝÔ´µÄÊý¾Ý£»2¡¢where×Ó¾ä»ùÓÚÖ¸¶¨µÄÌõ¼þ¶Ô¼Ç¼ÐнøÐÐɸѡ£»3¡¢group by×Ӿ佫Êý¾Ý»®·ÖΪ¶à¸ö·Ö×飻4¡¢Ê¹Óþۼ¯º¯Êý½øÐмÆË㣻5¡¢Ê¹ÓÃhaving×Ó¾äɸѡ·Ö×飻
ÒÔϽáÂÛÊÇͨ¹ýÔÚSql Server2000ÉϵÄʵÑéµÃ³öµÄ½áÂÛ£¬ ......