OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.9.0.25.07 ai for Oracle Cloud and Engineered Systems
방법 : 오라클 23ai 신기능 insert set 구문과 by name position 구문
오라클 23ai 23.9 버전부터 insert into set 및 by name position 구문이 새롭게 도입되었음
이 기능은 기존의 insert into 구문보다 가독성이 높고, 직관적으로 어떤 값이 어떤 컬럼에 들어가는지 명확하게 표현할 수 있도록 도와주는 기능임
update 구문에서 사용되던 set 구문과 동일한 형식을 따르며, insert into에서도 동일하게 활용 가능함
서브쿼리 결과를 insert할 때 by name 절을 함께 사용하면, 컬럼 순서가 달라도 컬럼 이름을 기준으로 자동 매핑되어 더 이상 컬럼 순서를 맞출 필요가 없음
수백 개의 컬럼을 다뤄야 하는 경우에도 코드를 간결하고 명확하게 작성할 수 있어 유지보수성과 개발 효율성을 크게 향상시킬 수 있음
by name 구문(by_name_position_clause)은 서브쿼리를 이용한 insert 구문에서 컬럼 위치가 아니라 이름을 기준으로 소스와 타겟 컬럼을 매핑해주는 기능임
기존에는 select 절과 insert 대상 테이블의 컬럼 순서가 정확히 일치해야 했지만, 이제는 이름만 맞으면 순서와 무관하게 매핑됨
테스트
1. insert set 구문
2. insert set 구문 10053 트레이스 확인
3. by name position 구문
4. by name position 구문 10053 트레이스 확인
5. 에러 발생 상황
테스트
1. insert set 구문
샘플 테이블 생성
|
1
2
3
|
SQL>
drop table t1 purge;
create table t1 (col1 number, col2 varchar2(10));
|
기존 insert 구문
|
1
2
3
|
SQL> insert into t1 (col1, col2) values (1, 'A');
또는
SQL> insert into t1 values (1, 'A');
|
insert set 구문
예제1. 괄호 없이 단일 행 insert
|
1
2
3
|
SQL> insert into t1 set col1 = 1, col2 = 'A';
1 row created.
|
예제2. 괄호 포함 단일 행 insert
|
1
2
3
|
SQL> insert into t1 set (col1 = 1, col2 = 'A');
1 row created.
|
예제3. 다중 행 insert
|
1
2
3
4
5
|
SQL> insert into t1 set
(col1 = 1, col2 = 'A'),
(col1 = 2, col2 = 'B');
2 rows created.
|
set 절을 이용해 보다 명확하게 insert가 가능함
2. insert set 구문 10053 트레이스 확인
아래 쿼리 수행하여 10053 트레이스 확인
|
1
|
SQL> insert into t1 set col1 = 2, col2 = 'B'
|
결과 확인
|
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
35
|
$ vi 10053.trc
Final query after transformations: qb INS$1 (#0):******* UNPARSED QUERY IS *******
SELECT 0 FROM "IMSI"."T1" "T1"
kkoqbc: optimizing query block INS$1 (#0)
:
call(in-use=18424, alloc=36936), compile(in-use=70752, alloc=74608), execution(in-use=3448, alloc=4032)
Using Fast Optimize on query block INS$1 (#0)
cost: 1.000000
kkoqbc-end:
:
call(in-use=18424, alloc=36936), compile(in-use=70920, alloc=74608), execution(in-use=3448, alloc=4032)
kkoqbc: finish optimizing query block INS$1 (#0)
CBRID: T1 @ INS$1 - no blocking operation found
kkoadsDumpCtx(enter): dsCtx=0x7f350603beb0
sql_id=cxuf40vryvgbr totExe=0 (SPD cache hit = 0) succExe=0 failExe=0 toutExe=0 skipExe=0 failRecCt=0 foundEstCt=0 heapAllo=16416 heapUsed=5800 timeLimit=-1000 (ms) timeUsed=0 (ms) timeLeft=-1000 (ms) stopped=no
kkoadsDumpCtx(exit)
SPM: End hard parse: session cpu = 20000, cpu = 58628, elapsed = 5524133188467, consitent reads = 613, current reads = 6, bg = 619 Stats for sql_id cxuf40vryvgbr: execs = 0, result cache execs = 0, eofCnt = 0, cpu = 0, bg = 0, elaps = 0 xsc = 0x7f3503eea1e0, kxsCurrentCursor = 0x7f3503eea1e0
...
user_id=147 user_name=IMSI module=SQL*Plus action=
sql_id=cxuf40vryvgbr plan_hash_value=0(0) problem_type=0 problem_desc=INVALID PROBLEM command_type=2 command_name=INSERT
----- Current SQL Statement for this session (sql_id=cxuf40vryvgbr) -----
insert into t1 set col1 = 2, col2 = 'B'
----- Parser State -----
Parser state1: len1=0 len2=40 pos1=39 pos2=39
Parser state2: flg=0x0 xflg=0x4380000 xxflg=0x0 3xflg=0x80
Parser state3: ptty=2718 tty=0 tlen=1
Parser state4: vdepth=0 pdepth=0
Parser errs: err1=0 err2=0 err3=0
Parser string: prx=0x7f35066dcd20 base=0x0 cur=0x7ffd7d476f2f
sql_text_length=40
sql=insert into t1 set col1 = 2, col2 = 'B'
----- Explain Plan Dump -----
|
from 절 없는 select 문의 경우 10053 트레이스를 보면 내부적으로 dual 구문을 넣어 실행 되었었는데
insert set 절의 경우 "SELECT 0 FROM "IMSI"."T1" "T1"" 내용은 보이지만 insert 구문 자체가 기존 구문처럼 변하거나 하는 내용은 보이지 않았음
3. by name position 구문
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
|
SQL>
create table emp_history (
ename varchar2(10),
job_title varchar2(9),
salary number(7,2),
hire_date date,
dept_no number(2)
);
|
예제 1. by name 없이 insert(순서 맞춰야 함)
|
1
2
3
4
5
|
SQL>
insert into emp_history (ename, job_title, salary, hire_date, dept_no)
select ename, job, sal, hiredate, deptno from emp where empno = 7788;
1 row created.
|
예제 2. 컬럼 나열 + by name 사용 insert(순서 달라도 매핑됨)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
insert into emp_history (ename, job_title, salary, hire_date, dept_no)
by name
select
job as job_title,
deptno as dept_no,
ename,
sal as salary,
hiredate as hire_date
from emp
where empno = 7788;
1 row created.
|
예제 3. 컬럼 미나열 + by name 사용 insert(순서 달라도 매핑됨)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
insert into emp_history
by name
select
job as job_title,
deptno as dept_no,
ename,
sal as salary,
hiredate as hire_date
from emp
where empno = 7788;
1 row created.
|
테이블 확인
|
1
2
3
4
5
6
7
|
SQL> select * from emp_history;
ENAME JOB_TITLE SALARY HIRE_DATE DEPT_NO
---------- --------- ---------- ------------------ ----------
SCOTT ANALYST 3000 13-JUL-87 20
SCOTT ANALYST 3000 13-JUL-87 20
SCOTT ANALYST 3000 13-JUL-87 20
|
모두 정상적으로 삽입됨
4. by name position 구문 10053 트레이스 확인
아래 쿼리 수행하여 10053 트레이스 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
insert into emp_history (ename, job_title, salary, hire_date, dept_no)
by name
select
job as job_title,
deptno as dept_no,
ename,
sal as salary,
hiredate as hire_date
from emp
where empno = 7900;
|
결과 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
$ vi 10053.trc
Final query after transformations: qb INS$1 (#0):******* UNPARSED QUERY IS *******
SELECT 0 FROM "IMSI"."EMP_HISTORY" "EMP_HISTORY"
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=20608, alloc=32832), compile(in-use=102664, alloc=106432), execution(in-use=4968, alloc=8080)
kkoqbc-subheap (create addr=0x7f9c0c50f6e0)
****************
QUERY BLOCK TEXT
****************
select
job as job_title,
deptno as dept_no,
ename,
sal as salary,
hiredate as hire_date
from emp
where empno = 7900---------------------
QUERY BLOCK SIGNATURE
|
from 절 없는 select 문의 경우 10053 트레이스를 보면 내부적으로 dual 구문을 넣어 실행 되었었는데
by name position 구문의 경우 "SELECT 0 FROM "IMSI"."T1" "T1"" 내용은 보이지만 insert 구문 자체가 기존 구문처럼 변하거나 하는 내용은 보이지 않았음
5. 에러 발생 상황
예제1. alias를 잘못 작성한 경우
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
insert into emp_history
by name
select
job as job_title,
deptno as dept_no,
ename emp_name, --잘못된 alias 삽입
sal as salary,
hiredate as hire_date
from emp
where empno = 7788;
*
ERROR at line 1:
ORA-00904: "EMP_NAME": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/
|
예제2. 표현식에 별칭(alias)를 지정하지 않은 경우
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
insert into emp_history (ename, job_title, salary, hire_date, dept_no)
by name
select
job as job_title,
deptno as dept_no,
ename,
sal * 2, --표현식에 alias 미삽입
hiredate as hire_date
from emp
where empno = 7788;
*
ERROR at line 7:
ORA-63875: Non-aliased expressions or constants are not allowed in a BY NAME
clause.
Help: https://docs.oracle.com/error-help/db/ora-63875/
|
예제3. 컬럼을 중복해서 기입한 경우
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
insert into emp_history
by name
select
job as job_title,
deptno as dept_no,
ename,
ename, --컬럼을 중복사용
hiredate as hire_date
from emp
where empno = 7788;
*
ERROR at line 7:
ORA-63876: No duplicated exposed column names are allowed in the BY NAME
clause.
Help: https://docs.oracle.com/error-help/db/ora-63876/
|
예제4. select 절 컬럼와 일치하지 않는 경우
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
insert into emp_history (ename, job_title, salary, hire_date, dept_no)
by name
select
job as job_title,
deptno as dept_no,
sal as salary,
hiredate as hire_date
from emp
where empno = 7788;
*
ERROR at line 4:
ORA-63877: The explicit column list does not exactly match the exposed column
names.
Help: https://docs.oracle.com/error-help/db/ora-63877/
|
결론 :
Oracle 23ai 23.9부터 도입된 insert set 및 by name 구문은 기존 insert 구문 대비 가독성과 명확성이 향상됨
insert set은 update와 동일한 형식으로 컬럼-값 매핑을 명시할 수 있어 코드 유지보수가 쉬워짐
by name 구문은 서브쿼리의 컬럼 순서에 관계없이 이름 기반으로 정확하게 매핑되므로 컬럼 수가 많아도 유용함
에러 메시지도 직관적이고 alias 누락이나 중복 컬럼 사용 시 명확한 원인을 안내해 개발자의 실수를 줄이는 데 도움이 될것으로 보임
참조 :
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=2071
https://docs.oracle.com/en/database/oracle/oracle-database/23/nfcoa/ru_23_9.html#GUID-104667-1
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/INSERT.html#SQLRF-GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3423
https://tuna.tistory.com/212
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 group by all 구문 (0) | 2025.07.27 |
|---|---|
| 오라클 23ai 신기능 uuid 생성 함수 (0) | 2025.07.27 |
| 오라클 19c(19.28) 신기능 (0) | 2025.07.22 |
| 오라클 11gR2 to 19c 업그레이드 시 preupgrade.jar 스크립트 사용법 (0) | 2025.06.22 |
| 오라클 23ai 신기능 smallfile 테이블스페이스 shrink (0) | 2025.06.21 |