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

SQLÊý¾Ý¿â

 CURSOR
==================================
l  SQL ÓαêCURSORµÄʹÓÃ
 
ʹÓÃÆðÀ´ºÜ¼òµ¥£¬Ïȶ¨Ò壬Ȼºó¸³¸öÖµ£¬´ò¿ª£¬Í¨¹ýWhile Loop Ò»¸öÒ»¸ö¶ÁÏÂÈ¥£¬×îºó¹Ø±Õ£¬ÊÍ·ÅÄÚ´æ¡£»ù±¾Ì×·ÈçÏ£º
DECLARE MyCursor cursor /* ÉùÃ÷Óα꣬ĬÈÏΪµ¥´¿ÏòǰµÄÓαꡣÈç¹ûÏëҪǰºóÌøÀ´ÌøÈ¥µÄ£¬Ð´³ÉScroll Cursor¼´¿É */
FOR
SELECT Column1, Column2 from ijij±í
Where ijijÌõ¼þ
OPEN MyCursor /* ´ò¿ªÓαê */
FETCH NEXT from MyCursor Into @A, @B /* ¶ÁÈ¡µÚ1ÐÐÊý¾Ý*/
WHILE @@FETCH_STATUS = 0 /* ÓÃWHILEÑ­»·¿ØÖÆÓαê */
BEGIN /*BEGIN-END ¿é*/
//ÔÚÕâÀï,ÓÃ@A,@B×öµãÊÂ,»òÕ߸ÄÒ»¸ÄֵʲôµÄ.
FETCH NEXT from MyCursor Into @A, @B /* ×¥ÏÂÒ»ÐÐ */
END
CLOSE MyCursor /* ¹Ø±ÕÓαê */
DEALLOCATE MyCursor /* ɾ³ýÓαê,ÊÍ·ÅÄÚ´æ */
ÉùÃ÷Óαê///////////////////////
USE pubs
GO
DECLARE authors_cursor CURSOR FOR
SELECT au_id,au_fname,au_lname
from authors
GO
USE pubs
GO
DECLARE authors_cursor1 CURSOR FOR
SELECT au_id,au_fname,au_lname
from authors
FOR READ ONLY
GO
USE pubs
GO
DECLARE authors_cursor2 CURSOR FOR
SELECT au_id,au_fname,au_lname
from authors
FOR UPDATE
GO
 
´ò¿ªÓαê////////////
DECLARE mancursor CURSOR FOR
SELECT *
from db_manpowerinfo.dbo.ÈËʱí
WHERE 񅧏='00019'
OPEN mancursor
FETCH NEXT from mancursor
CLOSE mancursor
 
¶ÁÈ¡ÓαêÖеÄÊý¾Ý/////////////////////
DECLARE mancursor1 CURSOR FOR
SELECT ±àºÅ,ÐÕÃû,ÐÔ±ð,Éí·ÝÖ¤ºÅ
from db_manpowerinfo.dbo.ÈËʱí
OPEN mancursor1
FETCH NEXT from mancursor1
WHILE @@FETCH_STATUS=0
BEGIN
  FETCH NEXT from mancursor1
END
CLOSE mancursor1
DEALLOCATE mancursor1
 
¹Ø±Õ²¢ÊÍ·ÅÓαê//////////////////
USE ÏúÊÛ¹ÜÀíϵͳ
GO
DECLARE mycursor CURSOR FOR
SELECT ¿Í»§È«³Æ,count(*) as ÏúÊۼǼÊý,sum(ÊýÁ¿)as ÏúÊÛÊýÁ¿ from ÏúÊÛ±í WHERE ÊýÁ¿ >100 Group BY ¿Í»§È«³Æ
OPEN mycursor
FETCH NEXT from mycursor
WHILE @@FETCH_STATUS = 0   -- ÅжÏÊÇ·ñ»¹ÓÐÐëÒª¶ÁÈ¡µÄ¼Ç¼
BEGIN
   FETCH NEXT from mycursor     --¶ÁÈ¡¼Ç¼µ½Óαê
END
CLOSE mycursor
DEALLOCATE mycurso


Ïà¹ØÎĵµ£º

sql 2000ºÍsql 2005Ïà±È½Ï, 2005ÓÅÔ½ÐÔÔÚÄÄÀï?

Ò»¡¢Êý¾Ý¿âÉè¼Æ·½Ãæ
1¡¢×Ö¶ÎÀàÐÍ¡£
varchar(max)\nvarchar(max)ÀàÐ͵ÄÒýÈë´ó´óµÄÌá¸ßÁ˱à³ÌµÄЧÂÊ£¬¿ÉÒÔʹÓÃ×Ö·û´®º¯Êý¶ÔCLOBÀàÐͽøÐвÙ×÷£¬ÕâÊÇÒ»¸öÁÁµã¡£µ«ÊÇÕâ¾ÍÒý·¢Á˶ÔvarcharºÍcharЧÂÊÌÖÂÛµÄÀÏÎÊÌâ¡£µ½µ×ÈçºÎ·ÖÅävarcharµÄÊý¾Ý£¬ÊÇ·ñ»á³öÏÖ´ó¹æÄ£µÄË鯬£¿ÊÇ·ñË鯬»áÒý·¢Ð§ÂÊÎÊÌ⣿Õâ¶¼ÊÇÐèÒª½øÒ»²½Ì½ÌֵĶ«Î÷¡£
v ......

sql³£Óú¯Êý´óÈ«


Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼äµÄASCII ÂëÖµ£¬CHAR£¨£© ·µ»ØNULL ¡£
3¡¢LO ......

sqlÓï¾äÓÅ»¯3


 
µÍЧ: (Ë÷ÒýʧЧ)
SELECT … from DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
¸ßЧ: (Ë÷ÒýÓÐЧ)
SELECT … from DEPARTMENT WHERE DEPT_CODE >=0;
£¨27£©×ÜÊÇʹÓÃË÷ÒýµÄµÚÒ»¸öÁУº
Èç¹ûË÷ÒýÊǽ¨Á¢ÔÚ¶à ......

SQLÊý¾Ý¿âÖ®¶þ

l  INNER JOIN
ÄÚÁ¬½ÓÊÇ×î³£¼ûµÄÒ»ÖÖÁ¬½Ó£¬ËüÒ³±»³ÆÎªÆÕͨÁ¬½Ó£¬¶øE.FCodd×îÔç³ÆÖ®Îª×ÔÈ»Á¬½Ó¡£
ÏÂÃæÊÇANSI SQL£­92±ê×¼
select *  from    t_institution i
inner join t_teller t
on i.inst_no = t.inst_no  //˵Á½¸ö±íÖ®¼äµÄ¹ØÏµÓÃON
where i.inst_no = "5801"
ÆäÖÐinner¿ÉÒÔʡ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