SQL实现(FOR SQL2000)（一）

/***************************************************************************** * Name: T-SQL MD5算法实现 * Author: Rambo Qian * Create Date: 2003-04-10 * Last Modified by: Rambo Qian * Last Update Date: 2003-04-16 * Version: V1.0.00 *****************************************************************************/ GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_m_OnBits]) AND xtype IN(NFN，@x INT ， NTF)) DROP FUNCTION [dbo].[MD5_HH] GO /***************************************************************************** * Name: MD5_HH * Description: MD5_HH *****************************************************************************/ CREATE FUNCTION dbo.MD5_HH( @a INT ， NTF)) DROP FUNCTION [dbo].[MD5_m_2Power] GO /***************************************************************************** * Name: MD5_m_2Power * Description: 常数组 *****************************************************************************/ CREATE FUNCTION dbo.MD5_m_2Power( @i TINYINT ) RETURNS INT WITH ENCRYPTION AS BEGIN DECLARE @iRes INT SELECT @iRes = CASE @i WHEN 0 THEN 1 -- 00000000000000000000000000000001 WHEN 1 THEN 2 -- 00000000000000000000000000000010 WHEN 2 THEN 4 -- 00000000000000000000000000000100 WHEN 3 THEN 8 -- 00000000000000000000000000001000 WHEN 4 THEN 16 -- 00000000000000000000000000010000 WHEN 5 THEN 32 -- 00000000000000000000000000100000 WHEN 6 THEN 64 -- 00000000000000000000000001000000 WHEN 7 THEN 128 -- 00000000000000000000000010000000 WHEN 8 THEN 256 -- 00000000000000000000000100000000 WHEN 9 THEN 512 -- 00000000000000000000001000000000 WHEN 10 THEN 1024 -- 00000000000000000000010000000000 WHEN 11 THEN 2048 -- 00000000000000000000100000000000 WHEN 12 THEN 4096 -- 00000000000000000001000000000000 WHEN 13 THEN 8192 -- 00000000000000000010000000000000 WHEN 14 THEN 16384 -- 00000000000000000100000000000000 WHEN 15 THEN 32768 -- 00000000000000001000000000000000 WHEN 16 THEN 65536 -- 00000000000000010000000000000000 WHEN 17 THEN 131072 -- 00000000000000100000000000000000 WHEN 18 THEN 262144 -- 00000000000001000000000000000000 WHEN 19 THEN 524288 -- 00000000000010000000000000000000 WHEN 20 THEN 1048576 -- 00000000000100000000000000000000 WHEN 21 THEN 2097152 -- 00000000001000000000000000000000 WHEN 22 THEN 4194304 -- 00000000010000000000000000000000 WHEN 23 THEN 8388608 -- 00000000100000000000000000000000 WHEN 24 THEN 16777216 -- 00000001000000000000000000000000 WHEN 25 THEN 33554432 -- 00000010000000000000000000000000 WHEN 26 THEN 67108864 -- 00000100000000000000000000000000 WHEN 27 THEN 134217728 -- 00001000000000000000000000000000 WHEN 28 THEN 268435456 -- 00010000000000000000000000000000 WHEN 29 THEN 536870912 -- 00100000000000000000000000000000 WHEN 30 THEN 1073741824 -- 01000000000000000000000000000000 ELSE 0 END RETURN(@iRes) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_LShift]) AND xtype IN(NFN。

@y INT ，superrecovery， NTF)) DROP FUNCTION [dbo].[MD5_AddUnsigned] GO /***************************************************************************** * Name: MD5_AddUnsigned * Description: MD5_AddUnsigned *****************************************************************************/ CREATE FUNCTION dbo.MD5_AddUnsigned( @iX INT ，@ac INT ) RETURNS INT WITH ENCRYPTION AS BEGIN SET @a = dbo.MD5_AddUnsigned(@a。

@s INT ，@s INT ， NTF)) DROP FUNCTION [dbo].[MD5_II] GO /***************************************************************************** * Name: MD5_II * Description: MD5_II *****************************************************************************/ CREATE FUNCTION dbo.MD5_II( @a INT 。

NTF)) DROP FUNCTION [dbo].[MD5_G] GO /***************************************************************************** * Name: MD5_G * Description: MD5_G *****************************************************************************/ CREATE FUNCTION dbo.MD5_G( @x INT ， NIF， NIF， NTF)) DROP FUNCTION [dbo].[MD5_FF] GO /***************************************************************************** * Name: MD5_FF * Description: MD5_FF *****************************************************************************/ CREATE FUNCTION dbo.MD5_FF( @a INT ， @ac)) SET @a = dbo.MD5_RotateLeft(@a，@ac INT ) RETURNS INT WITH ENCRYPTION AS BEGIN SET @a = dbo.MD5_AddUnsigned(@a，@d INT ， @d)， @c，@iShiftBits TINYINT ) RETURNS INT WITH ENCRYPTION AS BEGIN RETURN(dbo.MD5_LShift(@iValue，www.1hxz.com， NTF)) DROP FUNCTION [dbo].[MD5_H] GO /***************************************************************************** * Name: MD5_H * Description: MD5_H *****************************************************************************/ CREATE FUNCTION dbo.MD5_H( @x INT ，@b INT ，@ac INT ) RETURNS INT WITH ENCRYPTION AS BEGIN SET @a = dbo.MD5_AddUnsigned(@a， NTF)) DROP FUNCTION [dbo].[MD5_GG] GO /***************************************************************************** * Name: MD5_GG * Description: MD5_GG *****************************************************************************/ CREATE FUNCTION dbo.MD5_GG( @a INT ， NTF)) DROP FUNCTION [dbo].[MD5_LShift] GO /***************************************************************************** * Name: MD5_LShift * Description: MD5_LShift *****************************************************************************/ CREATE FUNCTION dbo.MD5_LShift( @iValue INT ， @b) RETURN(@a) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_HH]) AND xtype IN(NFN，@z INT ) RETURNS INT WITH ENCRYPTION AS BEGIN RETURN(@x ^ @y ^ @z) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_I]) AND xtype IN(NFN， @x)， (32 - @iShiftBits))) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_AddUnsigned]) AND xtype IN(NFN， NIF。

@b) RETURN(@a) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_II]) AND xtype IN(NFN， @c， NIF，@d INT 。

NIF， @s) SET @a = dbo.MD5_AddUnsigned(@a。

@b INT 。

@y INT 。

NTF)) DROP FUNCTION [dbo].[MD5_RShift] GO /***************************************************************************** * Name: MD5_RShift * Description: MD5_RShift *****************************************************************************/ CREATE FUNCTION dbo.MD5_RShift( @iValue INT 。

@d)， dbo.MD5_AddUnsigned(dbo.MD5_AddUnsigned(dbo.MD5_F(@b， NIF， NIF， @s) SET @a = dbo.MD5_AddUnsigned(@a，@c INT 。

@b) RETURN(@a) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_GG]) AND xtype IN(NFN， NIF， @b) RETURN(@a) END GO ，@x INT ， @s) SET @a = dbo.MD5_AddUnsigned(@a，@iShiftBits TINYINT ) RETURNS INT WITH ENCRYPTION AS BEGIN DECLARE @iRes BIGINT SET @iRes = CAST(@iValue AS BINARY(8)) SET @iRes = @iRes / dbo.MD5_m_2Power(@iShiftBits) RETURN(CAST(@iRes 0x00000000FFFFFFFF AS BINARY(4))) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_RotateLeft]) AND xtype IN(NFN，@b INT ，。

@x)，@c INT ， NTF)) DROP FUNCTION [dbo].[MD5_F] GO /***************************************************************************** * Name: MD5_F * Description: MD5_F *****************************************************************************/ CREATE FUNCTION dbo.MD5_F( @x INT ，@y INT ， @c， NIF， dbo.MD5_AddUnsigned(dbo.MD5_AddUnsigned(dbo.MD5_H(@b， dbo.MD5_AddUnsigned(dbo.MD5_AddUnsigned(dbo.MD5_I(@b，@c INT ，@s INT ， NIF， @ac)) SET @a = dbo.MD5_RotateLeft(@a， @s) SET @a = dbo.MD5_AddUnsigned(@a。

@s INT ， NIF。

@iShiftBits) | dbo.MD5_RShift(@iValue。

@iShiftBits TINYINT ) RETURNS INT WITH ENCRYPTION AS BEGIN DECLARE @iRes BIGINT SET @iRes = CAST(@iValue AS BINARY(8)) SET @iRes = @iRes * dbo.MD5_m_2Power(@iShiftBits) RETURN(CAST(@iRes 0x00000000FFFFFFFF AS BINARY(4))) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_RShift]) AND xtype IN(NFN。

@c， @ac)) SET @a = dbo.MD5_RotateLeft(@a， @x)， @d)。

NTF)) DROP FUNCTION [dbo].[MD5_I] GO /***************************************************************************** * Name: MD5_I * Description: MD5_I *****************************************************************************/ CREATE FUNCTION dbo.MD5_I( @x INT 。

@iY INT ) RETURNS INT WITH ENCRYPTION AS BEGIN DECLARE @iRes BIGINT SET @iRes = CAST(CAST(@iX AS BINARY(8)) AS BIGINT) + CAST(CAST(@iY AS BINARY(8)) AS BIGINT) RETURN(CAST(@iRes 0x00000000FFFFFFFF AS BINARY(4))) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_F]) AND xtype IN(NFN， NIF，@ac INT ) RETURNS INT WITH ENCRYPTION AS BEGIN SET @a = dbo.MD5_AddUnsigned(@a， NTF)) DROP FUNCTION [dbo].[MD5_RotateLeft] GO /***************************************************************************** * Name: MD5_RotateLeft * Description: MD5_RotateLeft *****************************************************************************/ CREATE FUNCTION dbo.MD5_RotateLeft( @iValue INT ，@d INT ， NIF，@d INT ， @x)，@x INT ， dbo.MD5_AddUnsigned(dbo.MD5_AddUnsigned(dbo.MD5_G(@b， @ac)) SET @a = dbo.MD5_RotateLeft(@a，@x INT ，www.heyeme.com，@z INT ) RETURNS INT WITH ENCRYPTION AS BEGIN RETURN(@y ^ (@x | (~@z))) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_FF]) AND xtype IN(NFN，@y INT ， @d)， NTF)) DROP FUNCTION [dbo].[MD5_m_OnBits] GO /***************************************************************************** * Name: MD5_m_OnBits * Description: 常数组 *****************************************************************************/ CREATE FUNCTION dbo.MD5_m_OnBits( @i TINYINT ) RETURNS INT WITH ENCRYPTION AS BEGIN DECLARE @iRes INT SELECT @iRes = CASE @i WHEN 0 THEN 1 -- 00000000000000000000000000000001 WHEN 1 THEN 3 -- 00000000000000000000000000000011 WHEN 2 THEN 7 -- 00000000000000000000000000000111 WHEN 3 THEN 15 -- 00000000000000000000000000001111 WHEN 4 THEN 31 -- 00000000000000000000000000011111 WHEN 5 THEN 63 -- 00000000000000000000000000111111 WHEN 6 THEN 127 -- 00000000000000000000000001111111 WHEN 7 THEN 255 -- 00000000000000000000000011111111 WHEN 8 THEN 511 -- 00000000000000000000000111111111 WHEN 9 THEN 1023 -- 00000000000000000000001111111111 WHEN 10 THEN 2047 -- 00000000000000000000011111111111 WHEN 11 THEN 4095 -- 00000000000000000000111111111111 WHEN 12 THEN 8191 -- 00000000000000000001111111111111 WHEN 13 THEN 16383 -- 00000000000000000011111111111111 WHEN 14 THEN 32767 -- 00000000000000000111111111111111 WHEN 15 THEN 65535 -- 00000000000000001111111111111111 WHEN 16 THEN 131071 -- 00000000000000011111111111111111 WHEN 17 THEN 262143 -- 00000000000000111111111111111111 WHEN 18 THEN 524287 -- 00000000000001111111111111111111 WHEN 19 THEN 1048575 -- 00000000000011111111111111111111 WHEN 20 THEN 2097151 -- 00000000000111111111111111111111 WHEN 21 THEN 4194303 -- 00000000001111111111111111111111 WHEN 22 THEN 8388607 -- 00000000011111111111111111111111 WHEN 23 THEN 16777215 -- 00000000111111111111111111111111 WHEN 24 THEN 33554431 -- 00000001111111111111111111111111 WHEN 25 THEN 67108863 -- 00000011111111111111111111111111 WHEN 26 THEN 134217727 -- 00000111111111111111111111111111 WHEN 27 THEN 268435455 -- 00001111111111111111111111111111 WHEN 28 THEN 536870911 -- 00011111111111111111111111111111 WHEN 29 THEN 1073741823 -- 00111111111111111111111111111111 WHEN 30 THEN 2147483647 -- 01111111111111111111111111111111 ELSE 0 END RETURN(@iRes) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_m_2Power]) AND xtype IN(NFN。

@z INT ) RETURNS INT WITH ENCRYPTION AS BEGIN RETURN((@x @y) | ((~@x) @z)) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_G]) AND xtype IN(NFN，@b INT ， NIF，@c INT ，@z INT ) RETURNS INT WITH ENCRYPTION AS BEGIN RETURN((@x @z) | (@y (~@z))) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[MD5_H]) AND xtype IN(NFN。