程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> Sql存儲進程游標輪回的用法及sql若何應用cursor寫一個簡略的輪回

Sql存儲進程游標輪回的用法及sql若何應用cursor寫一個簡略的輪回

編輯：MSSQL

Sql存儲進程游標輪回的用法及sql若何應用cursor寫一個簡略的輪回。本站提示廣大學習愛好者：（Sql存儲進程游標輪回的用法及sql若何應用cursor寫一個簡略的輪回）文章只能為提供參考，不一定能成為您想要的結果。以下是Sql存儲進程游標輪回的用法及sql若何應用cursor寫一個簡略的輪回正文

用游標，和WHILE可以遍歷您的查詢中的每筆記錄並將請求的字段傳給變量停止響應的處置

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

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 …

……您要履行的操作寫在這裡

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

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

在運用法式開辟的時刻，我們常常能夠會碰到上面的運用，我們會經由過程查詢數據表的記載集，輪回每筆記錄，經由過程每條的記載集對另外一張表停止數據停止操作，如拔出與更新，我們如今假定有一個如許的營業：先生為地點班級的先生選課，選的課程若有哲學、馬克思主義政治經濟學、毛澤東思惟概論、鄧小平實際這些課，如今操作重要以下：

1) 先要查詢這些還沒有卒業的這些先生的名單，卒業事後的沒法停止選課;
2) 在批量的拔取先生的同時，還須要添加對應的某一門課程;
3) 點添加後選課停止。

數據量少能夠看不出用Java法式直接屢次停止數據庫操作這類方法完成的弱點，由於它每次在操作數據庫的時刻，都存在著頻仍的和數據庫的I/O直接交互，這點機能的就義實屬不該該，那我們就看上面的辦法，經由過程存儲進程的游標辦法來完成：樹立存儲進程：

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

應用游標對記載集輪回停止處置的時刻普通操作如以下幾個步調：

1、把記載集傳給游標;
2、翻開游標
3、開端輪回
4、從游標中取值
5、檢討那一行被前往
6、處置
7、封閉輪回
8、封閉游標

下面這類辦法在機能下面無疑曾經是進步許多了，但我們也想到，在存儲進程編寫的時刻，有時刻我們盡可能少的防止應用游標來停止操作，所以我們還可以對下面的存儲進程停止改革，應用上面的辦法來完成：

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

我們如今再來剖析以上這個存儲進程，它完成的辦法是先把知足前提的記載集數據寄存到一個表變量中，而且在這個表變量中增長一個FLAGID停止數據初始值為0的寄存，然後去輪回這個記載集，每輪回一次，就把對應的FLAGID的值改成1，然後再依據輪回來查找知足前提等於0的情形，可以看到，每輪回一次，處置的記載集就會少一次，然後輪回的往選好課程內外面拔出，直到記載集的條數為0時停滯輪回，此時完成操作。

比擬以上的幾種輪回辦法的運用，就會曉得，有時刻能夠關於統一種功效我們完成的辦法分歧，而終究運用法式機能的影響的差別就會很年夜，第二種、第三種就年夜年夜的削減的數據庫交互I/O操作的頻仍，會節儉許多時光，辦法三又防止用游標又可以節儉不用要的開支。

應用SQL的Agent可以履行籌劃義務，把寫好的SQL語句放在籌劃義務裡，可以到達奧妙的後果，如准時備份數據，准時履行特定操作等等，當觸及輪回操作許多條數據時，這裡就要應用游標了，固然SQL中也有輪回語句，如應用While。不外while的功效只能完成普通的操作，游標的功效更加壯大些，可在一個指定的一個聚集內輪回操作數據，完成靜態操作，那就更牛了，呵呵，以下材料供存檔用。

WHILE

設置反復履行 SQL 語句或語句塊的前提。只需指定的前提為真，就反復履行語句。可使用 BREAK 和 CONTINUE 症結字在輪回外部掌握 WHILE 輪回中語句的履行。

語法

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

參數

Boolean_expression

前往 TRUE 或 FALSE 的表達式。假如布爾表達式中含有 SELECT 語句，必需用圓括號將 SELECT 語句括起來。

{sql_statement | statement_block}

Transact-SQL 語句或用語句塊界說的語句分組。若要界說語句塊，請應用掌握流症結字 BEGIN 和 END。

BREAK

招致從最內層的 WHILE 輪回中加入。將履行湧現在 END 症結字前面的任何語句，END 症結字為輪回停止標志。

CONTINUE

使 WHILE 輪回從新開端履行，疏忽 CONTINUE 症結字後的任何語句。

正文

假如嵌套了兩個或多個 WHILE 輪回，內層的 BREAK 將招致加入到下一個外層輪回。起首運轉內層輪回停止以後的一切語句，然後下一個外層輪回從新開端履行。

示例

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

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

while 前提
begin
履行操作
set @i=@i+1
end

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

鄙人例中，假如均勻價錢少於 $30，WHILE 輪回就將價錢加倍，然後選擇最低價。假如最低價少於或等於 $50，WHILE 輪回從新啟動並再次將價錢加倍。該輪回赓續地將價錢加倍直到最低價格跨越 $50，然撤退退卻出 WHILE 輪回並打印一條新聞。

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. 在帶有游標的進程中應用 WHILE

以下的 WHILE 構造是名為 count_all_rows 進程中的一部門。下例中，該 WHILE 構造測試用於游標的函數 @@FETCH_STATUS 的前往值。由於 @@FETCH_STATUS 能夠前往 –2、-1 或 0，所以，一切的情形都應停止測試。假如某一行在開端履行此存儲進程今後從游標成果中刪除，將跳過該行。勝利提取 (0) 後將履行 BEGIN...END 輪回外部的 SELECT 語句。

單變量輪回

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

多變量輪回

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

上面接著給年夜家引見sql應用cursor寫一個簡略的輪回

1.排錯

　　和sql server較了一天的勁，只寫了兩個簡略的存儲進程。固然智商固然不高還沒低到這個份上。年夜半天都是消費在排錯上了。

　　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

　　冷不丁報了這個錯，這個就是從法式裡連不上。在辦事中除MSSQLserverADHelper之外一切的辦事都翻開了照樣不可；稱號准確、無別號、沒起實例稱號；端標語辦事器端和客戶端都是1433。為了找出成績只好上彀查查成果發明連上彀線後這個缺點就沒了。暈，看來是少打了甚麼補釘。

2.寫輪回

　　起首需求是如許的我手裡如今有兩張表，rights和roles。

　　表構造以下：

rights
　　-------------------
　　rightid int
　　right varchar(20)
　　
　　＝＝＝＝＝＝＝＝＝＝
　　
　　roles
　　-------------------
　　roleid int
　　roletype int
　　role varchar(20)
　　rightid int

　　　　在我的role內外有一個腳色admin。admin具有right表中的一切權限，然則有四個字段，如果用手寫完我這個速度，加上這個數目我看也就算了。省了手上的力量就要費頭腦。

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

　　起首應用truncate table把role本來的掉敗信息清失落，據說會比delete快一點，只不外數目少看不出顯著後果。

　　然後樹立一個暫時的用於填充的存儲進程。

create procedure TempFill
　　
　　declare mycursor cursor for
　　select rightid from rights--這兩個是一句，界說游標，然後從rights中取一個rightid給游標
　　
　　open mycursor
　　
　　declare @rightid int--界說一個變量
　　fetch mycursor into @rightid--把mycursor以後的值給@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 再履行他一下。

MSSQL