[20130901]12C在表中使用sequence(补充).txt
对以前写的内容做一些补充。
SQL> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
1.测试1：
drop table t purge ;
create table t
(
id number GENERATED by default as identity ( start with 1 increment by 1),
name varchar2(10)
);
SQL> insert into t values (1,'a');
1 row created.
SQL> insert into t (name) values ('b');
1 row created.
SQL> commit ;
Commit complete.
SQL> insert into t (name) values ('c');
1 row created.
SQL> select * from t;
ID NAME
---------- --------------------
1 a
1 b
2 c
--可以发现如果定义GENERATED by default as identity，值是可以重复的，并且插入时可以显示的定义值。
2.测试2：
SQL> drop sequence seq1 ;
Sequence dropped.
SQL> CREATE SEQUENCE SCOTT.seq1 START WITH 10 INCREMENT BY 1 MINVALUE 10 CACHE 100 NOCYCLE ORDER ;
Sequence created.
SQL> CREATE TABLE T1
(
ID NUMBER DEFAULT seq1.nextval NOT NULL,
NAME VARCHAR2(10 BYTE)
);
SQL> insert into t1 values (1,'a');
1 row created.
SQL> insert into t1 (name) values ('b');
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from t1;
ID NAME
---------- --------------------
1 a
10 b
3.看看这种定义在11g下是否可行：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL> CREATE TABLE T1
(
ID NUMBER DEFAULT seq1.nextval NOT NULL,
NAME VARCHAR2(10 BYTE)
);
*
ERROR at line 3:
ORA-00984: column not allowed here
--11G不支持这种定义！