Sql Server 2008 游標的使用

最新推荐文章于 2026-03-05 21:01:03 发布

原创最新推荐文章于 2026-03-05 21:01:03 发布 · 644 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

其它

本文介绍了一种使用T-SQL脚本批量更新数据库中角色权限的方法。通过声明局部变量和临时表来存储权限数据，并利用游标遍历这些数据进行插入或更新操作，实现了高效的角色权限管理。

                   DECLARE  @trancount	AS int,
		            @dRowId     AS int,
					@dAvailRoleCd   AS varchar(20),
					@dRoleCd	AS varchar(20),
					@dProgCd        AS varchar(20),
					@dGrantAdd	AS char(1),
					@dGrantUpd	AS char(1),
					@dGrantDel	AS char(1),
					@dGrantExp	AS char(1),
					@dGrantPnt   	AS char(1),
					@dGrantPwd	AS char(1),
					@dCreateDate	AS datetime,
					@dCreateUser	AS bigint,
				        @dActionType	AS VARCHAR(10),
		                        @dKeyVal		AS varchar(100) = NULL,
					@dStampDate		AS datetime = GETDATE(),
				        @dNeedUpd		AS int = 1;

		DECLARE @tbl TABLE (
		    wAvailRoleCd    varchar(20) NULL,
			wRoleCd		    varchar(20) NULL,
			wProgCd		    varchar(20) NULL,
			wGrantAdd		char(1) NULL,
			wGrantUpd		char(1) NULL,
			wGrantDel		char(1) NULL,
			wGrantExp		char(1) NULL,
			wGrantPnt   	char(1) NULL,
			wGrantPwd	    char(1) NULL,
			wCreateUser	    bigint  NULL
		);

		INSERT INTO @tbl(wAvailRoleCd,wRoleCd, wProgCd, wGrantAdd, wGrantUpd, wGrantDel, wGrantExp, wGrantPnt, wGrantPwd, wCreateUser)
		(SELECT
		    b.value('(wAvailRoleCd[not(@xsi:nil = "true")])[1]', 'varchar(20)') AS wAvailRoleCd,
			b.value('(wRoleCd[not(@xsi:nil = "true")])[1]', 'varchar(20)') AS wRoleCd,
			b.value('(wProgCd[not(@xsi:nil = "true")])[1]', 'varchar(20)') AS wProgCd,
			b.value('(wCanAdd[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantAdd,
			b.value('(wCanUpd[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantUpd,
			b.value('(wCanDel[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantDel,
			b.value('(wCanExp[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantExp,
			b.value('(wCanPnt[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantPnt,
			b.value('(wCanPwd[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantPwd,
			b.value('(wCreateUser[not(@xsi:nil = "true")])[1]', 'bigint') AS wCreateUser
			FROM @pRoleProgsLst.nodes('//List/Item') AS a(b));

		--SELECT TOP 1 @dShipId = wShipId, @dCreateUser = wCreateUser FROM @tbl;

		SET @trancount = @@TRANCOUNT;

		IF @trancount = 0
			BEGIN TRANSACTION;
		ELSE
			SAVE TRANSACTION spaUpdInsRoleProgs;
	    	 
		DECLARE C_DTL CURSOR FOR
		SELECT wAvailRoleCd,wRoleCd, wProgCd, wGrantAdd, wGrantUpd, wGrantDel, wGrantExp, wGrantPnt, wGrantPwd, wCreateUser FROM @tbl;
		OPEN C_DTL;
		FETCH NEXT FROM C_DTL INTO  @dAvailRoleCd,@dRoleCd, @dProgCd, @dGrantAdd, @dGrantUpd, @dGrantDel, @dGrantExp, @dGrantPnt, @dGrantPwd, @dCreateUser;
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @dActionType = 'ADD';
			SET @dRowId = NULL;

			SELECT @dRowId = wRowId, @dCreateDate = ISNULL(wStampDate, wCreateDate) FROM [dbo].[mRoleProg] WHERE wRoleCd = @dAvailRoleCd AND wProgCd = @dProgCd;
			IF @pIsSyncQ = 0 AND @dRowId IS NOT NULL AND @dCreateDate < @pSyncDate SET @dStampDate = @pSyncDate;
			IF @pIsSyncQ = 0 AND @dRowId IS NOT NULL AND @dCreateDate > @pSyncDate SET @dNeedUpd = 0;

			IF @dNeedUpd > 0
			BEGIN
				IF @dRowId IS NULL
				BEGIN	
					INSERT INTO mRoleProg ( wRoleCd, wProgCd, wGrantAdd, wGrantUpd, wGrantDel, wGrantExp, wGrantPnt, wGranPwd, wCreateDate, wCreateUser)
						VALUES (@dAvailRoleCd, @dProgCd, @dGrantAdd, @dGrantUpd, @dGrantDel,@dGrantExp, @dGrantPnt, @dGrantPwd, @dStampDate, @dCreateUser);
				END
				ELSE
				BEGIN
					   IF(@dRoleCd <> @dAvailRoleCd)
					   BEGIN
							DELETE FROM mRoleProg WHERE wRoleCd = @dAvailRoleCd AND wProgCd = @dProgCd;
					   END
					   ELSE
					   BEGIN
							UPDATE mRoleProg 
							SET wRoleCd= ISNULL(@dAvailRoleCd, wRoleCd), 
							wProgCd = ISNULL(@dProgCd, wProgCd), 
							wGrantAdd = ISNULL(@dGrantAdd, wGrantAdd),
							wGrantUpd = ISNULL(@dGrantUpd, wGrantUpd),
							wGrantDel = ISNULL(@dGrantDel, wGrantDel),
							wGrantExp = ISNULL(@dGrantExp, wGrantExp),
							wGrantPnt = ISNULL(@dGrantPnt, wGrantPnt),
							wGranPwd = ISNULL(@dGrantPwd, wGranPwd),
							wStampUser = @dCreateUser,
							wStampDate = @dStampDate
						WHERE wRoleCd = @dAvailRoleCd AND wProgCd = @dProgCd;
				   END
				END
			END
			FETCH NEXT FROM C_DTL INTO @dAvailRoleCd,@dRoleCd, @dProgCd, @dGrantAdd, @dGrantUpd, @dGrantDel, @dGrantExp, @dGrantPnt, @dGrantPwd, @dCreateUser;
		END
		CLOSE C_DTL;
		DEALLOCATE C_DTL;

标签

#Cursor #Fetch