ÔÚSqlServer´æ´¢¹ý³ÌÖÐʹÓÃCursor£¨Óα꣩²Ù×÷¼Ç¼
1. ΪºÎʹÓÃÓα꣺
ʹÓÃÓαê(cursor)µÄÒ»¸öÖ÷ÒªµÄÔÒò¾ÍÊǰѼ¯ºÏ²Ù×÷ת»»³Éµ¥¸ö¼Ç¼´¦Àí·½Ê½¡£ÓÃSQLÓïÑÔ´ÓÊý¾Ý¿âÖмìË÷Êý¾Ýºó£¬½á¹û·ÅÔÚÄÚ´æµÄÒ»¿éÇøÓòÖУ¬ÇÒ½á¹û
ÍùÍùÊÇÒ»¸öº¬Óжà¸ö¼Ç¼µÄ¼¯ºÏ¡£Óαê»úÖÆÔÊÐíÓû§ÔÚSQL serverÄÚÖðÐеطÃÎÊÕâЩ¼Ç¼£¬°´ÕÕÓû§×Ô¼ºµÄÒâÔ¸À´ÏÔʾºÍ´¦ÀíÕâЩ¼Ç¼¡£
2. ÈçºÎʹÓÃÓα꣺
Ò»°ãµØ£¬Ê¹ÓÃÓα궼×ñÑÏÂÁеij£¹æ²½Ö裺
(1) ÉùÃ÷Óαꡣ°ÑÓαêÓëT-SQLÓï¾äµÄ½á¹û¼¯ÁªÏµÆðÀ´¡£
(2) ´ò¿ªÓαꡣ
(3) ʹÓÃÓαê²Ù×÷Êý¾Ý¡£
(4) ¹Ø±ÕÓαꡣ
2.1. ÉùÃ÷Óαê
DECLARE CURSORÓï¾äSQL-92±ê×¼Óï·¨¸ñʽ£º
DECLARE ÓαêÃû [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR sql-statement
Eg:
Declare MycrsrVar Cursor
FOR Select * from tbMyData
2.2 ´ò¿ªÓαê
OPEN MycrsrVar
µ±Óα걻´ò¿ªÊ±£¬ÐÐÖ¸Õ뽫ָÏò¸ÃÓα꼯µÚ1ÐÐ֮ǰ£¬Èç¹ûÒª¶ÁÈ¡Óα꼯ÖеĵÚ1ÐÐÊý¾Ý£¬±ØÐëÒÆ¶¯ÐÐÖ¸ÕëʹÆäÖ¸ÏòµÚ1ÐС£¾Í±¾Àý¶øÑÔ£¬¿ÉÒÔʹÓÃÏÂÁвÙ×÷¶ÁÈ¡µÚ1ÐÐÊý¾Ý£º
FETCH FIRST from E1cursor
»ò FETCH NEXT from E1cursor
2.3 ʹÓÃÓαê²Ù×÷Êý¾Ý
ÏÂÃæµÄʾÀýÓÃ@@FETCH_STATUS¿ØÖÆÔÚÒ»¸öWHILEÑ»·ÖеÄÓαê»î¶¯
/* ʹÓÃÓαê¶ÁÈ¡Êý¾ÝµÄ²Ù×÷ÈçÏ¡£*/
DECLARE E1cursor cursor /* ÉùÃ÷Óα꣬ĬÈÏΪFORWARD_ONLYÓαê */
FOR SELECT * from c_example
OPEN E1cursor /* ´ò¿ªÓαê */
FETCH NEXT from E1cursor /* ¶ÁÈ¡µÚ1ÐÐÊý¾Ý*/
WHILE @@FETCH_STATUS = 0 /* ÓÃWHILEÑ»·¿ØÖÆÓαê»î¶¯ */
BEGIN
FETCH NEXT from E1cursor /* ÔÚÑ»·ÌåÄÚ½«¶ÁÈ¡ÆäÓàÐÐÊý¾Ý */
END
CLOSE E1cursor /* ¹Ø±ÕÓαê */
DEALLOCATE E1cursor &
Ïà¹ØÎĵµ£º
ÊÀʶ´Ã÷½ÔѧÎÊ£¬ÈËÇéÁ·´ï¼´ÎÄÕ¡£×öASPʱ£¬×î³£ÓõÄÊý¾Ý¿â¼´SqlserverÓëAccessÊý¾Ý¿âĪÊôÁË£¡
µ«Ê¹Óûᾳ£·¢ÏֺܶàSQLÖ´ÐеÄÎÊÌâ¡£ÕâÀïÕûÀí³öÖ®¼äµÄ²îÒ죬×ö¸öÊ®´ó²îÒìµÄ×ܽᡣ
ACCESS½á¹¹¼òµ¥ÈÝÒ×´¦Àí£¬¶øÇÒÒ²ÄÜÂú×ã¶àÊýµÄÍøÕ¾³ÌÐòÒªÇó£¬Ò²ÊdzõѧÕßµÄÊÔÅ£µ¶¡£
ACCESSÊÇСÐÍÊý¾Ý¿â£¬¼ÈÈ»ÊÇСÐ;ÍÓÐËû¸ù±¾µÄ¾ÖÏÞÐÔ£º
......
SQL Server 2000
Installing SQL Server 2000 (EÎÄ)
http://msdn.microsoft.com/en-us/library/aa299042(SQL.80).aspx
SQL Server 2000²¹¶¡
Microsoft SQL Server 2000 Service Pack 3a
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=90DCD52C-0488-4E46-AFBF-ACACE536 ......
ÓÃwindowsÕ˺ݹÊÇ¿ÉÒԵǼµÄ¡£
²éÕÒÎÊÌâµÄ¹ý³Ì£º
µÚÒ»²½£º Æô¶¯ËùÓÐÓëSQLÓйصķþÎñ£¬ÎÊÌâÒÀ¾É£»
µÚ¶þ²½£º ²é¿´windows·À»ðǽ£¬±»Ä¬ÈÏÆô¶¯ÁË£¬²»Æô¶¯Ñ¡ÔñÏî±»Disabled¡£Á½¸öÌáʾ“ÓÉÓÚ°²È«¿¼ÂÇ,ijЩÉèÖÃÓÉ×é²ßÂÔ¿ØÖÆ”“Windows·À»ðǽÕýÔÚʹÓÃÄúµÄÓòÉèÖÔ£¬ÏÈÓÃgpedit.msc´ò×é²ßÂÔ±à¼Æ÷£¬»úËã»úÅ ......
½ñÌìÔÚдÊÓͼʱ£¬Óöµ½Òª°ÑDatetimeÀàÐÍתVarcharÀàÐÍ¡£ÒÔǰÔÚORALCE¾ÍÈÝÒ×£¬Ö±½ÓToChar(getdate(),'yyyy-mm-dd')¡£ÔÚSQL Server 2005
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
200 ......