[복습] SQL | Oracle 조건을 나타내기, 일반함수(decode, case)

앞서 배운 함수를 다시 정리해보면,

1. 문자함수: upper, lower, initcap, replace, translate, lpad, trim, length, substr, instr 등

2. 숫자함수

  2-1) 단일행함수(1:1): round/trunc, ceil/floor, sign, abs

  2-2) 그룹함수: sum, avg, count, min/max

3. 날짜함수: sysdate, months_between, add_months, next_day, last_day

4. 변환함수: to_char, to_number, to_date

5. 일반함수: nvl, nvl2, 그리고 오늘 배운 decode, case

 

decode와 case를 공부하기 전에 잠시 묵시적 형 변환에 대하여 설명하겠다.

묵시적 형 변환은 보통 성능에 영향을 미치는데 아래와 같이 예를 들어보겠다.

select 1+'1'  는 1+ to_number('1')로  판단하여 계산되는데

이는 옵티마이저가 어떤 경로로 계산해야 가장 빠른 경로로 리턴하는가를 고민하게 되는데 여기서 to_number를 씌우는 것이 좋겠다고 판단하기 때문이다.

다음을 또 보면

select *

  from student

 where to_char(birthday, 'YYYY') = 2000;

는 의도하지 않았지만 수식이 to_number(to_char(birthday, 'YYYY'))를 또 씌운다.

그래서 성능이 느려지는 걸까?

 

어쨌든 여기서 중요한 것은, ★문자와 숫자를 동시에 비교할 때 숫자에 맞춰서 비교하려는 특징이 있다★는 것을 알아두면 좋다는 것이다.

 

 

이제 본격적으로 조건문에 대하여 공부해보자.

조건문은 조건에 따른 치환, 연산 등을 위한 문법이다.

예를 들어 emp 테이블에서 sal이 3000 이상이면 A등급, 2000에서 3000미만이면 B등급, 나머지는 C등급

이렇게 치환을 하기도 하며

sal이 3000 이상이면 10% 증가, 2000에서 3000미만이면 11% 증가, 나머지는 12% 연봉을 증가시킨다 와 같이 연산을 하기도 한다.

 

R에서는 다음과 같이 if를 사용하여 조건문을 만든다.

if문 in R

if(조건) {

              참일 때의 실행 명령어

               ......

} else {

            거짓일 때의 연산값 또는 리턴값 또는 실행명령어

}

 

PL/SQL 에서도 if문 사용이 가능하다. PL/SQL이란 절차적 언어이다.

예를들어

select SAL

  from EMP

 where ename = 'SMITH';

 

select SAL

  from EMP

 where ename = 'ALLEN;

 

Smith와 Allen의 sal을 합하고 싶다면 각각을 변수에 저장하여 두 값을 저장하는 과정을 거칠 수 있는데, 이러한 방법을 수행할 수 있는 언어가 절차적 언어이다.

SQL은 그냥 단독 결과만 확인할 수 있다.

같은 SQL이 붙어있지만 엄연히 다른 언어이며. PL/SQL은 그냥 단독 결과만 확인할 수 있다.

 

이렇듯 안타깝게도 SQL에는 if 문이 없다. 그러나 DBMS마다 if문을 대신하는 함수를 제공하며 oracle에서는 decode가 그 기능을 제공한다. 그리고 모든 DBMS가 if 문을 대신하는 case문이 있다.

 

먼저 decode를 보자.

decode는 oracle 조건함수이다. oracle에서만 사용할 수 있다.

decode(대상, 값1, 리턴1, 값2, 리턴2, ..., else)문법을 가지고 있다.

이를 해석하면 대상이 값1과 같으면 리턴 1을 출력하고, 값2와 같으면 리턴 2를 출력한다. 즉 짝 별로 나열하면 되고, 어떤 값과도 일치하지 않을 때 else를 리턴하게 된다. else를 생략하게 되면 null이 리턴된다.

 

예제를 풀어보자.

emp 테이블에서 deptno에 부서명을 부여하고자 한다. 각 직원의 이름, 부서 이름을 출력하여라. 

10번은 인사부, 20번은 총부무, 30번은 재무부이다.

10, 20, 30 부서번호를 전부 넣어 출력할 수 있으며(dname),

10, 20 만 넣고 나머지는 재무부로 넣을 수도 있다(dname2).

그리고 dname3처럼 인사부만 넣게되면 나머지는 null로 출력된다.

 

 

예제 한 문제를 더 풀어보자.

