Oracle functions that generate dynamic prefixes and auto-increment numbers

Create or replace
 
Function GetInvitationNO (prev varchar2, num1 varchar2, num2 varchar2, sessionSetting varchar2)
 
Return Varchar2
 
Authid Current_User Is PRAGMA AUTONOMOUS_TRANSACTION; -- you must use the "Current_User" permission to prevent you from running the "Execute Immediate" command & "PRAGMA AUTONOMOUS_TRANSACTION" Homemade transactions to prevent DML from DDL failures.
 
Totalprev Varchar2 (50 );
 
Hassequences Number;
 
ReNO varchar2 (50 );
 
Begin
 
-- Number prefix
 
Totalprev: = Prev | Num1 | Num2 | Sessionsetting;
 

 
-- Create Sequence
 
Select Count (*) Into Hassequences From User_Sequences Where Sequence_Name = Totalprev;
 
If Hassequences <> 1 Then
 
Execute Immediate 'create Sequence '| Totalprev | 'crement By 1 Start With 1 maxvalue 9999999 nocycle ';
 
End If;
 
 
 
-- Use a dynamic sequence name to obtain the Sequence Value and convert the obtained sequence value to a string of 7 characters. If the length is insufficient, use "0" to fill the string.
 
Execute Immediate 'select' | Totalprev | ''' | to_char ('| Totalprev | '. nextval, ''fm0000000'') From dual' into ReNO;
 
-- In to_char (), the value 0 after FM represents the number of digits. If it is not enough, fill it with "0". If it is not enough, add "FM" to avoid spaces, the leading space is reserved for the symbol bit. If to_char is used for a negative number, the space part is used to display-. Because it is a positive number, + is not displayed, instead of space.
 

 
Return ReNO;
 
End;
 
 
Test
 
Select GetInvitationNO ('P', '1', '01', '20180101') From dual
 
-- Dual is an existing table in Oracle that can be read by any user. It is often used in select statement blocks without a target table.