通过T-SQL语句创建游标与实现数据库加解密功能_MsSql_

DECLARE @sql varchar(100); DECLARE @TableName varchar(32); DECLARE @FieldName varchar(32); DECLARE @PrimaryKey varchar(32); DECLARE @DbValue varchar(32); DECLARE @PKey int; --赋值 set @TableName='Student'; SET @FieldName='Name'; SET @PrimaryKey='Id'; --创建动态游标 set @sql = 'declare rowCursor cursor For select '+@PrimaryKey+' as PKey , '+@FieldName +' as DbValue from '+@TableName; EXEC(@sql); --打开游标 OPEN rowCursor; --取值 fetch next from rowCursor into @PKey,@DbValue; --循环 while(@@FETCH_STATUS=0) begin declare @ExecSql varchar(100); set @ExecSql='Update '+@TableName +' set '+@FieldName+' = 234324324'; PRINT @ExecSql EXEC(@ExecSql) fetch next from rowCursor into @PKey,@DbValue; end --关闭游标 close rowCursor; --释放游标控件 deallocate rowCursor;

CREATE TABLE [dbo].[Users] ( [U_nbr] NVARCHAR(20) NOT NULL PRIMARY KEY, [Pwd] nvarchar(MAX) ) --加密 DECLARE @Pwd nvarchar(max) SET @Pwd='测试加密' --将varbinary类型转换成varchar类型 INSERT INTO Users VALUES ('admin',sys.fn_varbintohexstr(ENCRYPTBYPASSPHRASE('32423sd',@Pwd))) SELECT * FROM Users DECLARE @pv varbinary(max) DECLARE @str nvarchar(max) DECLARE @sql nvarchar(max) --将varchar类型转换成varbinary类型 SELECT @str='0x0100000079e98bc2209c61038f9fc2eaaba24d6b8daad08fd98a41b6' set @sql=N'set @p='+@str+N';' exec sp_executesql @sql, N'@p as varbinary(max) output', @p=@pv output PRINT @pv --解密 SELECT CONVERT(VARCHAR(MAX), DecryptByPassPhrase('123ASSDSS',@pv)) GO