๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
ORACLE/SQL

[Oracle] Multiple INSERT ALL, FIRST ๋‹ค์ค‘ ํ–‰ ์‚ฝ์ž… ์ฐจ์ด :: ๋งˆ์ด์ž๋ชฝ

by ๐ŸŒปโ™š 2020. 3. 27.

์ „์ฒด INSERT & ์กฐ๊ฑด๋ถ€ INSERT

๋‹ค์ค‘ํ–‰ INSERT์—์„œ ALL๊ณผ FIRST๋กœ ๊ตฌ๋ถ„ํ•˜๊ธฐ ์ „์— ์ „์ฒด INSERT์™€ ์กฐ๊ฑด๋ถ€ INSERT๋กœ ๊ตฌ๋ถ„ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ๋ง๊ทธ๋Œ€๋กœ ์ „์ฒด๋Š” ๋ชจ๋“  ํ–‰์„ ์‚ฝ์ž…ํ•˜๋Š” ๊ฒƒ์ด๊ณ , ์กฐ๊ฑด๋ถ€ INSERT๋Š” ์กฐ๊ฑด์— ๋”ฐ๋ผ INSERTํ•  ์ˆ˜ ์žˆ๋Š” ๊ธฐ๋Šฅ์ž…๋‹ˆ๋‹ค. Multiple Insert๋ฅผ ์‹ค์Šตํ•ด๋ณด๊ธฐ ์œ„ํ•ด Oracle HR ๊ณ„์ •์„ ์‚ฌ์šฉํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

 

์ „์ฒด INSERT ALL

์ž„์‹œ ์‚ฌ์› ํ…Œ์ด๋ธ” ์ƒ์„ฑ

1
2
3
4
5
6
7
8
9
10
11
--ํ˜„์žฌ ๊ทผ๋ฌดํ•˜๋Š” ์‚ฌ์› ํ…Œ์ด๋ธ”
CREATE TABLE CURR_EMP
AS
SELECT
    EMPLOYEE_ID
    ,FIRST_NAME
    ,HIRE_DATE
    ,JOB_ID
    ,SALARY
FROM EMPLOYEES
WHERE 1=0;

Multiple Insert ์ž‘์—…์„ ์ง„ํ–‰ํ•  ํ…Œ์ด๋ธ”์€ ํ•˜๋‚˜ ์ƒ์„ฑํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

์ด ํ…Œ์ด๋ธ”์—๋Š” 05๋…„ 01์›” 01์ผ ์ดํ›„ ์ž…์‚ฌ์ž๋งŒ ๋„ฃ๊ฒ ์Šต๋‹ˆ๋‹ค.

 

INSERT ALL

1
2
3
4
5
6
7
8
9
10
11
12
13
--INSERT ALL
INSERT ALL
    INTO CURR_EMP VALUES(
        EMPLOYEE_ID
        ,FIRST_NAME
        ,HIRE_DATE
        ,JOB_ID
        ,SALARY
    )
    SELECT * FROM EMPLOYEES
    WHERE HIRE_DATE >= '05/01/01';
    
SELECT * FROM CURR_EMP;

 

 

์กฐ๊ฑด๋ถ€ INSERT

 ALL & FIRST ์ฐจ์ด

ALL๊ณผ FIRST์˜ ์ฐจ์ด๋Š” ์œ„ ๊ทธ๋ฆผ์œผ๋กœ ํ‘œํ˜„ํ•˜๊ณ  ์‹ถ์Šต๋‹ˆ๋‹ค. ํ”„๋กœ๊ทธ๋ž˜๋ฐ์„ ํ•ด๋ณด์‹  ๋ถ„์ด๋ผ๋ฉด ๋ฌด์Šจ ์˜๋ฏธ์ธ์ง€ ๋ฐ”๋กœ ์ดํ•ดํ•˜์…จ์„ ๊ฑฐ๋ผ๊ณ  ์ƒ๊ฐํ•ฉ๋‹ˆ๋‹ค.

ALL์€ WHEN THEN ๊ตฌ์ ˆ์˜ ์กฐ๊ฑด์„ ๋ชจ๋‘ ํ™•์ธํ•˜๊ณ  ํ•ด๋‹น๋˜๋Š” ๋ชจ๋“  ์กฐ๊ฑด์— INSERT ์ž‘์—…์„ ํ•ด์ค๋‹ˆ๋‹ค. FIRST๋Š” WHEN THEN ์กฐ๊ฑด์—์„œ ์ฒ˜์Œ ์กฐ๊ฑด์ด ์ฐธ์— ๋ถ€ํ•ฉํ–ˆ์„๋•Œ ํ•ด๋‹น ๊ตฌ์ ˆ์•ˆ์œผ๋กœ ๋“ค์–ด๊ฐ€ INSERT ์ž‘์—…์„ ํ•ด์ฃผ๊ณ  ๋๊นŒ์ง€ ์ฐธ์ธ ์กฐ๊ฑด์ด ์—†๋Š” ๊ฒฝ์šฐ ELSE ๊ตฌ์ ˆ์˜ ์ž‘์—…์„ ์ง„ํ–‰ํ•ฉ๋‹ˆ๋‹ค.

 

์ด๋ฒˆ์—๋Š” 05๋…„ 01์›” 01์ผ์„ ๊ธฐ์ค€์œผ๋กœ ์ด์ „์— ์ž…์‚ฌํ•œ ์‚ฌ์›๋“ค์€ ํ‡ด์‚ฌ์ฒ˜๋ฆฌ๋˜๋„๋ก ํ…Œ์ด๋ธ”์„ ํ•˜๋‚˜ ์ƒ์„ฑํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

1
2
3
4
5
6
7
8
9
10
11
12
--์€ํ‡ด ์‚ฌ์›
CREATE TABLE RETIRE_EMP
AS
SELECT
    EMPLOYEE_ID
    ,FIRST_NAME
    ,HIRE_DATE
    ,DEPARTMENT_ID
FROM EMPLOYEES
WHERE 1=0;
 
TRUNCATE TABLE CURR_EMP;

 

FIRST

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--์กฐ๊ฑด๋ถ€ INSERT FIRST
INSERT FIRST
    WHEN HIRE_DATE >= '05/01/01' THEN
        INTO CURR_EMP VALUES(
            EMPLOYEE_ID
            ,FIRST_NAME
            ,HIRE_DATE
            ,JOB_ID
            ,SALARY
        )
    ELSE
        INTO RETIRE_EMP VALUES(
            EMPLOYEE_ID
            ,FIRST_NAME
            ,HIRE_DATE
            ,DEPARTMENT_ID
        )
 

 

ALL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--์กฐ๊ฑด๋ถ€ INSERT ALL
INSERT ALL
    WHEN HIRE_DATE >= '05/01/01' THEN
        INTO CURR_EMP VALUES(
            EMPLOYEE_ID
            ,FIRST_NAME
            ,HIRE_DATE
            ,JOB_ID
            ,SALARY
        )
    WHEN HIRE_DATE < '05/01/01' THEN
        INTO RETIRE_EMP VALUES(
            EMPLOYEE_ID
            ,FIRST_NAME
            ,HIRE_DATE
            ,DEPARTMENT_ID
        )
    SELECT * FROM EMPLOYEES;

 

ALL ๋ฐฉ์‹์œผ๋กœ ํ•˜๊ฒŒ๋˜๋ฉด  ๋ชจ๋“  ์กฐ๊ฑด์„ ํ™•์ธํ•ฉ๋‹ˆ๋‹ค.

 

๋Œ“๊ธ€