sequence.txt

orale培训教材包括了所有的sql说明和实例

序列的扩展：
1）一个经常出现的错误ORA-04013，如何解决？
2）序列的第一个值（第一次使用 .nextval）是多少？
3）cycle的缺省设置，nocycle时越界的表现怎样？


--1）一个经常出现的错误ORA-04013，及其解决。
--使用cache.为避免出现如下err则要求sequence可能的元素数必须>cache指定的数目。
--因为，cache要一次装入指定个数个元素。而如果cycle中元素的总共个数不足cache要装入个数，
--则出此err.缺省cache 为20。
create sequence emp_seq 
increment by 1
minvalue 1
maxvalue 5
cycle;
/*ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle*/
--使minvalue到maxvalue间的元素的总共>20就可，或指定nocache或nocycle都可
create sequence emp_seq 
increment by 1
minvalue 1
maxvalue 5
cycle
nocache;
--ok。不cache.
drop sequence emp_seq;

--共21个值
create sequence emp_seq 
increment by 1
minvalue 1
maxvalue 21
cycle
;
--ok
------------------------------------------

--2）测试序列的第一个值是多少
drop sequence emp_seq;

create sequence emp_seq
increment by -2
start with -1
maxvalue 10
minvalue -24
cycle
cache 10;

SELECT emp_seq.nextval from dual;
--结果-1.第一次.nextval取到的是start with指定的值。
--从此可以看出start with缺省值


  NEXTVAL
----------
       -21

SQL>  SELECT emp_seq.nextval from dual;

   NEXTVAL
----------
       -23
注意：在即将cycle时，sequence并不是再从start with开始。它是在maxvalue,minvalue之间cycle.
--达到maxvalue时自动返回minvalue.递减，达minvalue跳到maxvalue。
SQL>  SELECT emp_seq.nextval from dual;

   NEXTVAL
----------
        10

SQL>  SELECT emp_seq.nextval from dual;

   NEXTVAL
----------
         8

--------------------------递增sequence------------------------------------------
drop sequence seq_id;

--没有start with子句，increment by >0,则以minvalue为第一次.nextval的值。
--为缺省值 
create sequence seq_id 
increment by 2
minvalue -3
maxvalue 5
cycle
nocache;


select seq_id.nextval from emp;
--此时值在minvalue,maxvalue间循环。
   NEXTVAL
----------
        -3
        -1
         1
         3
         5
        -3
        -1
         1
         3
         5
        -3

-------------------------------递减----------------------------------
drop sequence seq_id;

--没有start with子句，increment by <0,则以maxvalue为第一次.nextval的值。
--为缺省值 
create sequence seq_id 
increment by -2
minvalue -3
maxvalue 5
cycle
nocache;

--此时值在minvalue,maxvalue间循环。
select seq_id.nextval from emp;
   NEXTVAL
----------
         5
         3
         1
        -1
        -3
         5
         3
         1
        -1

------------------------设定start with  cycle---------------------

drop sequence seq_id;


create sequence seq_id 
start with 1
increment by 2
minvalue -3
maxvalue 5
cycle
nocache;

--此时只有第一次从start with 开始。值仍在minvalue,maxvalue间循环。
--
select seq_id.nextval from emp;
   NEXTVAL
----------
         1
         3
         5
        -3
        -1
         1
         3
         5
        -3
        -1
         1
         3


---------------------------nocycle:在没有指定cycle时，就是nocycle---
--3)cycle的缺省设置，nocycle时越界的表现
create sequence seq_id 
increment by 2
start with 1
minvalue -3
maxvalue 5
nocache;

--此时，从start with 开始，递增到5,出err!
QL> /

   NEXTVAL
----------
         1

SQL> /

   NEXTVAL
----------
         3

SQL> /

   NEXTVAL
----------
         5

SQL> /
select seq_id.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence SEQ_ID.NEXTVAL exceeds MAXVALUE and cannot be instantiated


--DD
DESC user_sequences;
--select * from user_sequences where sequence_name='EMP_SEQ';






----------------------可以使用.nextval ,currval的sql语句-------------------
where子句不可以用.nextval

Uses and Restrictions of NEXTVAL and CURRVAL 
CURRVAL and NEXTVAL can be used in the following places: 

VALUES clause of INSERT statements 

The SELECT list of a SELECT statement 

The SET clause of an UPDATE statement 

CURRVAL and NEXTVAL cannot be used in these places: 

A subquery 

A view's query or snapshot's query 

A SELECT statement with the DISTINCT operator 

A SELECT statement with a GROUP BY or ORDER BY clause 

A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator 

The WHERE clause of a SELECT statement 

DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement 

The condition of a CHECK constraint 

----start with的值就是第一次.nextval取的值
SQL> create sequence seq_id 
  2  increment by 2
  3  start with 2
  4  minvalue -3
  5  maxvalue 5
  6  nocache;

序列已创建。

SQL> SELECT seq_id.nextval from dual;

   NEXTVAL
----------
         2




---------不可以alter sequence start with不可更改start with的数值

You can change any of the parameters that define how corresponding sequence numbers
 are generated; however, you cannot alter a sequence to change the starting number 
of a sequence. To do this, the sequence must be dropped and re-created. 


---------关于CACHE-----------------------------------------------
The CACHE option of the CREATE SEQUENCE command pre-allocates a set of sequence numbers 
and keeps them in memory so that they can be accessed faster. When the last of the 
sequence numbers in the cache have been used, another set of numbers is read into the cache. 


Sequence numbers can be kept in the sequence cache in the System Global Area (SGA). 
Sequence numbers can be accessed more
quickly in the sequence cache than they can be read from disk. 

The sequence cache consists of entries. Each entry can hold many sequence numbers for a single sequence. 

Follow these guidelines for fast access to all sequence numbers: 

Be sure the sequence cache can hold all the sequences used concurrently by your applications. 

Increase the number of values for each sequence held in the sequence cache.