[Oracle] 단일 행 함수 - 일반 함수

# 일반 함수 

NVL 함수 

- NULL값을 다른 값으로 바꿀 때 사용하며, 데이터 타입이 일치해야 함 

SELECT ename, sal, comm 
  , sal + comm "total1" 
  , sal + NVL(comm, 0) "total2" 
  FROM emp;

   

NVL2 함수 

- exp1, exp2, exp3을 인자로 받아들이며, exp1이 null이 아닐 경우 exp2를 리턴하고, exp1이 null일 경우 exp3을 리턴 

- 데이터 타입이 일치해야 함 

SELECT ename, mgr, NVL2(mgr, mgr, 9999) "total3" 
  FROM emp;

   

NULLIF 함수 

- exp1, exp2가 일치할 경우 NULL, 불일치할 경우 exp1을 반환하며, 데이터 타입이 일치해야 함 

SELECT NULLIF('a', 'a'), NULLIF(10, 20) 
  --, NULLIF(10, 'a') 
  FROM dual;

 

COALESCE 함수 

- exp1, ... expN 중 첫번째 NULL이 아닌 값을 반환하며, 데이터 타입이 일치해야 함 

SELECT COALESCE(10, 20, 30) 
  , COALESCE(NULL, 20, 30) 
  , COALESCE(NULL, NULL, 30) 
  , COALESCE(NULL, NULL, NULL) 
  FROM dual;

  

GREATEST 함수, LEAST 함수 

- GREATEST : 하나 이상의 인수 중 가장 큰 값을 반환하며, 데이터 타입이 일치해야 함 

- LEAST : 인수 리스트 중 가장 작은 값을 반환하며, 데이터 타입이 일치해야 함 

SELECT GREATEST(50, 286, 3, 600), LEAST('ABC', '교육', 'aaa') 
  FROM dual;

   

DECODE 함수 

- 절차 구현을 가능하게 하는 함수로 IF문의 기능을 수행 

- FROM 절을 제외하고 사용 가능하며 = 연산만 가능 

- VALUE값이 IF1일 경우, THEN1값 / IF2일 경우 THEN2값 / 불일치할 경우 default값 / default가 없을 경우 NULL값 

SELECT ename, job, sal 
  , DECODE(job, 'ANALYST', sal * 1.1 
              , 'CLERK', sal * 1.2 
              , 'MANAGER', sal * 1.3 
              , 'PRESIDENT', sal * 1.4 
--              , 'SALESMAN', sal * 1.5 
              , sal) "DECODE 결과1" 
  , deptno 
  , DECODE(deptno, 30, sal * 1.1 
                 , 10, sal * 1.2 
                 , 20, sal * 1.3 
                 , sal) "DECODE 결과2" 
  , DECODE(deptno, 30, sal * 1.1 
                 , 10, sal * 1.2 
                 , 20, sal * 1.3) "DECODE 결과3" 
  FROM emp 
  ORDER BY job, deptno;

 

CASE식 

- 함수가 아닌 '식'이며, = 연산자 외의 조건식 또한 사용 가능 

- CASE WHEN 비교조건1 THEN 처리1 WHEN 비교조건2 THEN 처리2 ... ELSE 디폴트처리 END 

- 현재 CASE식과 DECODE 함수간의 크게 성능 차이가 발생하지 않음 

SELECT ename, job, deptno, sal 
  , DECODE(deptno, 30, sal * 1.1 
                 , 10, sal * 1.2 
                 , 20, sal * 1.3) "DECODE 결과" 
  , CASE WHEN deptno = 30 THEN sal * 1.1 
         WHEN deptno = 10 THEN sal * 1.2 
         WHEN deptno = 20 THEN sal * 1.3 
         ELSE sal 
    END "CASE 결과1" 
  , CASE deptno WHEN 30 THEN sal * 1.1 
                WHEN 10 THEN sal * 1.2 
                WHEN 20 THEN sal * 1.3 
    END "CASE 결과2" 
    FROM emp; 

SELECT ename, deptno, sal 
  , CASE WHEN sal < 1500                 THEN 'low' 
         WHEN sal >= 1500 AND sal < 3000 THEN 'med' 
         WHEN sal >= 3000                THEN 'high' 
    END "CASE 결과1" 
  , CASE WHEN sal < 1500                 THEN 'low' 
         WHEN sal >= 1500 AND sal < 3000 THEN 'med' 
         ELSE 'high' 
    END "CASE 결과2" 
  FROM emp;

 

# 연습문제 

사원 이름의 두번째 문자가 'A'이거나 'M'이 포함된 사원의 이름과 직무를 표시

-- like 사용
SELECT ename, job 
  FROM emp 
  WHERE ename LIKE '_A%' or ename LIKE '_M%'; 
  
-- SUBSTR 함수 사용
SELECT ename, job 
  FROM emp 
  WHERE SUBSTR(ename, 2, 1) IN ('A', 'M'); 

-- INSTR 함수 사용
SELECT ename, job 
  FROM emp 
  WHERE INSTR(ename, 'A', 2, 1) = 2  
     OR INSTR(ename, 'M', 2, 1) = 2;

   

사원의 이름, 급여, 커미션금액을 표시하는 문장을 작성하며, 커미션이 정해지지 않은 사원은 커미션 금액에 No Comm 이라고 표시

-- NVL 함수 사용
SELECT ename, sal, NVL(TO_CHAR(comm), 'No Comm') "COMM" 
  FROM emp; 
   
-- NVL2 함수 사용
SELECT ename, sal, NVL2(TO_CHAR(comm), TO_CHAR(comm), 'No Comm') "COMM" 
  FROM emp; 
 
-- COALESCE 함수 사용
SELECT ename, sal, COALESCE(TO_CHAR(comm), 'No Comm') "COMM" 
  FROM emp; 
   
-- DECODE 함수 사용
SELECT ename, sal 
  , DECODE(comm, NULL, 'No Comm' 
                        , comm) "DECODE 1" 
  , DECODE(NVL(comm, -99), -99, 'No Comm' 
                              , TO_CHAR(comm)) "DECODE 2" 
  FROM emp; 

-- CASE식 사용
SELECT ename, sal 
  , CASE WHEN TO_CHAR(comm) IS NOT NULL THEN TO_CHAR(comm) 
         ELSE 'No Comm'  
    END "COMM" 
  FROM emp;

   

사원의 이름, 입사일자, 입사요일을 표시하되 월요일부터 시작한 요일순으로 결과를 정렬

SELECT ename, hiredate, TO_CHAR(hiredate, 'day') 
  FROM emp 
  ORDER BY TO_CHAR(hiredate-1, 'd') ASC;