Oracle生成单据编号存储过程的实例代码

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;

以上所述是www.887551.com给大家介绍的oracle生成单据编号存储过程的实例代码，希望对大家有所帮助