SQL SERVER Óαê
SqlServer 2000 ÓαêÓ÷¨Ð¡Àý ·µ¯ÑºÎ²É£Cannon(¿¨Ù¯) »
--------------------------------------------------------------------------------
DECLARE CURSOR (T-SQL)´´½¨Óαê
September 14th, 2006 by OoperMan (1 votes, average: 5 out of 5) Loading ...
SQL Server 2005 Áª»ú´ÔÊé
DECLARE CURSOR (Transact-SQL)
¸üÐÂÈÕÆÚ£º 2005 Äê 12 Ô 5 ÈÕ
¶¨Òå Transact-SQL ·þÎñÆ÷ÓαêµÄÊôÐÔ£¬ÀýÈçÓαêµÄ¹ö¶¯ÐÐΪºÍÓÃÓÚÉú³ÉÓαêËù²Ù×÷µÄ½á¹û¼¯µÄ²éѯ¡£DECLARE CURSOR ½ÓÊÜ»ùÓÚ SQL-92 ±ê×¼µÄÓï·¨ºÍʹÓÃÒ»×é Transact-SQL À©Õ¹²å¼þµÄÓï·¨¡£
Transact-SQL Óï·¨Ô¼¶¨
Óï·¨
SQL 92 Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR ...{ READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
²ÎÊý
cursor_name
Ëù¶¨ÒåµÄ Transact-SQL ·þÎñÆ÷ÓαêµÄÃû³Æ¡£cursor_name ±ØÐë·ûºÏ±êʶ·û¹æÔò¡£Óйرêʶ·û¹æÔòµÄÏêϸÐÅÏ¢£¬Çë²ÎÔÄʹÓñêʶ·û×÷Ϊ¶ÔÏóÃû³Æ¡£
INSENSITIVE
¶¨ÒåÒ»¸öÓα꣬ÒÔ´´½¨½«ÓɸÃÓαêʹÓõÄÊý¾ÝµÄÁÙʱ¸´±¾¡£¶ÔÓαêµÄËùÓÐÇëÇó¶¼´Ó tempdb ÖеÄÕâÒ»ÁÙʱ±íÖеõ½Ó¦´ð£»Òò´Ë£¬ÔÚ¶Ô¸ÃÓαê½øÐÐÌáÈ¡²Ù×÷ʱ·µ»ØµÄÊý¾ÝÖв»·´Ó³¶Ô»ù±íËù×öµÄÐ޸ģ¬²¢ÇÒ¸ÃÓα겻ÔÊÐíÐ޸ġ£Ê¹Óà SQL-92 Ó﷨ʱ£¬Èç¹ûÊ¡ÂÔ INSENSITIVE£¬ÔòÒÑÌá½»µÄ£¨ÈκÎÓû§£©¶Ô»ù´¡±íµÄɾ³ýºÍ¸üж¼·´Ó³ÔÚºóÃæµÄÌáÈ¡ÖС£
SCROLL
Ö¸¶¨ËùÓеÄÌáÈ¡Ñ¡ÏFIRST¡¢LAST¡¢PRIOR¡¢NEXT¡¢RELATIVE¡¢ABSOLUTE£©¾ù¿ÉÓá£Èç¹ûδÔÚ SQL-92 DECLARE CURSOR ÖÐÖ¸¶¨ SCROLL£¬Ôò NEXT ÊÇΨһ֧³ÖµÄÌáÈ¡Ñ¡Ïî¡£Èç¹ûÒ²Ö¸¶¨ÁË FAST_FORWARD£¬Ôò²»ÄÜÖ¸¶¨ SCROLL¡£
select_statement
¶¨ÒåÓαê½á¹û¼¯µÄ±ê×¼ SELECT Óï¾ä¡£ÔÚÓαêÉùÃ÷µÄ select_statement ÄÚ²»ÔÊÐíʹÓùؼü×Ö COM
Ïà¹ØÎĵµ£º
SQLµÄÓÅ»¯Ó¦¸Ã´Ó5¸ö·½Ãæ½øÐе÷Õû£º
1.È¥µô²»±ØÒªµÄ´óÐͱíµÄÈ«±íɨÃè
2.»º´æСÐͱíµÄÈ«±íɨÃè
3.¼ìÑéÓÅ»¯Ë÷ÒýµÄʹÓÃ
4.¼ìÑéÓÅ»¯µÄÁ¬½Ó¼¼Êõ
5.¾¡¿ÉÄܼõÉÙÖ´Ðмƻ®µÄCost
SQLÓï¾ä£º
ÊǶÔÊý¾Ý¿â(Êý¾Ý)½øÐвÙ×÷µÄΩһ;¾¶£»
ÏûºÄÁË70%~90%µÄÊý¾Ý¿â×ÊÔ´£»¶ÀÁ¢ÓÚ³ÌÐòÉè¼ÆÂß¼£¬Ïà¶ÔÓÚ¶Ô³ÌÐòÔ´´úÂëµÄÓÅ»¯£¬¶ÔSQLÓï¾äµÄÓÅ»¯Ô ......
SQL²Ù×÷È«¼¯
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREA ......
and exists (select * from sysobjects) //ÅжÏÊÇ·ñÊÇMSSQL
and exists(select * from tableName) //ÅжÏij±íÊÇ·ñ´æÔÚ..tableNameΪ±íÃû
and 1=(select @@VERSION) //MSSQL°æ±¾
And 1=(select db_name()) //µ±Ç°Êý¾Ý¿âÃû
and 1=(select @@servername) //±¾µØ·þÎñÃû
and 1=(select IS_SRVROLEMEMBER('sysadmin')) //Å ......
create PROCEDURE [dbo].[P_PageTest]
@SQL Nvarchar(max), --SQLÓï¾ä²»°üÀ¨ÅÅÐò
@CurPage int, --µ±Ç°Ò³
@PageRows int, --Ò³Ãæ³ß´ç
@Order Nvarchar(20), --ÅÅÐò×Ö¶Î
@OrderType Nvarchar( ......