Sql存儲(chǔ)過程游標(biāo)循環(huán)的用法及sql如何使用cursor寫一個(gè)簡單的循環(huán)

用游標(biāo)，和WHILE可以遍歷您的查詢中的每一條記錄并將要求的字段傳給變量進(jìn)行相應(yīng)的處理

==================

DECLARE 
@A1 VARCHAR(10),
@A2 VARCHAR(10),
@A3 INT
DECLARE CURSOR YOUCURNAME FOR SELECT A1,A2,A3 FROM YOUTABLENAME
OPEN YOUCURNAME
fetch next from youcurname into @a1,@a2,@a3
while @@fetch_status>-1
begin
update … set …-a3 where …

……您要執(zhí)行的操作寫在這里

fetch next from youcurname into @a1,@a2,@a3
end
close youcurname
deallocate youcurname

—————————————

在應(yīng)用程序開發(fā)的時(shí)候，我們經(jīng)?？赡軙?huì)遇到下面的應(yīng)用，我們會(huì)通過查詢數(shù)據(jù)表的記錄集，循環(huán)每一條記錄，通過每一條的記錄集對(duì)另一張表進(jìn)行數(shù)據(jù)進(jìn)行操作，如插入與更新，我們現(xiàn)在假設(shè)有一個(gè)這樣的業(yè)務(wù)：老師為所在班級(jí)的學(xué)生選課，選的課程如有哲學(xué)、馬克思主義政治經(jīng)濟(jì)學(xué)、毛澤東思想概論、鄧小平理論這些課，現(xiàn)在操作主要如下：

1) 先要查詢這些還沒有畢業(yè)的這些學(xué)生的名單，畢業(yè)過后的無法進(jìn)行選課;
2) 在批量的選取學(xué)生的同時(shí)，還需要添加對(duì)應(yīng)的某一門課程;
3) 點(diǎn)添加后選課結(jié)束。

數(shù)據(jù)量少可能看不出用Java程序直接多次進(jìn)行數(shù)據(jù)庫操作這種辦法實(shí)現(xiàn)的弱點(diǎn)，因?yàn)樗看卧诓僮鲾?shù)據(jù)庫的時(shí)候，都存在著頻繁的和數(shù)據(jù)庫的I/O直接交互，這點(diǎn)性能的犧牲實(shí)屬不應(yīng)該，那我們就看下面的方法，通過存儲(chǔ)過程的游標(biāo)方法來實(shí)現(xiàn)：建立存儲(chǔ)過程：

Create PROCEDURE P_InsertSubject
@SubjectId int
AS
DECLARE rs CURSOR LOCAL SCROLL FOR
select studentid from student where StudentGradu = 1
OPEN rs
FETCH NEXT FROM rs INTO @tempStudentID
WHILE @@FETCH_STATUS = 0
BEGIN
Insert SelSubject values (@SubjectId,@tempStudentID)
FETCH NEXT FROM rs INTO @tempStudentID
END
CLOSE rs
GO

使用游標(biāo)對(duì)記錄集循環(huán)進(jìn)行處理的時(shí)候一般操作如以下幾個(gè)步驟：

1、把記錄集傳給游標(biāo);
2、打開游標(biāo)
3、開始循環(huán)
4、從游標(biāo)中取值
5、檢查那一行被返回
6、處理
7、關(guān)閉循環(huán)
8、關(guān)閉游標(biāo)

上面這種方法在性能上面無疑已經(jīng)是提高很多了，但我們也想到，在存儲(chǔ)過程編寫的時(shí)候，有時(shí)候我們盡量少的避免使用游標(biāo)來進(jìn)行操作，所以我們還可以對(duì)上面的存儲(chǔ)過程進(jìn)行改造，使用下面的方法來實(shí)現(xiàn)：

Create PROCEDURE P_InsertSubject
@SubjectId int
AS
declare @i int,
@studentid
DECLARE @tCanStudent TABLE
(
studentid int
,FlagID TINYINT
)
BEGIN
insert @tCanStudent select studentid,0 from student where StudentGradu = 1
SET @i=1
WHILE( @i>=1)
BEGIN
SELECT @studentid=''
SELECT TOP 1 @studentid = studentid FROM @tCanStudent WHERE flagID=0
SET @i=@@ROWCOUNT
IF @i=0 GOTO Return_Lab
Insert SelSubject values (@SubjectId,@studentid)
IF @@error=0
UPDATE @tCanStudent SET flagID=1 WHERE studentid = @studentid
Return_Lab:
END
End
GO

我們現(xiàn)在再來分析以上這個(gè)存儲(chǔ)過程，它實(shí)現(xiàn)的方法是先把滿足條件的記錄集數(shù)據(jù)存放到一個(gè)表變量中，并且在這個(gè)表變量中增加一個(gè)FLAGID進(jìn)行數(shù)據(jù)初始值為0的存放，然后去循環(huán)這個(gè)記錄集，每循環(huán)一次，就把對(duì)應(yīng)的FLAGID的值改成1，然后再根據(jù)循環(huán)來查找滿足條件等于0的情況，可以看到，每循環(huán)一次，處理的記錄集就會(huì)少一次，然后循環(huán)的往選好課程表里面插入，直到記錄集的條數(shù)為0時(shí)停止循環(huán)，此時(shí)完成操作。

