Oracle) 기록용 주요 SQL 문법

주요 SQL 문법

1. 다중 조건문 (CASE문)

CASE
    WHEN [조건1] THEN [조건1가 참일 경우 반환 값]
    WHEN [조건2] THEN [조건2가 참일 경우 반환 값]
    ELSE [조건들이 모두 거짓일 경우 반환 값]
END

2. 특정 문자열 포함 여부 확인

예시에서 검사하는 컬럼은 VARCHAR2 타입으로 간주한다.

• LIKE를 사용하는 경우 : 특정 컬럼의 값에 주어진 문자열이 속한다면 참을, 그렇지 않다면 거짓을 반환한다.
• -- WHERE절의 조건문으로 사용 SELECT * FROM ITEMS WHERE ITEM_NAME LIKE '%문자열%'; -- CASE문의 조건문으로 사용 SELECT CASE WHEN ITEM_NAME LIKE '%문자열%' THEN 'YES' ELSE 'NO' END FROM ITEMS;
• Cf. 주어진 문자열로 시작하는 경우는 ‘문자열%’, 주어진 문자열로 끝나는 경우는 ‘%문자열', 어디에든 들어가는 경우는 ‘%문자열%’
• INSTR를 사용하는 경우 : 첫 번째 인자인 컬럼의 값에 두번째 인자로 주어진 문자열이 속한다면, 해당 컬럼값에서 문자열이 존재하는 인덱스를 반환한다.
• SELECT INSTR(COL, '문자열') FROM DUAL;

3. 데이터 개수 제한하기 (MySQL의 LIMIT)

Oracle에서는 해당 테이블의 ROWNUM = 1인 로우만 조회

SELECT *** FROM (SELECT * FROM tableName ORDER BY colName DESC) WHERE ROWNUM = 1;**

4. INSERT시 중복될 경우 UPDATE하기 (MySQL의 ON DUPLICATE KEY UPDATE)

Oracle에서는 MERGE를 제공한다.

MERGE INTO 테이블명
    USING [테이블/뷰/서브쿼리]
        ON ([join condition])
    WHEN MATCHED THEN
        INSERT([columns ...])
        VALUES([values ...])
    WHEN NOT MATCHED THEN
        UPDATE SET
            col1 = 'a',
            col2 = 'b',
            ...

예를 들어, ITEM이라는 테이블에 데이터를 넣되, 중복된 데이터라면 이를 업데이트 한다고 생각해보자. 이 ITEM은 Item_ID, Item_Name, Item_Price, Item_Amount라는 컬럼이 있고, PK(Primary Key)는 Item_ID이다.

---------------
|ITEM        |
---------------
|Item_ID(PK)  |
|Item_Name    |
|Item_Price   |
|Item_Amount  |
---------------

MERGE INTO ITEM
    USING DUAL -- 하나의 테이블만 사용하기 때문에 DUAL을 사용한다.
        ON (Item_ID = #{itemId}
    WHEN MATCHED THEN
        UPDATE SET
            Item_Name = #{itemName},
            Item_Price = #{itemPrice},
            Item_Amount = #{itemAmount}
    WHEN NOT MATCHED THEN
        INSERT (Item_ID, Item_Name, Item_Price, Item_Amount)
        VALUES (#{itemId}, #{itemName}, #{itemPrice}, #{itemAmount})

5. DISTINCT하는 컬럼과 ORDER BY하는 컬럼이 다를 경우

• Distinct와 Order By를 동시에 쓰고자 할 때 Order By 조건으로 Select절에 나오지 않는 컬럼이 제공될 경우 오류를 발생시킨다. (SELECT식이 부적합합니다.)
• Group By를 함께 사용하면 Group By에는 Select한 컬럼이 모두 들어가야 한다. 이러면 결국 중복 제거가 되지 않는다.
• Distinct 하려는 컬럼만으로 Group By를 하고, 나머지 컬럼들은 Select절과 Order By절에는 넣되, MIN 함수를 사용한다.

SELECT DISTINCT ITEMGROUP, MIN(ITEMID), MIN(CAST(POSITION AS Integer))
FROM ITEMS
GROUP BY ITEMGROUP
ORDER BY MIN(ITEMID), MIN(CAST(POSITION AS Integer));

• 유사한 상황으로, 업데이트 시기를 나타내는 컬럼값(UPDATEDATE, TIMESTAMP 타입)을 내림차순하여 가장 첫번째 로우의 키(KEY_NAME)를 구하는 경우 아래와 같이 사용했다.

SELECT *
FROM (
    SELECT DISTINCT KEY_NAME
    FROM 테이블명
    GROUP BY KEY_NAME
    ORDER BY MIN(UPDATEDATE) DESC
)
WHERE ROWNUM = 1;

6. NULL과 함께 Sort(ORDER BY)

오라클에서는 기본적으로 Null값을 가장 큰 값으로 간주한다. 별도의 처리를 원할 때에는 아래와 같이 수정할 수 있다.

• NULL값인 데이터를 먼저 보이게 하기 위해서는 NULLS FIRST
• NULL값인 데이터를 가장 마지막에 보이게 하기 위해서는 NULLS LAST

Order BY [정렬하고자 하는 컬럼명] [DESC/ASC] NULLS FIRST/LAST

MySQL의 경우(오라클과 반대)

Cf. 참고(https://codingcoding.tistory.com/1134)

기본적으로는 값이 NULL인 경우가 가장 먼저 온다. 만약 NULL일 경우 맨 뒤에 나오게 하고 싶으면 IS NULL ASC를 사용한다.

7. 전체 컬럼 조회 시 임의의 값을 별도의 컬럼으로 추가하기

테이블을 정확하게 명시해줘야 오류 발생하지 않음

SELECT *, "TEST" AS NEWCOLNAME FROM ITEMS; -- error 발생
SELECT I.*, "TEST" AS NEWCOLNAME FROM ITEMS.I; -- 정상적으로 조회

8. NUMBER Type의 컬럼값을 Java에서 String으로 가져올 때 발생하는 문제

java.math.BigDecimal cannot be cast to java.lang.String 발생

해결 방안

쿼리 상에서 해당 값을 TO_CHAR()로 감싸서 가져온다.

Cf. DTO를 통해 가져온다면 애초에 문제되지 않겠지만, 문제가 되는 경우는 주로 Map으로 가져오기 때문에 발생하는 경우이다. 이 때는 애초에 문자열 타입으로 변환해서 가져오면 에러가 발생하지 않는다.

9. DROP TABLE IF EXIST 제공 X

MySQL(MariaDB)에서는 테이블이 존재할 경우 기존 객체를 제거하고 생성하기 위해 IF EXIST 사용 가능하다. 하지만 오라클에서는 같은 기능을 제공하지 않기 때문에 프로시저를 사용하거나 실제 테이블이 있는지 검사 후 처리해야 한다.

10. DROP TABLE CASCADE CONSTRAINTS

테이블 제거시 제약조건도 같이 제거되게 하기 위해서는 CASCADE CONSTRAINTS를 사용해야 한다.