1、序列
1.1 序列的定义和作用
Use the CREATE
SEQUENCE
statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.
Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.
After a sequence is created, you can access its values in SQL statements with the CURRVAL
pseudocolumn, which returns the current value of the sequence, or the NEXTVAL
pseudocolumn, which increments the sequence and returns the new value
1.2 在哪里使用序列和哪里不能使用 Where to Use Sequence Values
You can use CURRVAL
and NEXTVAL
in the following locations:
-
The select list of a
SELECT
statement that is not contained in a subquery, materialized view, or view -
The select list of a subquery in an
INSERT
statement -
The
VALUES
clause of anINSERT
statement -
The
SET
clause of anUPDATE
statement
Restrictions on Sequence Values You cannot use CURRVAL
and NEXTVAL
in the following constructs:
-
A subquery in a
DELETE
,SELECT
, orUPDATE
statement -
A query of a view or of a materialized view
-
A
SELECT
statement with theDISTINCT
operator -
A
SELECT
statement with aGROUP
BY
clause orORDER
BY
clause -
A
SELECT
statement that is combined with anotherSELECT
statement with theUNION
,INTERSECT
, orMINUS
set operator -
The
WHERE
clause of aSELECT
statement -
The
DEFAULT
value of a column in aCREATE
TABLE
orALTER
TABLE
statement -
The condition of a
CHECK
constraint
Within a single SQL statement that uses CURRVAL
or NEXTVAL
, all referenced LONG
columns, updated tables, and locked tables must be located on the same database.
1.3 什么时候序列增长 Within a single SQL statement containing a reference to NEXTVAL
, Oracle increments the sequence once:
-
For each row returned by the outer query block of a
SELECT
statement. Such a query block can appear in the following places:-
A top-level
SELECT
statement -
An
INSERT
...SELECT
statement (either single-table or multitable). For a multitable insert, the reference toNEXTVAL
must appear in theVALUES
clause, and the sequence is updated once for each row returned by the subquery, even thoughNEXTVAL
may be referenced in multiple branches of the multitable insert. -
A
CREATE
TABLE
...AS
SELECT
statement -
A
CREATE
MATERIALIZED
VIEW
...AS
SELECT
statement
-
-
For each row updated in an
UPDATE
statement -
For each
INSERT
statement containing aVALUES
clause -
For each
INSERT
... [ALL
|FIRST
] statement (multitable insert). A multitable insert is considered a single SQL statement. Therefore, a reference to theNEXTVAL
of a sequence will increase the sequence only once for each input record coming from theSELECT
portion of the statement. IfNEXTVAL
is specified more than once in any part of theINSERT
... [ALL
|FIRST
] statement, then the value will be the same for all insert branches, regardless of how often a given record might be inserted. -
For each row merged by a
MERGE
statement. The reference toNEXTVAL
can appear in themerge_insert_clause
or themerge_update_clause
or both. TheNEXTVALUE
value is incremented for each row updated and for each row inserted, even if the sequence number is not actually used in the update or insert operation. IfNEXTVAL
is specified more than once in any of these locations, then the sequence is incremented once for each row and returns the same value for all occurrences ofNEXTVAL
for that row. -
For each input row in a multitable
INSERT
ALL
statement.NEXTVAL
is incremented once for each row returned by the subquery, regardless of how many occurrences of theinsert_into_clause
map to each row.
1.4 序列的一些特殊情况
If any of these locations contains more than one reference to NEXTVAL
, then Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL
.
If any of these locations contains references to both CURRVAL
and NEXTVAL
, then Oracle increments the sequence and returns the same value for both CURRVAL
and NEXTVAL
.
2、视图 view
2.1 视图的一些特殊情况
Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
3、约束状态 Constraint State
3.1 DEFERRABLE
DEFERRABLE Clause The DEFERRABLE
and NOT
DEFERRABLE
parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET
CONSTRAINT
(S
) statement. If you omit this clause, then the default is NOT
DEFERRABLE
.
-
Specify
NOT
DEFERRABLE
to indicate that in subsequent transactions you cannot use theSET
CONSTRAINT
[S
] clause to defer checking of this constraint until the transaction is committed. The checking of aNOT
DEFERRABLE
constraint can never be deferred to the end of the transaction.If you declare a new constraint
NOT
DEFERRABLE
, then it must be valid at the time theCREATE
TABLE
orALTER
TABLE
statement is committed or the statement will fail. -
Specify
DEFERRABLE
to indicate that in subsequent transactions you can use theSET
CONSTRAINT
[S
] clause to defer checking of this constraint until aCOMMIT
statement is submitted. If the constraint check fails, then the database returns an error and the transaction is not committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.
You cannot alter the deferrability of a constraint. Whether you specify either of these parameters, or make the constraint NOT
DEFERRABLE
implicitly by specifying neither of them, you cannot specify this clause in an ALTER
TABLE
statement. You must drop the constraint and re-create it.
指定为DEFERRABLE才能使用INITIALLY clause,它指定CREATE 或ALTER 语句是否执行检查
INITIALLY Clause The INITIALLY
clause establishes the default checking behavior for constraints that are DEFERRABLE
. The INITIALLY
setting can be overridden by a SET
CONSTRAINT
(S
) statement in a subsequent transaction.
-
Specify
INITIALLY
IMMEDIATE
to indicate that Oracle should check this constraint at the end of each subsequent SQL statement. If you do not specifyINITIALLY
at all, then the default isINITIALLY
IMMEDIATE
.If you declare a new constraint
INITIALLY
IMMEDIATE
, then it must be valid at the time theCREATE
TABLE
orALTER
TABLE
statement is committed or the statement will fail. -
Specify
INITIALLY
DEFERRED
to indicate that Oracle should check this constraint at the end of subsequent transactions.
This clause is not valid if you have declared the constraint to be NOT
DEFERRABLE
, because a NOT
DEFERRABLE
constraint is automatically INITIALLY
IMMEDIATE
and cannot ever be INITIALLY
DEFERRED
.