emp에서 각 직원의 급여, 인상된 급여를 출력하여라. 단, 부서마다 인상급여울이 다르다(10번부서: 10%, 20번부서 11%, 30번부서 12%). 인상된 급여는 소수점 첫 째 자리에서 반올림한다.

decode의 장점은 간편성이다. 개발자들은 라인 수가 중요하다고 한다. 그러나 사실 SQL은 라인수는 전혀 상관이 없다. 라인수가 많더라도 성능이 좋다면 좋은 쿼리이며, 가독성을 높일 목적으로 코드를 늘리기도 한다.

그러나 decode는 일치하는 조건 외에는 치환할 수 없다는 것이 가장 큰 단점이다.

대소를 포함한 조건 전달 불가하다는 의미이다. 예시로 살펴보자.

위와 같은 식은 잘못된 쿼리이다.

그렇다면 sal이 3000 이상 여부를 리턴하려면 어떻게 해야 하는가?

이럴 때 sign함수가 쓰인다.

이렇게, 또는

이렇게 쓰여질 수 있다.

 

decode 자체는 대소비교가 불가능하다. 따라서 decode + sign 조합으로 많이 풀어서 사용하며, 현업에서 많이 등장하는 표현이다.

곧 배울 case문에 대소 비교가 가능하므로 굳이 decode + sign을 가지고 쓸 필요는 없으나 개발자들이 이 조합을 굉장히 좋아한다고 한다.

 

그리고 decode 안에 decode를 사용하여 추가 조건 전달이 가능하다(리턴 위치에 들어감).

아래 예를 보자.

emp에서 10번 부서원이면서 job이 president이면 대표, 10번이면서 job이 그 외이면 대표후보, 10번 부서원이 아니면 조합원으로 출력해보자.

이렇게 사용이 가능하다.

 

 

다음으로 case문을 살펴보자.

case는 함수가 아니라 문장이다.

그리고 대소비교를 포함한 (between도 가능) 모든 조건을 전달할 수 있다.

else를 생략하면 null로 리턴한다.

그리고 반드시 end로 마무리 해야한다.

case문은 from 절을 제외한 모든 절에서 사용이 가능하다.

case문의 문법은 다음과 같다.

case when 조건1 then 리턴 1

         when 조건2 then 리턴2

                  .....

                            else 그 외 리턴

  end as alias (줄 맞추기)

 

예제로 바로 적용해보겠다. 아까 위에서 푼 부서번호를 부서명으로 치환하는 문제를 case문을 적용하여 다시 풀어보자.

여기서 성능과 관련하여 연급하자면 deptno와 10과의 데이터타입이 일치하여야 한다.

 

case문은 축약형 문법이 가능하다.

매 조건마다 대상이 같을 때, 그리고 모든 조건이 비교 조건일 때에만 축약형을 쓸 수 있다.

축약형 문법은 아래와 같다.

case 대상 when 값1 then 리턴1

                 when 값2 then 리턴2

                        .....

                                 else 그 외 리턴

 end as alias

 

위 예시를 축약형 case문으로 풀어보면

 

연습문제를 풀어보자.

student 테이블에서 각 학생의 이름, 학년, 성별을 출력하여라. 성별은 남자, 여자로 출력하여라.

(decode, case 둘 다 가능하면 두 가지 방법 모두 풀이할 것)

여기서 중요한 점은 남/여를 구분한 숫자 1/2가 그냥 1/2로 쓰이면 안되고 '1'/'2'로 쓰여야 한다는 것이다.

 case 축약형에서는 문자 숫자 묵시적 형 변환이 일어나지 않으므로 에러가 발생한다.

즉 비교대상과 값의 데이터 타입이 불일치 할 때 에러가 발생하므로 주의하여야 한다.

 

한 문제를 더 풀어보자.

emp 테이블에서 comm이 null일 경우 500, 아닐 경우 10% 인상된 comm을 출력하여라.

이건 내가 푼 방법인데, 선생님은 null자체를 case문에 넣으셨다.

그럼 다음과 같이 풀이할 수 있다.

여기서 강조하고자 한 건 축약형에서 comm이 null인 경우 null로 리턴된다는 것이다. 축약형 문법인 경우 null을 일치값으로 전달할 수 없다. 그러므로 축약형으로 쓰지 않거나 내가 풀이한 방법대로 써야 한다.

축약형 case가 아닌 일반 case문으로 작성해보자.

이렇게 작성하여야 한다. 사실 이 문제는 nvl2로 풀이가 가능하다.

select nvl2(comm, comm*1.1, 500) from emp;