数据库教程: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;