SQL 自定義函式 生成網路卡地址,MES開發中經常會用到的

SQL 自定義函式 生成網路卡地址,MES開發中經常會用到的

ALTER Function [dbo].[Fun_ReleaseMACadd]
(
	@CurrentSeqNo varchar(6)
)
Returns varchar(18)
-------------------------------------------------------------------------------------------------  
As
-------------------------------------------------------------------------------------------------  
-----Created by Jock.Luo, 2024/09/05.
-----Purpose: 得到新的流水號，目前是按照34進位制來計算的.
-----輸入的引數為當前流水號，返回下一個流水號.
-------------------------------------------------------------------------------------------------  
Begin
	Declare @iNewSeqNo varchar(18)
	--Declare @macAddress varchar(18)
	Declare @OEMmacAddress varchar(18)
	Declare @FormatString varchar(36)
	Set @FormatString='0123456789ABCDEF'
	set @OEMmacAddress='A1B2C3'
	-------------------------------------------------------------------------------------------------  
	Declare @CurrentFirstCode char(1)
	Declare @CurrentSecondCode char(1)
	Declare @CurrentThirdCode char(1)
	Declare @CurrentFourthCode char(1)
	Declare @CurrentLastModCode char(1)
	Declare @CurrentLastCode char(1)
	-------------------------------------------------------------------------------------------------  
	Declare @NextFirstCode char(1)
	Declare @NextSecondCode char(1)
	Declare @NextThirdCode char(1)
	Declare @NextFourthCode char(1)
	Declare @NextLastModCode char(1)
	Declare @NextLastCode char(1)
	-------------------------------------------------------------------------------------------------  
	Set @CurrentFirstCode=Substring(@CurrentSeqNo,1,1)
	Set @CurrentSecondCode=Substring(@CurrentSeqNo,2,1)
	Set @CurrentThirdCode=Substring(@CurrentSeqNo,3,1)
	Set @CurrentFourthCode=Substring(@CurrentSeqNo,4,1)
	Set @CurrentLastModCode=Substring(@CurrentSeqNo,5,1)
	Set @CurrentLastCode=Substring(@CurrentSeqNo,6,1)
	-------------------------------------------------------------------------------------------------
	if len(@CurrentSeqNo)=6
	begin
		If @CurrentSeqNo='FFFFFF'  
		Begin
			Set @iNewSeqNo='000000'
			set @iNewSeqNo=@OEMmacAddress+@iNewSeqNo
			set @iNewSeqNo=SUBSTRING(@iNewSeqNo, 1, 2) + '-' + SUBSTRING(@iNewSeqNo, 3, 2)+ '-' + SUBSTRING(@iNewSeqNo, 5, 2) + '-' + SUBSTRING(@iNewSeqNo, 7, 2) + '-' + SUBSTRING(@iNewSeqNo, 9, 2) + '-' + SUBSTRING(@iNewSeqNo, 11, 2)
			Return(@iNewSeqNo)
		End
		-------------------------------------------------------------------------------------------------  
		If @CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode+@CurrentLastModCode+@CurrentLastCode='FFFFF'
		Begin
			Set @NextFirstCode=Substring(@FormatString,CharIndex(@CurrentFirstCode,@FormatString)+1,1)
			Set @iNewSeqNo=@NextFirstCode+'00000'
			set @iNewSeqNo=@OEMmacAddress+@iNewSeqNo
			set @iNewSeqNo=SUBSTRING(@iNewSeqNo, 1, 2) + '-' + SUBSTRING(@iNewSeqNo, 3, 2)+ '-' + SUBSTRING(@iNewSeqNo, 5, 2) + '-' + SUBSTRING(@iNewSeqNo, 7, 2) + '-' + SUBSTRING(@iNewSeqNo, 9, 2) + '-' + SUBSTRING(@iNewSeqNo, 11, 2)
			Return(@iNewSeqNo)
		End
	
		-------------------------------------------------------------------------------------------------  
		If @CurrentThirdCode+@CurrentFourthCode+@CurrentLastModCode+@CurrentLastCode='FFFF'
		Begin
			Set @NextSecondCode=Substring(@FormatString,CharIndex(@CurrentSecondCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@NextSecondCode+'0000'
			set @iNewSeqNo=@OEMmacAddress+@iNewSeqNo
			set @iNewSeqNo=SUBSTRING(@iNewSeqNo, 1, 2) + '-' + SUBSTRING(@iNewSeqNo, 3, 2)+ '-' + SUBSTRING(@iNewSeqNo, 5, 2) + '-' + SUBSTRING(@iNewSeqNo, 7, 2) + '-' + SUBSTRING(@iNewSeqNo, 9, 2) + '-' + SUBSTRING(@iNewSeqNo, 11, 2)
			Return(@iNewSeqNo)
		End

		-------------------------------------------------------------------------------------------------  
		If @CurrentFourthCode+@CurrentLastModCode+@CurrentLastCode='FFF'
		Begin
			Set @NextThirdCode=Substring(@FormatString,CharIndex(@CurrentThirdCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@NextThirdCode+'000'
			set @iNewSeqNo=@OEMmacAddress+@iNewSeqNo
			set @iNewSeqNo=SUBSTRING(@iNewSeqNo, 1, 2) + '-' + SUBSTRING(@iNewSeqNo, 3, 2)+ '-' + SUBSTRING(@iNewSeqNo, 5, 2) + '-' + SUBSTRING(@iNewSeqNo, 7, 2) + '-' + SUBSTRING(@iNewSeqNo, 9, 2) + '-' + SUBSTRING(@iNewSeqNo, 11, 2)
			Return(@iNewSeqNo)
		End

		-------------------------------------------------------------------------------------------------  
		If @CurrentLastModCode+@CurrentLastCode='FF'
		Begin
			Set @NextFourthCode=Substring(@FormatString,CharIndex(@CurrentFourthCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@NextFourthCode+'00'
			set @iNewSeqNo=@OEMmacAddress+@iNewSeqNo
			set @iNewSeqNo=SUBSTRING(@iNewSeqNo, 1, 2) + '-' + SUBSTRING(@iNewSeqNo, 3, 2)+ '-' + SUBSTRING(@iNewSeqNo, 5, 2) + '-' + SUBSTRING(@iNewSeqNo, 7, 2) + '-' + SUBSTRING(@iNewSeqNo, 9, 2) + '-' + SUBSTRING(@iNewSeqNo, 11, 2)
			Return(@iNewSeqNo)
		End

		-------------------------------------------------------------------------------------------------  
		If +@CurrentLastCode='F'
		Begin
			Set @NextLastModCode=Substring(@FormatString,CharIndex(@CurrentLastModCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode+@NextLastModCode+'0'
			set @iNewSeqNo=@OEMmacAddress+@iNewSeqNo
			set @iNewSeqNo=SUBSTRING(@iNewSeqNo, 1, 2) + '-' + SUBSTRING(@iNewSeqNo, 3, 2)+ '-' + SUBSTRING(@iNewSeqNo, 5, 2) + '-' + SUBSTRING(@iNewSeqNo, 7, 2) + '-' + SUBSTRING(@iNewSeqNo, 9, 2) + '-' + SUBSTRING(@iNewSeqNo, 11, 2)
			Return(@iNewSeqNo)
		End
		-------------------------------------------------------------------------------------------------  
		-----除上述情況之外的其它情況.
		Set @NextFirstCode=@CurrentFirstCode
		Set @NextSecondCode=@CurrentSecondCode
		Set @NextThirdCode=@CurrentThirdCode
		Set @NextFourthCode=@CurrentFourthCode
		set @NextLastModCode=@CurrentLastModCode
		Set @NextLastCode=Substring(@FormatString,CharIndex(@CurrentLastCode,@FormatString)+1,1)
		Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode+@CurrentLastModCode+@NextLastCode
		set @iNewSeqNo=@OEMmacAddress+@iNewSeqNo
		set @iNewSeqNo=SUBSTRING(@iNewSeqNo, 1, 2) + '-' + SUBSTRING(@iNewSeqNo, 3, 2)+ '-' + SUBSTRING(@iNewSeqNo, 5, 2) + '-' + SUBSTRING(@iNewSeqNo, 7, 2) + '-' + SUBSTRING(@iNewSeqNo, 9, 2) + '-' + SUBSTRING(@iNewSeqNo, 11, 2)
		Return(@iNewSeqNo)
		-------------------------------------------------------------------------------------------------
	end
	Return(@iNewSeqNo)
		-------------------------------------------------------------------------------------------------
End