UPDATE 문장을 위한 Default 값 NULL 설정 가능
23c 부터 테이블 생성시 컬럼 정의할 때 사용하는 옵션 중 하나인 DEFAULT ON NULL 구문을
UPDATE 구문을 위해서 정의할 수 있게 되었습니다.
이전 버전에서는 INSERT 문장을 위해서만 정의할 수 있었는데 UPDATE 문을 위해서도 정의가 가능해졌습니다.
트리거를 통해서 구현을 했거나 개발자가 더 많은 코드를 사용해 정의한 부분을
테이블 정의에서 해결할 수 있게 되었다고 볼 수 있습니다.
이것 역시 개발 편의성 중에 하나가 아닐까 생각이 듭니다.
아래는 이 기능에 대한 테스트 내용입니다.
테스트 테이블을 생성해 보겠습니다.
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> create table test_employees
2 (employee_id number
3 ,first_name varchar2(20)
4 ,last_name varchar2(25)
5 ,hire_date date default sysdate
6 ,phone_number varchar2(20) default on null for insert only '000.000.0000'
7 ,email varchar2(25) default on null for insert and update 'xxx@koreaoug.org');
테이블이 생성되었습니다.
경 과: 00:00:00.02
|
23c 버전에서 default on null for insert and update 옵션으로 생성해 봤습니다.
아래는 21c 버전에서 같은 테이블을 생성해 보겠습니다.
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> create table test_employees
2 (employee_id number
3 ,first_name varchar2(20)
4 ,last_name varchar2(25)
5 ,hire_date date default sysdate
6 ,phone_number varchar2(20) default on null for insert only '000.000.0000'
7 ,email varchar2(25) default on null for insert and update 'xxx@koreaoug.org');
,phone_number varchar2(20) default on null for insert only '000.000.0000'
*
6행에 오류:
ORA-00936: 누락된 표현식
|
ORA-00936: 누락된 표현식 이라는 에러가 발생하며 테이블 생성이 되지 않습니다.
그러면 update 구문을 빼고 21c에서 다시 테이블을 생성해 보겠습니다.
|
1
2
3
4
5
6
7
8
9
|
SQL> create table test_employees
2 (employee_id number
3 ,first_name varchar2(20)
4 ,last_name varchar2(25)
5 ,hire_date date default sysdate
6 ,phone_number varchar2(20) default on null '000.000.0000'
7 ,email varchar2(25) default on null 'xxx@koreaoug.org');
테이블이 생성되었습니다.
|
default on null 구문만으로 생성이 가능하다. insert 문장에만 한정되는 옵션입니다.
이제 23c에서 이 옵션을 통해 만든 테이블이 어떻게 동작하는지 확인해 보겠습니다.
테스트 데이터를 생성하고 확인해 보겠습니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
SQL> insert into test_employees(employee_id, first_name, last_name) values(1, '철수','김');
1 개의 행이 만들어졌습니다.
경 과: 00:00:00.01
SQL> insert into test_employees values(2, '영희','김',null,null,null);
1 개의 행이 만들어졌습니다.
경 과: 00:00:00.00
SQL> commit;
커밋이 완료되었습니다.
경 과: 00:00:00.00
SQL> col first_name for a10
SQL> col last_name for a10
SQL> col phone_number for a15
SQL> col email for a20
SQL>
SQL> select employee_id
2 , first_name
3 , last_name
4 , hire_date
5 , phone_number
6 , email
7 from test_employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DAT PHONE_NUMBER EMAIL
----------- ---------- ---------- -------- --------------- --------------------
1 철수 김 23/04/26 000.000.0000 xxx@koreaoug.org
2 영희 김 000.000.0000 xxx@koreaoug.org
경 과: 00:00:00.01
|
hire_date, phone_number, email 값을 생략한 insert인 경우
모두 default 값으로 들어간 것을 확인할 수 있습니다.
하지만 위 세 컬럼을 명시적으로 null 정의해서 insert 할 경우
세 컬럼에 명시적으로 null 값이 insert 됩니다.
하지만 phone_number, email의 경우 insert 문장에 한해서 null 값이 들어온 경우
default 값으로 입력되게 정의해서 값이 들어간 것을 확인할 수 있습니다.
이제 update 문장 수행할 때 어떤 동작을 하는지 확인해 보겠습니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
SQL> update test_employees
2 set hire_date=null, phone_number=null, email=null;
set hire_date=null, phone_number=null, email=null
*
2행에 오류:
ORA-01407: NULL로 ("DB_MON"."TEST_EMPLOYEES"."PHONE_NUMBER")을 업데이트할 수 없습니다
경 과: 00:00:00.01
SQL> update test_employees
2 set hire_date=null, email=null;
2 행이 업데이트되었습니다.
경 과: 00:00:00.00
SQL> commit;
커밋이 완료되었습니다.
경 과: 00:00:00.00
SQL> select employee_id
2 , first_name
3 , last_name
4 , hire_date
5 , phone_number
6 , email
7 from test_employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DAT PHONE_NUMBER EMAIL
----------- ---------- ---------- -------- --------------- --------------------
1 철수 김 000.000.0000 xxx@koreaoug.org
2 영희 김 000.000.0000 xxx@koreaoug.org
경 과: 00:00:00.00
|
hire_date, phone_number, email 컬럼을 null 값으로 update 하면 에러가 발생을 합니다.
ORA-01407: NULL로 ("DB_MON"."TEST_EMPLOYEES"."PHONE_NUMBER")을 업데이트할 수 없습니다 라고 에러가 발생합니다.
phone_number 의 경우 default on null for insert only 옵션으로 컬럼 정의를 했기 때문에
null 값으로 update시 에러가 발생을 합니다.
그럼 phone_number 컬럼을 빼고 hire_date, email 컬럼만 null 값으로 update 할 경우는 문제 없이 update가 수행됩니다.
또 update 된 데이터를 확인해 보면 email 컬럼의 값이 null 이 아닌 default 값으로 되어 있는 것을 확인할 수 있습니다.
컬럼에 default on null 옵션을 정의할 수 있고 적용되는 경우도 insert 일 때만 적용할지 insert, update 일 때 적용할지 정의할 수 있습니다.
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 11 | (23c 신기능) GROUP BY 절에 컬럼 별칭이나 포지션 사용 | 명품관 | 2023.04.26 | 992 |
| » | (23c 신기능) UPDATE 문장을 위한 Default 값 NULL 설정 가능 | 명품관 | 2023.04.26 | 647 |
| 9 | (23c 신기능) PL/SQL 에서 SQL로 Transpiler 자동변환(Automatic PL/SQL to SQL Transpiler) | 명품관 | 2023.04.25 | 283 |
| 8 | (23c 신기능) INTERVAL 데이터타입에 집계함수와 분석함수 사용 가능 | 명품관 | 2023.04.20 | 389 |
| 7 | (23c 신기능) 23c에서 개발자를 위해 새로 추가된 DB_DEVELOPER_ROLE role | 명품관 | 2023.04.19 | 280 |
| 6 | (23c 신기능) IF EXISTS와 IF NOT EXISTS 사용 | 명품관 | 2023.04.18 | 1531 |
| 5 | (23c 신기능) Annotation 사용하기(comment와 유사한) | 명품관 | 2023.04.18 | 236 |
| 4 | (23c 신기능) Direct Joins for UPDATE and DELETE Statements(직접 조인을 사용한 UPDATE, DELETE) | 명품관 | 2023.04.12 | 224 |
| 3 | JSON-Relational Duality View 튜토리얼 | 명품관 | 2023.04.10 | 188 |
| 2 | 오라클 프로세스 prefix가 ora_ 에서 db_로 변경 | 명품관 | 2023.04.06 | 222 |
| 1 | Oracle Database 23c Free Install with Oracle Linux 8.2(리눅스 8.2 버전에서 오라클 23c 설치) [1] | 명품관 | 2023.04.05 | 1659 |