[오라클 실습] 쿼리 공부하기(MERGE문, WITH절)

Orange for Oracle 이용, Oracle 문법 공부하기 🍊

소요 시간: 2시간

 

과제 1

MERGE문이란?

: 테이블에 데이터를 삽입할 때, 이미 해당 데이터가 존재하면 값을 INSERT 또는 UPDATE하는 구문

쿼리 하나만 사용하여 간단하게 INSERT, UPDATE 가능

- 오라클의 경우 DUAL이라는 dummy 테이블 제공

- 단일 테이블의 경우 USING 절에서 DUAL을 사용하여 구현 가능함. 또는 실제 테이블이 올 수도 있음.

- WHEN MATCHED는 조건이 일치할 때 수행할 작업, WHEN NOT MATCHED는 조건이 일치하지 않을 때 수행할 작업

 

바인드 변수(예시)

: 콜론으로 시작하는 식별자로, SQL 문에서 실제 값이 들어갈 자리를 표시함

- 다른 정답을 보면 바인드 변수를 이용하는 방법도 있음. SQL 문에서 값을 동적으로 전달하는 기능.

- 바인드 변수를 이용하면 실제 Java 코드 로직과 섞어서 동적으로 나타내기 편리함.

INSERT INTO departments (department_id, department_name)
VALUES (:dept_id, :dept_name)

 

과제 2

left join의 결과 값 뿐만 아니라, 다른 inner join, outer join, right join 등 결과를 알고 있어야 함.

 

과제 3

INSERT INTO SELECT문이란?

: 하나의 테이블에 저장된 데이터 일부 혹은 전체를 다른 테이블로 복사하여 삽입하는 SQL 구문

- WITH 절을 사용하지 않아도 무방함

INSERT INTO 대상테이블 (컬럼1, 컬럼2, ...)
SELECT 컬럼1, 컬럼2, ...
FROM 원본테이블
WHERE 조건;

 

과제4

서브쿼리란?

: UPDATE 문 안에 SELECT 문 등이 들어있는 구조

- 괄호 안의 서브쿼리가 먼저 실행됨(순서 중요!!)

 

문제와 코드

/* 과제1) 
PRODUCT_CATEGORIES 테이블에 CATEGORY_ID : 5, CATEGORY_NAME : 'PRINTER' 를 INSERT 하시오. 
단 기존에 데이터가 있는 경우 UPDATE 하는 sql을 작성 하시요. 힌트) MERGE문 사용 */
-- 1
select * from PRODUCT_CATEGORIES;

MERGE INTO PRODUCT_CATEGORIES PC
USING DUAL 
ON (PC.CATEGORY_ID=5)
WHEN MATCHED THEN
  UPDATE SET PC.CATEGORY_NAME='PRINTER'
WHEN NOT MATCHED THEN
   INSERT (PC.CATEGORY_ID, PC.CATEGORY_NAME)
   VALUES (5, 'PRINTER')
;

--1 다른 정답
MERGE INTO PRODUCT_CATEGORIES PC
USING (SELECT :catId AS CATEGORY_ID, :catName AS CATEGORY_NAME FROM DUAL) D 
ON (PC.CATEGORY_ID=D.CATEGORY_ID)
WHEN MATCHED THEN
  UPDATE
    SET PC.CATEGORY_NAME=D.CATEGORY_NAME
WHEN NOT MATCHED THEN
   INSERT (PC.CATEGORY_ID, PC.CATEGORY_NAME)
   VALUES (:catId, :catName)
;

/* 과제2) 
WITH문을 이용하여 임시테이블을 생성하고 PRODUCT_CATEGORIES 테이블과 
LEFT JOIN 하여 조회하는 sql 을 작성하시오.
임시테이블명은 TEMP1 로 하고 컬럼은 CATEGORY_ID, PRICE 로 하고 
데이터는 {(1, 10000), (2, 20000), (3, 30000), (4, 40000)} 로 한다.
WITH문 내에서는 DUAL 테이블 사용 */ 
-- 2
WITH TEMP1 AS
(
  SELECT 1 as CATEGORY_ID, 10000 as PRICE FROM DUAL UNION ALL
  SELECT 2, 20000 FROM DUAL UNION ALL
  SELECT 3, 30000 FROM DUAL UNION ALL
  SELECT 4, 40000 FROM DUAL
) 
SELECT *
  FROM TEMP1 T
  LEFT JOIN PRODUCT_CATEGORIES PC
    ON T.CATEGORY_ID=PC.CATEGORY_ID
;

/* 과제3) 
INSERT ~ SELECT문을 이용하여 PRODUCT_CATEGORIES 테이블에 
새로운 데이터를 등록하는 sql 을 작성하시오.
SELECT 에서 사용하는 테이블은 임시 테이블을 생성하여 사용하고 
임시 테이블의 명칭은 TEMP2으로 하고 
데이터는 {(6, 'KEYBOARD'), (7, 'LAN CARD'), (8, 'TV'), (9, 'MONITOR')} 로 한다. */
-- 3
select * from temp2;

INSERT INTO PRODUCT_CATEGORIES(CATEGORY_ID, CATEGORY_NAME)
WITH TEMP2 AS
(
  SELECT 6 as CATEGORY_ID, 'KEYBOARD' as CATEGORY_NAME FROM DUAL UNION ALL
  SELECT 7, 'LAN CARD' FROM DUAL UNION ALL
  SELECT 8, 'TV' FROM DUAL UNION ALL
  SELECT 9, 'MONITOR' FROM DUAL
)
SELECT T.CATEGORY_ID, T.CATEGORY_NAME
  FROM TEMP2 T
;

select * from PRODUCT_CATEGORIES;

/* 과제4)
COUNTRIES 테이블에 있는 region_name 컬럼이 현재 비어 있는데 
REGIONS 테이블을 읽어서 region_name 값을 갱신하는 sql 을 작성하시오.
힌트) UPDATE 문에서 SELECT 문 조인 */

-- 4
select * from COUNTRIES;
select * from REGIONS;

UPDATE COUNTRIES C
  SET C.REGION_NAME =
  (
    SELECT REGION_NAME 
    FROM REGIONS R
    WHERE C.REGION_ID=R.REGION_ID
  )
 WHERE  COUNTRY_ID LIKE 'A%'
;

 

힌트가 없더라도 해당 데이터를 추출할 수 있는 능력을 키우자