创建序列sequence

在oracle数据库中，不想MySQL数据库中可以使用一个关键词就可以使ID号自动滚动增加，
而oracle则通过需要独立创建一个对象sequence序列来达到这个自动滚动变化的功能。以下是
创建一个序列的测试过程：

---创建序列：

--创建测试表：

suxing@PROD>create table seqtest(

  2  id number(3),

  3  name varchar2(10),

  4  score number(3,1),

  5  created date default sysdate);

Table created.

 

suxing@PROD>desc seqtest

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER(3)

 NAME                                               VARCHAR2(10)

 SCORE                                              NUMBER(3,1)

 CREATED                                            DATE

#测试表已经创建。

 

--创建测试序列：

suxing@PROD>create sequence mysequence

  2  increment by 1

  3  start with 10

  4  maxvalue 999

  5  nocache

  6  nocycle;

Sequence created.

 #序列创建成功。

--尝试查看currval：

suxing@PROD>select mysequence.currval from dual;

select mysequence.currval from dual

       *

ERROR at line 1:

ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session

#使用CURRVAL之前必须先查看NEXTVAL。

 

--查看nextval：

suxing@PROD>select mysequence.nextval from dual;

   NEXTVAL

----------

        10

 

 

--再次查看currval：

suxing@PROD>select mysequence.currval from dual;

   CURRVAL

----------

        10

#可以查看到。

 

--新会话尝试查看currval：

[oracle@enmo ~]$ sqlplus suxing/sky16

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 29 11:14:32 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

suxing@PROD>show user

USER is "SUXING"

suxing@PROD>select mysequence.currval from dual;

select mysequence.currval from dual

*

ERROR at line 1:

ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session

#新会话同样的先要查看nextval。

 

--新会话查看nextval：

suxing@PROD>select mysequence.nextval from dual;

   NEXTVAL

----------

        11

--新会话再次查看currval：

suxing@PROD>select mysequence.currval from dual;

   CURRVAL

----------

        11

#现在可以查看当前的序列号。

#每当更换到另外的一个会话查看序列号时候，都要重新执行查看nextval，接着才能查看currval。

 

--新回话尝试往测试表ID为当前序列号的一条记录：

suxing@PROD>insert into seqtest values(mysequence.currval,'susu',93.5,sysdate);

1 row created.

suxing@PROD>commit;

Commit complete.

 

--尝试查看测试表的数据记录：

suxing@PROD>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

 

suxing@PROD>select * from seqtest;

        ID NAME            SCORE CREATED

---------- ---------- ---------- -------------------

        11 susu             93.5 2016-11-29 11:22:46

 

--再往测试表中插入一条记录并查看新的数据记录：

suxing@PROD>insert into seqtest values(mysequence.nextval,'kkkk',98,sysdate);

1 row created.

suxing@PROD>commit;

Commit complete.

 

suxing@PROD>select * from seqtest;

        ID NAME            SCORE CREATED

---------- ---------- ---------- -------------------

        11 susu             93.5 2016-11-29 11:22:46

        12 kkkk               98 2016-11-29 11:27:04

 

--往测试表中插入第3条记录并查看：

suxing@PROD>insert into seqtest values(mysequence.nextval,'ookk',95.8,sysdate);

1 row created.

suxing@PROD>commit;

Commit complete.

 

suxing@PROD>select * from seqtest;

        ID NAME            SCORE CREATED

---------- ---------- ---------- -------------------

        11 susu             93.5 2016-11-29 11:22:46

        12 kkkk               98 2016-11-29 11:27:04

        13 ookk             95.8 2016-11-29 11:29:2

--以上在往表中插入数据记录的时候使用了sequence，其中
当插入table_name.currval时候，只是插入当前一个序列号，插入多条，多条的值时相同的，没有递增关系；
当插入table_name.nextval时候，插入的是当前序列号的下一个序列号，插入多条是各只不相同，有递增关系。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2129286/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2129286/