Oracle生成单据编号存储过程的实例代码
Oracle生成单据编号存储过程，在做订单类似的系统都可能会存在订单编号不重复，或是流水号按日，按年，按月进行重新编号。
可以参考以下存储过程
CREATEORREPLACE procedurePro_GetBillNO(TypeTableinvarchar2,cur_mycursoroutsys_refcursor) as DReceiptCodevarchar2(40); DReceiptNamevarchar2(50); DPrefix1varchar2(50); DISOvarchar2(50); DIsAutoCreatevarchar2(20); DPrefix2varchar2(20); DPrefix3varchar2(20); DDateValuedate; DNOnumber; DLengthnumber; DResetTypenumber; DSeparatorvarchar2(20); DReturnValuevarchar2(50); strSqlvarchar2(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,DSeparatorfrom "SysReceiptConfig"where"ReceiptCode"=TypeTable; ifto_number(DResetType)>0 then ifDIsAutoCreate=1THEN ifDResetType=1then--按年份 ifto_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"+1where"ReceiptCode"=TypeTable; endif;--年份 endif;--DResetType=1 ifDResetType=2then--按月份 ifto_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"+1where"ReceiptCode"=TypeTable; endif;--月份 endif;--DResetType=2 ifDResetType=3then--按日 ifto_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"+1where"ReceiptCode"=TypeTable; endif;--月份 endif;--DResetType=3 else update"SysReceiptConfig"set"NO"="NO"+1where"ReceiptCode"=TypeTable; endif;--DResetType endif; strSql:='select*from"SysReceiptConfig"where1=1'; strSql:=strSql||'and"ReceiptCode"='''||TypeTable||''''; opencur_mycursorforstrSql; end;
以上所述是小编给大家介绍的Oracle生成单据编号存储过程的实例代码，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！