Oracle Auto-increment field creation method-ORACLE SEQUENCE

Oracle Auto-increment field creation method-oracle sequence first assumes that there is such a table: www.2cto.com create table S_Depart (DepartId INT not null, partition name NVARCHAR2 (40) not null, DepartOrder INT default 0, constraint PK_S_DEPART primary key (DepartId); in oracle, sequence is the so-called serial number, which is automatically increased each time it is obtained. It is generally used in places where the sequence numbers need to be sorted. 1. Create Sequence you must first have the create sequence or create any sequence permission, create sequence emp_sequence increment by 1 -- add several www.2cto.com start with 1 each time -- count NOMAXvalue from 1 -- do not set the maximum value NOCYCLE -- always accumulate, no loop CACHE 10; -- set the cache sequence. If the system goes down or is not continuous in other cases, you can also set it to --------- the sequence created by NOCACHE for S_Depart as follows: create sequence s_depart minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 nocach E; once emp_sequence is defined, you can use CURRVAL, nextval currval = to return the current value of sequence NEXTVAL = to increase the value of sequence, and then return the sequence value, for example: emp_sequence.CURRVAL emp_sequence.NEXTVAL: -The "-UPDATE" SET in the "valueS" of the "www.2cto.com-NSERT" statement in the "SELECT statement-INSERT" subquery that does not contain subqueries, snapshot, and VIEW can be seen as follows: insert into S_Depart (departId, partition name, Departorder) values (S_S_Depart.Nextval, '000000', 1); SELECT em1_q. currval from dual; note that -The first time NEXTVAL returns the initial value. The subsequent NEXTVAL will automatically add your defined increment by value, and then return 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 alter sequence emp_sequence increment by 10 www.2cto.com MAXvalue 10000 CYCLE -- to 10000 and then NOCACHE; initialization parameter affecting Sequence: SEQUENCE_CACHE_ENTRIES = set the number of sequence that can be simultaneously cached. You can easily Drop Sequence drop sequence order_seq; a simple example: create sequence SEQ_IDminvalue 1 maxvalue 99999999 start with 1 increment by 1 nocacheorder; the Code for creating the deissuer is: www.2cto.com create or replace trigger tri_test_id before insert on S_Depart -- S_Depart is the table name for each rowdeclare nextid number; begin IF: new. departId IS NULLor: new. departId = 0 THEN -- DepartId is the column name select SEQ_ID.nextval -- SEQ_ID, which is exactly the into nextid from sys. dual;: new. departId: = nextid; end if; end tri_test_id; Author: xiahuawuyu