Oracle generates the example code of the document numbering stored procedure

Oracle generates a stored procedure for document numbering, and similar systems that make orders may have order numbers that are not duplicated, or serial numbers may be re-numbered daily, yearly, and monthly.

You can refer to the following stored procedures

CREATE OR REPLACEprocedure Pro_GetBillNO (TypeTable in varchar2,cur_mycursor out sys_refcursor) asDReceiptCode 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); beginDReturnValue:='' Select "ReceiptCode", "ReceiptName", "Prefix1", "ISO", "IsAutoCreate", "Prefix2", "Prefix3", "DateValue", "NO", "Length", "ResetType", "Separator" intoDReceiptCode,DReceiptName,DPrefix1,DISO,DIsAutoCreate,DPrefix2,DPrefix3,DDateValue,DNO,DLength,DResetType,DSeparator from "SysReceiptConfig" where "ReceiptCode" = TypeTable If to_number (DResetType) > 0thenif DIsAutoCreate=1 THENif DResetType=1 then-by year if to_number (to_char (sysdate,'yyyy')) to_number (to_char (DDateValue,'yyyy')) thenupdate "SysReceiptConfig" set "NO" = 1, "DateValue" = to_date (sysdate) where "ReceiptCode" = TypeTable;elseupdate "SysReceiptConfig" set "NO" = "NO" + 1 where "ReceiptCode" = TypeTable;end if;-year end if -- DResetType=1if DResetType=2 then-- by month if to_number (to_char (sysdate,'MM')) to_number (to_char (DDateValue,'MM')) thenupdate "SysReceiptConfig" set "NO" = 1, "DateValue" = to_date (sysdate) where "ReceiptCode" = TypeTable;elseupdate "SysReceiptConfig" set "NO" = "NO" + 1 where "ReceiptCode" = TypeTable;end if;-month end if -- DResetType=2if DResetType=3 then-- if to_number (to_char (sysdate,'dd')) to_number (to_char (DDateValue,'dd')) thenupdate "SysReceiptConfig" set "NO" = 1, "DateValue" = to_date (sysdate) where "ReceiptCode" = TypeTable;elseupdate "SysReceiptConfig" set "NO" = "NO" + 1 where "ReceiptCode" = TypeTable;end if;-- month end if;--DResetType=3elseupdate "SysReceiptConfig" set "NO" = "NO" + 1 where "ReceiptCode" = TypeTable;end if;--DResetTypeend if StrSql:=' select * from "SysReceiptConfig" where 1: 1; strSql:=strSql | | 'and "ReceiptCode" =''| | TypeTable | |''; open cur_mycursor for strSql;end |

The above is the example code of the Oracle document number stored procedure that the editor introduced to you. I hope it will be helpful to you. If you have any questions, please leave a message for me, and the editor will reply you in time. Thank you very much for your support to the website!