231019 Oracle SQL INSERT 문, 다중테이블 INSERT, ALTER, MERGE, 컬럼 추가, 컬럼 타입·사이즈 수정 및 삭제

Data Base/Oracle SQL

231019 Oracle SQL INSERT 문, 다중테이블 INSERT, ALTER, MERGE, 컬럼 추가, 컬럼 타입·사이즈 수정 및 삭제

잇꼬 2023. 10. 19. 18:11

728x90

SMALL

■ 다중테이블 INSERT 문
A. SOURCE TABLE 에서 데이터를 추룰해서 여러개의 TARGET TABLE 에 데이터를 로드(INSERT) 하는 INSERT 문
B. ETL(Extraction:추출, Transformation:변형, Loading:적재)

기본 INSERT 문	INSERT 문	INSERT INTO 테이블명 VALUES (컬럼명1, 컬럼명2, ... 컬럼명N) ;
다중테이블 INSERT	무조건 INSERT ALL	INSERT ALL INTO 타켓테이블명 VALUES ( 컬럼명1, 컬럼명2, ... 컬럼명N ) SELECT 컬럼명1, 컬럼명2, .. 컬럼명N FROM 테이블명;
	조건 INSERT ALL	INSERT ALL WHEN 조건절 THEN INTO 테이블명 VALUES( 컬럼명1, 컬럼명2, ... ,컬럼명N ) WHEN 조건절 THEN INTO 테이블명 VALUES( 컬럼명1, 컬럼명2, ... ,컬럼명N ) ... WHEN 조건절 THEN INTO 테이블명 VALUES( 컬럼명1, 컬럼명2, ... ,컬럼명N ) SELECT 컬럼명1, 컬럼명2, .. 컬럼명N FROM 테이블명;
	FIRST INSERT	FIRST INSERT WHEN 조건절 THEN INTO 타켓테이블 VALUES( 컬럼명1, 컬럼명2, ... ,컬럼명N ) WHEN 조건절 THEN INTO 타켓테이블 VALUES( 컬럼명1, 컬럼명2, ... ,컬럼명N ) ... WHEN 조건절 THEN INTO 타켓테이블 VALUES( 컬럼명1, 컬럼명2, ... ,컬럼명N ) SELECT 컬럼명1, 컬럼명2, .. 컬럼명N FROM 테이블명;

1. 무조건 INSERT ALL

test table 생성

CREATE TABLE hr.sal_history
AS
SELECT employee_id, hire_date, salary
FROM hr.employees
WHERE 1 = 2;

SELECT * FROM hr.sal_history;

test table 생성

CREATE TABLE hr.mgr_history
AS 
SELECT employee_id, manager_id, salary
FROM hr.employees
WHERE 1 = 2;

SELECT * FROM hr.mgr_history;

INSERT 문을 통해 data 삽입

INSERT INTO hr.sal_history (employee_id, hire_date, salary)
SELECT employee_id, hire_date, salary
FROM hr.employees; 

SELECT * FROM hr.sal_history;

INSERT INTO hr.mgr_history (employee_id, manager_id, salary)
SELECT employee_id, manager_id, salary
FROM hr.employees;

SELECT * FROM hr.mgr_history;

단, 문제점 발생!

# 문제점: 데이터 과부하

이를 해결하기 위한 방법이

INSERT ALL

# DATA를 한번에 삽입

INSERT ALL 
INTO hr.sal_history (employee_id, hire_date, salary) VALUES(id, day, sal)
INTO hr.mgr_history (employee_id, manager_id, salary) VALUES(id, mgr, sal)
SELECT employee_id id, hire_date day, manager_id mgr, salary*1.1 sal
FROM hr.employees;

2. 조건 INSERT ALL

test table 생성

CREATE TABLE hr.emp_history
AS
SELECT employee_id, hire_date, salary
FROM hr.employees
WHERE 1 = 2;

CREATE TABLE hr.emp_sal
AS
SELECT employee_id, commission_pct, salary
FROM hr.employees
WHERE 1 = 2;

tip) test table 생성하고 나서는 SELECT 문으로 확인을 해보는 습관을 가자보자!

SELECT * FROM hr.emp_history;
SELECT * FROM hr.emp_sal;

# 컬럼명 별칭 생성

- 컬럼명이 길 때에는 별칭을 이용하자!

SELECT employee_id id, hire_date day, commission_pct comm, salary sal
FROM hr.employees;

INSERT ALL
WHEN day < to_date('2005-01-01', 'yyyy-mm-dd') AND sal >= 5000 THEN 
        INTO hr.emp_history(employee_id, hire_date, salary) VALUES (id, day, sal)
WHEN comm IS NOT NULL THEN
        INTO hr.emp_sal(employee_id, commission_pct, salary) VALUES (id, comm, sal)
