遊标的的使用有日常的開發和維護的過程不使用的并不多，但是碰到一些棘手的問題的時候，遊标時常是個非常好的幫手，下面就說下遊标的使用方法，方法自己以後查閱，和加深一些印象，下面以一個存儲過程為例
T-SQL中的遊标定義在MSDN中如下:
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;] ALTER PROCEDURE [dbo]. [XXXX]
@ProcessName NVARCHAR (20) ---流程名
AS
DECLARE @tempIncident INT
DECLARE @tempCode NVARCHAR( 20)
DECLARE @incident NVARCHAR( 100) ---執行個體号
DECLARE @bicode NVARCHAR( 200)--- 單号
DECLARE @taskCnt INT ---task為的數量
DECLARE @flag INT
DECLARE @bflag INT
SET @flag = 0
SET @bflag = 0
----定義一個遊标--
----定義遊标和定義一個變量是差不多的，不過後面要指定一個的查詢語句，
DECLARE mycur CURSOR
FOR
( SELECT bic. BI_Code ,----這時兩個的資料是要使用的資料
bic.BI_Incident
FROM xxxxx AS inc ,
bic AS bic
WHERE inc .PROCESSNAME = bic .BI_ProcessName
AND INCIDENT = bic.BI_Incident
AND PROCESSNAME = @ProcessName
AND inc .STATUS = 2
AND ( bic. BI_AuditState = 4010
OR bic .BI_AuditState = 4040
)
)
OPEN mycur--打開這個遊标
FETCH NEXT FROM mycur INTO @tempCode, @tempIncident ---把值放到變量中
--循環這個值
--這是一個系統的取遊标值的一個變量
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @taskCnt = COUNT(*)
FROM xxxxx
WHERE INCIDENT = @tempIncident
AND PROCESSNAME = @ProcessName
AND STATUS = 1
IF @taskCnt > 0 ---有task=1 的資料則是可以自動處理的
BEGIN
IF @flag = 0
BEGIN
SET @incident = CAST(@tempIncident AS NVARCHAR)
END
ELSE
SET @incident = @incident + \',\'
+ CAST (@tempIncident AS NVARCHAR )
SET @flag = 1
END
ELSE
BEGIN
IF @bflag = 0
SET @bicode = @tempCode
ELSE
SET @bicode = @bicode + \',\' + @tempCode
SET @bflag = 1
END
---這裡的是把資料再次放到變量裡面，以便循環的使用
FETCH NEXT FROM mycur INTO @tempCode, @tempIncident
END
CLOSE mycur
DEALLOCATE mycur
IF @bicode IS NULL
SET @bicode = \'NULL\'
IF @incident IS NULL
SET @incident = \'NULL\'
ELSE
UPDATE xxxxx
SET STATUS = 1
WHERE PROCESSNAME = @ProcessName
AND INCIDENT IN ( SELECT value
FROM dbo .fn_Split( @incident, \',\') )
SQL 函數的使用：
函數在SQL中的使用提高了SQL代碼的重複利用，也友善我們更高效的去用SQL做更多的事：下面我就對标量的函數進行說明，其它的東西也基本上是大同小異.
1、标量函數
Create function 函數名（參數）
Returns 傳回值資料類型
[with {Encryption | Schemabinding }]
[as]
begin
SQL語句(必須有return 變量或值)
例如：
ALTER FUNCTION [dbo]. [fn_Split](@sText varchar( 8000), @sDelim varchar(20 ) = \' \')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000 ) COLLATE SQL_Latin1_General_CP1_CI_AS ) 這樣就是把一個函數的基本形式定義好了 如同在代碼中寫出 public string get(){}
後面的代碼也就是日常的sql查詢或者是一些的特殊的處理
ALTER FUNCTION [dbo]. [fn_Split](@sText varchar( 8000), @sDelim varchar(20 ) = \' \')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000 ) COLLATE SQL_Latin1_General_CP1_CI_AS )
AS
BEGIN
DECLARE @idx smallint ,
@value varchar(8000 ),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint
IF @sDelim = \'Space\'
BEGIN
SET @sDelim = \' \'
END
SET @idx = 0
SET @sText = LTrim(RTrim (@sText))
SET @iDelimlength = DATALENGTH(@sDelim )
SET @bcontinue = 1
IF NOT ((@iDelimlength = 0 ) or (@sDelim = \'Empty\'))
BEGIN
WHILE @bcontinue = 1
BEGIN
--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
IF CHARINDEX (@sDelim, @sText)> 0
BEGIN
SET @value = SUBSTRING(@sText ,1, CHARINDEX( @sDelim,@sText )-1)
BEGIN
INSERT @retArray (idx , value )
VALUES (@idx , @value )
END
--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value ) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText ,DATALENGTH( @sText) - @iStrike ))
END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx , value )
VALUES (@idx , @value )
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText )>1
BEGIN
SET @value = SUBSTRING(@sText ,1, 1)
BEGIN
INSERT @retArray (idx , value )
VALUES (@idx , @value )
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText ,2, DATALENGTH(@sText )-1)
END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx , value )
VALUES (@idx , @sText )
SET @bcontinue = 0
END
END
END
RETURN
END