sql server 2000/2005 ÓαêµÄʹÓòÙ×÷
1. ¶¨ÒåÓα궨Òå
ÓαêÓï¾äµÄºËÐÄÊǶ¨ÒåÁËÒ»¸öÓαê±êʶÃû£¬²¢°ÑÓαê±êʶÃûºÍÒ»¸ö²éѯÓï¾ä¹ØÁªÆðÀ´¡£DECLAREÓï¾äÓÃÓÚÉùÃ÷Óα꣬Ëüͨ¹ýSELECT²éѯ¶¨ÒåÓαê´æ´¢µÄÊý¾Ý¼¯ºÏ¡£Óï¾ä¸ñʽΪ£º
DECLARE ÓαêÃû³Æ [INSENSITIVE] [SCROLL]
CURSOR FOR selectÓï¾ä
[FOR{READ ONLY|UPDATE[OF ÁÐÃû×Ö±í]}]
²ÎÊý˵Ã÷£º
INSENSITIVEÑ¡Ï˵Ã÷Ëù¶¨ÒåµÄÓαêʹÓÃSELECTÓï¾ä²éѯ½á¹ûµÄ¿½±´£¬¶ÔÓαêµÄ²Ù×÷¶¼»ùÓڸÿ½±´½øÐС£Òò´Ë£¬ÕâÆÚ¼ä¶ÔÓαê»ù±¾±íµÄÊý¾ÝÐ޸IJ»ÄÜ·´Ó³µ½ÓαêÖС£ÕâÖÖÓαêÒ²²»ÔÊÐíͨ¹ýËüÐ޸Ļù±¾±íµÄÊý¾Ý¡£
SCROLLÑ¡Ïָ¶¨¸ÃÓαê¿ÉÓÃËùÓеÄÓαêÊý¾Ý¶¨Î»·½·¨ÌáÈ¡Êý¾Ý£¬Óα궨λ·½·¨°üÀ¨PRIOR¡¢FIRST¡¢LAST¡¢ABSOLUTE n ºÍRELATIVE n Ñ¡Ïî¡£
SelectÓï¾ä£ºÎª±ê×¼µÄSELECT²éѯÓï¾ä£¬Æä²éѯ½á¹ûΪÓαêµÄÊý¾Ý¼¯ºÏ£¬¹¹³ÉÓαêÊý¾Ý¼¯ºÏµÄÒ»¸ö»ò¶à¸ö±í³Æ×÷ÓαêµÄ»ù±í¡£
ÔÚÓαêÉùÃ÷Óï¾äÖУ¬ÓÐÏÂÁÐÌõ¼þ֮һʱ£¬ÏµÍ³×Ô¶¯°ÑÓα궨ÒåΪINSENSITIVEÓα꣺
SELECTÓï¾äÖÐʹÓÃÁËDISTINCT¡¢UNION¡¢ GROUP BY»òHAVINGµÈ¹Ø¼ü×Ö£»
ÈÎÒ»¸öÓαê»ù±íÖв»´æÔÚΨһË÷Òý¡£
ÆäËû
READ ONLYÑ¡Ï˵Ã÷¶¨ÒåÖ»¶ÁÓαꡣ
UPDATE [OF ÁÐÃû×Ö±í]Ñ¡Ï¶¨ÒåÓαê¿ÉÐ޸ĵÄÁС£Èç¹ûʹÓÃOF ÁÐÃû×Ö±íÑ¡Ï˵Ã÷Ö»ÔÊÐíÐÞ¸ÄËùÖ¸¶¨µÄÁУ¬·ñÔò£¬ËùÓÐÁоù¿ÉÐ޸ġ£
ÀýÈ磬²éѯ½ÌʦÃû×ÖºÍËù½ÌµÄ¿Î³ÌÃû£¬¶¨ÒåÓαêTCURSORµÄÓï¾äÈçÏ £º
DECLARE TCURSOR CURSOR FOR
SELECT tname, cname
from teacher ,couse
WHERE teacher.tno = couse.tno
2. ´ò¿ªÓαê
´ò¿ªÓαêÓï¾äÖ´ÐÐÓα궨ÒåÖеIJéѯÓï¾ä£¬²éѯ½á¹û´æ·ÅÔÚÓα껺³åÇøÖС£²¢Ê¹ÓαêÖ¸ÕëÖ¸ÏòÓαêÇøÖеĵÚÒ»¸öÔª×飬×÷ΪÓαêµÄȱʡ·ÃÎÊλÖᣲéѯ½á¹ûµÄÄÚÈÝÈ¡¾öÓë²éѯÓï¾äµÄÉèÖúͲéѯÌõ¼þ¡£
´ò¿ªÓαêµÄÓï¾ä¸ñʽ£º
EXEC SQL OPEN ¡´ÓαêÃû¡µ
Èç¹û´ò¿ªµÄÓαêΪINSENSITIVEÓα꣬ÔÚ´ò¿ªÊ±½«²úÉúÒ»¸öÁÙʱ±í£¬½«¶¨ÒåµÄÓαêÊý¾Ý¼¯ºÏ´ÓÆä»ù±íÖп½±´¹ýÀ´¡£
SQL ServerÖÐ,Óαê´ò¿ªºó£¬¿ÉÒÔ´ÓÈ«¾Ö±äÁ¿@@CURSOR_ROWSÖжÁÈ¡Óαê½á¹û¼¯ºÏÖеÄÐÐÊý¡£
Àý1£º´ò¿ªÇ°ÃæËù´´½¨µÄ²éѯ½ÌʦÐÕÃûºÍËù½Ì¿ÎÃû³ÆµÄÓαꡣ
OPEN tcursor
Àý2£ºÏÔʾÓαê½á¹û¼¯ºÏÖÐÊý¾ÝÐÐÊý
SELECT Êý¾ÝÐÐÊý = @@CURSOR_ROWS
3. ¶ÁÓαêÇøÖеĵ±Ç°Ôª×é
¶ÁÓαêÇøÊý¾ÝÓï¾äÊǶÁÈ¡ÓαêÇøÖе±Ç°Ôª×éµÄÖµ£¬²¢½«¸÷·ÖÁ¿ÒÀ´Î¸³¸øÖ¸¶¨µÄ¹²ÏíÖ÷±äÁ¿¡£FETCHÓï¾äÓÃÓÚ¶ÁÈ¡ÓαêÖеÄÊý¾Ý£¬Óï¾ä¸ñʽΪ£º
FETCH [[NEXT|PRIOR|FIRST|LAST| ABSOLUTE n| RELATIVE n]
from ] ÓαêÃû
Ïà¹ØÎĵµ£º
1¡¢ SQL×¢Èë¹¥»÷µÄ±¾ÖÊ£ºÈÿͻ§¶Ë´«µÝ¹ýÈ¥µÄ×Ö·û´®±ä³ÉSQLÓï¾ä£¬¶øÇÒÄܹ»±»Ö´ÐС£
2¡¢ ÿ¸ö³ÌÐòÔ±¶¼±ØÐë¼ç¸ºÆð·ÀÖ¹SQL×¢Èë¹¥»÷µÄÔðÈΡ£
¡¡¡¡ËµÆð·ÀÖ¹SQL×¢Èë¹¥»÷£¬¸Ð¾õºÜÓôÃÆ£¬Õâô¶àÄêÁË´ó¼ÒÒ»Ö±ÔÚÌÖÂÛ£¬Ò²Ò»Ö±ÔÚÕùÂÛ£¬¿ÉÊǵ½ÁËÏÖÔÚËƺõ»¹ÊÇûÓж¨ÂÛ¡£µ±²»ÖªµÀ×¢ÈëÔÀíµÄʱºò»á¾õµÃºÜÉñÆ棬Ôõô¾Í±»×¢ÈëÁËÄØ£¿ ......
select * from formtable_main_20
sp_help formtable_main_20
--drop view zcountQJ01
create view zcountQJ01
as
select appman ,year(startdata) as zyear,jb,cast(sum(num) as decimal(4,1) ) as num
from formtable_main_20
where year(getdate()) = year(startdata)
group by appman,jb,year(startdata) ......
1. SQLÓÅ»¯µÄÔÔòÊÇ£º½«Ò»´Î²Ù×÷ÐèÒª¶ÁÈ¡µÄBLOCKÊý¼õµ½×îµÍ,¼´ÔÚ×î¶ÌµÄʱ¼ä´ïµ½×î´óµÄÊý¾ÝÍÌÍÂÁ¿¡£
µ÷Õû²»Á¼SQLͨ³£¿ÉÒÔ´ÓÒÔϼ¸µãÇÐÈ룺
¼ì²é²»Á¼µÄSQL£¬¿¼ÂÇÆäд·¨ÊÇ·ñ»¹ÓпÉÓÅ»¯ÄÚÈÝ
¼ì²é×Ó²éѯ ¿¼ÂÇSQL×Ó²éѯÊÇ·ñ¿ÉÒÔÓüòµ¥Á¬½ÓµÄ·½Ê½½øÐÐÖØÐÂÊéд
¼ì²éÓÅ»¯ ......
ÔÚSQLServerµÄÐÔÄܵ÷ÓÅÖУ¬ÓÐÒ»¸ö²»¿É±ÈÄâµÄÎÊÌ⣺ÄǾÍÊÇÈçºÎÔÚÒ»¶ÎÐèÒª³¤Ê±¼äµÄ´úÂë»ò±»Æµ·±µ÷ÓõĴúÂëÖд¦ÀíÁÙʱÊý¾Ý¼¯?±í±äÁ¿ºÍÁÙʱ±íÊÇÁ½ÖÖÑ¡Ôñ¡£ÈçºÎÈ·¶¨Ê²Ã´Ê±ºòÓÃÁÙʱ±í£¬Ê²Ã´Ê±ºòÓñí±äÁ¿ÄØ£¿ÁÙʱ±íºÍ±í±äÁ¿¶¼ÓÐÌض¨µÄÊÊÓû·¾³¡£
¡¡¡¡±í±äÁ¿
¡¡¡¡±äÁ¿¶ ......
Access SQL×¢Èë²Î¿¼
°æ±¾ 0.2.1
(×î½ü¸üР10/10/2007)
Ô×÷Õß²»Ïê
ÃèÊö SQL²éѯ¼°×¢ÊÍ
×¢ÊÍ·û AccessÖÐûÓÐרÃŵÄ×¢ÊÍ·ûºÅ.Òò´Ë"/*", "--"ºÍ"#"¶¼Ã»·¨Ê¹ÓÃ.µ«ÊÇ¿ÉÒÔʹÓÿÕ×Ö·û"NULL"(%00)´úÌæ:
' UNION SELECT 1,1,1 from validTableName%00
Óï·¨´íÎóÐÅÏ¢ "[Microsoft][Driver ODBC Micros ......