Oracle creates tables and adds primary keys, setting primary key self-growth

Oracle sequence understanding and creating a self-augmented primary key

Oracle Sequence PRIMARY Key
Sequence: is a database object provided by Oacle to produce a series of unique numbers.
L automatically provide a unique value
L Shared objects
L mainly used to provide primary key value
L Loading sequence values into memory improves access efficiency

1. First create the sequence, the syntax format of the Oracle sequence is:

       CREATESEQUENCE 序列名         [INCREMENT BY n]         [START WITH n]         [{MAXVALUE/ MINVALUEn|NOMAXVALUE}]         [{CYCLE|NOCYCLE}]         [{CACHE n|NOCACHE}];

1) INCREMENT by is used to define the step of the sequence, and if omitted, the default is 1.
If a negative value appears, the values for the Oracle sequence are decremented by this step.
2) START with defines the initial value of the sequence (that is, the first value produced), which defaults to 1.
3) MAXVALUE defines the maximum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means that there is no maximum value defined,
At this point, for incrementing the Oracle sequence, the maximum value that the system can produce is 10 27, and for the descending sequence, the maximum is-1.
4) MinValue defines the minimum value that the sequence generator can produce. Option Nomaxvalue is the default option,
There is no minimum definition, and for the descending sequence, the minimum value that the system can produce is 10 of 26, and for the increment sequence, the minimum value is 1.
5) cycle and nocycle indicate whether the sequence generator will loop if its value reaches the limit value. The cycle represents the loop, and the nocycle represents no loops. If the loop is, it loops to the minimum when the increment sequence reaches its maximum value, and to the maximum value when the descending sequence reaches the minimum value. If you do not loop, the error occurs when the limit value is reached and the new value continues to be generated.
6) the cache (buffer) defines the size of the memory block that holds the sequence, which defaults to 20. NoCache indicates that the sequence is not buffered in memory.
Memory buffering of a sequence can improve the performance of the sequence.

2. The syntax for deleting an oracle sequence is the drop SEQUENCE sequence name;

Using sequences can create cracks
The l sequence cracks in the following cases:
? Rolling back
? System exceptions

Example application:

1. Sqlplus logged in first:
Sql> Conn Scott/tiger;
2. Build table:
drop table users;

   create table users(       id number(6) ,      name varchar2(30),      constraint pk_id primary key(id)   );

3. Build sequence:
Create sequence AQ1
Start with 1
Increment by 1
MinValue 1
MaxValue 9999999
Nocycle
NoCache
Noorder;
/or
Drop sequence sq1;
Create sequence sq1;
4. Build Trigger:

Create or Replace Trigger Pn_trigger
Before insert on users
For each row
Begin
Select Sq1.nextval into:new.id from Sys.dual;
End

5. OK, insert a record and test it ....

Insert into users (name) values (' Zhsan ');
select * from Users;

Create a Trigger

create or replace trigger 触发器名before insert on 表名for each rowbegin    select 序列名.nextval into:new.主键 from dual;end 触发器名;

Oracle creates tables and adds primary keys, setting primary key self-growth