ORACLE Sequence Usage

In Oracle, sequence is the serial number, which automatically increases each time it is taken. Sequence is not related to tables.    1, create Sequence first to have create Sequence or create any Sequence permissions. Create the statement as follows: Creating SEQUENCE seqtestincrement by 1--adding several start with 1 each--starting from 1 counting nomaxvalue--not setting the maximum nocycle--always accumulating, not looping the cache 1 0; --Set the cache sequence, if the system down or otherwise will cause the sequence is not contiguous, can also be set to---------NOCACHE2, get sequence value defined sequence, you can use Currval,    Nextval gets the value.   Currval: Returns the current value of sequence Nextval: Increases the value of sequence and then returns the value of the increment after sequence value statement as follows: SELECT sequence name. Currval from DUAL;  If you get the statement above to create the sequence value: Select Seqtest.currval from dual can use sequence in SQL statements:-no subquery, snapshot, view's SELECT statement- INSERT statement in the subquery-insert statement in values-UPDATE set in the INSERT statement as INSERT into table name (Id,name) VALUES (seqtest.   Nextval, ' sequence insert test ');      Note:-The first nextval returns the initial value, and the subsequent nextval automatically increments the increment by value that you defined, and then returns the incremented value.       Currval always returns the value of the current sequence, but the currval is not used until the first nextval is initialized, otherwise an error occurs.    A nextval will increment the value of sequence once, so if you use multiple nextval in the same statement, the value is different. -If you specify the cache value, Oracle can pre-place some sequence in memory so that it accesses faster. After the cache has been taken out, ORacle automatically takes a group to the cache. Using the cache may jump, such as the database suddenly abnormal down (shutdown abort), the cache sequence will be lost.    So you can use NoCache to prevent this when the create sequence. 3, ALTER Sequence has alter any Sequence permission to change the Sequence. You can alter all sequence parameters except start to. If you want to change the start value, you must drop sequence and then re-create.    Example: Alter sequence seqtest MaxValue 9999999; Another: Sequence_cache_entries parameter, set the number of SEQUENCE that can be simultaneously CACHE.   4, Drop sequencedrop SEQUENCE seqtest; 5, an example copy code create sequence Seq_idminvalue 1maxvalue 99999999start with 1increment by 1nocacheorder; The code for the hair generator is: Create or REPL Ace 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 just created into the nextid from sys.dual; : New.  Departid:=nextid; End If;end tri_test_id; Copy the code OK, the above code can realize the function of auto-increment.

ORACLE Sequence Usage