DB2 唯一流水号生成

最近开发的系统要求生成很多唯一的单证号，单证号的规则  前缀_yyyyMMdd+顺序号，如 P_201210160001， P_201210160002。

 

因为生成多种的单证号。于是在系统中建了一个单证号种子表BAS_SEQ_SEED

 

 

ID (PK)	主键ID	BIGINT
NET_ID	网络ID	BIGINT
COMP_ID	加盟公司ID	BIGINT
SEED_TYPE	种子类型	SMALLINT
SEED_DATE	日期	VARCHAR(16)
SEED_VALUE	种子值	SMALLINT
SEED_PREFIX	种子前缀	VARCHAR(6)
SEED_SPLIT	种子分隔符	VARCHAR(2)
CREATE_TIME		VARCHAR(19)

 

其中NET_ID、COMP_ID、SEED_TYPE建立一个唯一索引

 

生成的单证号有存储过程来实现

 

CREATE PROCEDURE PROC_SEQ_SEED_GET
( IN net_id_in bigint,   --网络ID
  IN comp_id_in bigint,  --公司ID
  IN seed_type_in integer,--序列号类型
  IN seed_date_in VARCHAR(16),--当前日期yyyymmdd格式
  OUT return_value VARCHAR(32) --返回值
) 
P1: BEGIN  
  DECLARE seed_value_t integer;
  DECLARE seed_date_t varchar(16);
  DECLARE return_value_t integer DEFAULT 1; 
  
  DECLARE rs1 CURSOR WITH RETURN FOR
  select SEED_VALUE,SEED_DATE FROM BAS_SEQ_SEED as t where t.NET_ID=net_id_in and t.COMP_ID=comp_id_in and t.SEED_TYPE=seed_type_in for update WITH RR USE AND KEEP UPDATE LOCKS;
  OPEN rs1;
  FETCH  rs1 into seed_value_t ,seed_date_t;
 
  IF (seed_value_t is null) then --记录不存在
    begin
	           insert INTO BAS_SEQ_SEED(ID,NET_ID,COMP_ID,SEED_TYPE,SEED_DATE,SEED_VALUE)
		    VALUES
		   (NEXTVAL FOR SE_EXPRESSBAS,net_id_in,comp_id_in,seed_type_in,seed_date_in,1);
		   SET return_value_t = 1;   
    end; 
 else
	begin 
	   --set seed_date_in=  SCM_WULIU.ts_fmt(current timestamp,'yyyymmdd');
       if(seed_date_in=seed_date_t) then  --当天
	   begin
	      update BAS_SEQ_SEED set SEED_VALUE=SEED_VALUE+1 where NET_ID=net_id_in and COMP_ID=comp_id_in and SEED_TYPE=seed_type_in;
	      SET return_value_t = seed_value_t+1;
	   end;
	 else     --非当天
	    begin
	       update BAS_SEQ_SEED set SEED_DATE=seed_date_in,SEED_VALUE=1 where NET_ID=net_id_in and COMP_ID=comp_id_in and SEED_TYPE=seed_type_in;
	       SET return_value_t = 1;
	     end;
	   end if;
	 end; 
  end if;
     if(return_value_t<10) then
       set returnvalue=seed_date_in ||'000'||char(return_value_t);
     elseif(return_value_t>=10 AND return_value_t<100) then
        set returnvalue=seed_date_in ||'00'||char(return_value_t); 
     elseif(return_value_t>=100 AND return_value_t<1000) then
        set returnvalue=seed_date_in ||'0'||char(return_value_t);  
     else    
        set returnvalue=seed_date_in||char(return_value_t); 
     end if; 
    
  close rs1;
END P1

 

 

 

大数据量并发测试生成流程号唯一。

 

java调用存储过程

 

 

		try {
			Session session = this.getHibernateSession();
			String sql ="{call SCM_WULIU.PROC_SEQ_SEED_GET(?,?,?,?,?)}";
			CallableStatement cs = session.connection().prepareCall(sql);// 存储过程调用       
			cs.setLong(1, net_id);
			cs.setLong(2, comp_id);
			cs.setShort(3, seed_type);
			cs.setString(4,PubMethod.getCurSysDate("yyyyMMdd"));
			cs.registerOutParameter(5, Types.CHAR);
			cs.execute();
		    result = cs.getString(5);   //得到输出参数 
			session.flush();
		} catch (Exception he) {
			throw he;
		} finally {
			closeSession();
		}