欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-09-06 14:12:24
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生成单据编号存储过程的实例代码,希望对大家有所帮助