[20140823]12c null与缺省值.txt
--12c 当插入NULL时可以指定缺省值.不知道为什么设置这个特性,有点怪怪的.
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t (id number,idx number default on null 100);
insert into t(id) values (1);
insert into t(id,idx) values (2,200);
insert into t(id,idx) values (3,NULL);
commit;
SCOTT@test01p> select * from t;
ID IDX
---------- ----------
1 100
2 200
3 100
--可以发现如果插入idx=NULL,实际上插入是缺省值100.真不知道那个大客户提出这种需求....
select obj#,col#,segcol#,name,default$,type# from sys.col$ where
obj# in ( select object_id from dba_objects where owner=user and object_name='T')
order by col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
----- ----- ---------- ----- ---------- ----------
96076 1 1 ID NULL 2
96076 2 2 IDX 100 2
SCOTT@test01p> select dbms_metadata.get_ddl('TABLE', 'T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "ID" NUMBER,
"IDX" NUMBER DEFAULT 100 NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SCOTT@test01p> select constraint_name, constraint_type, deferrable, search_condition from user_constraints where table_name='T';
CONSTRAINT_NAME C DEFERRABLE SEARCH_CONDITION
-------------------- - -------------- ------------------------------
SYS_C0010756 C NOT DEFERRABLE "IDX" IS NOT NULL
--当DEFAULT NOT NULL 使用时 NOT NULL和NOT DEFERRABLE约束是隐含建立的.
