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 ......
Ò»¡¢Ê²Ã´ÊÇSQL×¢Èëʽ¹¥»÷£¿
ËùνSQL×¢Èëʽ¹¥»÷£¬¾ÍÊǹ¥»÷Õß°ÑSQLÃüÁî²åÈëµ½Web±íµ¥µÄÊäÈëÓò»òÒ³ÃæÇëÇóµÄ²éѯ×Ö·û´®£¬ÆÛÆ·þÎñÆ÷Ö´ÐжñÒâµÄSQLÃüÁî¡£ÔÚijЩ±íµ¥ÖУ¬Óû§ÊäÈëµÄÄÚÈÝÖ±½ÓÓÃÀ´¹¹Ô죨»òÕßÓ°Ï죩¶¯Ì¬SQLÃüÁ»ò×÷Ϊ´æ´¢¹ý³ÌµÄÊäÈë²ÎÊý£¬ÕâÀà±íµ¥ÌرðÈÝÒ×Êܵ½SQL×¢Èëʽ¹¥»÷¡£³£¼ûµÄSQL×¢Èëʽ¹¥»÷¹ý³ÌÀàÈ磺
¢Å ......
SQL Server 2008 й¦ÄÜ
ÓÃÓÚ¹ÜÀíµÄй¦ÄÜÓÐÄÄЩ£¿
¶ÔÓÚ¾³£Òª¸ºÔð¹ÜÀí·Ö²¼ÔÚÊýʮ̨ÉõÖÁÊý°Ų̀·þÎñÆ÷ÖеÄÊýÒ԰ټƻòǧ¼ÆµÄ´óÐ͸´ÔÓÊý¾Ý¿â»·¾³µÄÊý¾Ý¿â¹ÜÀíÔ±¶øÑÔ£¬ÐµIJßÂÔ¹ÜÀí¡¢¶à·þÎñÆ÷²éѯ¹¦ÄÜ¡¢ÅäÖ÷þÎñÆ÷ÒÔ¼°Êý¾ÝÊÕ¼¯Æ÷/¹ÜÀí²Ö¿â¹¦ÄÜΪËûÃǸ³ÓèÁËÇ¿´óµÄÄÜÁ¦¡£
ÓÃÓÚ¿ÉÉìËõÐÔµÄй¦ÄÜÓÐÄ ......
¹¦ÄÜ£ºpl plusÖ´Ðб¾µØµÄsqlÎļþÖеÄsqlÓï¾ä
˵Ã÷£º±ÈÈ磺e:\zhaozhenlongÏÂÓÐcreate_table.sqlÎļþ£¬Ôò°´ÈçÏ·½·¨Ö´ÐУº
²½Ö裺
1¡¢ÔÚpl plusµÄ windowÏ£¬
2¡¢Ö´ÐУº
@e:\zhaozhenlong\drop_table.sql
@e:\zhaozhenlong\create_table.sql
......