SET NOCOUNT ON;
IF EXISTS(
SELECT 1
FROM SYS.types AS t
WHERE t.name = 'a01_A01_NODE_R_TABLE_TYPE'
)
DROP TYPE dbo.a01_A01_NODE_R_TABLE_TYPE
GO
CREATE TYPE dbo.a01_A01_NODE_R_TABLE_TYPE AS TABLE
(
ID NUMERIC(10),
A00 NVARCHAR(36) NOT NULL,
A0102 NVARCHAR(11) NOT NULL,
A0104 NVARCHAR(11) NOT NULL
PRIMARY KEY CLUSTERED(ID)
)
GO
IF EXISTS(
SELECT 1
FROM SYS.types AS t
WHERE t.name = 'USR_NODE_TABLE_TYPE'
)
DROP TYPE dbo.USR_NODE_TABLE_TYPE
GO
CREATE TYPE dbo.USR_NODE_TABLE_TYPE AS TABLE
(
ID NUMERIC(10) ,
LOGON_NAME NVARCHAR(11) NOT NULL,
NodeID NVARCHAR(36) NOT NULL,
NodeNAME NVARCHAR(36) NOT NULL
PRIMARY KEY CLUSTERED(ID)
)
GO
DECLARE @PEOPLE_SQL NVARCHAR(500) = N'
SELECT ROW_NUMBER() OVER( ORDER BY A00) AS ID,* FROM (
SELECT DISTINCT A.A00,
A.A0102,
A.A0104
FROM A01 A
INNER JOIN A01_Node_R R
ON R.A00 = A.A00
WHERE R.NodeID = @NODEID
) AS T1 ',
@NODE_SQL NVARCHAR(500) = N'
SELECT ROW_NUMBER() OVER( ORDER BY LOGON_NAME) AS ID,T1.* FROM (
SELECT DISTINCT BU.LOGON_NAME,
ZNT.NodeID,
ZNT.Name
FROM BAP_USER BU
INNER JOIN Z_NodeTree AS znt
ON znt.NodeID = BU.NodeID
) AS T1',
@PEOPLE_ROWID NUMERIC(10) = 1,
@PEOPLE_ROMAX NUMERIC(10) = 0,
@_NODEID UNIQUEIDENTIFIER ,
@PEOPLE_RECORD a01_A01_NODE_R_TABLE_TYPE,
@PEOPLE_CURSOR a01_A01_NODE_R_TABLE_TYPE,
@NODE_ROWID NUMERIC(10) = 1,
@NODE_MAX NUMERIC(10) = 0,
@NODE_RECORD USR_NODE_TABLE_TYPE,
@NODE_CURSOR USR_NODE_TABLE_TYPE
INSERT INTO @NODE_CURSOR EXEC SYS.sp_executesql @STMT=@NODE_SQL
SET @NODE_MAX=@@ROWCOUNT
WHILE (@NODE_ROWID<@NODE_MAX)
BEGIN
DELETE FROM @NODE_RECORD/*清空*/
DELETE FROM @NODE_CURSOR OUTPUT DELETED.* INTO @NODE_RECORD WHERE ID=@NODE_ROWID/*取一条*/
SELECT @_NODEID=NODEID FROM @NODE_RECORD/*赋值*/
INSERT INTO @PEOPLE_CURSOR EXEC SYS.sp_executesql @STMT=@PEOPLE_SQL,
@PARAMS=N'@NODEID UNIQUEIDENTIFIER',@NODEID=@_NODEID/*结果敀到 table type 变量,模拟带参数游标*/
SET @PEOPLE_ROMAX=@@ROWCOUNT/*记录数*/
SET @PEOPLE_ROWID=1/*内循环*/
WHILE (@PEOPLE_ROWID<=@PEOPLE_ROMAX)
BEGIN
DELETE FROM @PEOPLE_RECORD
DELETE FROM @PEOPLE_CURSOR OUTPUT DELETED.* INTO @PEOPLE_RECORD WHERE ID=@PEOPLE_ROWID
PRINT @PEOPLE_ROWID
--SELECT * FROM @@PEOPLE_RECORD
SET @PEOPLE_ROWID+=1
END
--SELECT * FROM @NODE_RECORD
SET @NODE_ROWID+=1
END
TSQL 带参数的游标 表值类型+record + cursor 模仿 plsql %rowtype
最新推荐文章于 2023-05-11 07:30:00 发布