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

2024-02-24 23:28:05 361

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生成单据编号存储过程的实例代码

热门推荐

随机推荐