`
xiaofengtoo
  • 浏览: 491959 次
  • 性别: 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 ...
评论
发表评论

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

相关推荐

    最全的oracle常用命令大全.txt

    依此类推，就可以轻松知道数据字典的详细名称和解释，不用查看ORACLE的其它文档资料了。 下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。 1、用户 查看当前用户的缺省表空间 SQL&gt;select username...

    SQLServer,MySQL,Oracle三者的区别.pdf

    - **自动增长**：Oracle使用序列（Sequences）处理自动增长，MySQL则有自增（AUTO_INCREMENT）属性。 - **主键**：Oracle主键可由多个列组成，MySQL主键通常为单列。 - **单引号处理**：Oracle在SQL语句中需要...

    Oracle数据库维护

    冷备份适用于数据库完全可用的情况。 **2.2 逻辑备份** 逻辑备份是利用Oracle提供的导出（EXP）和导入（IMP）工具来备份数据库中的对象。这种方法非常适合备份特定的模式或表。 **2.3 物理备份** 物理备份是直接...

    Oracle操作

    使用Oracle的Expdp工具进行完全备份。 ### 20. 备份指定用户 ```sql EXPDP username/password DIRECTORY=dir_name DUMPFILE=backup.dmp SCHEMAS=(schema1, schema2); ``` 仅备份指定用户的数据。 ### 21. 备份指定...

    MySQL 80道面试题及答案.docx

    6. **自动增长类型**：MySQL和SQL Server通常使用自动增长类型，而Oracle没有内置的自动增长类型，需要通过序列（Sequences）实现类似功能。 7. **资源消耗**：MySQL安装文件小，占用资源较少；Oracle在安装和运行时...

    rman_xttconvert_VER4.3.zip.7z

    （比如存储在 SYSTEM 表空间内的 pl/sql 对象，sequences 等），你可以使用数据泵来拷贝这些对象至目标系统。 注意： 考虑使用新release的版本V4的过程。 这个版本极大地简化了相关步骤。 请参考文档：V4 Reduce ...

    SQL最佳实践

    如果后续的SQL语句与第一次完全相同，则可以重用之前的执行计划。但是，如果每次查询都使用实际值代替参数，则每个查询都需要重新解析。使用绑定变量可以让多个查询共享同一个执行计划，从而节省资源。 ##### ...

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