Oracle Sequences 完全解释

Oracle Sequences

	Oracle Sequences
	Version 10.2

General
Dependent Objects	seq$ user_sequences all_sequences dba_sequences seq
Related System Privileges	create sequence create any sequence alter any sequence drop any sequence select any sequence
NOTE : The alternative to sequences used in other RDBMS products is autonumbering and keeping the current number in a table. Both of these other methods demand serialization as they can only dispense one number at a time.	Table example: CREATE TABLE seqnum ( next_number NUMBER(1); 1. Lock the seqnum table for your transaction 2. SELECT next_number FROM seqnum; 3. UPDATE seqnum SET next_number=next_number+1; 4. Unlock the seqnum table for the next transation
Tables For Sequence Demos	CREATE TABLE campus_site ( site_id NUMBER(4), organization_name VARCHAR2(40), campus_name VARCHAR2(30), address_id NUMBER(10)) TABLESPACE data_sml; CREATE TABLE division ( division_id NUMBER(5), site_id NUMBER(4), division_name VARCHAR2(40), address_id NUMBER(10)) TABLESPACE data_sml; CREATE TABLE department ( department_id NUMBER(5), division_id NUMBER(5), department_name VARCHAR2(40), address_id NUMBER(10)) TABLESPACE data_sml; CREATE TABLE seq_test ( test NUMBER(10)) TABLESPACE data_sml;
Create Sequence
Full Create Sequence Syntax	CREATE SEQUENCE <seq_name> INCREMENT BY <integer> START WITH <integer> MAXVALUE <integer> / NOMAXVALUE MINVALUE <integer> / NOMINVALUE CYCLE / NOCYCLE CACHE <#> / NOCACHE ORDER / NOORDER;
Create Sequence Simplest Form	CREATE SEQUENCE <sequence_name>;
CREATE SEQUENCE seq_campus_site_id; SELECT seq_campus_site_id.NEXTVAL FROM dual; / /
Simple Autonumber With Sequence	INSERT INTO <table_name> (<column_name>) VALUES (<sequence_name>.NEXTVAL);
INSERT INTO campus_site (site_id, organization_name, campus_name) VALUES (seq_campus_site_id.NEXTVAL ,'Univ. of Washington','Main Seattle'); SELECT * FROM campus_site; INSERT INTO campus_site (site_id, organization_name, campus_name) VALUES (seq_campus_site_id.NEXTVAL , 'Univ. of Washington', 'Bothell'); SELECT * FROM campus_site;
Simple Autonumber With Sequence Into Two Tables	INSERT INTO <table_name> (<column_name>) VALUES (<sequence_name>.CURRVAL);
CREATE SEQUENCE seq_division_id; INSERT INTO campus_site (site_id, organization_name, campus_name) VALUES (seq_division_id.NEXTVAL, 'Univ. of Washington', 'Tacoma'); INSERT INTO division (division_id, site_id, division_name) VALUES (seq_division_id.NEXTVAL, seq_campus_site_id.CURRVAL , 'Engineering'); SELECT * FROM campus_site; SELECT * FROM division;
Simple Transaction Number For Audit Demoing START WITH and a caution with CURRVAL	CREATE SEQUENCE <sequence_name> START WITH <integer>;
CREATE SEQUENCE seq_audit_tx START WITH 297 ; INSERT INTO campus_site (site_id, organization_name, campus_name) VALUES (seq_audit_tx.NEXTVAL, 'Univ. of Washington', 'Everett'); INSERT INTO division (division_id, site_id, division_name) VALUES (seq_audit_tx.NEXTVAL, seq_audit_tx.CURRVAL, 'Science'); INSERT INTO department (department_id, division_id, department_name) VALUES (seq_audit_tx.NEXTVAL, seq_audit_tx.CURRVAL, 'Astronomy'); SELECT * FROM campus_site; SELECT * FROM division; SELECT * FROM department; ROLLBACK; INSERT INTO campus_site (site_id, organization_name, campus_name) VALUES (seq_audit_tx.NEXTVAL, 'Univ. of Washington', 'Everett'); INSERT INTO division (site_id, division_id, division_name) VALUES (seq_audit_tx.CURRVAL, seq_audit_tx.NEXTVAL, 'Science'); INSERT INTO department (division_id, department_id, department_name) VALUES (seq_audit_tx.CURRVAL, seq_audit_tx.NEXTVAL, 'Astronomy'); SELECT * FROM campus_site; SELECT * FROM division; SELECT * FROM department;
INCREMENT BY	CREATE SEQUENCE <sequence_name> INCREMENT BY <positive integer>;
CREATE SEQUENCE seq_inc_by_two INCREMENT BY 2; INSERT INTO seq_test VALUES (seq_inc_by_two.NEXTVAL); / / SELECT * FROM seq_test; CREATE SEQUENCE seq_inc_by_ten INCREMENT BY 10; INSERT INTO seq_test VALUES (seq_inc_by_ten.NEXTVAL); / / SELECT * FROM seq_test; ALTER TABLE seq_test ADD test2 NUMBER(10); desc seq_test INSERT INTO seq_test (test, test2) VALUES (seq_inc_by_ten.NEXTVAL, seq_inc_by_ten.NEXTVAL); SELECT * FROM seq_test; INSERT INTO seq_test (test, test2) VALUES (seq_inc_by_ten.NEXTVAL, seq_inc_by_ten.CURRVAL); SELECT * FROM seq_test;
Reverse DECREMENT BY	CREATE SEQUENCE <sequence_name> MAX VALUE <integer value> INCREMENT BY <negative integer>;
CREATE SEQUENCE seq_reverse INCREMENT BY - 5; ALTER TABLE seq_test DROP COLUMN test2; INSERT INTO seq_test VALUES (seq_reverse.NEXTVAL); / / / SELECT * FROM seq_test; DROP SEQUENCE seq_reverse; CREATE SEQUENCE seq_reverse MAXVALUE 150 START WITH 150 INCREMENT BY - 5; INSERT INTO seq_test VALUES (seq_reverse.NEXTVAL); / / / SELECT * FROM seq_test;
MAXVALUE Demo	CREATE SEQUENCE <sequence_name> START WITH <integer> MAXVALUE <integer>;
CREATE SEQUENCE seq_maxval START WITH 1 MAXVALUE 5; INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL); / / / SELECT * FROM seq_test; INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL); SELECT * FROM seq_test; INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL);
CYCLE Demo	CREATE SEQUENCE <sequence_name> START WITH <integer> MAXVALUE <integer> CYCLE;
CREATE SEQUENCE seq_cycle START WITH 1 MAXVALUE 5 CYCLE ; -- default cache is 20 CREATE SEQUENCE seq_cycle START WITH 1 MAXVALUE 5 CYCLE CACHE 4; TRUNCATE TABLE seq_test; INSERT INTO seq_test VALUES (seq_cycle.NEXTVAL); / / / / / / SELECT * FROM seq_test;
CACHE Demo	CREATE SEQUENCE <sequence_name> CACHE <integer>;
CREATE SEQUENCE seq_cache CACHE 100; SELECT sequence_name, last_number FROM user_sequences; SELECT seq_cache.NEXTVAL FROM dual; SELECT sequence_name, last_number FROM user_sequences; SELECT seq_cache.NEXTVAL FROM dual; / SELECT sequence_name, last_number FROM user_sequences; conn / as sysdba shutdown abort; startup conn uwclass/uwclass SELECT sequence_name, last_number FROM user_sequences; SELECT seq_cache.NEXTVAL FROM dual;
ORDER Demo	CREATE SEQUENCE <sequence_name> START WITH 1 ORDER;
CREATE SEQUENCE seq_order START WITH 1 ORDER ;
Alter Sequence
Change Increment	ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 20;
Change Max Value	ALTER SEQUENCE <sequence_name> MAX VALUE <integer>
ALTER SEQUENCE seq_maxval MAXVALUE 10;
Change Cycle	ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>
ALTER SEQUENCE seq_cycle NOCYCLE ;
Change Cache	ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE
ALTER SEQUENCE seq_cache NOCACHE ;
Change Order	ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>
ALTER SEQUENCE seq_order NOORDER ;
Drop Sequence
Drop Sequence	DROP SEQUENCE <sequence_name>;
DROP SEQUENCE seq_cache;
Sequence Resets
By finding out the current value of the sequence and altering the increment by to be negative that number and selecting the sequence once -- the sequence can be reset to 0. If any session attempts to use the sequence while this is happening an ORA-08004 error will be generated.	CREATE SEQUENCE seq; SELECT seq.NEXTVAL FROM dual; SELECT seq.NEXTVAL FROM dual; SELECT seq.NEXTVAL FROM dual; COLUMN S new_val inc ; SELECT seq.NEXTVAL S FROM dual; ALTER SEQUENCE seq INCREMENT BY -&inc MINVALUE 0; SELECT seq.NEXTVAL S FROM dual; ALTER SEQUENCE seq increment by 1; SELECT seq.NEXTVAL FROM dual; / /
Stored Procedure Method	CREATE OR REPLACE PROCEDURE reset_sequence ( seq_name IN VARCHAR2, startvalue IN PLS_INTEGER) AS cval INTEGER; inc_by VARCHAR2(25); BEGIN EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0'; EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval; cval := cval - startvalue + 1; IF cval < 0 THEN inc_by := ' INCREMENT BY '; cval:= ABS(cval); ELSE inc_by := ' INCREMENT BY -'; END IF; EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by || cval; EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval; EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY 1'; END reset_sequence; /
Sequence Related Queries
Last Number Selected From Sequence	SELECT sequence_name, last_number FROM user_sequences;
Next Number From Sequence	SELECT sequence_name, (last_number + increment_by) NEXT_VALUE FROM user_sequences;

Related Topics

 

来自：雪迎的Blog

 

 

另外记录下一个地址:   http://www.w3school.com.cn/sql/sql_insert.asp