Oracle - NVL(), NVL2(), COALESCE(), 문자열 다루기

- NVL(a, b)

첫 번째 파라미터 값(a)이 NULL 이면, 두 번째 파라미터 값(b)을 반환한다. 첫 번째 파라미터 값(a)이 NULL 이 아니면, 그 값을 그대로(a) 반환한다. 

SELECT NULL "COL1", NVL(NULL, 10) "COL2", NVL(5, 10) "COL3" 
FROM DUAL; 
--==>> (null)   10      5 

SELECT NVL(출력값, null 일때 출력값)
FROM DUAL;

 

- NVL2(a, b, c)

첫 번째 파라미터 값(a)이 NULL 이 아닌 경우, 두 번째 파라미터 값(b)을 반환하고 첫 번째 파라미터 값(a)이 NULL 인 경우, 세 번째 파라미터 값(c)을 반환한다.

SELECT NVL2(NULL, 'NOT NULL', 'IS NULL') 
FROM DUAL;
--==>> IS NULL

SELECT NVL2(판별할거, 널이 아닐때 출력값, 널일때 출력값)
FROM DUAL;

 

- COALESCE()

매개변수 제한이 없는 형태로 인지하고 활용한다. 맨 앞에 있는 매개변수부터 차례로 NULL 인지 아닌지 확인하여 NULL 이 아닐 경우 그 값을 반환하고, NULL 인 경우에는 그 다음 매개변수의 값을 반환한다. 
NVL() 이나 NVL2() 와 비교했을 때 모~~~~~~~~~~든 경우의 수를 고려할 수 있다는 특징을 갖는다.

SELECT NULL"COL1" 
    , COALESCE(NULL, NULL, NULL, 40) "COL2" 
    , COALESCE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 100) "COL3" 
    , COALESCE(NULL, NULL, 30, NULL, NULL, 60) "COL4" 
    , COALESCE(10, NULL, NULL, NULL, NULL, 60) "COL5" 
FROM DUAL; 
--==>> (null) 40 100 30 10 

SELECT COALESCE(경우1, 경우2, 경우3, 경우4, ..., 모두 NULL이면 출력값)
FROM DUAL;

 

- 문자열의 결합, 컬럼과 컬럼의 결합

오라클에서는 문자 타입의 형태로 형(TYPE)을 변환하는 별도의 과정 없이  『||』 만 삽입해주면 간단히 컬럼과 컬럼(서로 다른 종류의 데이터)을 결합하는 것이 가능하다. 
cf)  MSSQL 에서는 모든 데이터를 문자열로 CONVERT 해야 한다.

SELECT '가' + '나' 
FROM DUAL; 
--==>> 에러 발생 
--     (ORA-01722: invalid number) 

SELECT '가' || '나' 
FROM DUAL; 
--==>> 가나

-- 컬럼 간의 결합도 가능
SELECT ENAME || '의 현재 연봉은 ' || COALESCE(SAL*12+COMM, SAL*12, COMM, 0) || '인데 희망 연봉은 ' 
       || COALESCE(SAL*12+COMM, SAL*12, COMM, 0)*2 || '이다.' 
FROM TBL_EMP; 
--==>> 
/* 
SMITH의 현재 연봉은 9600인데 희망 연봉은 19200이다. 
ALLEN의 현재 연봉은 19500인데 희망 연봉은 39000이다.
*/

 

- 문자열에서 홑따옴표 사용

문자열을 나타내는 홑따옴표 사이에서(시작과 끝) 홑따옴표 두개가 홑따옴표 하나(어퍼스트로피)를 의미한다.(JAVA에서 \\... 잊지 않으셨죠?) 즉, 홑따옴표 하나(『'』)는 문자열의 시작을 나타내고, 홑따옴표 두개(『''』)는 문자열 영역 안에서 어퍼스트로피를 나타내며 다시 마지막에 등장하는 홑따옴표 하나(『'』)는 문자열 영역의 종료를 의미하게 되는 것이다.

SELECT 'WOONA''s 지금 자고싶은디 ' || '안될랑가....' 
FROM DUAL;
--==>> WOONA's 지금 자고싶은디 안될랑가....

 

- 대소문자 변환

UPPER() : 모두 대문자로 반환 
LOWER() : 모두 소문자로 반환 

INITCAP() : 첫 글자만 대문자로 하고 나머지는 모두 소문자로 변환하여 반환 

SELECT 'oRaCLe' 
    , UPPER('oRaCLe') 
    , LOWER('oRaCLe') 
    , INITCAP('oRaCLe')
FROM DUAL; 
--==>> oRaCLe ORACLE oracle Oracle

 

- ASCII()

매개변수로 넘겨받은 해당 문자의 아스키 코드 값을 반환한다. 

SELECT ASCII('A') 
    , ASCII('B') 
    , ASCII('a') 
    , ASCII('b')
FROM DUAL; 
--==>> 65 66 97 98

 

- LIKE, WILDE CARD(CHARACTER)

문자열에 조건을 두어 검색 또는 활용...? 암튼 하고 싶을 때 LIKE를 사용한다.

『LIKE』와 함께 사용되는 『%』는 모든 글자를 의미하고 『LIKE』와 함께 사용되는 『_』는 아무 글자 한 개를 의미한다.

SELECT NAME 
FROM MY_MAN 
WHERE NAME LIKE '이%';
-- WHERE NAME LIKE '이__'; 대신 이땐 이로 시작하는 세글자의 이름만 검색 mz 답지 못한 아주 편협적인 사고방식
--==>> 이동욱

SELECT NAME
FROM MY_MAN
WHERE NAME LIKE '%욱';
-- WHERE NAME LIKE '__욱'; 대신 이땐 욱으로 끝나는 세글자의 이름만 검색
--==>> 이동욱

SELECT NAME
FROM MY_MAN
WHERE NAME LIKE '%동%';
-- WHERE NAME LIKE '_동_';
--==>> 이동욱

 

※ 데이터베이스 설계 과정에서 성과 이름을 분리하여 처리할 업무 계획이 있다면 테이블에서 성 컬럼과 이름 컬럼을 구분하여 구성해야 한다. 

ex)

'남궁' 성을 가진 당신 당첨입니다!

남궁민 : 저 남씬데요?(남궁민씨의 성은 남궁이 맞습니다만 여기서만...)

 

- ESCAPE

ESCAPE 로 정한 문자의 다음 한 글자를 와일드카드에서 탈출시켜라. 일반적으로 사용 빈도가 낮은 특수문자(특수기호)를 사용한다. 

SELECT * 
FROM TBL_WATCH 
WHERE BIGO LIKE '%99.99\%%' ESCAPE '\'; 
--==>> 금시계 순금 99.99% 함유된 최고급 시계 

SELECT * 
FROM TBL_WATCH 
WHERE BIGO LIKE '%99.99$%%' ESCAPE '$'; 

SELECT * 
FROM TBL_WATCH 
WHERE BIGO LIKE '%99.99@%%' ESCAPE '@';