sqlserver,Oracle通用处理自动产生流水编号：字母、日期、数字等组成

曾经在实训时,做了最后一个项目是伟库网里的ERP-进销存网站。这个网站有一点就是每操作保存成功,都会产生一个单据编号。

而这个单据编号是由不同方式组成的，比如以下两个图：JH10120001，CD10120001。以下我来详细的讲解个人的理解与做法,

随笔中如若有错之处或者修要修改的，欢迎各位高手或浏览者指教，并希望你们提出宝贵的意见和建议。以下的内容希望对那些做ERP或者电子商务等等需要用到流水编号的同志们有所帮助.方法不是唯一的，知其中原理。

第一幅图的编号：JH10120001--进货首字母、日期是10年12月、4为编号组成的。

第二幅图的编号：CD10120001--采购订货中的采和订首字母、日期是10年12月、4为编号组成的

我们来看下sql server的做法，这里用到标量值函数，也可以用存储过程。

------------------------------------

----

/*

*作    者：lin sen

*功能说明：标量值函数(返回自动流水编号)

*编写日期：2010-12-23

*/

------------------------------------

DROP FUNCTION FUN_AutoLSH

GO

CREATE FUNCTION FUN_AuTOLSH()

RETURNS VARCHAR(20)

AS

BEGIN

    DECLARE @LSHID VARCHAR(20)    --返回流水编号

    DECLARE @NUM INT



    --查询该表最大的流水号

    SELECT @NUM=ISNULL(MAX(CONVERT(INT,SUBSTRING(LSH,8,4))),0)+1

    FROM YW_MESSAGE



    --拼接下一个流水号：J+年份4位+月份2位+4位编号(如J2010120001)

    SELECT @LSHID='J'+SUBSTRING(CONVERT(VARCHAR(100),GETDATE(),112),1,6)+SUBSTRING(CONVERT(CHAR(5),10000+@NUM),2,4)

    FROM YW_MESSAGE



    RETURN @LSHID

END

调用：select dbo.FUN_AutoLSH();

有人会说哥们公司不用sql server，而是oracle。这个跟底下的通用一样的道理，只不过几个系统的函数不一样。以下是oracle标量值函数做法：

create or replace function Fun_AutoLSH

return  nvarchar2

AS

  LSHID nvarchar2;

  NUM   number;

begin

  --查询该表最大的流水号

  SELECT nvl(max(to_number(substr(LSH,6,4))),0) + 1 into NUM

  From TEST.YW_MESSAGE;



  --拼接下一个流水号：J+年份4位+月份2位+4位编号(如J2010120001)

  SELECT 'J'||to_number(to_char(sysdate,'yyyymm'))||substr(to_char(10000+NUM),2,4) into LSHID

  FROM TEST.YW_MESSAGE;

  return(LSHID);

end Fun_AutoLSH;

调用：select Fun_AutoLSH() from dual;

我们看到一个单据编号写一个函数，两个呢....上百个呢，总不可能写一百个函数，关键是他们基本相同。

比如那个进销存网站有采购订货、采购入库...销售签单、销售出库...库存管理的调拨、货品盘点等等保存数据都会产生单据编号。
可以写个通用的函数，其他要用的话，只要调用就行。以下是个人写的：通用存储过程

------------------------------------

----

/*

*作    者：lin sen

*功能说明：返回自动流水编号的存储过程

*编写日期：2010-12-23

*/

------------------------------------

DROP PROCEDURE PRO_AutoLSH

GO

CREATE PROCEDURE PRO_AutoLSH

(

    @letter        varchar(10),    --首字母,可以多个字母

    @LSHkey        varchar(20),    --表中的流水号列名,最好是主键

    @tablename    varchar(20)     --表名

)

AS

begin

    DECLARE @LSHID  NVARCHAR(20)

    DECLARE @TLSH   VARCHAR(20)

    DECLARE @NUM    INT

    DECLARE @sql    NVARCHAR(200)



    ----查询该表最大的流水号

    SET @sql='SELECT ISNULL(MAX(CONVERT(INT,SUBSTRING('+@LSHkey+',8,4))),0)+1 FROM '+@tablename

    --创建临时表

    CREATE TABLE TEMP1(TOTAL INT);

    --写入查询得到的数据

    INSERT INTO TEMP1 EXEC(@sql);

    --再查询出值

    SET @NUM=(SELECT TOTAL FROM TEMP1)

    DROP TABLE TEMP1



    --拼接下一个流水号：J+年份4位+月份2位+4位编号(如J2010120001)

    SET @TLSH=@letter+SUBSTRING(CONVERT(VARCHAR(100),GETDATE(),112),1,6)+SUBSTRING(CONVERT(CHAR(5),10000+@NUM),2,4)

    SET @sql=''

    SET @sql='SELECT @LSH='+''''+@TLSH+''''+' FROM '+@tablename



    EXEC sp_executesql

            @sql,

            N'@LSH NVARCHAR(20) OUTPUT',

            @LSHID OUTPUT

    SELECT @LSHID

end