SQL Server用游标循环遍历结果集

大大源码 • 2023年3月20日 pm11:56 • 其他

1.SQL Server中select 和set 都是赋值用的

DECLARE 
@A1 INT
@A2 INT

SET @A1=10
WHILE @A1>0
BEGIN
	SELECT @A2=@A1
	PRINT @A1+@A2
	Set @A1=@A1-1
END

2.用游标将表中查出的一个变量存储到一个变量中

DECLARE 
@A1 INT,
@A2 INT

DECLARE YOUCURNAME CURSOR FOR SELECT  
	   Accnt_SN
       FROM FAC_COA WHERE SN=2537
OPEN YOUCURNAME --打开游标
fetch next from youcurname into @a1 
 PRINT @A1
close youcurname    --关闭游标
deallocate youcurname   --释放游标

3.循环：用游标对表查出的结果集（先测试一列的情况）进行遍历

DECLARE 
@A1 VARCHAR(100)  --处理结果的变量

DECLARE YOUCURNAME CURSOR FOR SELECT  
	   Accnt_Code      --一列
       FROM FAC_COA    --查询语句
OPEN YOUCURNAME 
FETCH NEXT FROM youcurname INTO  @A1 
WHILE(@@FETCH_STATUS=0) 
 BEGIN
  PRINT @A1  --处理结果
  FETCH NEXT FROM youcurname INTO  @A1 --下一条
 END 
CLOSE youcurname 
DEALLOCATE youcurname

4.循环：用游标对表查出的结果集（测试多列列的情况）进行遍历

DECLARE 
@A1 VARCHAR(100),
@A2 VARCHAR(100)  --处理结果的变量

DECLARE YOUCURNAME CURSOR FOR SELECT  
	   Accnt_Code,Accnt_Name   --多列
       FROM FAC_COA    --查询语句
OPEN YOUCURNAME 
FETCH NEXT FROM youcurname INTO  @A1,@A2    --此处变量位置和查询的列要对应类型
WHILE(@@FETCH_STATUS=0) 
 BEGIN
  PRINT @A1 + @A2  --处理结果
  FETCH NEXT FROM youcurname INTO  @A1,@A2 --下一条
 END 
CLOSE youcurname 
DEALLOCATE youcurname

5.@@fetch_status

@@fetch_status是MSSQL的一个全局变量

其值有以下三种，分别表示三种不同含义：【返回类型integer】

0 FETCH 语句成功

-1 FETCH 语句失败或此行不在结果集中

-2 被提取的行不存在

@@fetch_status值的改变是通过fetch next from实现的

在遍历循环结果集中，需要“FETCH NEXT FROM Cursor” 进行跳入下一行

6.临时表

#号加名字创建临时表：

SELECT * INTO #interim FROM FAC_COA
SELECT *FROM #interim

删除临时表：

drop table #interim

7.SQL內置Function--游标函数

游标函数

这些标量函数返回有关游标的信息。

@@CURSOR_ROWS

CURSOR_STATUS

@@FETCH_STATUS

所有游标函数都不具有确定性。每次用一组特定输入值调用它们时，返回的结果不总是相同的。有关函数确定性的更多信息，请参见确定性函数和非确定性函数。

1@@CURSOR_ROWS

返回连接上最后打开的游标中当前存在的合格行的数量。为提高性能，Microsoft® SQL Server™ 可以异步填充大键集和静态游标。可调用 @@CURSOR_ROWS，以确定当它被调用时，符合游标的行的数目被进行了检索。

返回值	描述
-m	游标被异步填充。返回值 (-m) 是键集中当前的行数。
-1	游标为动态。因为动态游标可反映所有更改，所以符合游标的行数不断变化。因而永远不能确定地说所有符合条件的行均已检索到。
0	没有被打开的游标，没有符合最后打开的游标的行，或最后打开的游标已被关闭或被释放。
n	游标已完全填充。返回值 (n) 是在游标中的总行数。

语法

@@CURSOR_ROWS

返回类型

integer

注释

若最后打开的游标是异步打开的，则 @@CURSOR_ROWS 返回的值是负数。若 sp_configure cursor threshold 的值大于0，则键集驱动程序或静态游标被异步打开，且游标结果集中的行数大于游标阈值。

示例：

下面的示例声明了一个游标，并且用 SELECT 显示 @@CURSOR_ROWS 的值。在游标打开前，设置值为 0，值 -1 则表示游标键集被异步填充。

