Generate serial number method in Oracle

Ideas:

Record the current maximum serial number for the next fetch using a serial number table. Each time the number is taken, the serial number table is updated to large.

Tables involved:

Serial Number Table Hp_no:

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

Code:

1) The maximum value is obtained by the function. There are three parameters,

The first parameter is the type, which makes it easy to get different serial numbers.

The second parameter is a prefix that is used to place special characters in front of the serial number.

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

Code CREATE OR REPLACE FUNCTIONHp_test_getno (
P_type_nameinch   VARCHAR2 DEFAULT "',  --type parameter 1
P_start_noinch   VARCHAR2,  --prefix parameter 2
P_lengthinch    Number    --get the serial number length parameter 3
)
RETURN VARCHAR2      --return type
is
V_resultVARCHAR2 ( -) := "'; --result Variable 1
V_current_noVARCHAR2 ( -) := "'; --Current value Variable 2
V_num Number         := 0; --The value of the complement variable 3
V_start_noVARCHAR2( -); --prefix variable 4
V_rowidVARCHAR2 ( -); --to update the row's ROWID variable 4
BEGIN
V_start_no:=P_start_no; --assigning parameter values to variables

SELECT COUNT (*)
intoV_num
fromHp_no
WHEREtype_name=P_type_name andStart_no=P_start_no; --gets the number of the same value as the type and prefix, mainly used to determine whether a serial number was generated

--determine if there is no data, then generate an initial value into the library
   IFV_num= 0  --no record is out of date
    Then
V_current_no:=Lpad ('1', P_length,'0'); --get the P_length long 0001 serial number
V_result:=V_current_no; --Connect the prefix to the serial number.

INSERT  intohp_no (type_name, Start_no, Current_no)
VALUES(P_type_name, P_start_no, v_current_no); --record the new maximum value
   ELSE
SELECTROWID, Current_no+ 1
intoV_rowid, V_current_no
fromHp_no
WHEREtype_name=P_type_name andStart_no=P_start_no;--get maximum value plus 1

V_current_no:=Lpad (V_current_no, P_length,'0');


UPDATEHp_no
SETCurrent_no=V_current_no
WHEREROWID=V_rowid; --Update Maximum Value
   END IF;

IFV_start_no is NULL ORV_start_no="'  Then
V_result:=V_current_no;
ELSE
V_result:=V_start_no||V_current_no;
END IF;

RETURNV_result;
END;

Methods for calling functions using stored procedures:

Code --calling a function stored procedure
CREATE OR REPLACE PROCEDUREHp_pro_gettestno
(
V_firstletterinch varchar2,
V_result outvarchar2
)
is
V_firstvaluevarchar2( -);
V_systdatevarchar2( -);
BEGIN
SelectTo_char (Sysdate,'yyyymm')  intov_systdate fromdual;
V_firstvalue:=(V_firstletter||v_systdate);
V_result:=Hp_test_getno ('hptest', V_firstvalue,4);
END;

Results:

Call the stored procedure hp_pro_gettestno(' Hp ', 4);

The result is: hp2016060001

Other considerations :

First, no need to display the new serial number, such as to obtain a continuous serial number

Method:

This table is used to save unused serial numbers. When the user takes the number, it is preferred to determine whether the sub-table contains a serial number, when sometimes, take out one, and delete this (to prevent other users from fetching); otherwise call the main table to get the new serial number.

When the insertion fails, the current serial number is saved to the running cell table for the next fetch.

When deleted, the current serial number is saved to the running cell table for the next fetch.

Second, the new must display the serial number, such as to obtain a continuous serial number

Method:

This table is used to save unused serial numbers. When the user takes the number, it is preferred to determine whether the sub-table contains a serial number, when sometimes, take out one, and delete this (to prevent other users from fetching); otherwise call the main table to get the new serial number.

When the user does not save or insert fails, the current serial number is saved to the running cell table for the next fetch.

When deleted, the current serial number is saved to the running cell table for the next fetch.

Other thinking code I did not write, interested friends can write their own.

Generate serial number method in Oracle