SQL:多个游标嵌套实例
SQL Server 游标不像PLSQL 游标,可以游标里定义输入参数,
但 T-SQL在 可以在任意地方声明变量,故在嵌套的游标里再定义一个游标变量,且嵌套定义的游标里带有参数即可,该参数与上层级的游标输出变量相同即可。例如以下例子。
SQL Server 2008R2 环境多个游标嵌套使用例子。
打印系统的用户表以及表的列清单
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | /* 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; |
优质生活从拆开始
标签:
SQL
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了