[SQL] 2. 단일행함수(일반함수)

2016. 9. 2. 17:31

일반함수

함수의 입력되는 값이 숫자, 문자, 날짜 구분없이 모두 사용할 수 있는 함수. 일반적으로 많이 사용하는 함수 위주.

NVL(컬럼, 치환할 값) : 컬럼값이 null일 경우, 해당 값으로 치환

NVL2(Col1,Col2,Col3) : Col1의 값이 null이 아니면 Col2를, null이면 Col3 출력

ex) emp 테이블에서 deptno 가 30번 인 사람들의 empno , ename , sal , comm 값을 출력하되 만약 comm 값이 null 이 아니면 sal+comm 값을 출력하고 comm 값이 null 이면 sal*0 의 값을 출력

SCOTT>SELECT empno, ename, sal, comm,
2          NVL2(comm,sal+comm,sal*0) "NVL2"
3  FROM emp
4  WHERE deptno = 30;

EMPNO ENAME             SAL       COMM       NVL2
---------- ---------- ---------- ---------- ----------
7499 ALLEN            1600        300       1900
7521 WARD             1250        500       1750
7654 MARTIN           1250       1400       2650
7698 BLAKE            2850                     0
7844 TURNER           1500          0       1500

DECODE() : 개발 언어에서 사용중인 IF 문을 오라클 SQL 안으로 가져온 함수. 오라클에서만 사용됨.

DECODE(A, B, '1', null) : A=B면 '1' 출력, 아니면 null출력 (null 생략가능)
DECODE(A, B, '1', '2') : A=B면 '1' 출력, 아니면 '2' 출력.

DECODE(A, B, '1', C, '2', '3') : A=B면 '1' 출력, A=C면 '2' 출력. 둘다아니면 '3' 출력.(조건과 결과 추가해 확장가능)

ex) Professor 테이블에서 교수의 이름과 학과 명을 출력하되 학과 번호가 101 번 이면 ‘Computer Engineering’ , 102 번이면 ‘Multimedia Engineering' , 103 번이면 ‘Software Engineering ‘ 나머지는 ‘ETC’ 로 출력

SCOTT>SELECT deptno, name, DECODE(deptno,101,'Computer Engineering',
2                                      102,'Multimedia Engineering',
3                                      103,'Software Engineering',
4                                          'ETC') "DNAME"
5  FROM professor;
DEPTNO NAME                      DNAME
------- --------------- -----------------

101 Audie Murphy              Computer Engineering
101 Angela Bassett            Computer Engineering
101 Jessica Lange             Computer Engineering
102 Winona Ryder              Multimedia Engineering
102 Michelle Pfeiffer         Multimedia Engineering
102 Whoopi Goldberg           Multimedia Engineering
103 Emma Thompson             Software Engineering
103 Julia Roberts             Software Engineering
103 Sharon Stone              Software Engineering
201 Meryl Streep              ETC
201 Susan Sarandon            ETC
202 Nicole Kidman             ETC
202 Holly Hunter              ETC

