Method for creating auto-increment fields in Oracle-Sequence Method (2)

Sequence:

If you want to query employeesTableMaximum Value of medium ID
Select max (employee_id) from employees;

However, to ensure the uniqueness of the primary key insertion, if you use max (employee_id) + 1
Because concurrent operations may occur.
1 is added for all inserts, and the result is already inserted with the employee ID.

Another way is to make the primary keySetIsAuto-IncrementColumn: InOracleSQL statements cannot be used to implement subincrements,
It can only be implemented through triggers!

Another method is the sequence:
For example, create a sequence to automatically generateSerial number
Create sequence name
Increment by N
Start with N
Maxvalue n | nomaxvalue
Minvalue n | nominvalue
Cycle | nocycle
Cache n | nocache; by default, 20 are cached.

Example:

Create sequence seq_a
Start with 1 starts from 1 by default.
Maxvalue 11
Increment by 10;
This sequence increases from 1 to 11 with a value of 10 each time.

After the sequence is created, two pseudo columns are used for the value from the middle out. One is nextval.
Another name is currval.

Select seq_a.currval from dual;
Execution failed. The first execution of any sequence must be nextval but not currval.

Select seq_a.nextval from dual;
The first execution of the preceding statement is 1, and the second execution is 11.

If you wantSetThe cache value must be smaller than the cycle value if the loop is set. Note that the cache value must be greater than 1.

Alter sequence seq_a
Maxvalue 21
Cycle
Cache 2

After the sequence is changed
After executing select seq_a.nextval from dual;
21 is displayed. After the same statement is executed, 1 is displayed.
And then execute 11, and then execute 21, becauseSetSet Loop
Therefore, the number of loops from 1 to 21 is increased by 10 each time.

If you want to view the sequence

Enter DESC seq.

Then, select sequence_name from seq to find the sequence name.

In this caseTableIf the primary key is inserted in, do not insert records that do not exist in the primary key.
The value that appears can be inserted, but it is very dangerous. It will conflict with the sequence in the future !!
Insert into employees (employee_id, last_name, email, hire_date,
Job_id) values (employees_seq.nextval, 'aaa', 'ccc ',
Sysdate, 2, 'it _ prog ');
The nextval method of the primary key sequence can ensure that the values of the primary key are not repeated and consistent with the sequence,
Available at this time
Select employees_seq.currval from dual;
The current primary key sequence value can be found, for example, 207.

Note: even if the insertion fails, the sequence value increases accordingly.

FalseSetThe following person inserts a primary key value of 208, but does not use nextval.
If a person inserts data according to the nextval method, the primary key uniqueness constraint is violated.

In fact, if you directly specify the primary key insertion, the sequence value will not be changed unless you use
Employees_seq.nextval,
Note that if employees_seq.nextval is used during insertion, no matter whether the insertion is successful or not,
The sequence will increase, and the sequence value will not be affected by transaction rollback !!

Transactions cannot be restored to sequence, so the jump value will appear, just as the jump value of the subincrement.
The essence is the same. The essence of subincrement is also a sequence. Whether inserted or not, subincrement is performed.

In actual databases, the number of hops cannot be avoided. Primary Key insertion is performed using the nextval method.

Note that the nextval sequence can completely avoid concurrency conflicts, because there is a concurrency mechanism in it,
There will be no two people at the same time nextval, but the same value is taken out.

Transaction rollback, system crash, two or moreTableWhen a sequence is shared, a sequence jump occurs.
Number.