Oracle sequence detailing and creating a self-added primary key
Oracle Sequence PRIMARY Key
Sequence: A database object provided by Oacle to produce a series of unique digits.
L provide a unique numeric value automatically
L Shared objects
L primarily used to provide primary key values
L Loading sequence values into memory can improve access efficiency
1. First create the sequence, the syntax format of the Oracle sequence is:
CREATE SEQUENCE sequence name
[INCREMENT by n]
[START with n]
[{maxvalue/minvaluen| Nomaxvalue}]
[{cycle| Nocycle}]
[{CACHE n| NoCache}];
1) INCREMENT by the step used to define the sequence, and if omitted, the default is 1.
If a negative value occurs, the values that represent the Oracle sequence are decremented by this step.
2 START with defines the initial value of the sequence (that is, the first value produced), and the default is 1.
3) MAXVALUE defines the maximum value that a sequence generator can produce. Option Nomaxvalue is the default option, which represents the absence of a maximum value definition,
In this case, the maximum value that the system can produce for an ascending Oracle sequence is 10 27; For a descending sequence, the maximum value is-1.
4) MinValue defines the minimum value that a sequence generator can produce. Option Nomaxvalue is the default option,
Represents the absence of a minimum value definition, at which point the system can produce a minimum value of 10 for a descending sequence of 26; For an ascending sequence, the minimum value is 1.
5 CYCLE and Nocycle indicate whether the value of the sequence generator loops after it reaches its limit. Cycle represents loops, and nocycle represents not loops. Loops to the minimum when the increment sequence reaches its maximum value, and loops to the maximum value for the descending sequence when it reaches the minimum value. If you do not loop, and the limit is reached, the new value continues to produce an error.
6 cache (buffer) defines the size of the memory block that holds the sequence, and defaults to 20. NoCache indicates no memory buffering of the sequence.
Memory buffering of the sequence can improve the performance of the sequence.
2. The syntax for removing the Oracle sequence is the drop SEQUENCE sequence name;
Using sequences creates cracks.
The l sequence cracks in the following cases:
• Rolling back
• System Exceptions
Instance application:
1. Sqlplus Log In First:
Sql> Conn Scott/tiger;
2. Build table:
drop table users;
CREATE TABLE users (
ID number (6),
name varchar2,
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. To build a 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 to test it ....
Insert into users (name) values (' Zhsan ');
select * from Users;
Creating triggers
Create or Replace trigger trigger name
before insert on table name for
each row
begin
Select sequence name. Nextval into:new. Primary Key from Dual;
End trigger name;