序列的擴展：
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.