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

ÄÚÁª SQL PL(Inline SQL PL)

----start
    Í¨³£SQL PLÖ»ÄÜʹÓÃÔÚ´æ´¢¹ý³Ì¡¢´¥·¢Æ÷¡¢Óû§×Ô¶¨Ò庯ÊýÖУ¬µ«ÊÇÓÐÒ»²¿·ÖSQL PLÒ²¿ÉÒÔÖ±½ÓÔÚÃüÁîÐб༭Æ÷»ò½Å±¾ÖÐʹÓã¬ËüÃÇÊÇ£º
DECLARE <variable>
SET
CASE
FOR
GET DIAGNOSTICS
GOTO
IF
RETURN
SIGNAL
WHILE
ITERATE
LEAVE 
ÒÔÏÂSQL PL²»ÄÜÖ±½ÓÔÚÃüÁîÐб༭Æ÷»ò½Å±¾ÖÐʹÓã¬ËüÃÇÖ»ÄÜÔÚ´æ´¢¹ý³Ì¡¢´¥·¢Æ÷¡¢Óû§×Ô¶¨Ò庯ÊýÖÐʹÓãº
ALLOCATE CURSOR
ASSOCIATE LOCATORS
DECLARE <cursor>
DECLARE ...HANDLER
PREPARE
EXECUTE
EXECUTE IMMEDIATE
LOOP
REPEAT
RESIGNAL
CALL
COMMIT/ROLLBACK 
ʾÀý£º
--Á¬½ÓÊý¾Ý¿â
CONNECT TO SAMPLE!

--ÄÚÁªSQL PL
BEGIN
DECLARE I INT DEFAULT 1;
DECLARE NUM INT DEFAULT 0;
SET NUM=0;

WHILE I<=10
DO
SET NUM=NUM+I;
SET I=I+1;
END WHILE;
END!

--¹Ø±ÕÁ¬½Ó
CONNECT RESET!
µ÷ÓÃʾÀý£º
½«Ê¾ÀýµÄÄÚÈݱ£´æÎªc:\test.sql,È»ºóÔÚDB2 ÃüÁî´°¿ÚÖÐÖ´ÐÐÈçÏÂÃüÁî
db2 -td! -vf c:\test.sql 
----¸ü¶à²Î¼û£ºSQL PL ¼ò½é
----ÉùÃ÷£º×ªÔØÇë×¢Ã÷³ö´¦¡£
----last updated 2010.2.9
----written by wave at 2010.2.9
----end


Ïà¹ØÎĵµ£º

SQL³£Ó÷ÖÒ³µÄ°ì·¨ ×ªÔØ

±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[ID] int IDENTITY (1,1)
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
Óï¾äÐÎʽ£º
SELECT TOP Ò³¼Ç¼ÊýÁ¿ *
from ±íÃû
WHERE (ID NOT IN
(SELECT TOP (ÿҳÐÐÊý*(Ò³Êý-1)) ID
from ±íÃû
ORDER BY ID))
ORDER BY ID
//×Ô¼º»¹¿ÉÒÔ¼ÓÉÏһЩ²éѯÌõ¼þ
Àý:
select top 2 ......

SQL³£Ó÷ÖÒ³µÄ°ì·¨

SQL³£Ó÷ÖÒ³µÄ°ì·¨:
±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[ID] int IDENTITY (1,1)
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
Óï¾äÐÎʽ£º 
SELECT TOP Ò³¼Ç¼ÊýÁ¿ *
from ±íÃû
WHERE (ID NOT IN
  (SELECT TOP (ÿҳÐÐÊý*(Ò³Êý-1)) ID
  from ±íÃû
  ORDER BY ID))
  ORDER BY ID
//×Ô ......

¸ß¼¶SQL 2

1 ÓÃUNIONÌæ»»OR (ÊÊÓÃÓÚË÷ÒýÁÐ)
ͨ³£Çé¿öÏÂ, ÓÃUNIONÌæ»»WHERE×Ó¾äÖеÄOR½«»áÆðµ½½ÏºÃµÄЧ¹û. ¶ÔË÷ÒýÁÐʹÓÃOR½«Ôì³ÉÈ«±íɨÃè. ×¢Òâ, ÒÔÉϹæÔòÖ»Õë¶Ô¶à¸öË÷ÒýÁÐÓÐЧ.
Èç¹ûÓÐcolumnûÓб»Ë÷Òý, ²éѯЧÂÊ¿ÉÄÜ»áÒòΪÄãûÓÐÑ¡ÔñOR¶ø½µµÍ. ÔÚÏÂÃæµÄÀý×ÓÖÐ, LOC_ID ºÍREGIONÉ϶¼½¨ÓÐË÷Òý.
¸ßЧ: SELECT LOC_ID , LOC_DESC , ......

SQLÖÐÖ÷¼üºÍÍâ¼üµÄÇø±ð

Ö÷¼ü:
Äܹ»Î¨Ò»±íʾÊý¾Ý±íÖеÄÿ¸ö¼Ç¼µÄ¡¾×ֶΡ¿»òÕß¡¾×ֶΡ¿µÄ×éºÏ¾Í³ÆÎªÖ÷Âë(Ö÷¼ü)¡£ Ò»¸öÖ÷¼üÊÇΨһʶ±ðÒ»¸ö±íµÄÿһ¼Ç¼£¬µ«ÕâÖ»ÊÇÆä×÷ÓõÄÒ»²¿·Ö£¬Ö÷¼üµÄÖ÷Òª×÷ÓÃÊǽ«¼Ç¼ºÍ´æ·ÅÔÚÆäËû±íÖеÄÊý¾Ý½øÐйØÁª¡£ÔÚÕâÒ»µãÉÏ£¬Ö÷¼üÊDz»Í¬±íÖи÷¼Ç¼֮¼äµÄ¼òµ¥Ö¸Õë¡£Ö÷¼üÔ¼Êø¾ÍÊÇÈ·¶¨±íÖеÄÿһÌõ¼Ç¼¡£Ö÷¼ü²»ÄÜÊÇ¿ÕÖµ¡£Î¨Ò ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