• Sql Server中利用自定义函数完成单据流水号的设计


    之前我也做了这么一个系统,流水号的格式为:单据前缀+业务日期+几位顺序编号.
    知道了流水号的固定格式,设计流水号就非常方便了.
    在SqlServer中,我们可以通过客户端程序来生成新的流水号,也可以利用存储过程来生成.
    在实际的项目中,我觉得利用自定义函数来生成非常方便,方便存储过程调用,也方便客户端的调用.
    说了一大堆废话,来看代码吧.
    1.主调用函数,由于在设计过程中有很多类似单据表,而且每个单据表包含了一个相同的流水号字段,所以为了方便代码调用,提供了对应表的输入参数.

    /**//*
    获取一条新的单据流水号
    -流水号格式为 @PrefixString+'-'+当前日期+4位顺序编号:CGRK-20070509-0001
    */
    
    CREATE FUNCTION dbo.fn_GetNewFlowNumber
    (
    @SheetTableName varchar(50)
    )
    RETURNS varchar(50) AS 
    BEGIN
    
    --流水号前缀
    declare @PrefixString varchar(50)
    --流水号后缀数字的位数
    declare @PostfixLength int 
    --定义好当日单据所有的流水号数据表
    declare @Table table(SheetNo varchar(50))
    --1.取得单据的最后一条SheetNo
    IF @SheetTableName=null OR @SheetTableName=''
    return ''
    /**//*库存部分*/
    --其他入库
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_StoreInSheet')
    BEGIN
    SET @PrefixString='QTRK'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_StoreInSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    --其他出库
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_StoreOutSheet')
    BEGIN
    SET @PrefixString='QTCK'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_StoreOutSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    
    END
    --转仓
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_StoreTransferSheet')
    BEGIN
    SET @PrefixString='CKZC'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_StoreTransferSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    --盘点
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_StoreCheckSheet')
    BEGIN
    SET @PrefixString='CKPD'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_StoreCheckSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    /**//*采购单据操作部分*/
    --请购单
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_PurchaseRequestSheet')
    BEGIN
    SET @PrefixString='QGD'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchaseRequestSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    --采购订单
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_PurchaseOrderSheet')
    BEGIN
    SET @PrefixString='CGDD'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchaseOrderSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    
    END
    --采购询价单
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_PurchaseQuotationSheet')
    BEGIN
    SET @PrefixString='CGXJ'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchaseQuotationSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    --采购入库单
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_PurchaseInSheet')
    BEGIN
    SET @PrefixString='CGRK'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchaseInSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    --采购退货
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_PurchaseReturnSheet')
    BEGIN
    SET @PrefixString='CGTH'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchaseReturnSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    --付款单
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_PurchasePaymentSheet')
    BEGIN
    SET @PrefixString='CGFK'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchasePaymentSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    
    /**//*销售单据操作部分*/
    --销售询价
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_SaleQuotationSheet')
    BEGIN
    SET @PrefixString='XSXJ'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_SaleQuotationSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    --销售订单
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_SaleOrderSheet')
    BEGIN
    SET @PrefixString='XSDD'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_SaleOrderSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    --销售出库
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_SaleOutSheet')
    BEGIN
    SET @PrefixString='XSCK'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_SaleOutSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    --销售退货
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_SaleReturnSheet')
    BEGIN
    SET @PrefixString='XSTH'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_SaleReturnSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    --销售付款
    ELSE IF LOWER(@SheetTableName)=LOWER('AT_SalePaymentSheet')
    BEGIN
    SET @PrefixString='XSFK'
    SET @PostfixLength=4
    INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_SalePaymentSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
    END
    
    /**//*不属于任何单据,返回空的单号*/
    ELSE
    RETURN ''
    
    /**//*
    存在一个当日同前缀的流水号的条件:
    1.流水号总长度相同
    2.相同的流水号前缀
    3.相同的中间日期部分 
    */
    --当日日期部分字符串 
    declare @DateString varchar(8)
    SET @DateString=dbo.fn_FormatDate(dbo.fn_GetNowDate())
    --记录中最后一条流水号
    declare @LastSheetNo varchar(50)
    /**//*--定义好相关参数,比较是否有相同的流水号前缀*/
    --存在,获取最后一条流水+1 
    SELECT TOP 1 @LastSheetNo=SheetNO FROM @Table WHERE
    LEN(SheetNO)=len(@PrefixString)+10+@PostfixLength
    AND LEFT(SheetNO,len(@PrefixString+'-'))=@PrefixString+'-'
    AND LEFT(SheetNO,len(@PrefixString+'-'+@DateString+'-'))=@PrefixString+'-'+@DateString+'-'
    ORDER BY SheetNo DESC
    --return 'ssss'
    IF @LastSheetNo=NULL
    return @PrefixString+'-'+@DateString+'-'+dbo.fn_FillNumberWithZero(1,@PostfixLength)
    ELSE
    return @PrefixString+'-'+@DateString+'-'+dbo.fn_FillNumberWithZero(convert(int,right(@LastSheetNo,@PostfixLength))+1,@PostfixLength)
    
    return ''
    
    END

    2.相关函数

    /**//*
    生成流水号后面几位数字字符的相关函数
    不足位数在左边用0填充
    */
    CREATE FUNCTION dbo.fn_FillNumberWithZero
    (
    --填充的数字
    @num int,
    --总位数
    @len int
    )
    RETURNS varchar(50) AS 
    BEGIN 
    --如果传入的流水号大于总的长度,那么直接返回流水号字符串格式
    if(len(Convert(varchar(50),@num))>@len)
    return Convert(varchar(50),@num)
    ELSE
    BEGIN
    --需要填充0的位数
    declare @NeedFillLen int
    set @NeedFillLen=@Len-len(Convert(varchar(50),@num))
    --获取需要填充的0的字符串
    declare @i int
    set @i=0
    declare @temp varchar(50)
    set @temp=N''
    while @i<@NeedFillLen
    BEGIN
    SET @temp=@temp+'0'
    SET @i=@i+1
    END
    --返回组后的字符串
    return @temp+Convert(varchar(50),@num)
    END
    return ''
    END
    
    
    /**//*
    流水号函数相关函数
    返回某个日期的格式化形式如20070509
    
    */
    CREATE FUNCTION dbo.fn_FormatDate(@Date datetime)
    RETURNS char(8) AS 
    BEGIN 
    declare @year char(4)
    declare @month char(2)
    declare @day char(2)
    
    set @year=convert(char(4),year(@Date))
    set @month=convert(char(4),month(@Date))
    set @day=convert(char(4),day(@Date))
    
    if len(@month)=1
    set @month=N'0'+@month
    if len(@day)=1
    set @day=N'0'+@day
    return @year+@month+@day
    END
    
    /**//*
    获取当天日期
    */
    
    CREATE FUNCTION dbo.fn_GetNowDate()
    RETURNS DateTime AS 
    BEGIN 
    declare @nowDate datetime
    select @nowDate=NowDate FROM v_DateNow
    return @nowDate
    END
    
    注意这里由于sqlserver的自定义函数无法直接获取日期(无法调用getdate()函数),所以我们通过视图的方式来获取服务器的时间.视图如下:
    /**//*
    获取当前系统日期
    这个视图主要供自定义函数调用,
    切勿删除!!!!!!!
    */
    
    CREATE VIEW dbo.v_DateNow
    AS
    SELECT GETDATE() AS NowDate

    全部函数完毕.如何调用呢?
    很简单:
    比如需要入库单的新流水号:

    select dbo.fn_GetNewFlowNumber('AT_StoreCheckSheet')

    是不是非常方便?

  • 相关阅读:
    Java后端知识体系
    HashMap底层实现整理
    Java线程池
    Spring Boot+Dubbo 入门
    Go 代码记录(一)
    Servlet 复习
    Spring Cloud(二)Eureka:服务注册与发现
    Spring Cloud (一)概述
    数据结构基础知识
    容器技术-Docker入门
  • 原文地址:https://www.cnblogs.com/masahiro/p/10131616.html
Copyright © 2020-2023  润新知