DECODE(A, B, DECODE(C,D, '1', null)) : A=B일 경우 C=D면 '1'출력, 아니면 null출력 (null생략가능)
- ex) professor 테이블에서 교수의 이름과 부서번호를 출력하고 101 번 부서 중에서 이름이 "Audie Murphy" 교수에게 "BEST!" 라고 출력하고 101번 부서 중에서 이름이 "Audie Murphy" 교수가 아닌 나머지에는 NULL 값을 출력하시오. 만약 101 번 외 다른 학과에 "Audie Murphy" 교수가 있어도 "BEST!" 가 출력되면 안됨.
```
SCOTT>SELECT deptno, name, DECODE(deptno, 101, DECODE(name,'Audie Murphy','BEST!',NULL)) "decode"
2  FROM professor;
DEPTNO NAME                 decode
-------- ------------ --------

101 Audie Murphy         BEST!
101 Angela Bassett
101 Jessica Lange
102 Winona Ryder
102 Michelle Pfeiffer
102 Whoopi Goldberg
103 Emma Thompson
103 Julia Roberts
```
DECODE(A, B, DECODE(C, D, '1', '2')) : A=B일 경우, C=D면 '1'출력, 아니면 '2'출력.
DECODE(A, B, DECODE(C, D, '1', '2'), '3') : A=B일경우, C=D면 '1'출력, 아니면 '2'출력, A=B아니면 '3'출력.
- ex) professor 테이블에서 교수의 이름과 부서번호를 출력하고 101 번 부서 중에서 이름이 "Audie Murphy" 교수에게 비고란에 “BEST!” 이라고 출력하고 101번 학과의 "Audie Murphy" 교수 외에는 비고란에 “GOOD!”을 출력하고 101번 교수가 아닐 경우는 비고란에 "N/A" 을 출력.
```
SCOTT>SELECT deptno, name,
2          DECODE(deptno, 101, DECODE(name, 'Audie Murphy', 'BEST!', 'GOOD!'), 'N/A') "ETC"
3  FROM professor;
DEPTNO NAME                 ETC
---------- -------------------- -----
101 Audie Murphy         BEST!
101 Angela Bassett       GOOD!
101 Jessica Lange        GOOD!
102 Winona Ryder         N/A
102 Michelle Pfeiffer    N/A
102 Whoopi Goldberg      N/A
```

CASE() : DECODE 함수는 주로 = 인 값을 처리하는데 주로 사용되고, CASE문은 크거나 작은 조건을 처리할 경우 쉽게 처리할 수 있음. DECODE 함수와는 다르게 함수내부가 콤마(,)로 조건들이 구분되지 않는것을 주의해야함.

 CASE(조건) WHEN 결과1 THEN 출력1
           [WHEN 결과2 THEN 출력2] 
            ELSE 출력3
 END "컬럼명"

ex1) decode와 동일하게 '='조건으로 사용되는 경우. (복잡해서 주로 decode로사용)

SCOTT>SELECT name, tel,
2          CASE(SUBSTR(tel,1,INSTR(tel,')')-1)) WHEN '02' THEN 'SEOUL'
3                                               WHEN '031' THEN 'GYEONGGI'
4                                               WHEN '051' THEN 'BUSAN'
5                                               WHEN '052' THEN 'ULSAN'
6                                               WHEN '055' THEN 'GYEONGNAM'
7                                                          ELSE 'ETC'
8          END "LOC"
9  FROM student
10  WHERE deptno1 = 201;
NAME                           TEL             LOC
---------------- ----------- -----------

Demi Moore                     02)6255-9875    SEOUL
Macaulay Culkin                02)312-9838     SEOUL
Wesley Snipes                  053)736-4981    ETC
Steve Martin                   02)6175-3945    SEOUL
Sean Connery                   02)381-5440     SEOUL
Christian Slater               031)345-5677    GYEONGGI

ex2) 비교조건이 '='이 아닌경우 (Student 테이블의 jumin 컬럼을 참조하여 학생들의 이름과 태어난 달 , 그리고 분기를 출력하세요. 태어난 달이 01-03월 은 1/4, 04 – 06월 은 2/4 , 07 – 09 월 은 3/4 , 10 – 12 월은 4/4 로 출력)

SCOTT>SELECT name, SUBSTR(jumin,3,2) "MONTH",
2          CASE WHEN SUBSTR(jumin,3,2) BETWEEN '01' AND '03' THEN '1/4'
3               WHEN SUBSTR(jumin,3,2) BETWEEN '04' AND '06' THEN '2/4'
4               WHEN SUBSTR(jumin,3,2) BETWEEN '07' AND '09' THEN '3/4'
5               WHEN SUBSTR(jumin,3,2) BETWEEN '10' AND '12' THEN '4/4'
6          END "Quarter"
7  FROM student;
NAME                           MONT Qua
------------------ --- ----

