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

sql ÓαêµÄ»ù±¾Ó÷¨

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 ] ÓαêÃû
[INTO @


Ïà¹ØÎĵµ£º

SQL ¿ª·¢ÈËÔ± Oracle Berkeley DB Ö¸ÄÏ£¨zz£©

×ªÔØÒ»¸öBDBµÄÏà¹Ø½éÉÜ£¬»ù±¾¿ÉÒÔµ±ËÙ³ÉÊÖ²áÓ㬸ü¶àµÄÎĵµ¿ÉÒÔÈ¥Oracle¹Ù·½ÍøÕ¾ÏÂÔØ¡£
SQL ¿ª·¢ÈËÔ± Oracle Berkeley DB Ö¸ÄÏ
×÷ÕߣºMargo Seltzer
Oracle Berkeley DB Öг£Óà SQL º¯ÊýʹÓÃÖ¸ÄÏ¡£
2007 Äê 9 Ô·¢±í
³£³£ÓÐÈËÎÊ Oracle Berkeley DB “ÎÒÈçºÎÔÚ Berkeley DB ÖнøÐÐ <SQL ²éѯ>£¿"Òò´Ë£¬ÎÒÃÇ ......

SQL Ìí¼Óɾ³ý·þÎñÆ÷

//--Ìí¼Ó·þÎñÆ÷
EXEC sp_addlinkedserver
      @server='LQXLSJ-600A5A60',--±»·ÃÎʵķþÎñÆ÷±ðÃû
      @srvproduct='',
      @provider='SQLOLEDB',
      @datasrc='LQXLSJ-600A5A60'   --Òª·ÃÎ ......

SQL °´ÖÜ,ÔÂ,¼¾¶È,Äê²éѯͳ¼ÆÊý¾Ý

//°´×ÔÈ»ÖÜͳ¼Æ 
select to_char(date,'iw'),sum() 
from 
where 
group by to_char(date,'iw') 
//°´×ÔÈ»ÔÂͳ¼Æ 
select to_char(date,'mm'),sum() 
from 
where 
group by to_char(date,'mm') 
//°´¼¾Í³¼Æ 
select to_char(date,'q'),sum() 
fr ......

Sql Server ÖÐÒ»¸ö·Ç³£Ç¿´óµÄÈÕÆÚ¸ñʽ»¯º¯Êý

Sql Server ÖÐÒ»¸ö·Ç³£Ç¿´óµÄÈÕÆÚ¸ñʽ»¯º¯Êý
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GE ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