比較以上的幾種循環(huán)方法的應(yīng)用，就會(huì)知道，有時(shí)候可能對(duì)于同一種功能我們實(shí)現(xiàn)的方法不同，而最終應(yīng)用程序性能的影響的差異就會(huì)很大，第二種、第三種就大大的減少的數(shù)據(jù)庫交互I/O操作的頻繁，會(huì)節(jié)省很多時(shí)間，方法三又避免用游標(biāo)又可以節(jié)省不必要的開銷。

使用SQL的Agent可以執(zhí)行計(jì)劃任務(wù)，把寫好的SQL語句放在計(jì)劃任務(wù)里，可以達(dá)到奇妙的效果，如定時(shí)備份數(shù)據(jù)，定時(shí)執(zhí)行特定操作等等，當(dāng)涉及循環(huán)操作很多條數(shù)據(jù)時(shí)，這里就要使用游標(biāo)了，當(dāng)然SQL中也有循環(huán)語句，如使用While。不過while的功能只能實(shí)現(xiàn)一般的操作，游標(biāo)的功能更為強(qiáng)大些，可在一個(gè)指定的一個(gè)集合內(nèi)循環(huán)操作數(shù)據(jù)，實(shí)現(xiàn)動(dòng)態(tài)操作，那就更牛了，呵呵，以下資料供存檔用。

WHILE

設(shè)置重復(fù)執(zhí)行 SQL 語句或語句塊的條件。只要指定的條件為真，就重復(fù)執(zhí)行語句。可以使用 BREAK 和 CONTINUE 關(guān)鍵字在循環(huán)內(nèi)部控制 WHILE 循環(huán)中語句的執(zhí)行。

語法

WHILE Boolean_expression
  { sql_statement | statement_block }
  [ BREAK ]
  { sql_statement | statement_block }
  [ CONTINUE ]

參數(shù)

Boolean_expression

返回 TRUE 或 FALSE 的表達(dá)式。如果布爾表達(dá)式中含有 SELECT 語句，必須用圓括號(hào)將 SELECT 語句括起來。

Transact-SQL 語句或用語句塊定義的語句分組。若要定義語句塊，請(qǐng)使用控制流關(guān)鍵字 BEGIN 和 END。

BREAK

導(dǎo)致從最內(nèi)層的 WHILE 循環(huán)中退出。將執(zhí)行出現(xiàn)在 END 關(guān)鍵字后面的任何語句，END 關(guān)鍵字為循環(huán)結(jié)束標(biāo)記。

CONTINUE

使 WHILE 循環(huán)重新開始執(zhí)行，忽略 CONTINUE 關(guān)鍵字后的任何語句。

注釋

如果嵌套了兩個(gè)或多個(gè) WHILE 循環(huán)，內(nèi)層的 BREAK 將導(dǎo)致退出到下一個(gè)外層循環(huán)。首先運(yùn)行內(nèi)層循環(huán)結(jié)束之后的所有語句，然后下一個(gè)外層循環(huán)重新開始執(zhí)行。

示例

declare @i int
set @i=1
while @i30
begin
insert into test (userid) values(@i)
set @i=@i+1
end

------------------------------------------------------------

while 條件
begin
執(zhí)行操作
set @i=@i+1
end

A. 在嵌套的 IF...ELSE 和 WHILE 中使用 BREAK 和 CONTINUE

在下例中，如果平均價(jià)格少于 $30，WHILE 循環(huán)就將價(jià)格加倍，然后選擇最高價(jià)。如果最高價(jià)少于或等于 $50，WHILE 循環(huán)重新啟動(dòng)并再次將價(jià)格加倍。該循環(huán)不斷地將價(jià)格加倍直到最高價(jià)格超過 $50，然后退出 WHILE 循環(huán)并打印一條消息。

USE pubs
GO
WHILE (SELECT AVG(price) FROM titles)  $30
BEGIN
  UPDATE titles
  SET price = price * 2
  SELECT MAX(price) FROM titles
  IF (SELECT MAX(price) FROM titles) > $50
  BREAK
  ELSE
  CONTINUE
END
PRINT 'Too much for the market to bear'

B. 在帶有游標(biāo)的過程中使用 WHILE

以下的 WHILE 結(jié)構(gòu)是名為 count_all_rows 過程中的一部分。下例中，該 WHILE 結(jié)構(gòu)測試用于游標(biāo)的函數(shù) @@FETCH_STATUS 的返回值。因?yàn)?@@FETCH_STATUS 可能返回 –2、-1 或 0，所以，所有的情況都應(yīng)進(jìn)行測試。如果某一行在開始執(zhí)行此存儲(chǔ)過程以后從游標(biāo)結(jié)果中刪除，將跳過該行。成功提取 (0) 后將執(zhí)行 BEGIN...END 循環(huán)內(nèi)部的 SELECT 語句。

