SQL:多个游标嵌套实例

SQL Server 游标不像PLSQL 游标，可以游标里定义输入参数，

但 T-SQL在可以在任意地方声明变量，故在嵌套的游标里再定义一个游标变量，且嵌套定义的游标里带有参数即可，该参数与上层级的游标输出变量相同即可。例如以下例子。

SQL Server 2008R2 环境多个游标嵌套使用例子。

打印系统的用户表以及表的列清单

/*
Author: Samrv8
Date: 2023-1-19
Description: 打印系统的用户表以及表的列清单
环境： SQL SERVER 2008R2 
*/
DECLARE C1 CURSOR FOR
SELECT TB.NAME AS TABLE_NAME, TB.OBJECT_ID 
  FROM SYS.TABLES  TB
 WHERE TB.TYPE= 'U'
-- AND TB.NAME IN ( 'EBCFG','SEWST')
 ORDER BY TB.NAME ;
 
/*
 -- SWEI    994102582
DECLARE C2 CURSOR FOR 
 SELECT COL.NAME AS COLUMN_NAME 
  FROM SYS.ALL_columns COL,
 SYS.TABLES  TB
  WHERE TB.OBJECT_ID = COL.OBJECT_ID
   AND TB.TYPE= 'U'
   --AND COL.OBJECT_ID = 994102582
   AND TB.NAME = @TABLE_NAME  
ORDER BY COL.OBJECT_ID, COL.COLUMN_ID;
*/
DECLARE @TABLE_NAME NVARCHAR(32);  -- 表名
DECLARE @COLUMN_NAME NVARCHAR(32);  -- 列名
DECLARE @OBJECT_ID BIGINT;  -- 对象ID
DECLARE @FETCH_STATUS1 INT;  -- 表游标状态 
DECLARE @FETCH_STATUS2 INT;  -- 列游标状态
 
OPEN C1 
FETCH NEXT FROM C1 INTO @TABLE_NAME ,@OBJECT_ID
SET @FETCH_STATUS1 = @@FETCH_STATUS;
WHILE @FETCH_STATUS1 = 0 
BEGIN
   PRINT 'SELECT TOP 90 * FROM '+ @TABLE_NAME +' ;';
   PRINT '/*';
    -- SWEI 994102582
DECLARE C2 CURSOR FOR
 SELECT COL.NAME AS COLUMN_NAME 
  FROM SYS.ALL_columns COL,
 SYS.TABLES  TB
  WHERE TB.OBJECT_ID = COL.OBJECT_ID
   AND TB.TYPE= 'U'
  -- AND COL.OBJECT_ID = 994102582
  AND TB.NAME = @TABLE_NAME  
ORDER BY COL.OBJECT_ID, COL.COLUMN_ID;
 
   OPEN C2 
   FETCH NEXT FROM C2 INTO @COLUMN_NAME 
   SET @FETCH_STATUS2 = @@FETCH_STATUS;
   WHILE @FETCH_STATUS2 = 0 
   BEGIN
     PRINT ''+ @COLUMN_NAME + ':';
     FETCH NEXT FROM C2 INTO @COLUMN_NAME;
     SET @FETCH_STATUS2 = @@FETCH_STATUS;
   END ; -- END C2; 
   close C2;                   --关闭游标
   deallocate C2;
    PRINT '*/';
FETCH NEXT FROM C1 INTO @TABLE_NAME ,@OBJECT_ID; 
SET @FETCH_STATUS1 = @@FETCH_STATUS;
END ; -- END C1;
close C1                   --关闭游标
deallocate C1;