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 ;