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
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai pdb 기동순서 조정 (0) | 2024.05.03 |
|---|---|
| 오라클 OCI 2024 Generative AI Professional 무료 강의 및 무료 자격증 (0) | 2024.05.02 |
| 오라클 19c 체크포인트 발생 상황 테스트(기타 체크포인트) (0) | 2024.04.12 |
| 오라클 19c 체크포인트 발생 상황 테스트(테이블스페이스 체크포인트) (0) | 2024.04.12 |
| 오라클 19c 체크포인트 발생 상황 테스트(스레드 체크포인트) (0) | 2024.04.10 |