Oracle functions "Automatically generate order Numbers"

1 Create or Replace functionGet_request_codereturn varchar2  as2 3         --function: Automatic generation of order numbers4V_mca_no Mcode_apply_01.mca_no%TYPE;5 6        CURSORGet_max_mca_no is   7               SELECT Max(Substr (Mca_no, One,1))--the largest number detected, intercept the last one, such as: 1, 2...N8                frommcode_apply_019               WHERESUBSTR (Mca_no,3,8)=To_char (Sysdate,'YYYYMMDD');--intercept number, such as: 20170422Ten  OneV_requestcodeVARCHAR2(3); A  -   BEGIN -        OPENGet_max_mca_no; the        FETCHGet_max_mca_no intoV_requestcode; -        CLOSEGet_max_mca_no; -  -   IFV_requestcode is NULL  Then +V_requestcode:=NVL (V_requestcode,0);--NVL () function: When V_requestcode is null, take 0 as the value -   END IF; +  AV_requestcode:=Lpad (V_requestcode+ 1,3,'0');--add 1 to the value that is intercepted in the cursor, and then fill 0 to the left, generating a 001...00n ordinal; Lpad () function: padding left at  -V_mca_no:= 'MA' ||To_char (Sysdate,'YYYYMMDD')||V_requestcode;--the resulting application number (eg: ma20170422001;ma20170422002; ma2017042200n) -  -    -   RETURN '0~,'||V_mca_no; -  in END;

function substr (), Detailed: http://www.cnblogs.com/dshore123/p/7805050.html

Oracle functions "Automatically generate order Numbers"