SQL 自定义函数生成网卡地址,MES开发中经常会用到的

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

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