Sql Server 三個很有用的函式
好久沒有寫有關SqlServer 資料庫方面技術的文章了,正好今天遇到了一個問題,我就把這個當做一個練習記錄下來。今天遇到一個麻煩事,詳情如下:公司買了一個系統,在這個系統裡面有一個“充值卡”的功能,但是充值卡的當前所剩下的金額已經被亂碼了,看不出來真正的金額,而且,賣軟體的那家公司把所有的Sql 函式和儲存過程都加密了,沒有辦法看到。
怎麼辦?為了和公司的其他系統進行整合,於是,我就試試來寫一個逆運算來還原當前“充值卡”的剩餘金額,搞了一天終於搞出來了。其實這個東西不是很難,只是剛開始的時候什麼也不知道,東找西找,做測試,浪費了很多時間。解決了就好,現在有時間,我就把兩個函式的實現程式碼都貼出來,大家一看,原來如此。然後有時間,在貼一下第三個函式,去掉帶有小數後面多餘0的一個函式的實現,都很有用,大家可以保留,沒準什麼時候就能用到。
1、我們先看看獲取“充值卡”原始值得Sql Server的函式吧,程式碼如下:
1-- ============================================= 2-- Author:<PatrickLiu> 3-- Create date: <2018-11-20 18:21> 4-- Description:<根據亂碼的資料獲取原始的資料> 5-- ============================================= 6ALTER FUNCTION [dbo].[fun_GetResidualAmount] 7( 8@GarbledValue as varchar(40) 9) 10RETURNS decimal(16,4) 11AS 12BEGIN 13IF(@GarbledValue is null or @GarbledValue='' or @GarbledValue = '0.00') 14BEGIN 15RETURN 0.00; 16END 17 18DECLARE @TempValue varchar(40); 19DECLARE @Length int; 20DECLARE @Index int; 21DECLARE @CurrentMoney decimal(16,4) 22 23SET @TempValue=''; 24SET @Index=1; 25SET @Length = len(@GarbledValue) 26 27WHILE @Index<=@Length 28BEGIN 29SET @TempValue = @TempValue + '' + char(ASCII(SUBSTRING(@GarbledValue,@Index,1))-((@Length-@Index-2) * 2)) 30 31SET @Index = @Index + 1; 32END 33 34SET @TempValue = REVERSE(@TempValue)--字串反轉 35IF(ISNUMERIC(@TempValue)=1) 36SET @CurrentMoney = CAST(@TempValue asdecimal(16,4)) / 10000--轉數字 37ELSE 38SET @CurrentMoney = 0 39 40RETURN @CurrentMoney 41 END
2、這個函式算是生成亂碼結果值的函式,功能很簡單,這個就是我費力寫出來的。
1-- ============================================= 2-- Author:<PatrickLiu> 3-- Create date: <2018-11-20 10:32> 4-- Description:<根據原始的資料生成亂碼的資料> 5-- ============================================= 6ALTER FUNCTION [dbo].[fun_GetGarbledValue] 7( 8@OriginalValue as decimal(16,4) 9) 10RETURNS varchar(40) 11AS 12BEGIN 13IF(@OriginalValue is null or @OriginalValue=0) 14BEGIN 15return '0'; 16END 17 18declare @GarbledValue varchar(40); 19declare @length int; 20declare @index int; 21declare @OriginalValueCopy decimal(16,4); 22declare @OriginalValueCopy2 varchar(40); 23 24set @GarbledValue=''; 25set @OriginalValueCopy2=''; 26set @index=1; 27set @OriginalValueCopy =@OriginalValue*10000; 28set @OriginalValueCopy2 = REVERSE(@OriginalValueCopy); 29set @OriginalValueCopy2='0'+SUBSTring(@OriginalValueCopy2,CHARINDEX('.',@OriginalValueCopy2),100); 30set @length = len(@OriginalValueCopy2); 31 32WHILE @index<=@length 33begin 34set @GarbledValue = @GarbledValue + '' + char(ASCII(SUBSTRING(@OriginalValueCopy2,@index,1))+((@length - @index- 2) * 2)); 35set @index = @index + 1; 36end 37return @GarbledValue 38END
3、這是第三個有用的函式,我也拿出來,給大家分享
1-- ============================================= 2-- Author:<PatrickLiu> 3-- Create date: <2018-11-20 17:57> 4-- Description:<去掉小數點後面多餘的0> 5-- ============================================= 6CREATE FUNCTION [dbo].[fun_ClearNumberPointZero](@NumberValue varchar(50)) 7RETURNS VARCHAR(50) 8AS 9BEGIN 10DECLARE @returnValue varchar(20); 11 12IF(@NumberValue='') 13BEGIN 14SET @returnValue='' --空的時候為空 15END 16ELSE IF(CHARINDEX('.',@NumberValue) ='0') 17BEGIN 18SET @returnValue=@NumberValue --針對不含小數點的 19END 20ELSE IF ( SUBSTRING(REVERSE(@NumberValue),PATINDEX('%[^0]%',REVERSE(@NumberValue)),1)='.') 21BEGIN 22SET @returnValue =left(@NumberValue,LEN(@NumberValue)-PATINDEX('%[^0]%',REVERSE(@NumberValue))) --針對小數點後全是0的 23END 24ELSE 25BEGIN 26SET @returnValue =left(@NumberValue,LEN(@NumberValue)- PATINDEX('%[^0]%.%',REVERSE(@NumberValue))+1) --其他任何情形 27END 28RETURN @returnValue 29END
好了,就到這裡了。對於程式員來說,解決問題是最幸福的,繼續努力,每天進步一點點。