sql 단일행 함수 - 일반 함수

1. NVL 함수  

NULL 값을 만나면 다른 값으로 치환해서 출력하는 함수

NVL(칼럼, 치환할 값)

※치환 값이  숫자일 경우

NVL(sal, 0) -> sal 칼럼의 값이 null 일 경우 null 대신 0 으로 치환

NVL(sal, 100) -> sal 칼럼의 값이 null 일 경우 null 대신 0 으로 치환

※치환 값이 문자일 경우

NVL(position, '사원') -> position 값이 null 일 경우 '사원'으로 치환

※치환 값이 날짜일 경우

NVL(hiredate, '2014/05/01') -> hiredate 값이 null 일 경우 '2014/05/01' 날짜로 치환

NVL 함수 퀴즈

Professor 테이블에서 201번 학과 교수들의 이름과 급여, bonus, 총연봉을 아래와 같이 출력

단 총연봉은 (pay*12+bonus)로 계산하고 bonus 가 없는 교수는 0으로 계산

SELECT profno, name, pay, bonus, TO_CHAR(pay*12+NVL(bonus,0)) "총연봉" 

2 FROM professor 

3 WHERE deptno=201

NVL2 함수

NVL 함수의 확장판으로 NULL 값이 아닐 경우 출력할 값을 지정할 수 있음

NVL2( COL1, COL2, COL3)

= COL1 의 값이 NULL 이 아니면 COL2를, NULL 이면 COL3 을 출력

NVL2 함수 퀴즈

아래 화면과 같이 emp 테이블에서 deptno 가 30 번인 사원들을 조회하여 comm 값이 있을 경우 '상여금 있음' 을 출력하고 comm 값이 null 일 경우 '상여금 없음' 을 출력

SELECT empno,ename,comm,NVL2(comm,'상여금 있음','상여금 모름') "NVL2"

2 FROM emp

3 WHERE deptno=30

3. DECODE 함수

유형 1. A 가 B 일 경우 '1' 을 출력

-DECODE( A, B, '1', null) (단, 마지막 null은 생략 가능)

유형 2. A 가 B 일 경우 '1' 을 출력하고 아닐 경우 '2' 를 출력

-DECODE( A, B, '1', '2')

유형 3. A 가 B 일 경우 '1' 을 출력하고 A 가 C 일 경우 '2' 를 출력하고 둘 다 아닐 경우 '3' 을 출력

-DECODE ( A, B, '1', C, '2', '3' )

유형 4. A 가 B 일 경우 중에서 C 가 D 를 만족하면 '1' 을 출력하고 C 가 D 가 아닐 경우 NULL 을 출력

-DECODE ( A, B, DECODE( C, D, '1', null) )   null은 생략 가능

유형 5. A 가 B 일 경우 중 C 가 D 를 만족하면 '1'을 출력 C 가 D가 아닐 경우 '2' 를 출력

-DECODE ( A, B, DECODE( C, D, '1', '2,'))

답> SELECT name,jumin,DECODE(substr(jumin,7,1),1,' 남자','여자 ') "성별"

2  FROM student

3  WHERE deptno1=101 ;

답> SELECT name "이름",tel "연락처",DECODE(substr(tel,1,instr(tel,')')-1),02,'서울',031,'경기',051,'부산',052,'울산',055,'경남') "지역"

2 FROM student

3 WHERE deptno1=101

4. CASE 문

CASE 조건 WHEN 결과1 THEN 출력1

                [WHEN 결과2 THEN 출력2]

     ELSE 출력3

END " 칼럼명"

※DECODE 함수의 경우 함수 내부에 , 로 조건들이 구분 되었으니 CASE 표현식 내부는 콤마가 사용되지 않음

답> SELECT empno, ename, sal,

CASE WHEN (sal) between 1 and 1000 then 'level 1'

when (sal) between 1001 and 2000 then 'level 2'

when (sal) between 2001 and 3000 then 'level 3'

when (sal) between 3001 abd 4000 then 'level 4'

else 'level 5'

END "LEVEL"

from emp

order by sal desc;