SELECT employee_id id, hire_date day, commission_pct comm, salary sal
FROM hr.employees;

다른 쿼리문이나, 비슷한 실행창이 나온다.

SELECT employee_id FROM hr.emp_history
INTERSECT
SELECT employee_id FROM hr.emp_sal;

SELECT * 
FROM hr.emp_history h
WHERE EXISTS ( SELECT 'X'
               FROM hr.emp_sal
               WHERE employee_id = h.employee_id );

3. FIRST INSERT 문

test table 생성

CREATE TABLE hr.sal_low
AS
SELECT employee_id, last_name, salary
FROM hr.employees
WHERE 1 = 2;

CREATE TABLE hr.sal_mid
AS
SELECT employee_id, last_name, salary
FROM hr.employees
WHERE 1 = 2;

CREATE TABLE hr.sal_high
AS
SELECT employee_id, last_name, salary
FROM hr.employees
WHERE 1 = 2;

SELECT * FROM hr.sal_low;
SELECT * FROM hr.sal_mid;
SELECT * FROM hr.sal_high;

# 컬럼명 별칭 생성

SELECT employee_id id, last_name name, salary sal
FROM hr.employees;

# INSERT FIRST 문

INSERT FIRST
WHEN sal < 5000 THEN
         INTO hr.sal_low (employee_id, last_name, salary) VALUES (id, name, sal)
WHEN sal BETWEEN 5000 AND 10000 THEN
         INTO hr.sal_mid (employee_id, last_name, salary) VALUES (id, name, sal)
ELSE
         INTO hr.sal_high (employee_id, last_name, salary) VALUES (id, name, sal)
SELECT employee_id id, last_name name, salary sal
FROM hr.employees;

test table 생성

CREATE TABLE hr.oltp_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM hr.employees;

CREATE TABLE hr.dw_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM hr.employees
WHERE department_id = 20;

DESC HR.oltp_emp

■ 기존 테이블의 컬럼을 추가하는 방법
# 컬럼 추가: 문자타입

ALTER TABLE hr.oltp_emp ADD flag char(1);

# hr.oltp_emp 확인해보면 flag 컬럼안에는 data 가 없어 null 값으로 나온다.

SELECT * FROM hr.oltp_emp;

SELECT * 
FROM hr.oltp_emp 
WHERE employee_id IN (201, 202);

SELECT * FROM hr.dw_emp;

# 데이터 추가:UPDATE

UPDATE hr.oltp_emp
SET flag = 'd'
WHERE employee_id = 202;

UPDATE hr.oltp_emp
SET salary = 20000
WHERE employee_id = 201;

COMMIT;

[문제1] hr.oltp_emp 에 있는 사원들 중에 hr.dw_emp 테이블에 존재하는 사원들의 정보를 출력해주세요.

SELECT * 
FROM hr.oltp_emp o 
WHERE EXISTS ( SELECT 'x'
               FROM hr.dw_emp
               WHERE employee_id = o.employee_id );

[문제2] hr.dw_emp 에 있는 사원들 중에 hr.oltp_emp 테이블에 존재하는 사원들의 정보를 출력해주세요.

SELECT * 
FROM hr.dw_emp d
WHERE EXISTS ( SELECT 'x'
               FROM hr.oltp_emp
               WHERE employee_id = d.employee_id );

[문제3] hr.dw_emp 에 있는 사원들 중에 hr.oltp_emp 테이블에 존재하는 사원들은 hr.oltp_emp 에 급여를 기준으로 10% 인상한 값으로 수정해주세요.
# UPDATE 전 DATA 확인

SELECT * FROM hr.dw_emp;

# UPDATE 후 DATA 확인

UPDATE hr.dw_emp d
SET salary = (
                SELECT salary * 1.1
                FROM hr.oltp_emp
                WHERE employee_id = d.employee_id
);

SELECT * FROM hr.dw_emp;

[문제4] hr.dw_emp 에 있는 사원 중에 hr.oltp_emp 에 존재하는 사원에 flag 컬럼의 값이 'd' 인 사원에 대해서 삭제해주세요.

# flag 컬럼에 'd' 인 사원이 존재여부를 확인

SELECT * 
FROM hr.dw_emp d
WHERE EXISTS ( SELECT * 
               FROM hr.oltp_emp
               WHERE employee_id = d.employee_id
               AND flag = 'd' );

# 해당 되는 사원을 삭제

DELETE FROM hr.dw_emp d
WHERE EXISTS ( SELECT * 
               FROM hr.oltp_emp
               WHERE employee_id = d.employee_id
               AND flag = 'd' );

SELECT * FROM hr.dw_emp;

