[Oracle] 고급함수

9. 고급함수

 

9.1 null값 처리함수

9.1.1 NVL

null값을 포함하는 컬럼을 특정한 값으로 전환하는 함수

 

-- 형식: nvl(null값을 포함하고 있는 컬럼이나 식, 전환하고자 하는 목표값)

 

(문제) emp 테이블에서 사원의 이름, 직책, 커미션 (수당) 을 나타내시오.

단, 커미션이 없는 사원은 3%의 커미션을 적용하시오.

SELECT ename, job, sal*nvl(comm, 3)/100 커미션
FROM Emp;

 

(문제) emp 테이블에서 사원의 이름, 직책, 연봉을 나타내시오.

SELECT ename, job, sal, comm, (sal*12 + comm) 연봉
FROM Emp;

 

-- nvl(A, B) A의 값이 null이면 B, null이 아니면 A

select ename, deptno, sal, comm, (sal*12 + nvl(comm, 0)) 연봉

 

9.1.2 nvl2

null값인 경우와 아닌 경우 모두 특정 값으로 변환하는 함수

 

-- 형식 : nvl2(null값을 포함하고 있는 컬럼이나 식, null이 아닌 경우의 값, null인 경우의 값)

 

(문제) emp 테이블에서 커미션이 원래 있는 직원은 5%를 적용하고 없던 직원은 3%를 적용하여 특별보너스를 계산하시오.

 

select ename, deptno, sal*nvl2(comm, 5, 3) / 100 커미션

from emp;

 

9.1.3 nullif

두 값을 비교하여 같으면 null, 다르면 첫번째 값을 반환하는 함수

 

-- 형식 : nullif(비교값1, 비교값2)

 

(문제) emp 테이블에서 이름이 '김'으로 시작하는 직원의 이름과 부서코드를 검색하시오.

 

select ename, deptno from emp

where nullif(ename, LTRIM(ename, '김')) is not null;

 

9.1.4 coalesce(코얼레스, 더 큰 덩어리로 합치다)

여러 개의 list 중 null이 아닌 첫번째 값을 돌려주는 함수

 

-- 형식 : coalesce(값1, 값2, 값3, ...)

 

(문제) emp 테이블에서 커미션이 있으면 커미션을, 만약 커미션이 없으면 해당 급여를, 커미션과 급여가 다 없는(null)인 경우는 임의로 20을 치환하여 사원의 이름 및 해당 치환값을 검색하시오.

 

select ename, comm, sal, coalesce(comm, sal, 20) 치환값

from emp;

 

 

9.2 비교 처리 함수

9.2.1 decode

값을 비교하여 해당하는 값을 돌려주는 함수. 단, 비교시에는 정확히 같은 값(=)만 비교가 가능함

-- 형식 : decode(컬럼이나 값, 비교값1, 결과값1,

비교값2, 결과값2,

.......        기본치)

** 기본치는 컬럼이나 값이 비교값과 맞지 않을 때 갖는 값이며 생략되면 null값을 가짐

 

(문제) emp 테이블에서 각 사원의 이름과 급여, 급여등급을 나타내시오.

단, 급여가 400만원 이상이면 A등급, 300만원 이상이면 B등급, 200만원 이상이면 C등급, 100만원 이상이면 D등급, 100만원 미만이면 E등급으로 나타내시오.

 

select ename, sal, decode(trunc(sal/100), 0, 'E'

1, 'D',

2, 'C',

3, 'B',

'A') 급여등급 from emp;

 

(문제) score 테이블에서 이름, 국어점수, 영어점수, 수학점수, 총점, 평균, 등급을 나타내시오. 등급은 평균점수가 90~100점이면 A등급, 80점대이면 B등급, 70점대이면 C등급, 60점대이면 D등급, 60점 미만이면 F등급으로 나타내시오.

 

 

 

 

 

9.2.2 case

decode 함수를 보완하여 大小(대소), like 등의 비교처리 가능한 함수

 

-- 형식 : case 컬럼이나 값 when 비교값1 then 치환값1

[ when 비교값2 then 치환값2

...

when 비교값n then 치환값n

else 기본치 ]

end

(문제) professor 테이블에서 각 교수의 이름과 직위, 급여총액(pay+bonus)을 나타내시오. 단, 이번달 급여는 정교수이면 급여총액의 10%를, 조교수이면 7%를, 전임강사이면 5%를 더하여 주고 급여가 많은 사람부터 나타내시오.

 

 

 

(문제) score 테이블에서 이름, 국어점수, 영어점수, 수학점수, 총점, 평균, 등급을 나타내시오. 등급은 평균점수가 90~100점이면 A등급, 80점대이면 B등급, 70점대이면 C등급, 60점대이면 D등급, 60점 미만이면 F등급으로 나타내시오.

 

 

 

9.3 순위를 구하는 함수

9.3.1 rank

order by를 포함한 query문에서 특정 컬럼에 대한 순위를 구하는 함수

 

-- 형식 : rank() over([partition by 컬럼]order by 컬럼)

-- 형식 : dense_rank() over([partition by 컬럼] order by 컬럼)

중복 rank를 무시함

 

partition by : 순위를 지정하기 위한 컬럼 그룹을 지정함

 

(문제) emp 테이블에서 전체 사원에 대하여 부서번호, 이름, 급여, 급여순위를 출력하시오.

 

(문제) emp 테이블에서 전체 사원에 대하여 부서번호, 이름, 급여, 해당부서 내 급여 순위를 출력하시오.