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; /
Ayuan
9CBS certified blog expert
Blog expert
Huawei old employees
Big Data
More than ten years, in Huawei, Internet Corporation's experience, profound understanding and practical experience in CRM, big data, mainly sharing various project experience, including architecture, Java, big data and other articles
