Auto-incrementing by SEQUENCE and TRIGGER in oracle

In oracle, auto-incrementing columns are implemented through SEQUENCE and TRIGGER. 1. The column data type to be auto-incrementing in the table is NUMBER, which cannot be LONG 2. create sequence s_id NOMAXVALUE NOCYCLE
3. create trigger 1 create or replace trigger before_insert_xxx2 before insert on "T_XXX" for each ROW3begin4 select s_id.nextval into: new. COL_ID from dual; 5end; a brief introduction to the Oracle SEQUENCE method for creating an auto-incrementing field in ORACLE: 1 create table S_Depart (2 DepartId INT not null, 3 parameter name NVARCHAR2 (40) not null, 4 DepartOrder INT default constraint PK_S_DEPART primary key (DepartId) 6); in oracle, sequence is the so-called serial number. It will automatically increase, usually used in the place where the serial number needs to be sorted. 1. Create Sequence you must first have the create sequence or create any sequence permission, and create sequence emp_sequence increment by 1 -- add a few
Start with 1 -- count NOMAXvalue from 1 -- do not set the maximum value NOCYCLE -- accumulate all the time, do not cycle CACHE 10; -- set cache CACHE sequence, if the system is down or in other cases, the sequence is not continuous. You can also set it to --------- NOCACHE. The sequence created for S_Depart is as follows: 1 create sequence S_S_DEPART2minvalue 13 maxvalue 9999999999999999999999999994 start with 15 increment by 16 nocache; once emp_sequence is defined, you can use CURRVAL, nextval currval = returns the current value of sequence NEXTVAL = increases the value of sequence, and then returns the value of sequence, for example, emp_sequence.CURRVAL e. Mp_sequence.NEXTVAL where sequence can be used: -the SET of-UPDATE in the valueS of-NSERT statement in the subquery that does not contain the SELECT statement-INSERT statement of the subquery, snapshot, and VIEW can be seen as follows: 1 insert into S_Depart (departId, partition name, Departorder) values (s_s_0000.nextval, '000000', 1); 2 3 SELECT em1_q. currval from dual; but note:-the first NEXTVAL returns the initial value. The subsequent NEXTVAL automatically increases the value of your defined increment by and then returns the added value. CURRVAL always returns the value of the current SEQUENCE, but CURRVAL can be used only after the first NEXTVAL initialization; otherwise, an error will occur. NEXTVAL increases the SEQUENCE value once. Therefore, if you use multiple NEXTVAL values in the same statement, their values are different. Understand? -If the CACHE value is specified, ORACLE can place some sequence in the memory in advance, so that the access speed is faster. After the cache is obtained, oracle automatically retrieves another group to the cache. The cache may be skipped. For example, if the database suddenly fails to be shut down (shutdown abort), the sequence in the cache will be lost. Therefore, nocache can be used to prevent this situation when creating sequence. 2. Alter Sequence: you are the owner of the sequence, or you have the alter any sequence permission to modify the sequence. you can alter all sequence parameters except start. if you want to change the start value, you must drop sequence and re-create. alter sequence Example 1 alter sequence emp_sequence2INCREMENT BY 103 MAXvalue 100004 CYCLE -- to 5 NOCACHE from the beginning after 10000; initialization parameters that affect Sequence: SEQUENCE_CACHE_ENTRIES = set the number of sequence that can be cached at the same time. You can easily Drop Sequence 1 drop sequence order_seq; a simple example: 1 create sequence SEQ_ID2minvalue 13 maxvalue 999999994 start with 15 increment by 16nocache7order; the Code of the producer is: 01 create or replace trigger tri_test_id02 before insert on S_Depart -- S_Depart is the table name 03 for each row04declare05 nextid number; 06begin07 IF: new. departId IS NULLor: new. departId = 0 THEN -- DepartId is the column name 08 select SEQ_ID.nextval -- SEQ_ID is exactly the 09 int O nextid10 from sys. dual; 11: new. DepartId: = nextid; 12 end if; 13end tri_test_id; OK, the above Code can implement the auto-increment function.