å¨æ°æ®åºå¼åè¿ç¨ä¸ï¼å½ä½ æ£ç´¢çæ°æ®åªæ¯ä¸æ¡è®°å½æ¶ï¼ä½ æç¼åçäºå¡è¯å¥ä»£ç å¾å¾ä½¿ç¨SELECT INSERT è¯å¥ãä½æ¯æ们常常ä¼éå°è¿æ ·æ
åµï¼å³ä»æä¸ç»æéä¸éä¸å°è¯»åä¸æ¡è®°å½ãé£ä¹å¦ä½è§£å³è¿ç§é®é¢å¢ï¼æ¸¸æ 为æ们æä¾äºä¸ç§æ为ä¼ç§ç解å³æ¹æ¡ã
1.1 游æ å游æ çä¼ç¹
å¨æ°æ®åºä¸ï¼æ¸¸æ æ¯ä¸ä¸ªååéè¦çæ¦å¿µã游æ æä¾äºä¸ç§å¯¹ä»è¡¨ä¸æ£ç´¢åºçæ°æ®è¿è¡æä½ççµæ´»æ段ï¼å°±æ¬è´¨èè¨ï¼æ¸¸æ å®é
ä¸æ¯ä¸ç§è½ä»å
æ¬å¤æ¡æ°æ®è®°å½çç»æéä¸æ¯æ¬¡æåä¸æ¡è®°å½çæºå¶ã游æ æ»æ¯ä¸ä¸æ¡T_SQL éæ©è¯å¥ç¸å
³èå 为游æ ç±ç»æéï¼å¯ä»¥æ¯é¶æ¡ãä¸æ¡æç±ç¸å
³çéæ©è¯å¥æ£ç´¢åºçå¤æ¡è®°å½ï¼åç»æéä¸æåç¹å®è®°å½ç游æ ä½ç½®ç»æãå½å³å®å¯¹ç»æéè¿è¡å¤çæ¶ï¼å¿
须声æä¸ä¸ªæå该ç»æéç游æ ãå¦ææ¾ç»ç¨ C è¯è¨åè¿å¯¹æ件è¿è¡å¤ççç¨åºï¼é£ä¹æ¸¸æ å°±åæ¨æå¼æ件æå¾å°çæ件å¥æä¸æ ·ï¼åªè¦æ件æå¼æåï¼è¯¥æ件å¥æå°±å¯ä»£è¡¨è¯¥æ件ã对äºæ¸¸æ èè¨ï¼å
¶éçæ¯ç¸åçãå¯è§æ¸¸æ è½å¤å®ç°æä¸ä¼ ç»ç¨åºè¯»åå¹³é¢æ件类似çæ¹å¼å¤çæ¥èªåºç¡è¡¨çç»æéï¼ä»èæ表ä¸æ°æ®ä»¥å¹³é¢æ件çå½¢å¼åç°ç»ç¨åºã æ们ç¥éå
³ç³»æ°æ®åºç®¡çç³»ç»å®è´¨æ¯é¢åéåçï¼å¨MS SQL SERVER ä¸å¹¶æ²¡æä¸ç§æ述表ä¸åä¸è®°å½ç表达形å¼ï¼é¤é使ç¨where åå¥æ¥éå¶åªæä¸æ¡è®°å½è¢«éä¸ãå æ¤æ们å¿
é¡»åå©äºæ¸¸æ æ¥è¿è¡é¢ååæ¡è®°å½çæ°æ®å¤çã
ç±æ¤å¯è§ï¼æ¸¸æ å
许åºç¨ç¨åºå¯¹æ¥è¯¢è¯å¥select è¿åçè¡ç»æéä¸æ¯ä¸è¡è¿è¡ç¸åæä¸åçæä½ï¼èä¸æ¯ä¸æ¬¡å¯¹æ´ä¸ªç»æéè¿è¡åä¸ç§æä½ï¼å®è¿æä¾å¯¹åºäºæ¸¸æ ä½ç½®è对表ä¸æ°æ®è¿è¡å é¤ææ´æ°çè½åï¼èä¸ï¼æ£æ¯æ¸¸æ æä½ä¸ºé¢åéåçæ°æ®åºç®¡çç³»ç»åé¢åè¡çç¨åºè®¾è®¡ä¸¤è
è系起æ¥ï¼ä½¿ä¸¤ä¸ªæ°æ®å¤çæ¹å¼è½å¤è¿è¡æ²éã1.2 游æ ç§ç±»
MS SQL SERVER æ¯æä¸ç§ç±»åç游æ ï¼Transact_SQL 游æ ï¼API æå¡å¨æ¸¸æ å客æ·æ¸¸æ ã
ï¼1ï¼ Transact_SQL 游æ
Transact_SQL 游æ æ¯ç±DECLARE CURSOR è¯æ³å®ä¹ã主è¦ç¨å¨Transact_SQL èæ¬ãåå¨è¿ç¨å触åå¨ä¸ãTransact_SQL 游æ 主è¦ç¨å¨æå¡å¨ä¸ï¼ç±ä»å®¢æ·ç«¯åéç»æå¡å¨çTransact_SQL è¯å¥ææ¯æ¹å¤çãåå¨è¿ç¨ã触åå¨ä¸çTransact_SQL è¿è¡ç®¡çã Transact_SQL 游æ ä¸æ¯ææåæ°æ®åæå¤è¡æ°æ®ã
ï¼2ï¼ API 游æ
API 游æ æ¯æå¨OLE DBï¼ ODBC 以åDB_library ä¸ä½¿ç¨æ¸¸æ å½æ°ï¼ä¸»è¦ç¨å¨æå¡å¨ä¸ãæ¯ä¸æ¬¡å®¢æ·ç«¯åºç¨ç¨åºè°ç¨API 游æ å½æ°ï¼MS SQL SEVER çOLE DB æä¾è
ãODBC驱å¨å¨æDB_library çå¨æé¾æ¥åºï¼DLLï¼ é½ä¼å°è¿äºå®¢æ·è¯·æ±ä¼ éç»æå¡å¨ä»¥å¯¹API游æ è¿è¡å¤çã
ï¼3ï¼ å®¢æ·æ¸¸æ
客æ·æ¸¸æ 主è¦æ¯å½å¨å®¢æ·æºä¸ç¼åç»æéæ¶æ使ç¨ãå¨å®¢æ·æ¸¸æ ä¸ï¼æä¸ä¸ªç¼ºççç»æé被ç¨æ¥å¨å®¢æ·æºä¸ç¼åæ´ä¸ªç»æéã客æ·æ¸¸æ ä»
æ¯æéæ游æ èéå¨æ游æ ãç±äºæå¡å¨æ¸¸æ 并ä¸æ¯æææçTransact-SQL è¯å¥ææ¹å¤çï¼æ以客æ·æ¸¸æ 常常ä»
被ç¨ä½æå¡å¨æ¸¸æ çè¾
å©ãå 为å¨ä¸è¬æ
åµä¸ï¼æå¡å¨æ¸¸æ è½æ¯æç»å¤§å¤æ°ç游æ æä½ã
ç±äºAPI 游æ åTransact-SQL 游æ 使ç¨å¨æå¡å¨ç«¯ï¼æ以被称为æå¡å¨æ¸¸æ ï¼ä¹è¢«ç§°ä¸ºåå°æ¸¸æ ï¼è客æ·ç«¯æ¸¸æ 被称为åå°æ¸¸æ ãå¨æ¬ç« ä¸æ们主è¦è®²è¿°æå¡å¨ï¼åå°ï¼æ¸¸æ ã select count(id) from infoselect * from info--æ¸
é¤ææè®°å½
truncate table infodeclare @i int
set @i=1
while @i<1000000
begin
insert into info values('Justin'+str(@i),'æ·±å³'+str(@i))
set @i=@i+1
end1.3 游æ æä½
使ç¨æ¸¸æ æåç§åºæ¬çæ¥éª¤:声æ游æ ãæå¼æ¸¸æ ãæåæ°æ®ãå
³é游æ ã
声æ游æ
象使ç¨å
¶å®ç±»åçåéä¸æ ·,使ç¨ä¸ä¸ªæ¸¸æ ä¹å,é¦å
åºå½å£°æå®ã游æ ç声æå
æ¬ä¸¤ä¸ªé¨å:游æ çå称;è¿ä¸ªæ¸¸æ æç¨å°çSQLè¯å¥ãå¦è¦å£°æä¸ä¸ªå«ä½Cus-tomerCursorç游æ ç¨ä»¥æ¥è¯¢å°åå¨å京ç客æ·çå§åãå¸å·åå
¶ä½é¢,æ¨å¯ä»¥ç¼åå¦ä¸ä»£ç :
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE province="å京";
å¨æ¸¸æ ç声æä¸æä¸ç¹å¼å¾æ³¨æçæ¯,å¦åå
¶å®åéç声æä¸æ ·,声æ游æ çè¿ä¸æ®µä»£ç è¡æ¯ä¸æ§è¡ç,æ¨ä¸è½å°debugæ¶çæç¹è®¾å¨è¿ä¸ä»£ç è¡ä¸,ä¹ä¸è½ç¨IF...END IFè¯å¥æ¥å£°æ两个ååç游æ ,å¦ä¸åç代ç å°±æ¯é误çã
IF Is_prov="å京"THEN
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE province="å京";
ELSE
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE provinceãã"å京";
END IF
æå¼æ¸¸æ
声æäºæ¸¸æ åå¨ä½å
¶å®æä½ä¹å,å¿
é¡»æå¼å®ãæå¼æ¸¸æ æ¯æ§è¡ä¸å
¶ç¸å
³çä¸æ®µSQLè¯å¥,ä¾å¦æå¼ä¸ä¾å£°æçä¸ä¸ªæ¸¸æ ,æ们åªéé®å
¥:
OPEN CustomerCursor;
ç±äºæå¼æ¸¸æ æ¯å¯¹æ°æ®åºè¿è¡ä¸äºSQL SELECTçæä½,å®å°èè´¹ä¸æ®µæ¶é´,主è¦åå³äºæ¨ä½¿ç¨çç³»ç»æ§è½åè¿æ¡è¯å¥çå¤æç¨åº¦ãå¦ææ§è¡çæ¶é´è¾é¿,å¯ä»¥èèå°å±å¹ä¸æ¾ç¤ºçé¼ æ æ¹ä¸ºhourglassã
æåæ°æ®
å½ç¨OPENè¯å¥æå¼äºæ¸¸æ 并å¨æ°æ®åºä¸æ§è¡äºæ¥è¯¢å,æ¨ä¸è½ç«å³å©ç¨å¨æ¥è¯¢ç»æéä¸çæ°æ®ãæ¨å¿
é¡»ç¨FETCHè¯å¥æ¥åå¾æ°æ®ãä¸æ¡FETCHè¯å¥ä¸æ¬¡å¯ä»¥å°ä¸æ¡è®°å½æ¾å
¥ç¨åºåæå®çåéä¸ãäºå®ä¸,FETCHè¯å¥æ¯æ¸¸æ 使ç¨çæ ¸å¿ãå¨DataWindowåDataStoreä¸,æ§è¡äºRetrieve()å½æ°ä»¥å,æ¥è¯¢çææç»æå
¨é¨å¯ä»¥å¾å°;è使ç¨æ¸¸æ ,æ们åªè½éæ¡è®°å½å°å¾å°æ¥è¯¢ç»æã
å·²ç»å£°æ并æå¼ä¸ä¸ªæ¸¸æ å,æ们就å¯ä»¥å°æ°æ®æ¾å
¥ä»»æçåéä¸ãå¨FETCHè¯å¥ä¸æ¨å¯ä»¥æå®æ¸¸æ çå称åç®æ åéçå称ãå¦ä¸ä¾:
FETCH CustmerCur-sor
INTO:ls_acct_no,
:ls_name,
:ll_balance;
ä»è¯æ³ä¸è®²,ä¸é¢æè¿°çå°±æ¯ä¸æ¡åæ³çåæ°æ®çè¯å¥,ä½æ¯ä¸è¬æ们使ç¨æ¸¸æ å´è¿åºå½å
æ¬å
¶å®çé¨åãæ£å¦æ们åé¢æè°å°ç,游æ åªè½ä¸æ¬¡ä»åå°æ°æ®åºä¸åä¸æ¡è®°å½,èå¨å¤æ°æ
åµä¸,æ们ææ³è¦ä½çæ¯å¨æ°æ®åºä¸ä»ç¬¬ä¸æ¡è®°å½å¼å§æå,ä¸ç´å°ç»æãæ以æ们ä¸è¬è¦å°æ¸¸æ æåæ°æ®çè¯å¥æ¾å¨ä¸ä¸ªå¾ªç¯ä½å
,ç´è³å°ç»æéä¸çå
¨é¨æ°æ®æåå,è·³åºå¾ªç¯åãéè¿æ£æµSQLCA.SQL-CODEçå¼,å¯ä»¥å¾ç¥æåä¸æ¡FETCHè¯å¥æ¯å¦æåãä¸è¬,å½SQLCODEå¼ä¸º0æ¶è¡¨æä¸åæ£å¸¸,100表示已ç»åå°äºç»æéçæ«å°¾,èå
¶å®å¼å表ææä½åºäºé®é¢,è¿æ ·æ们å¯ä»¥ç¼å以ä¸ç代ç :
lb_continue=True
ll_total=0
DO WHILE lb_continue
FETCH CustomerCur-sor
INTO:ls_acct_no,
:ls_name,
:ll_balance;
If sqlca.sqlcode=0 Then
ll_total+=ll_balance
Else
lb_continue=False
End If
LOOP
循ç¯ä½çç»ææå¤ç§,è¿éæå°çæ¯æ常è§çä¸ç§ãä¹æçç¨åºååç±å°ä¸æ¡FETCHè¯å¥æ¾å¨å¾ªç¯ä½çåé¢,循ç¯ä½å
åæ¾ç½®å¦å¤ä¸æ¡FETCHè¯å¥,并æ£æµSQLCA.SQLCODEæ¯å¦ä¸º100ãä½æ¯è¿æ ·å,ç»´æ¤æ¶éåæ¶ä¿®æ¹ä¸¤æ¡FETCHè¯å¥,ç¨éº»ç¦äºäºã
å
³é游æ
å¨æ¸¸æ æä½çæå请ä¸è¦å¿è®°å
³é游æ ,è¿æ¯ä¸ä¸ªå¥½çç¼ç¨ä¹ æ¯,以使系ç»éæ¾æ¸¸æ å ç¨çèµæºãå
³é游æ çè¯å¥å¾ç®å:
CLOSE CustomerCursor;
使ç¨Whereåå¥å
æ们å¯ä»¥å¨æå°å®ä¹æ¸¸æ ä¸çWhereåå¥çåæ°,ä¾å¦å¨æ¬ä¾ä¸æ们æ¯ç´æ¥å®ä¹äºæ¥è¯¢ç份æ¯å京çè®°å½,ä½ä¹è®¸å¨åºç¨ä¸æ们è¦ä½¿ç¨ä¸ä¸ªä¸æå¼å表æ¡,ç±ç¨æ·æ¥éæ©è¦æ¥è¯¢çç份,æ们该ææ ·åå¢?
æ们å¨åé¢æ¾ç»æå°è¿,DECLAREè¯å¥çä½ç¨åªæ¯å®ä¹ä¸ä¸ªæ¸¸æ ,å¨OPENè¯å¥ä¸è¿ä¸ªæ¸¸æ æä¼çæ£å°è¢«æ§è¡ãäºè§£äºè¿äº,æ们就å¯ä»¥å¾æ¹ä¾¿å°å®ç°è¿æ ·çåè½,å¨DECLAREçWhereåå¥ä¸å å
¥åéä½åæ°,å¦ä¸æ示:
DECLARE CustomerCursor CURSOR FOR
SELCECT acct_no,name,balance
FROM customer
WHERE province=:ls_province;
â¥å®ä¹ls_provinceçå¼
OPEN CustomerCursor;
游æ çç±»å
åå
¶å®åéä¸æ ·,æ们ä¹å¯ä»¥å®ä¹æ¸¸æ ç访é®ç±»å:å
¨å±ãå
±äº«ãå®ä¾æå±é¨,游æ åéçå½åè§è建议ä¹åå
¶å®åéä¸æ ·ã
--声æ游æ
declare my_cursor cursor keyset for select * from info
--å é¤æ¸¸æ èµæº
deallocate my_cursor--æå¼æ¸¸æ ,å¨æ¸¸æ å
³éæå é¤åé½ææ
open my_cursor
--å
³é游æ
close my_cursor--声æå±é¨åé
declare @id int,@name varchar(20),@address varchar(20)
--å®ä½å°æå®ä½ç½®çè®°å½
fetch absolute 56488 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--å®ä½å°å½åè®°å½ç¸å¯¹ä½ç½®è®°å½
fetch relative -88 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--å®ä½å°å½åè®°å½åä¸æ¡
fetch prior from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--å®ä½å°å½åè®°å½åä¸æ¡
fetch next from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--å®ä½å°é¦è®°å½
fetch first from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--å®ä½å°å°¾è®°å½
fetch last from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as addresså®ä¾ï¼
use database1
declare my_cursor cursor scroll dynamic
/**//*scroll表示å¯éæ移å¨æ¸¸æ æ éï¼å¦ååªè½ååï¼ï¼dynamic表示å¯ä»¥è¯»å游æ ï¼å¦å游æ åªè¯»ï¼*/
for
select productname from productopen my_cursor
declare @pname sysname
fetch next from my_cursor into @pname
while(@@fetch_status=0)
begin
print 'Product Name: ' + @pname
fetch next from my_cursor into @pname
end
fetch first from my_cursor into @pname
print @pname
/**//*update product set productname='zzg' where current of my_cursor */
/**//*delete from product where current of my_cursor */
close my_cursor
deallocate my_cursor
1.4 游æ çé«çº§æ巧尽管ç®ååºäºSQLè¯å¥çåå°æ°æ®åºææ¯æçè¯è¨é½å¤§è´ç¸å½,ä½å¯¹æ¸¸æ çæ¯æå´æçä¸äºå·®å¼,ä¾å¦å¯¹æ»å¨æ¸¸æ æ¯æãæè°æ»å¨æ¸¸æ ,å°±æ¯ç¨åºåå¯ä»¥æå®æ¸¸æ åååä»»æä¸ä¸ªæ¹åæ»å¨ãå¦å¨Informixä¸,æ¨çè³è¿å¯ä»¥å°æ¸¸æ æ»åç»æéå¼å¤´ææ«å°¾,使ç¨çè¯å¥åå«æ¯FETCH FIRST,FETCH LASTãFETCH PRIORåFETCH NEXTãå½ç¨åºåç¨FETCHè¯å¥,å
¶ç¼ºçæ¯æFETCH NEXTãç±äºæ»å¨æ¯å¨æ°æ®åºåå°å®ç°ç,æ以æ»å¨æ¸¸æ 为ç¨æ·ç¼ç¨æä¾äºæ大çæ¹ä¾¿ã
对游æ æ¯æçå¦ä¸ä¸ªä¸åæ¯å¯ä¿®æ¹æ¸¸æ ãä¸è¿°æ¸¸æ ç使ç¨é½æ¯æåªè¯»æ¸¸æ ,è象OracleãSybaseçæ°æ®åºå´å¦å¤æ¯æå¯ä½ä¿®æ¹ç游æ ã使ç¨è¿æ ·çæ°æ®åº,æ¨å¯ä»¥ä¿®æ¹æå é¤å½å游æ æå¨çè¡ãä¾å¦ä¿®æ¹å½å游æ æå¨è¡çç¨æ·çä½é¢,æ们å¯ä»¥å¦ä¸æä½:
UPDATE customer
SET balance=1000
WHERE CURRENT of customerCursor;
å é¤å½åè¡çæä½å¦ä¸:
DELETE FROM Customer
WHERE CURRENT OF CustomerCursor;
ä½æ¯å¦ææ¨å½å使ç¨çæ°æ®åºæ¯Sybase,æ¨éè¦ä¿®æ¹æ°æ®åºçåæ°,å°æ¸¸æ å¯ä¿®æ¹çå¼å®ä¸º1,æè½æ§è¡ä¸è¿°æä½ãè¿ä¸èµå¼å¨è¿æ¥æ°æ®åºçååè¿è¡åå¯ã
SQLCA.DBParm="Cursor Update=1"
å¦å¤ä¸ä¸ªå
容æ¯å¨æ游æ ,ä¹å°±æ¯è¯´æ¨å¯ä»¥è¿è¡è¿ç¨ä¸å¨æå°å½¢æ游æ çSELECTè¯å¥ãè¿åå¨PowerBuilderä¸å¨æå°ä½¿ç¨åµå
¥å¼SQLä¸æ ·,éè¦ç¨å°DynamicStagin-gAreaçæ°æ®ç±»å,è¿å·²è¶
åºäºæ¬èçèå´ãèµæå¼ç¨:
http://www.knowsky.com/344071.html