오라클 23ai 신기능 update 시 default on null for update

OS 환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : Oracle Database 23.4.0.24.05 ai Free

 

방법 : 오라클 23ai 뉴피처 update 시 default on null for update

오라클 23ai 부터 update 구문에 대해 DEFAULT ON NULL 기능을 사용할 수 있음
이 기능은 update 시 null 값을 넣게 되면 default 값으로 지정해둔 값으로 update 되는 기능임
12c 부터 insert 구문에 대해 DEFAULT ON NULL 기능을 사용할 수 있었고 23ai 부터는 update에 대해서도 이 기능 사용이 가능함
*12c 구문 "create table t1 (col3 varchar2(10) default on null 'test');"

 

 

테스트
샘플 테이블 생성

1 2 3 4 5 6 7 8

SQL> drop table t1 purge; create table t1 ( col1 number,  col2 varchar2(6) default 'test', col3 varchar2(6) default on null for insert only 'test', col4 varchar2(6) default on null for insert and update 'test' );

설명
default 'test' : insert 시 값을 넣지 않으면 기본값으로 'test' 를 넣음
default on null for insert only 'test' : default 'test' 기능 + insert 시 null을 넣으면 기본값으로 'test' 를 넣음
default on null for insert and update 'test' : default 'test' 기능 + insert, update 시 null을 넣으면 기본값으로 'test' 를 넣음

 

 

샘플 데이터 insert

1 2 3 4 5

SQL>  insert into t1 (col1, col2, col3, col4) values (1, 'A', 'A', 'A'); insert into t1 (col1, col2, col3, col4) values (2, null, null, null); insert into t1 (col1) values (3); commit;

 

 

테이블 조회

1 2 3 4 5 6 7

SQL> select * from t1;   COL1       COL2       COL3       COL4 ---------- ---------- ---------- ---------- 1          A          A          A 2                     test       test 3          test       test       test

위에서 설명한것 처럼
첫번째 row는 데이터를 모두 넣었기 때문에 데이터가 모두 존재함
두번째 row는 모두 null을 넣었기 때문에 col2에는 null이 들어가고, col3, col4에는 default 값이 들어감
세번째 row는 col1 컬럼값만 입력하고 다른값은 넣지 않았기 때문에 col2, col3, col4에 default 값이 들어감

 

 

샘플 데이터 update(col2)

1 2 3

SQL>  update t1 set col2 = null; commit;

 

 

테이블 조회

1 2 3 4 5 6 7

SQL> select * from t1;   COL1       COL2       COL3       COL4 ---------- ---------- ---------- ---------- 1                     A          A 2                     test       test 3                     test       test

col2 컬럼은 별다른 옵션을 지정하지 않았기 때문에 값들이 모두 null로 update 됨

 

 

샘플 데이터 update(col3)

1 2 3 4 5 6

SQL>  update t1 set col3 = null;               * ERROR at line 1: ORA-01407: cannot update ("SYS"."T1"."COL3") to NULL Help: https://docs.oracle.com/error-help/db/ora-01407/

col3 컬럼은 default on null for insert only 로 지정했기 때문에 에러가 발생함

insert 시에만 동작함, update 시에는 에러 발생함

 

 

샘플 데이터 update(col4)

1 2 3

SQL>  update t1 set col4 = null; commit;

 

 

테이블 조회

1 2 3 4 5 6 7

SQL> select * from t1;   COL1       COL2       COL3       COL4 ---------- ---------- ---------- ---------- 1                     A          test 2                     test       test 3                     test       test

col4 컬럼은 default on null for insert and update 'test'  를 지정했기 때문에 값들이 모두 test로 update 됨

 

 

컬럼 속성 조회

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SQL>  set lines 200 pages 1000 col column_name for a10 col nullable for a10 col data_default_vc for a10 col default_on_null for a20 col default_on_null_upd for a20 select column_name, nullable, data_length, default_length, data_default_vc, default_on_null, default_on_null_upd from dba_tab_columns where table_name = 'T1';   COLUMN_NAM NULLABLE   DATA_LENGTH DEFAULT_LENGTH DATA_DEFAU DEFAULT_ON_NULL      DEFAULT_ON_NULL_UPD ---------- ---------- ----------- -------------- ---------- -------------------- -------------------- COL1       Y                   22                           NO                   NO COL2       Y                    6              6 'test'     NO                   NO COL3       N                    6              6 'test'     YES                  NO COL4       N                    6              7 'test'     YES                  YES

DEFAULT ON NULL 설정시 not null 제약조건도 자동으로 생김(col3, col4에 대해 NULLABLE이 N임)
그리고 신기한점을 발견했는데 default 값이 'test'로 동일함에도 불구하고
DEFAULT_ON_NULL_UPD 이 YES인 컬럼(col4)은 DEFAULT_LENGTH가 다른값에 비해 1이 많음
col2,3,4 컬럼의 data_length 가 6인데도 default_length가 7인점도 신기함

 

 

참조 : 

3002987.1
https://docs.oracle.com/en/database/oracle/oracle-database/23/nfcoa/oracle-database-23c-new-features-guide.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/23/nfcoa/application_development.html#GUID-86014-1
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6__CJAIGEJE
https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/DBA_TAB_COLUMNS.html
https://blogs.oracle.com/connect/post/improved-defaults-in-oracle-database-12c
https://tuna.tistory.com/91