[문제5] hr.oltp_emp 테이블에 있는 데이터 중에 hr.dw_emp 테이블에 존재하지 않은 데이터들을 hr.dw_emp 테이블에 로드(INSERT)해주세요.

# 존재하지 않는 data 체크

SELECT * 
FROM hr.oltp_emp o
WHERE NOT EXISTS ( SELECT 'X'
                   FROM hr.dw_emp
                   WHERE employee_id = o.employee_id );

# 해당 data 를 로드

INSERT INTO hr.dw_emp ( employee_id, last_name, salary, department_id ) 
SELECT employee_id, last_name, salary, department_id
FROM hr.oltp_emp o
WHERE NOT EXISTS ( SELECT 'X'
                   FROM hr.dw_emp
                   WHERE employee_id = o.employee_id );

SELECT * FROM hr.oltp_emp;

■ MERGE (9i)
    A. INSERT, UPDATE, DELETE 문을 한꺼번에 수행하는 SQL 문
    B. 객체 권한이 있어야 한다. 소유자가 작업하지 X
    C. WHEN MATCHED THEN UPDATE 문, DELETE 문 ,INSERT 문
       WHEN NOT MATCHED THEN UPDATE 문, DELETE 문 , INSERT 문 작성하되, 구문을 하나만 작성하도 무방하다. (단, 두 구문을 쓸때에는 헷갈리지 않게 작성할 것.)
    D. Transaction 작동함.

# target table (실제 INSERT, UPDATE, DELETE )

SELECT * FROM hr.dw_emp;

# source table (SELECT)

SELECT * FROM hr.oltp_emp;

MERGE INTO hr.dw_emp d -- target table 
USING hr.oltp_emp o -- source table 기준
ON ( d.employee_id = o.employee_id ) -- key value 일치하면
WHEN MATCHED THEN /* 일치하면 */
        UPDATE /* target table 지정해줬기 때문에 작성하지 않음 */ SET
                d.salary = o.salary*1.1
        DELETE /* target table data */ WHERE o.flag = 'd'         
WHEN NOT MATCHED THEN /* 일치하지 않으면 */
        INSERT (d.employee_id, d.last_name, d.salary, d.department_id) /* INTO 를 쓰지 않는다. */
        VALUES (o.employee_id, o.last_name, o.salary, o.department_id);

# test table 생성

CREATE TABLE hr.emp (
    id    NUMBER ,
    name  VARCHAR2(30),
    day   DATE
);

DESC hr.emp

■ 컬럼 추가
# auto commit 기능

ALTER TABLE hr.emp ADD job_id varchar2(30);

# table 확인

SELECT * FROM user_tables WHERE table_name = 'EMP';

# 컬럼 확인

SELECT * FROM user_tab_columns WHERE table_name = 'EMP';

■ 컬럼 타입, 사이즈 수정

# MODIFY 키워드 사용

# ALTER TABLE 테이블명 MODIFY 컬럼명;

DESC hr.emp
SELECT * FROM user_tables  WHERE table_name = 'EMP';

# 사이즈 조절

ALTER TABLE hr.emp MODIFY job_id varchar2(20);

# 타입 수정

ALTER TABLE hr.emp MODIFY job_id char(30);

# 타입 수정 : 문자 타입의 컬럼을 숫자 타입의 털럼으로 수정이 가능한 이유는 데이터가 없기 때문에 가능하다.

ALTER TABLE hr.emp MODIFY job_id number;

■ 컬럼 삭제

# ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

ALTER TABLE hr.emp DROP COLUMN job_id;

# 삭제하고 나서 확인.

desc hr.emp
SELECT * 
FROM user_tab_columns
WHERE table_name = 'EMP';

728x90

LIST

저작자표시 비영리 동일조건

'Data Base > Oracle SQL' 카테고리의 다른 글

231019 Oracle SQL FOREIGN KEY 참조키, 외래키 (1)	2023.10.19
231019 Oracle SQL 제약 조건 PRIMARY KEY (0)	2023.10.19
231018 Oracle SQL 'INSA' 계정_생성, 수정, 권한 확인,TABLE 생성, INSERT 문, UPDATE 문, DELETE 문, COMMIT, ROLLBACK, Transaction (1)	2023.10.18
231018 Oracle SQL 'HR' 계정_생성, 삭제, 권한 부여, TABLE 생성 (1)	2023.10.18
231018 Oracle SQL 'SYS' 계정_생성, 수정, 삭제, 권한 부여, TABLE 생성 (1)	2023.10.18

현재글231019 Oracle SQL INSERT 문, 다중테이블 INSERT, ALTER, MERGE, 컬럼 추가, 컬럼 타입·사이즈 수정 및 삭제

250x250

일	월	화	수	목	금	토
	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

안녕? 나야 :)