Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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. Óα


Ïà¹ØÎĵµ£º

SQL SERVER 2005 ´æ´¢¹ý³Ì

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×¢Èëʽ¹¥»÷£¬¾ÍÊǹ¥»÷Õß°ÑSQLÃüÁî²åÈëµ½Web±íµ¥µÄÊäÈëÓò»òÒ³ÃæÇëÇóµÄ²éѯ×Ö·û´®£¬ÆÛÆ­·þÎñÆ÷Ö´ÐжñÒâµÄSQLÃüÁî¡£ÔÚijЩ±íµ¥ÖУ¬Óû§ÊäÈëµÄÄÚÈÝÖ±½ÓÓÃÀ´¹¹Ô죨»òÕßÓ°Ï죩¶¯Ì¬SQLÃüÁ»ò×÷Ϊ´æ´¢¹ý³ÌµÄÊäÈë²ÎÊý£¬ÕâÀà±íµ¥ÌرðÈÝÒ×Êܵ½SQL×¢Èëʽ¹¥»÷¡£³£¼ûµÄSQL×¢Èëʽ¹¥»÷¹ý³ÌÀàÈ磺
¢Å ......

SQL Server 2008 й¦ÄÜ

SQL Server 2008 й¦ÄÜ
 
 ÓÃÓÚ¹ÜÀíµÄй¦ÄÜÓÐÄÄЩ£¿
¶ÔÓÚ¾­³£Òª¸ºÔð¹ÜÀí·Ö²¼ÔÚÊýʮ̨ÉõÖÁÊý°Ų̀·þÎñÆ÷ÖеÄÊýÒ԰ټƻòǧ¼ÆµÄ´óÐ͸´ÔÓÊý¾Ý¿â»·¾³µÄÊý¾Ý¿â¹ÜÀíÔ±¶øÑÔ£¬ÐµIJßÂÔ¹ÜÀí¡¢¶à·þÎñÆ÷²éѯ¹¦ÄÜ¡¢ÅäÖ÷þÎñÆ÷ÒÔ¼°Êý¾ÝÊÕ¼¯Æ÷/¹ÜÀí²Ö¿â¹¦ÄÜΪËûÃǸ³ÓèÁËÇ¿´óµÄÄÜÁ¦¡£
 
 ÓÃÓÚ¿ÉÉìËõÐÔµÄй¦ÄÜÓÐÄ ......

pl plusÖ´Ðб¾µØµÄsqlÎļþÖеÄsqlÓï¾ä


¹¦ÄÜ£ºpl plusÖ´Ðб¾µØµÄsqlÎļþÖеÄsqlÓï¾ä
˵Ã÷£º±ÈÈ磺e:\zhaozhenlongÏÂÓÐcreate_table.sqlÎļþ£¬Ôò°´ÈçÏ·½·¨Ö´ÐУº
²½Ö裺
1¡¢ÔÚpl plusµÄ windowÏ£¬
 
2¡¢Ö´ÐУº
@e:\zhaozhenlong\drop_table.sql
@e:\zhaozhenlong\create_table.sql
......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