游标操作会锁住被遍历的表,容易引起死锁,应当尽量避免使用。
原游标代码:
改用While后仍然可以遍历执行存储过程 ps_TargetPermissionEntity_Select
执行效果相同,就是这里的while与上一方法的排序是相反的,如果对排序顺序有要求的,可以改变一下算法。
按顺序排列的代码如下:
原游标代码:
DECLARE
@tbTargetPermissions
table
(TargetPermissionId
uniqueidentifier
NOT
NULL
PRIMARY
KEY
)
INSERT INTO @tbTargetPermissions
SELECT [ TargetPermissionId ]
FROM [ ps_RolePermissions ]
WHERE [ TargetId ] = @TargetId AND [ RoleId ] = @RoleId
DECLARE @TargetPermissionId uniqueidentifier ;
-- 定义游标
DECLARE TargetPermissions_ByRoleId_Cursor CURSOR FOR
SELECT [ TargetPermissionId ] FROM @tbTargetPermissions
-- 打开游标
OPEN TargetPermissions_ByRoleId_Cursor
-- 读取游标第一条记录
FETCH NEXT FROM TargetPermissions_ByRoleId_Cursor INTO @TargetPermissionId
-- 检查@@FETCH_STATUS的值,以便进行循环读取
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ps_TargetPermissionEntity_Select @TargetPermissionId ;
FETCH NEXT FROM TargetPermissions_ByRoleId_Cursor INTO @TargetPermissionId ;
END
-- 关闭游标
CLOSE TargetPermissions_ByRoleId_Cursor
DEALLOCATE TargetPermissions_ByRoleId_Cursor
INSERT INTO @tbTargetPermissions
SELECT [ TargetPermissionId ]
FROM [ ps_RolePermissions ]
WHERE [ TargetId ] = @TargetId AND [ RoleId ] = @RoleId
DECLARE @TargetPermissionId uniqueidentifier ;
-- 定义游标
DECLARE TargetPermissions_ByRoleId_Cursor CURSOR FOR
SELECT [ TargetPermissionId ] FROM @tbTargetPermissions
-- 打开游标
OPEN TargetPermissions_ByRoleId_Cursor
-- 读取游标第一条记录
FETCH NEXT FROM TargetPermissions_ByRoleId_Cursor INTO @TargetPermissionId
-- 检查@@FETCH_STATUS的值,以便进行循环读取
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ps_TargetPermissionEntity_Select @TargetPermissionId ;
FETCH NEXT FROM TargetPermissions_ByRoleId_Cursor INTO @TargetPermissionId ;
END
-- 关闭游标
CLOSE TargetPermissions_ByRoleId_Cursor
DEALLOCATE TargetPermissions_ByRoleId_Cursor
改用While后仍然可以遍历执行存储过程 ps_TargetPermissionEntity_Select
--
把合符条件的目标权限Id加载到一个临时表变量中
DECLARE @tbTargetPermissions table (IndexId int IDENTITY ( 0 , 1 ) NOT NULL PRIMARY KEY , TargetPermissionId uniqueidentifier NOT NULL )
INSERT INTO @tbTargetPermissions
SELECT [ TargetPermissionId ]
FROM [ ps_RolePermissions ]
WHERE [ TargetId ] = @TargetId AND [ RoleId ] = @RoleId
DECLARE @MaxIndexId int ;
SELECT @MaxIndexId = MAX ( [ IndexId ] ) FROM @tbTargetPermissions ; -- 计算最大记录序号,用于遍历列表
WHILE @MaxIndexId > - 1
BEGIN
DECLARE @TargetPermissionId uniqueidentifier ;
SELECT @TargetPermissionId = [ TargetPermissionId ] FROM @tbTargetPermissions WHERE [ IndexId ] = @MaxIndexId ;
EXEC ps_TargetPermissionEntity_Select @TargetPermissionId ;
-- 删除最大记录项,重新判断记录项是否大于-1,以此判断是否遍历完列表
DELETE @tbTargetPermissions WHERE [ IndexId ] = @MaxIndexId ;
SELECT @MaxIndexId = MAX ( [ IndexId ] ) FROM @tbTargetPermissions
-- SELECT @MaxIndexId--测试(倒序)
END
DECLARE @tbTargetPermissions table (IndexId int IDENTITY ( 0 , 1 ) NOT NULL PRIMARY KEY , TargetPermissionId uniqueidentifier NOT NULL )
INSERT INTO @tbTargetPermissions
SELECT [ TargetPermissionId ]
FROM [ ps_RolePermissions ]
WHERE [ TargetId ] = @TargetId AND [ RoleId ] = @RoleId
DECLARE @MaxIndexId int ;
SELECT @MaxIndexId = MAX ( [ IndexId ] ) FROM @tbTargetPermissions ; -- 计算最大记录序号,用于遍历列表
WHILE @MaxIndexId > - 1
BEGIN
DECLARE @TargetPermissionId uniqueidentifier ;
SELECT @TargetPermissionId = [ TargetPermissionId ] FROM @tbTargetPermissions WHERE [ IndexId ] = @MaxIndexId ;
EXEC ps_TargetPermissionEntity_Select @TargetPermissionId ;
-- 删除最大记录项,重新判断记录项是否大于-1,以此判断是否遍历完列表
DELETE @tbTargetPermissions WHERE [ IndexId ] = @MaxIndexId ;
SELECT @MaxIndexId = MAX ( [ IndexId ] ) FROM @tbTargetPermissions
-- SELECT @MaxIndexId--测试(倒序)
END
执行效果相同,就是这里的while与上一方法的排序是相反的,如果对排序顺序有要求的,可以改变一下算法。
按顺序排列的代码如下:
--
把合符条件的目标权限Id加载到一个临时表变量中
DECLARE @tbTargetPermissions table (IndexId int IDENTITY ( 0 , 1 ) NOT NULL PRIMARY KEY , TargetPermissionId uniqueidentifier NOT NULL )
INSERT INTO @tbTargetPermissions
SELECT [ TargetPermissionId ]
FROM [ ps_RolePermissions ]
WHERE [ TargetId ] = @TargetId AND [ RoleId ] = @RoleId
DECLARE @MinIndexId int ;
DECLARE @MaxIndexId int ;
SELECT @MinIndexId = MIN ( [ IndexId ] ) FROM @tbTargetPermissions ; -- 计算最小记录序号,用于遍历列表
SELECT @MaxIndexId = MAX ( [ IndexId ] ) FROM @tbTargetPermissions ; -- 计算最大记录序号,用于遍历列表
WHILE @MinIndexId <= @MaxIndexId
BEGIN
-- SELECT @MinIndexId,@MaxIndexId--测试(顺序)
DECLARE @TargetPermissionId uniqueidentifier ;
SELECT @TargetPermissionId = [ TargetPermissionId ] FROM @tbTargetPermissions WHERE [ IndexId ] = @MinIndexId ;
EXEC ps_TargetPermissionEntity_Select @TargetPermissionId ;
-- 删除最小记录项,重新判断最小记录项是否小于等于最大记录项,以此判断是否遍历完列表
DELETE @tbTargetPermissions WHERE [ IndexId ] = @MinIndexId ;
SELECT @MinIndexId = MIN ( [ IndexId ] ) FROM @tbTargetPermissions ;
END
DECLARE @tbTargetPermissions table (IndexId int IDENTITY ( 0 , 1 ) NOT NULL PRIMARY KEY , TargetPermissionId uniqueidentifier NOT NULL )
INSERT INTO @tbTargetPermissions
SELECT [ TargetPermissionId ]
FROM [ ps_RolePermissions ]
WHERE [ TargetId ] = @TargetId AND [ RoleId ] = @RoleId
DECLARE @MinIndexId int ;
DECLARE @MaxIndexId int ;
SELECT @MinIndexId = MIN ( [ IndexId ] ) FROM @tbTargetPermissions ; -- 计算最小记录序号,用于遍历列表
SELECT @MaxIndexId = MAX ( [ IndexId ] ) FROM @tbTargetPermissions ; -- 计算最大记录序号,用于遍历列表
WHILE @MinIndexId <= @MaxIndexId
BEGIN
-- SELECT @MinIndexId,@MaxIndexId--测试(顺序)
DECLARE @TargetPermissionId uniqueidentifier ;
SELECT @TargetPermissionId = [ TargetPermissionId ] FROM @tbTargetPermissions WHERE [ IndexId ] = @MinIndexId ;
EXEC ps_TargetPermissionEntity_Select @TargetPermissionId ;
-- 删除最小记录项,重新判断最小记录项是否小于等于最大记录项,以此判断是否遍历完列表
DELETE @tbTargetPermissions WHERE [ IndexId ] = @MinIndexId ;
SELECT @MinIndexId = MIN ( [ IndexId ] ) FROM @tbTargetPermissions ;
END