We often need to use a stream to uniquely represent a data, and we sometimes use the queue to move to generate a unique water number, but the queue often does not meet our needs, for example, this queue can only set a minimum, the biggest Value, then accumulate, the date that does not generate this water number includes this water number; one type will create a new queue. Below this stored procedure can generate a water number, its format is the current date (format YYYMMMDD) 6 bit number, and the different types can be given as long as a different type name is given. Before using this stored procedure, create a table to save different types, the table structure is as follows: Create Table T_Genid (Class Number (2) Not null, Gendate Char (8) Not null, ID varchar2 (12) Not null) tablespace SERVICE_MAIN_DAT pctfree 10 pctused 80 initrans 1 maxtrans 255 storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0); - Create / Recreate primary, unique and foreign key constraints alter table T_GENID add constraint PK_GENID primary key (CLASS, ID) using Index TableSpace Service_main_idx PctFree 10 initrans 2 MaxTrans 255 Storage (Initial 1M Next 1M Mineltnce) 1 maxExtents unlimited pctincrease 0);
The stored procedure is as follows: Create or Replace Procedure P_getserialno (i_type in varcha2, - Type of water number O_Serialno Out varcha2 - Return to the water number) Is v_dat varchar2 (10); v_serialnum number (7); begin - Require system date generation String v_dat: = to_char (sysdate, ('YYYYMMDD'));
- generating a serial number generated BEGIN SELECT Decode (GENDATE, v_Dat, (ID 1), 1) INTO v_SerialNum FROM T_GENID WHERE CLASS = i_Type FOR UPDATE; EXCEPTION WHEN NO_DATA_FOUND THEN v_SerialNum: = 1; INSERT INTO T_GENID (CLASS, GenDate, ID) Values (i_type, v_dat, v_serialnum);
WHEN TOO_MANY_ROWS THEN SELECT NVL (Max (ID) 1, 1) INTO V_SERIALNUM from T_Genid WHERE CLASS = i_TYPE
DELETE t_AGT_GENID WHERE CLASS = i_Type; INSERT INTO T_GENID (CLASS, GENDATE, ID) VALUES (i_Type, v_Dat, v_SerialNum); END; - serial modify parameters UPDATE T_GENID SET GENDATE = v_Dat, ID = v_SerialNum WHERE CLASS = i_Type; COMMIT ; - get the required water number o_serialno: = v_dat || LPAD (v_serialnum, 6, '0'); Exception when Others Then O_Serialno: = NULL; END; /
Using the method is very simple, this stored procedure is called in PL / SQL, set a type, as follows: create or replace procedure p_test () is v_serid varchar2 (14); begin p_AGT_GETSERIALNO ('Test', v_serid); - Generate a stream number, put it in the variable v_serid, the first parameter is the type of INSERT INT T_TEST (TestID) VALUES (v_serid) to which it is set; - Insert the generated water number in the table (Exception When Others Then) Rollback; end; /
