How to achieve id self-growth after inserting data

Reference Blog:
http://www.cnblogs.com/hyzhou/archive/2012/04/12/2444158.html

ORACLE SEQUENCE Usage

In oracle, sequence is the serial number, which increases automatically every time it is taken. Sequence has nothing to do with tables.

1,Create Sequence

First, you need to have CREATE SEQUENCE or CREATE ANY SEQUENCE permissions.
Create statements as follows:

CREATE SEQUENCE seqTest
    INCREMENT BY 1 -- Add a few at a time
    START WITH 1 -- Count from the beginning
    NOMAXvalue -- No maximum setting
    NOCYCLE -- It's always cumulative, it's not circular.
    CACHE 10; --Set cache cache Sequences, if the system down If dropped or otherwise, the sequence will be discontinuous. It can also be set to---------NOCACHE

2. Get Sequence Value

After defining the sequence, you can use currVal, nextVal to get the value.
CurrVal: Returns the current value of sequence
NextVal: Increase the sequence value, and then return the increased sequence value
The resulting value statement is as follows:
SELECT Sequence name. CurrVal FROM DUAL;

If you get the above statement to create a Sequence value, it is as follows:
select seqtest.currval from dual

Where sequence can be used in Sql statements:
- SELECT statements that do not contain subqueries, snapshot, VIEW
- Subqueries in INSERT statements
- In the values of the INSERT statement
- In SET of UPDATE
As in the insertion statement

insert into Table name(id,name)values(seqtest.Nextval,'sequence Insertion test');

Note:
- The first NEXTVAL returns the initial value; the subsequent NEXTVAL automatically increases the INCREMENT BY value you defined, and then returns the added value.
CURRVAL always returns the current SEQUENCE value, but after the first initialization of NEXTVAL, CURRVAL can be used, otherwise errors will occur.
A NEXTVAL increases the SEQUENCE value once, so if you use multiple NEXTVAL in the same statement, the value is different.
- If the CACHE value is specified, ORACLE can place some sequences in memory beforehand, so that access is faster. When the cache is finished, Oracle automatically retrieves another set to the cache. Using cache may cause a jump sign, such as a sudden abnormal shut down of the database, and the sequence in the cache will be lost. Therefore, nocache can be used to prevent this when creating sequence.

3,Alter Sequence

ALTER ANY SEQUENCE permission is available to change the sequence. All sequence parameters except start to can be altered. If you want to change the start value, you must drop sequence and re-create.

Example:

alter sequence SEQTEST maxvalue 9999999;

In addition, the SEQUENCE_CACHE_ENTRIES parameter sets the number of sequences that can be cached at the same time.

4,Drop Sequence

DROP SEQUENCE seqTest; 

5. An example

create sequence SEQ_ID
    minvalue 1
    maxvalue 99999999
    start with 1
    increment by 1
    nocache
    order;

The code of the builder is:

    create or replace trigger tri_test_id
      before insert on S_Depart   --S_Depart Table name
      for each row
    declare
      nextid number;
    begin
      IF :new.DepartId IS NULL or :new.DepartId=0 THEN --DepartId Column names
        select SEQ_ID.nextval --SEQ_ID It was just created.
        into nextid
        from sys.dual;
        :new.DepartId:=nextid;
      end if;
    end tri_test_id;

 OK, the above code can achieve the function of automatic increment.

Note: New represents the new value after the data changes, corresponding to the old original value.
:= Represents assignment
nextid represents a reference to a variable defined in sqlplus

According to the above tutorials, the following tests are carried out:

The contents and structure of the test table are as follows:

--Establish sequence
-- Create table
create table TEST
(
  id    NUMBER,
  count NUMBER
)
tablespace KINGWANG
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table TEST
  is 'test';
--Create sequence
create sequence SEQ_ID
minvalue 1
maxvalue 99999999
start with 1
increment by 1
nocache
order;
--insert data
insert into test(id,count)values(seq_id.nextval,0);
insert into test(id,count)values(seq_id.nextval,0);
insert into test(id,count)values(seq_id.nextval,0);
insert into test(id,count)values(seq_id.nextval,0);
insert into test(id,count)values(seq_id.nextval,0);
--There is no inconsistency in multiple statement submissions
--The above can be self-increasing (but there will be a problem if inset Not as follows seq_id.nextval The way will not increase by itself.
--The sender code is:
create or replace trigger tri_test_id
  before insert on test--test Table name
  for each row--row-level trigger
declare
  nextid number;
begin
  IF :new.id IS NULL or :new.id=0 THEN --id Column names(Attention Conditions)
    select SEQ_ID.nextval --SEQ_ID It was just created.
    into nextid
    from sys.dual;
    :new.id:=nextid;
  end if;
end tri_test_id;
/*
    Note: New represents the new value after the data changes, corresponding to the old original value.
          := Representative assignment
          :nextid Represents references to variables defined in sqlplus
    Note:
          One problem with sequences is that if they are just inserts all the time, the sequence will be continuous. If records are deleted, then inserts will be discontinuous, which is unavoidable. 
*/

--test
insert into test(count)values(0);