`
xiaofengtoo
  • 浏览: 484704 次
  • 性别: Icon_minigender_1
  • 来自: xiamen
社区版块
存档分类
最新评论

Oracle Sequences 完全解释

    博客分类：
  • DB
OracleCacheSQLASPBlog 
阅读更多

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

 

分享到：
| error:ids for this class must be manuall ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics