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;
优质生活从拆开始