單變量循環(huán)

USE pubs
DECLARE tnames_cursor CURSOR
FOR
  SELECT TABLE_NAME 
  FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS > -1)
BEGIN
  IF (@@FETCH_STATUS > -2)
  BEGIN 
  SELECT @tablename = RTRIM(@tablename) 
  EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM ' 
    + @tablename )
  PRINT ' '
 END
  FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

多變量循環(huán)

CREATE  PROCEDURE my_FeeCount AS
declare @到期時(shí)間  char(10)
declare @片區(qū)   char(20)
declare @繳費(fèi)用戶數(shù) char(10)
declare @sql char(2000)
declare cur_data cursor for 
 select convert(varchar(10),到期時(shí)間,120) as 到期時(shí)間 ,片區(qū),count(distinct main_id) as 繳費(fèi)用戶數(shù) 
 from V_aipu_fee where 提交時(shí)間>=convert(varchar(10),getdate()-90,120) and 提交時(shí)間convert(varchar(10),getdate()+1-90,120)
 and 收費(fèi)類型='續(xù)費(fèi)收費(fèi)'
 Group by convert(varchar(10),到期時(shí)間,120),片區(qū)
 order by convert(varchar(10),到期時(shí)間,120) 
open cur_data
fetch next from cur_data into @到期時(shí)間,@片區(qū),@繳費(fèi)用戶數(shù) 
while(@@fetch_status = 0) 
begin
  set @sql='update '+RTRIM(@片區(qū))+'實(shí)收='+RTRIM(@片區(qū))+'實(shí)收+'+RTRIM(@繳費(fèi)用戶數(shù))+' where 收費(fèi)日期='''+RTRIM(@到期時(shí)間)+''''
 print @sql
 fetch next from cur_data into @到期時(shí)間,@片區(qū),@繳費(fèi)用戶數(shù) 
end
close cur_data

下面接著給大家介紹sql使用cursor寫一個(gè)簡單的循環(huán)

1.排錯(cuò)

　　和sql server較了一天的勁，只寫了兩個(gè)簡單的存儲(chǔ)過程。當(dāng)然智商雖然不高還沒低到這個(gè)份上。大半天都是花費(fèi)在排錯(cuò)上了。

　　System.Data.SqlClient.SqlException: SQL Server 不存在或訪問被拒絕。 at

System.Data.SqlClient.ConnectionPool.GetConnection(Boolean isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at _20060409.WebForm1.test() in d:\work files\20060409\webform1.aspx.cs:line 52

　　冷不丁報(bào)了這個(gè)錯(cuò)，這個(gè)就是從程序里連不上。在服務(wù)中除了MSSQLserverADHelper以外所有的服務(wù)都打開了還是不行；名稱正確、無別名、沒起實(shí)例名稱；端口號(hào)服務(wù)器端和客戶端都是1433。為了找出問題只好上網(wǎng)查查結(jié)果發(fā)現(xiàn)連上網(wǎng)線后這個(gè)毛病就沒了。暈，看來是少打了什么補(bǔ)丁。

2.寫循環(huán)

　　首先需求是這樣的我手里現(xiàn)在有兩張表，rights和roles。

　　表結(jié)構(gòu)如下：

rights
　　-------------------
　　rightid int
　　right varchar(20)
　　
　?。剑剑剑剑剑剑剑剑剑?
　　
　　roles
　　-------------------
　　roleid int
　　roletype int
　　role varchar(20)
　　rightid int

　　　　在我的role表里有一個(gè)角色admin。admin擁有right表中的所有權(quán)限，但是有四個(gè)字段，要是用手寫完我這個(gè)速度，加上這個(gè)數(shù)量我看也就算了。省了手上的力氣就要費(fèi)腦子。

　　在查詢分析器里寫了半天，總算是吭哧出來了。

　　首先使用truncate table把role原來的失敗信息清掉，聽說會(huì)比delete快一點(diǎn)，只不過數(shù)量少看不出明顯效果。

　　然后建立一個(gè)臨時(shí)的用于填充的存儲(chǔ)過程。

create procedure TempFill
　　
　　declare mycursor cursor for
　　select rightid from rights--這兩個(gè)是一句，定義游標(biāo)，然后從rights中取一個(gè)rightid給游標(biāo)
　　
　　open mycursor
　　
　　declare @rightid int--定義一個(gè)變量
　　fetch mycursor into @rightid--把mycursor當(dāng)前的值給@rightid
　　
　　while @@fetch_status=
　　begin
　　insert into rolestable (roletype,role,rightid)values(,'admin',@rightid)
　　fetch mycursor into @rightid
　　end
　　
　　close mycursor
　　deallocate mycursor

　　這樣就寫完了。

　　execute TempFill 再執(zhí)行他一下。