James Seo                      10   4/4
Rene Russo                     02   1/4
Sandra Bullock                 06   2/4
Demi Moore                     12   4/4
Danny Glover                   03   1/4
Billy Crystal                  01   1/4
Nicholas Cage                  04   2/4
Micheal Keaton                 09   3/4
Bill Murray                    01   1/4

decode와 비슷하지만 더 넓은 범위에서 사용할 수 있어 sql 튜닝에서 요긴하게 많이 사용됨. 함수를 많이 알수록 성능 좋은 쿼리를 작성할 수 있음.

QUIZ1

Professor 테이블에서 201번 학과 교수들의 이름과 급여, bonus , 총 연봉을 아래와 같이 출력하시오. 단 총 연봉은 (pay*12+bonus) 로 계산하고 bonus 가 없는 교수는 0으로 계산.

SCOTT>SELECT profno, name, pay, bonus, TO_CHAR(pay*12+NVL(bonus,0),'99,999') "TOTAL"
  2  FROM professor
  3  WHERE deptno = 201;

QUIZ2

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

SCOTT>SELECT empno, ename, comm, NVL2(comm,'Exist','NULL') "NVL2"
  2  FROM emp
  3  WHERE deptno = 30;

QUIZ3

Student 테이블을 사용하여 제 1 전공 (deptno1) 이 101 번인 학과 학생들의 이름과 주민번호, 성별을 출력하되 성별은 주민번호(jumin) 컬럼을 이용하여 7번째 숫자가 1일 경우 “ 남자” , 2일 경우 “여자 ” 로 출력하시오.

SCOTT>SELECT name, jumin, DECODE(SUBSTR(jumin,7,1),1,'MAN',2,'WOMAN') "Gender"
  2  FROM student
  3  WHERE deptno1 = 101;

QUIZ4

Student 테이블에서 1 전공이 (deptno1) 101번인 학생의 이름과 연락처와 지역을 출력하시오. 단,지역번호가 02 는 "SEOUL" , 031 은 "GYEONGGI" , 051 은 "BUSAN" , 052 는 "ULSAN" , 055 는 "GYEONGNAM".

  SCOTT>SELECT name, tel, DECODE(SUBSTR(tel,1,INSTR(tel,')')-1), '02', 'SEOUL',
  2                                                            '031', 'GYEONGGI',
  3                                                            '051', 'BUSAN',
  4                                                            '052', 'ULSAN',
  5                                                            '055', 'GYEONGNAM') "LOC"
  6  FROM student
  7  WHERE deptno1 = 101

QUIZ5

emp 테이블을 조회하여 empno , ename , sal , LEVEL(급여등급)을 아래와 같이 출력하시오. 단 급여등급은 sal 을 기준으로 1 - 1000 이면 Level 1 , 1001 - 2000 이면 Level 2 , 2001 - 3000 이면 Level 3 , 3001 - 4000 이면 Level 4 , 4001 보다 많으면 Level 5 로 출력

SCOTT>SELECT empno, ename, sal,
  2          CASE WHEN sal BETWEEN 1 AND 1000 THEN 'LEVEL1'
  3               WHEN sal BETWEEN 1001 AND 2000 THEN 'LEVEL2'
  4               WHEN sal BETWEEN 2001 AND 3000 THEN 'LEVEL3'
  5               WHEN sal BETWEEN 3001 AND 4000 THEN 'LEVEL4'
  6                                              ELSE 'LEVEL5'
  7          END "LEVERL"
  8  FROM emp
  9  ORDER BY sal DESC;

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

저작자표시 비영리 변경금지

'Oracle > sql' 카테고리의 다른 글

[SQL] 3. 복수행함수(그룹함수) (153)	2016.09.05
[SQL] 2. 단일행함수(정규식표현) (315)	2016.09.05
[SQL] 2. 단일행함수(숫자, 날짜, 형변환 함수) (993)	2016.08.30
[SQL] 2. 단일행함수(문자함수) (7)	2016.08.29
[SQL] 1. 오라클 서버접속, 기본 SELECT 구문 (9)	2016.08.24

justdoit