함수/단일행 처리 함수) 기타 함수(nvl, nvl2, decode, case)

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

FUNCTION

- 일련의 작업 절차를 모아 놓은 database 객체

- 호출 시 인자를 전달하고 리턴 값을 받아 처리

- SQL의 function은 반드시 리턴 값을 가짐

함수 유형
단일행 처리 함수	문자 처리 함수
	숫자 처리 함수
	날짜 처리 함수
	형변환 처리 함수
	기타 함수
그룹 처리 함수	그룹마다 호출

---

단일행 처리 함수

- 매 행마다 함수 호출, 리턴된 값을 결과 집합에 반영

 

기타 함수

 

null 처리 함수

☞ nvl(value, null일 때 값), nvl2(value, not null일 때 값, null일 때 값)

select
    nvl('abc', 'xxx') "nvl",
    nvl(null, 'xxx') "nvl",
    
    nvl2('abc', 'hello', 'world') "nvl2",
    nvl2(null, 'hello', 'world') "nvl2"
from
    dual;

결과 값을 보면 유추할 수 있겠지만, nvl과 nvl2 함수의 차이점이 있습니다.

 

nvl 함수는 null일 때 대체 값('xxx') 출력, null이 아닐 때는 그대로 value 값('abc')가 출력됩니다.

nvl2 함수는 null일 때 대체 값 ('world') 출력, null이 아닐 때는 value 값('abc')이 아닌 대체 값 ('hello')가 출력됩니다.

 

응용) 사원 테이블에서 보너스 유무 조회(사원명, 보너스 유무)

select
    emp_name 사원명,
    nvl2(bonus, '유', '무') "보너스 유무"
from   
    employee;

선택함수

☞ decode(표현식, 값1, 결과값1, 값2, 결과값2, 값3, 결과값3 ... [기본값])

- [기본값] 생략 시 null로 대체

- 조건별로 동일한 자료형의 데이터를 반환해야 함

 

직급명
J1 대표
J2 부사장
J3 부장
J4 차장
J5 과장
J6 대리
J7 사원

select
    emp_name 사원명,
    decode(job_code, 'J1', '대표', 'J2', '부사장', 'J3', '부장', 'J4', '차장', 'J5', '과장', 'J6', '대리', '사원') 직급명
--    decode(job_code, 'J1', '대표', 'J2', '부사장', 'J3', '부장', 'J4', '차장', 'J5', '과장', 'J6', '대리', 'J7', '사원') --동일!!
from
    employee;

J1 ~ J6에 해당 하지 않으면 나머지는 사원이므로 기본값을 사원으로 지정하여 출력하거나,

아예 기본값을 빼고 다 지정해서 출력할 수도 있습니다.

 

사원별 성별 조회

select
    emp_name 사원명,
    decode(substr(emp_no, 8, 1), '1', '남', '3', '남', '여') 성별
from
    employee;

 

☞ case

- 조건별로 동일한 자료형의 데이터를 반환해야 함

타입1> 
(decode와 유사)
    case 표현식
            when 값1 then 결과값1
            when 값2 then 결과값2
            ...
            [else 기본값]
    end
    
타입2>
    case
            when 조건식1 then 결과값1
            when 조건식2 then 결과값2
             ...
            [else 기본값]
    end

 

사원별 성별 조회

case 타입 1)

-- 타입 1
select
    emp_name 사원명,
    
    case substr(emp_no, 8, 1)
        when '1' then '남'
        when '2' then '여'
        when '3' then '남'
        when '4' then '여'
    end 성별,
    
    case substr(emp_no, 8, 1)
        when '1' then '남'
        when '3' then '남'
        else '여'
    end 성별
from
    employee;

 

case 타입 2)

select
    emp_name 사원명,
    
    case
        when substr(emp_no, 8, 1) in ('1') then '남'
        when substr(emp_no, 8, 1) in ('2') then '여'
        when substr(emp_no, 8, 1) in ('3') then '남'
        when substr(emp_no, 8, 1) in ('4') then '여'
    end 성별,
    
    case
        when substr(emp_no, 8, 1) in ('1') then '남'
        when substr(emp_no, 8, 1) in ('3') then '남'
        else '여'
    end 성별
from
    employee;

 

select
    emp_id, emp_name, bonus,
    case
        when bonus is null then '해당사항없음'
        when bonus = 0 then '수당없음'
        when bonus > 0 then '수당 : ' || to_char(bonus, '0.0')
    end as bonus_txt
from
    employee;

---

사원 나이 조회

한국나이  = 현재년도 - 출생년도 + 1

먼저 나이를 구하기 전에 출생년도를 구해야하는데, 주민번호 앞자리를 참고하여 가져와야합니다.

select
    emp_name 사원명,
    emp_no 생년월일,
    extract(year from to_date(substr(emp_no, 1, 2), 'yy')) birth,  -- 2자리 년도를 가지로 현재 세기에서 유추(2000 ~ 2099)
    extract(year from to_date(substr(emp_no, 1, 2), 'rr')) birth	-- -- 2자리 년도를 가지고 (1950 ~ 2049)에서 유추
from
    employee;

추출해온 년도가 정말 이상하다는 걸 볼 수 있습니다.

특히 birth는 2062, 2063 .. 출생으로 가져온 것을 볼 수 있고, birth_1은 그에 비해 양반이지만 마지막 '고두밋'의 출생년도를 2047년으로 가져왔습니다.

그 이유는 'yy'는 2자리 년도를 가지고 현재 세기에서 유추(2000 ~ 2099)하고,

'rr'는 2자리 년도를 가지고 (1950 ~ 2049)에서 유추합니다.

따라서 50년생 미만인 사람들은 20으로 유추하게 되어 이상한 결과를 얻게 되는 것이죠.

 

이를 해결하기 위하여 아래처럼 해보았습니다.

substr(emp_no, 1, 2) + decode(substr(emp_no, 8, 1), '1', '1900', '2', '1900', '2000') birth

decode를 이용하여 주민번호 뒷자리의 첫번째 숫자가 '1' 혹은 '2'라면 1900을 더해주고 아니라면 2000을 더해주도록 하였습니다.

출생년도를 잘 추출했으니 이제 나이를 구하면 됩니다!

 

select
    emp_name 사원명,
    emp_no 생년월일,
--    extract(year from to_date(substr(emp_no, 1, 2), 'yy')) birth,  -- 2자리 년도를 가지로 현재 세기에서 유추(2000 ~ 2099)
--    extract(year from to_date(substr(emp_no, 1, 2), 'rr')) birth,    -- 2자리 년도를 가지고 (1950 ~ 2049)에서 유추
    substr(emp_no, 1, 2) + decode(substr(emp_no, 8, 1), '1', '1900', '2', '1900', '2000') birth,
    extract(year from sysdate) - (substr(emp_no, 1, 2) + decode(substr(emp_no, 8, 1), '1', '1900', '2', '1900', '2000')) + 1 age
from
    employee;