SELECT @@CURSOR_ROWS
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
SELECT @@CURSOR_ROWS
CLOSE authors_cursor
DEALLOCATE authors_cursor
----------- 
0           
(1 row(s) affected)
au_lname                                 
---------------------------------------- 
White                                    
(1 row(s) affected)         
----------- 
-1          
(1 row(s) affected)

2CURSOR_STATUS

这是一个标量函数，该函数允许存储过程的调用方确定针对一个给定参数，该过程是否返回游标和结果集。
语法
CURSOR_STATUS
    (
        { 'local' , 'cursor_name' }
        | { 'global' , 'cursor_name' }
        | { 'variable' , 'cursor_variable' }
    )
参数
'local'
指定一个常量，该常量表明游标的源是一个本地游标名。
'cursor_name'
游标名。游标名必须符合标识符的规则。
'global'
指定一个常量，该常量表明游标的源是一个全局游标名。
'variable'
指定一个常量，该常量表明游标的源是一个本地变量。
'cursor_variable'
游标变量的名称。必须使用 cursor 数据类型定义游标变量。
返回类型
smallint

返回值	游标名	游标变量
1	游标的结果集至少有一行，并且：对于不感知游标和键集游标，结果集至少有一行。对于动态游标，结果集可以有零行、一行或多行。	分配给该变量的游标已经打开，并且：对于不感知游标和键集游标，结果集至少有一行。对于动态游标，结果集可以有零行、一行或多行。
0	游标的结果集为空。*	分配给该变量的游标已经打开，然而结果集肯定为空。*
-1	游标被关闭。	分配给该变量的游标被关闭。
-2	不可用。	可以是：先前调用的过程并没有将游标指派给 OUTPUT 变量。先前调用的过程给 OUTPUT 变量指派了游标，然而在过程结束时，游标处于关闭状态。因此，游标被释放，并且没有返回给调用过程。没有将游标指派给已声明的游标变量。
-3	带有指定名称的游标不存在。	带有指定名称的游标变量并不存在，或者即使存在这样一个游标变量，但并没有给它分配游标。

* 动态游标从不返回这个结果。
示例
下面的示例创建一个名为 lake_list 的过程，并将执行 lake_list 的输出结果用作 CURSOR_STATUS 的检验。
说明本示例依赖于一个名为 check_authority 的过程，该过程尚未创建。


USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'lake_list' AND type = 'P')
   DROP PROCEDURE lake_list
GO
CREATE PROCEDURE lake_list
   ( @region varchar(30),
     @size integer,
     @lake_list_cursor CURSOR VARYING OUTPUT )
AS 
BEGIN
   DECLARE @ok SMALLINT
   EXECUTE check_authority @region, username, @ok OUTPUT
   IF @ok = 1
      BEGIN
      SET @lake_list_cursor =CURSOR LOCAL SCROLL FOR
         SELECT name, lat, long, size, boat_launch, cost
         FROM lake_inventory
         WHERE locale = @region AND area >= @size
         ORDER BY name
      OPEN @lake_list_cursor
      END
END
DECLARE @my_lakes_cursor CURSOR
DECLARE @my_region char(30)
SET @my_region = 'Northern Ontario'
EXECUTE lake_list @my_region, 500, @my_lakes_cursor OUTPUT
IF Cursor_Status('variable', '@my_lakes_cursor') <= 0
   BEGIN
   /* Some code to tell the user that there is no list of
   lakes for him/her */
   END
ELSE
   BEGIN
      FETCH @my_lakes_cursor INTO -- Destination here
      -- Continue with other code here.
END

3@@FETCH_STATUS

返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。

返回值	描述
0	FETCH 语句成功。
-1	FETCH 语句失败或此行不在结果集中。
-2	被提取的行不存在。

语法
@@FETCH_STATUS
返回类型
integer
注释
由于 @@FETCH_STATUS 对于在一个连接上的所有游标是全局性的，要小心使用 @@FETCH_STATUS 。在执行一条 FETCH 语句后，必须在对另一游标执行另一 FETCH 语句前测试 @@FETCH_STATUS 。在任何提取操作出现在此连接上前，@@FETCH_STATUS 的值没有定义。
例如，用户从一个游标执行一条 FETCH 语句，然后调用一个存储过程，此存储过程打开并处理另一个游标的结果。当控制从被调用的存储过程返回后，@@FETCH_STATUS 反映的是在存储过程中执行的最后的 FETCH 语句的结果，而不是在存储过程被调用之前的 FETCH 语句的结果。
示例
下面的示例用 @@FETCH_STATUS 控制在一个 WHILE 循环中的游标活动。
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

原文链接：https://blog.csdn.net/ClearLoveQ/article/details/83579287

THE END