[SQL] 6. 반복을 최소화 하기 - 단일 행 함수 - 형변환, NVL, CASE, DECODE

함수란?

• 쿼리를 작성하다 보면 자주 반복해서 사용하는 기능이나 코드가 너무 길어 매번 작성하기 어려운 기능들이 있습니다. 이런 기능들을 쉽게 사용하기 위해 사용하는 것이 함수 라는 개념입니다. 

• SQL 에서 사용할 수 있는 함수는 단일 행 함수와 복수 행 함수로 나누어집니다. 그 중 이번 포스팅에서 설명할 단일 행 함수는 단일 행에 대해서만 실행되며 행당 하나의 결과를 반환합니다. 단일 행 함수는 문자, 숫자, 날짜, 변환, 일반, 조건부 함수로 나눌 수 있으며 이번 포스팅에선 1편 에서 다루지 않은 함수들인 변환 함수와 일반 함수, 조건부 함수에 대해서 살펴보겠습니다. 

변환 함수 

• SQL 은 문자형, 숫자형, 날짜형 세 가지의 변수형을 사용하며, 세 가지 데이터 유형 변환 함수를 제공합니다. TO_CHAR(), TO_NUMBER(), TO_DATE() 로 총 세가지의 명시적 데이터 유형 변환 함수가 있습니다.

• TO_CHAR() : 숫자형에서 문자형으로, 날짜형에서 문자형으로 변환할 때에 사용하는 함수 입니다.

• 1. 숫자형 -> 문자형

• 숫자형을 변환하는 경우에는 TO_CHAR(number, format) 의 형태로 사용하며 format 에 들어가는 요소들은 다음과 같습니다.

• SELECT TO_CHAR(1932, 'L099,999.99') FROM DUAL; ->              \001,932.00

• SELECT TO_CHAR(1932, '$00000') FROM DUAL; -> $01932

• 2. 날짜형 -> 문자형

• 날짜형을 변환하는 경우에는 TO_CHAR(date, format) 의 형태로 사용하며 format 에 들어가는 요소들은 다은과 같습니다.

• SELECT TO_CHAR(to_date('90/02/12'), 'YEAR MONTH DAY') FROM DUAL; -> NINETEEN NINETY FEBRUARY MONDAY

• SELECT TO_CHAR(to_date('90/02/12'), 'YYYY-MM-DD') FROM DUAL; -> 1990-02-12

• TO_NUMBER( char, format ) : 문자형을 숫자형으로 변경할 때 사용하는 함수 입니다.
• TO_DATE( char, format ) : 문자형을 날짜형으로 변경할 때 사용하는 함수 입니다.
• SELECT TO_DATE('90/12/12', 'YY/MM/DD') FROM DUAL; ->         90/12/12

일반 함수 

• ORACLE 에서 일반 한수는 어떠한 데이터 유형에도 사용가능 하며 NULL 과 관련된 함수들을 말합니다. 일반 함수는 NVL, NVL2, NULLIF, COALESCE 로 총 4가지가 있습니다.

• NVL( args, replace ) : NVL 함수는 null 값을 다른 값으로 변환해주는 함수 입니다. args 의 값이 null 일 경우에 replace 값을 반환해 줍니다. 이 때 데이터 유형이 꼭 일치해야 합니다.

• SELECT NVL ( COMM, 0 ) FROM EMP; -> EMP 테이블에서 COMM 열의 값이 NULL 일 경우 0을 반환합니다. COMM 열은 숫자형 입니다.

• NVL2( args1, args2, args3 ) : NVL2 함수는 IF 함수와 비슷합니다. args1 이 NULL 이 아닐 경우에는 args2 를, NULL 일 경우에는 args3 을 반환하는 함수 입니다.
• SELECT NVL (COMM, 'SAL+COMM', 'ONLY SAL') FROM EMP; -> COMM이 NULL 이 아닐 경우에는 SAL + COMM 을 반환하고 NULL 일 경우에는 ONLY SAL 이라는 문자열을 반환합니다.
• NULLIF( args1, args2 ) : NULLIF 함수는 args1 과 args2 가 동일하면 NULL 을 반환하는 함수 입니다. 만약 동일하지 않으면 args1 을 반환합니다.
• SELECT NULLIF( 'A', 'B' ) FROM DUAL; -> A
• SELECT NULLIF( 'A', 'A' ) FROM DUAL; -> NULL
• COALESCE( args1, args2, .... , argsN ) : COALESCE 함수는 NVL 함수를 엮어놓은 형태라고 생각하면 됩니다. args1 이 NULL 일 경우 args2 를 반환하는데, 혹 args2 가 NULL 이라면 args3 을 반환하는 함수 입니다. 다시 말해 앞의 인수가 NULL 이라면 바로 뒤의 인수를 반환하는 함수 입니다.

조건부 함수

• SQL 에서는 IF-THEN-ELSE 논리를 사용할 수 있습니다. 이 논리를 구현하기 위해 ORACLE 에서는 CASE 문과 DECODE 문을 사용합니다.

• CASE 문 : CASE 문은 ORACLE 뿐 아니라 다른 SQL 에서도 사용가능합니다. CASE 문은 문자형 열에 사용할 때와 숫자형 열에 사용할 때 조금 다르게 사용할 수 있습니다.

• 문자형 열 : CASE 뒤에 바로 열이름이 나오고 WHEN 뒤에는 연산자 없이 조건 문자열을 작성합니다.

SELECT CASE job WHEN 'CLERK' THEN 'A' 

WHEN 'MANAGER' THEN 'B'

ELSE 'C' END FROM EMP;

• 숫자형 열 : 문자형 열과는 달리 CASE 뒤에 열이름이 나오지 않습니다. 대신 WHEN 뒤에 열과 연산자 조건이 같이 나옵니다. 밑의 쿼리를 해석해보면 SAL 이 3000 보다 큰 경우에 HIGH, 3000 보다는 자기만 1500 보다는 큰 경우에 MID, 그 이외에는 LOW 를 출력한다는 의미입니다. 좌측부터 진행하며 조건을 검색합니다.

SELECT CASE WHEN SAL > 3000 THEN 'HIGH'

WHEN SAL > 1500 THEN 'MID'

ELSE 'LOW' END FROM EMP;

• DECODE 문 : DECODE 문은 ORACLE 에서만 사용가능한 함수입니다.  CASE 와 다른 것은 없지만 조금 더 간편하게 사용할 수 있다는 점이 장점입니다. 하지만 CASE 문에서 숫자형 열일 경우 처럼 값의 크기를 비교하는 조건문은 사용할 수 없습니다.

SELECT DECODE( JOB, 'SALESMAN', 'A',

'CLERK', 'B', 'C' ) FROM EMP;

• 이 쿼리는 JOB 이 'SALESMAN' 일 때는 'A' 를 반환하고 'CLERK' 일 때는 'B' 를 반환하며 'SALESMAN' 과 'CLERK' 둘 다 아닐 경우에는 'C' 를 반환하는 쿼리 입니다.