Oracle生成单据编号存储过程的实例代码
程序员文章站
2022-04-23 20:01:32
oracle生成单据编号存储过程,在做订单类似的系统都可能会存在订单编号不重复,或是流水号按日,按年,按月进行重新编号。
可以参考以下存储过程
create o...
oracle生成单据编号存储过程,在做订单类似的系统都可能会存在订单编号不重复,或是流水号按日,按年,按月进行重新编号。
可以参考以下存储过程
create or replace procedure pro_getbillno(typetable in varchar2,cur_mycursor out sys_refcursor) as dreceiptcode varchar2(40); dreceiptname varchar2(50); dprefix1 varchar2(50); diso varchar2(50); disautocreate varchar2(20); dprefix2 varchar2(20); dprefix3 varchar2(20); ddatevalue date; dno number; dlength number; dresettype number; dseparator varchar2(20); dreturnvalue varchar2(50); strsql varchar2(1000); begin dreturnvalue:=''; select "receiptcode","receiptname","prefix1","iso","isautocreate","prefix2","prefix3","datevalue","no","length","resettype","separator" into dreceiptcode,dreceiptname,dprefix1,diso,disautocreate,dprefix2,dprefix3,ddatevalue,dno,dlength,dresettype,dseparator from "sysreceiptconfig" where "receiptcode"=typetable; if to_number(dresettype)>0 then if disautocreate=1 then if dresettype=1 then --按年份 if to_number(to_char(sysdate,'yyyy')) <>to_number(to_char(ddatevalue,'yyyy')) then update "sysreceiptconfig" set "no"=1,"datevalue"=to_date(sysdate) where "receiptcode"=typetable; else update "sysreceiptconfig" set "no"="no"+1 where "receiptcode"=typetable; end if; --年份 end if;--dresettype=1 if dresettype=2 then --按月份 if to_number(to_char(sysdate,'mm')) <>to_number(to_char(ddatevalue,'mm')) then update "sysreceiptconfig" set "no"=1,"datevalue"=to_date(sysdate) where "receiptcode"=typetable; else update "sysreceiptconfig" set "no"="no"+1 where "receiptcode"=typetable; end if; --月份 end if;--dresettype=2 if dresettype=3 then --按日 if to_number(to_char(sysdate,'dd')) <>to_number(to_char(ddatevalue,'dd')) then update "sysreceiptconfig" set "no"=1,"datevalue"=to_date(sysdate) where "receiptcode"=typetable; else update "sysreceiptconfig" set "no"="no"+1 where "receiptcode"=typetable; end if; --月份 end if;--dresettype=3 else update "sysreceiptconfig" set "no"="no"+1 where "receiptcode"=typetable; end if;--dresettype end if; strsql:=' select * from "sysreceiptconfig" where 1=1 '; strsql:=strsql ||' and "receiptcode"='''||typetable||''''; open cur_mycursor for strsql; end;
以上所述是小编给大家介绍的oracle生成单据编号存储过程的实例代码,希望对大家有所帮助