8. 일반함수(2), 숫자함수

1. 조건문/함수

- if문, case문과 같이 조건별 명령을 다르게 하는 구문
- SQL은 if문 사용 불가(PL/SQL문에서는 가능)
- ORACLE에서는 case문과 decode를 사용한 조건처리

예) if문 형식(oracle 문법 아님)
if comm is null
   then 0
   else comm*1.1

 

1) decode
- 조건문 축약 함수
- 일치조건에 대한 처리만 가능(대소비교 불가)

** 사용법 : decode(대상,조건상수1,리턴1[,조건상수2,리턴2,.....,기타리턴])
- 기타리턴(else값) 생략 시 null 리턴

select deptno, 
       decode(deptno,10,'A'), 
       decode(deptno,10,'A','B'),
       decode(deptno,10,'A',20,'B','C')
  from emp
 order by deptno;

 

예제1) student 테이블을 사용하여 모든 학생의 이름, 제1전공번호, 주민번호, 성별 출력
단, 성별은 주민번호 컬럼을 사용하여 남자 또는 여자로 가공하여 출력한다.

sol)

 

select name, deptno1, jumin,
       decode(substr(jumin,7,1),'1','남자','여자') as 성별
  from student;

 

결과)

 

예제2) emp에서 각 직원의 이름, 급여, 보너스(comm) 출력
단, 보너스가 정의되지 않은 경우 100으로 출력(decode사용)

 

sol)
select ename, sal, comm,
       nvl(comm,100),
       nvl2(comm,comm,100),
       coalesce(comm,100),
       decode(comm,null,100,comm)
  from emp;

 

결과)

 

 

예제3) student 테이블에서 각 학생의 이름, 학년, 대표여부 출력
단, 대표여부 컬럼에는 4학년이면서 101번 학과소속(deptno1)은 대표로 출력, 101번이 아니면 부대표,
4학년이 아닌 경우는 일반회원으로 출력

 

sol)
select name, grade, deptno1,
       decode(grade,4,decode(deptno1,101,'대표','부대표'),'일반회원') as 대표여부
  from student;

 

결과)

 

 

다음에서 볼 수 있듯이 decode 문 안에 decode 를 두번 써서도 다음 결과를 만들 수 있다. 

하지만 실무에서 이런식으로 코드를 작성하기 보다는 case문을 이용해서 작성한다. 

 

2) case문

 

- SQL의 대표 조건문
- 다중 분기 처리 가능
- 대소 비교 조건 처리 가능

 

** 사용법  
case when 조건1 then 리턴1
     when 조건2 then 리턴2
     ....
     else 그외리턴
 end as 컬럼별칭

case when 조건1 then 리턴1
     when 조건2 then 리턴2
     ....
     else 그외리턴
 end as 컬럼별칭

** 축약 문법(항상 같은 대상에 대해 일치조건이 반복되는 경우 사용 가능)
case 대상 when 상수1 then 리턴1
         when 상수2 then 리턴2
         ....
        else 그외리턴
 end as 컬럼별칭

 

예제1)  emp에서의 모든 직원의 이름, 입사일, 부서명 출력
단, 부서명은 10번 부서인 경우 인사부, 20번은 총무부, 30번 재무부
sol)

select ename, hiredate, deptno,
       decode(deptno,10,'인사부',20,'총무부','재무부') as 부서명1,
       case when deptno = 10 then '인사부'
            when deptno = 20 then '총무부'
            else '재무부'
        end as 부서명2,
       case deptno when 10 then '인사부'
                   when 20 then '총무부'
                   else '재무부'
        end as 부서명3             
  from emp;
  

결과)

 

예제2)  student 테이블을 사용하여 모든 학생의 이름, 주민번호, 성별 출력
    (case문으로 출력)
sol)

select name, jumin,
       case when substr(jumin,7,1) = 1 then '남자'
            else '여자'
        end as 성별1,
       case substr(jumin,7,1) when '1' then '남자'
                              else '여자'
        end as 성별2
  from student;

 

결과)

 

위의 예제에서 볼 수 있듯이 case문을 이용하면 가독성이 더 좋아진 것을 확인 할 수 있따.

 

** 축약형 문법 주의
 비교 대상과 상수의 데이터타입이 일치해야 함
select name, 
       case substr(jumin,7,1) when 1 then '남자'    -- 에러 발생
                              else '여자'
        end as 성별2
  from student;

=> substr(jumin,7,1) 값은 문자타입인데 숫자상수(1)와 비교했기 때문에 에러 발생!

 

 

2. 숫자함수

 

1) 반올림/버림 : round, trunc

** 사용법 : round(숫자[,자리수])
- 자리수 생략 시 소수점 첫번째 자리에서 반올림/버림 발생
- 반올림 후 결과가 자리수가 되도록 자리수보다 아래에서 반올림 수행

select round(17.6),         
       trunc(17.6),          
       round(17.9876,2),     
       round(45678.98,-3)    
  from dual;

 

 

2) 올림/내림 : ceil, floor

** 사용법 : ceil(숫자)

- ceil은 특정값보다 큰값중 가장 작은 정수(올림)
- floor는 특정값보다 작은값중 가장 큰 정수(내림)

select ceil(2.7) as 결과1,    
       floor(2.7) as 결과2,   
       ceil(-2.7) as 결과3,    
       floor(-2.7) as 결과4   
  from dual;

 

3) 나머지 : mod

** 사용법 : mod(숫자1,숫자2)
- 숫자1을 숫자2로 나눈 나머지 리턴

select trunc(7/2) as 몫,
       mod(7,2) as 나머지
  from dual;

4) 절대값 : abs

** 사용법 : abs(숫자)

select abs(7) as 결과1,   
       abs(0) as 결과2,   
       abs(-7) as 결과3 
  from dual;

 

5) 양/음 판별 함수 : sign
- 양수이면 1, 음수이면 -1, 0이면 0 리턴

** 사용법 : sign(숫자)

select sign(10),  
       sign(0),        
       sign(-10)     
  from dual;

** sign 활용 예
예제) emp 테이블에서 급여 등급 출력
단, 급여등급은 3000 초과이면 A, 이하이면 B
select sal, sal - 3000, sign(sal - 3000)
  from emp
 order by sal desc;
 

 

select sal, 
       decode(sign(sal - 3000), 1, 'A','B') as sal_grade1,
       case when sal > 3000 then 'A'
            else 'B'
        end as sal_grade2
  from emp
 order by sal desc;

 

 

** 문자와 숫자의 형식 차이
select hiredate,
       to_char(hiredate,'MM') as 월1,
       extract(month from hiredate) as 월2
  from professor;
 

 

 

 

** 문자와 숫자 타입의 비교연산 시 차이
예제) professor 테이블에서 상반기에 입사한 교수의 이름, 입사일 출력
select name, hiredate
  from professor
 where to_char(hiredate,'MM') < 7;  

 정상(왼쪽 함수식에 to_number를 씌워서 숫자 변환후 숫자상수와 비교)

 

select name, hiredate
  from professor
 where to_char(hiredate,'MM') < '7';  

 

비정상(숫자를 문자형태로 비교하므로 정상적인 비교가 성립되지 X)

 

 

select name, hiredate
  from professor
 where extract(month from hiredate) < 7;

 

완벽(숫자와 숫자를 비교하므로)