How to generate a serial number in Oracle

Ideas:

You can use a sequential number table to record the current maximum sequential number for future use. Each time a serial number is obtained, the serial number table is updated to a large one.

Involved tables:

Sequential number table hp_no:

Field	Type
Type_name	Varchar2 (100)
Start_no	Varchar2 (100)
Current_no	Varchar2 (100)

 Code:

1) Use the function to obtain the maximum value. There are three parameters,

The first parameter is of type to facilitate different serial numbers.

The second parameter is the prefix used to place special characters before the sequential number.

The third parameter is the length of the generated serial number.

 

Code Create   Or   Replace   Function Hp_test_getno (
P_type_name In Varchar2   Default   '' , -- Type parameter 1
P_start_no In Varchar2 , -- Prefix parameter 2
P_length In Number -- Obtain the sequential number length parameter 3.
)
Return   Varchar2 -- Return type
Is
V_result Varchar2 ( 100 ): =   '' ; -- Result variable 1
V_current_no Varchar2 ( 100 ): =   '' ; -- Current Value variable 2
V_num Number : =   0 ; -- Value population variable 3
V_start_no Varchar2 ( 100 ); -- Prefix variable 4
V_rowid Varchar2 ( 100 ); -- Rowid variable 4 of the row to be updated
Begin
V_start_no: = P_start_no; -- Parameter value assigned to variable

Select   Count ( * )
Into V_num
From Hp_no
Where Type_name = P_type_name And Start_no = P_start_no; -- Obtains the number of values with the same type and prefix. It is mainly used to determine whether a serial number has been generated.

-- If no data exists, the initial value is inserted into the database.
If V_num =   0 -- No record expired
Then
V_current_no: = Lpad ( ' 1 ' , P_length, ' 0 ' ); -- Get the 0001 sequential number of p_length.
V_result: = V_current_no; -- Connect the prefix with the serial number

Insert   Into Hp_no (type_name, start_no, current_no)
Values (P_type_name, p_start_no, v_current_no ); -- Record the new maximum value
Else
Select Rowid, current_no +   1
Into V_rowid, v_current_no
From Hp_no
Where Type_name = P_type_name And Start_no = P_start_no; -- The maximum value is 1.

V_current_no: = Lpad (v_current_no, p_length, ' 0 ' );


Update Hp_no
Set Current_no = V_current_no
Where Rowid = V_rowid; -- Maximum update Value
End   If ;

If V_start_no Is   Null   Or V_start_no = ''   Then
V_result: = V_current_no;
Else
V_result: = V_start_no | V_current_no;
End   If ;

Return V_result;
End ;

How to call a function using a stored procedure:

Code -- Call the function Stored Procedure
Create   Or   Replace   Procedure Hp_pro_gettestno
(
V_firstletter In   Varchar2 ,
V_result out Varchar2
)
Is
V_firstvalue Varchar2 ( 30 );
V_1_date Varchar2 ( 30 );
Begin
Select To_char (sysdate, ' Yyyymm ' ) Into V_1_date From Dual;
V_firstvalue: = (V_firstletter | V_1_date );
V_result: = Hp_test_getno ( ' Hptest ' , V_firstvalue, 4 );
End ;

Result:

Call Stored ProcedureHp_pro_gettestno('Hp ', 4 );

Result: hp2010020001

 

Other ideas:

I,The serial number does not need to be displayed when adding a new serial number. If you get a continuous serial number

Method:

Use a sequential number subtable, which is used to save unused sequential numbers. When a user obtains a number, it is preferred to determine whether the Sub-table contains a sequential number. When one sub-table is retrieved, the sub-table is deleted (to prevent other users from using the sub-table); otherwise, the master table is called to obtain a new sequential number.

If insertion fails, the current serial number is saved in the subtable of the serial number for the next use.

When deleted, save the current serial number to the subtable of the serial number for your next use.

II,The serial number must be displayed when adding a new serial number.

Method:

Use a sequential number subtable, which is used to save unused sequential numbers. When a user obtains a number, it is preferred to determine whether the Sub-table contains a sequential number. When one sub-table is retrieved, the sub-table is deleted (to prevent other users from using the sub-table); otherwise, the master table is called to obtain a new sequential number.

If the user does not save or the insertion fails, the current serial number is saved in the subtable of serial number for the next use.

When deleted, save the current serial number to the subtable of the serial number for your next use.

 

I did not write code for other ideas. If you are interested, you can write it yourself.