第14篇 SQL游标、函数的使用方法

来源：互联网发布：c语言课程考核要求编辑：程序博客网时间：2024/05/22 06:53

游标的的使用有日常的开发和维护的过程不使用的并不多，但是碰到一些棘手的问题的时候，游标时常是个非常好的帮手，下面就说下游标的使用方法，方法自己以后查阅，和加深一些印象，下面以一个存储过程为例

 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]beginSQL语句(必须有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 = 0SET @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

0 0