오라클 SQL과 PL/SQL ( 일반 함수 - 1 )

일반함수는 함수의 입력되는 값이 숫자, 문자, 날짜 구분 없이 다 사용할 수있는 함수이다.
다양한 함수가 있지만 가장 일반적으로 많이 사용하는 함수를 살펴보겠습니다.

---

NVL() 함수 :  NULL값을 만나면 다른 값으로 치환해서 출력하는 함수

NVL(SAL, 0) : SAL컬럼의 값이 NULL일 경우 NULL 대신 0으로 치환
NVL(SAL,100) : SAL컬럼의 값이 NULL일 경우 NULL 대신 100으로 치환

COMM의 값이 있을 경우 기존의 COMM 값을 출력하고 COMM 값이 NULL일 경우 다른 값으로 바꾸어 출력이 되는 것을 볼 수 있다.

 

하지만 꼭 숫자값만 되는 것은 아니고 문자나 날짜도 가능하다.

NVL(POSITION, '사원') : POSITION값이 NULL일 경우 '사원'으로 치환
NVL(HIREDATE, '2014/05/01') : HIREDATE값이 없을 경우 2014/05/01로 치환

---

문제 1.
PROFESSOR테이블 에서 201번 학과 교수들의 이름과 급여, BONUS, 충 연봉을 아래와 같이 출력하세요. 
단 총연봉은 (PAY * 12 + BOUNS)로 계산하고 BONUS가 없는 교수는 0으로 계산하세요

SELECT PROFNO, NAME, PAY ,BONUS, TO_CHAR( (PAY * 12) + NVL(BONUS, 0), '999,999') "TOTAL"  FROM PROFESSOR WHERE DEPTNO = 201;

---

NVL2() 함수 : NVL함수의 확장으로 NULL 값이 아닐 경우 출력할 값을 지정할 수 있습니다.
COL1 값이 NULL이 아니면 COL2값을, NULL값이면 COL3를 출력합니다.

 

 

문제 2.
EMP테이블에서 DEPTNO가 30번인 사람들의 EMPNO, ENAME, SAL, COMM값을 출력하되 만약 COMM값이 NULL이 아니면, SLA + COMM값을 출력하고
COMM 값이 NULL 이면 SAL * 0 의 값을 출력하세요

SELECT EMPNO, ENAME, SAL,COMM, NVL2(COMM, SAL + COMM, SAL * 0) "NVL2" FROM EMP WHERE DEPTNO = 30;

 

문제 3.
EMP 테이블에서 DEPTNO가 30번인 사원들을 조회하여 COMM 값이 있을 경우 'EXIST'를 출력하고 COMM 값이 NULL 일경우 'NULL' 을 출력하세요.

SELECT EMPNO, ENAME, COMM, NVL2(COMM, 'EXIST', 'NULL') "NVL2" FROM EMP WHERE DEPTNO = 30;

---

DECODE() 함수 : IF문 - 오라클에서 사용하는 아주 중요한 분기문

 

유형 1 - A가 B일 경우 '1'을 출력하는 경우

DECODE(A, B, '1', NULL) / 단 마지막 NULL은 생략가능
A가 B면 1을 출력하고 아니라면 NULL을 출력합니다.

---

예제 1. PROFESSOR 테이블에서 학과번호와 교수명, 학과명을 출력하되, DEPTNO가 101번인 교수만 학과명을 "COMPUTER ENGINEERING"로 출력하고 101번이 아닌 교수들은 학과명에 아무것도 출력하지마세요.

SELECT DEPTNO, NAME, DECODE(DEPTNO, 101, 'COMPUTER ENGINEERING') "DNAME" FROM PROFESSOR;

위 화면을 보면 DEPTNO가 101번이면 COMPUTER ENGINEERING가 출력되고 아니면 빈값이 출력이 되었습니다.

---

유형 2 - A가 B일 경우 '1'을 출력하고 아닐 경우 '2'를 출력하는 경우
DECODE (A, B, '1', '2')

---

예제 2. DEPTNO가 101번인 교수만 학과명을 "COMPUTER ENGINEERING"로 출력하고 101번이 아닌 교수들은 학과명에 "ETC"로 출력하세요.

 

 

SELECT DEPTNO, NAME, DECODE(DEPTNO, 101, 'COMPUTER ENGINEERING', 'ETC') "DNAME" FROM PROFESSOR;

---

유형 3 - A 가 B 일경우 '1'을 출력하고 A 가 C일 경우 '2' 를 출력하고 둘다 아닐경우 '3'을 출력하는 경우
DECODE(A, B, '1', C, '2', '3')

---

예제 3. PROFESSOR테이블에서 교수의 이름과 학과명을 출력하되 학과번호가 101번이면 COMPUTER ENGINEERING, 102번이면 MULTIMEDIA ENGINEERING, 103번이면 SOFTWARE ENGINEERING , 나머지는 ETC로 출력하세요

SELECT DEPTNO, NAME, DECODE(DEPTNO, 101, 'COMPUTER ENGINEETING', 102, 'MULTIMEDIA ENGINEERING', 103, 'SOFRWARE ENGINEERING', 'ETC') FROM PROFESSOR;

---

유형 4 - A가 B일 경우 중에서 C가 D를 만족하면 '1'을 출력하고 C가 D가 아닐 경우 NULL을 출력하는 경우

( DECODE 중첩 ) DECODE(A, B, DECODE(C, D, '1', NULL)) / NULL 생략가능

---

예제 4. PROFESSOR테이블에서 교수의 이름과 부서번호를 출력하고 101번 부서 중에서 이름이 AUDIE MURPGY교수에게 'BEST!' 라고 출력하고 다른 교수에게는 NULL 값을 출력하세요
만약 101번 외 다른 학과에 AUDIE MURPGY교수가 있어도 'BEST!'가 출력되면 안됩니다.

 

SELECT DEPTNO, NAME, DECODE(DEPTNO, 101, DECODE(NAME, 'Audie Murphy', 'BEST!', NULL)) "ETC" FROM PROFESSOR;

---

유형 5. A가 B일 경우 중에서 C가 D를 만족하면 '1', C가 D가 아닐 경우 '2'를 출력하는 경우
DECODE ( A, B, DECODE (C, D, '1', '2') )

---

예제 5. PROFESSOR테이블에서 교수의 이름과 부서번호를 출력하고 101번 부서 중에서 이름이 'Audie Murphy' 교수에게 비고란에 'BEST!' 라고 출력한 다음 101번 학과의 'Audie Murphy' 교수외에는 비고란에 'GOOD!'을 출력하고 101번 교수가 아닐경우는 공란이 되게 출력하세요

SELECT DEPTNO, NAME, DECODE(DEPTNO, 101, DECODE(NAME, 'Audie Murphy', 'BEST!', 'GOOD!')) FROM PROFESSOR;

---

유형 6. A가 B일 경우 중에서 C가 D를 만족하면 '1'을 출력하고 C가 D가 아닐 경우 '2'를 출력하고 A가 B가 아닐 경우 '3'을 출력하는 경우
DECODE(A, B, DECODE(C, D, '1', '2'), '3')

---

예제 6. PROFESSOR테이블에서 교수의 이름과 부서번호를 출력하고 101번 부서 중에서 이름이 'Audie Murphy' 교수에게 비고란에 'BEST!' 라고 출력한 다음 101번 학과의 'Audie Murphy' 교수외에는 비고란에 'GOOD!'을 출력하고 101번 교수가 아닐경우는 'N/A'를 출력하세요.

 

SELECT DEPTNO, NAME, DECODE(DEPTNO, 101, DECODE(NAME, 'Audie Murphy', 'BEST!', 'GOOD!'), 'N/A') "ETC" FROM PROFESSOR;

---

총합 연습 문제.

 

문제 1.

STUDENT 테이블을 사용하여 제1전공(DEPTNO1)이 101번인 학과 학생들의 이름과 주민번호, 성별을 출력하되 성별은 주민번호 컬럼을 이용하여 7번째숫자가 1일경우 'MAN' 2일경우 'WOMAN'을 출력하세요.

SELECT NAME, JUMIN, DECODE(SUBSTR(JUMIN, 7, 1), 1, 'MAN', 'WOMAN') "GENDER" 

FROM STUDENT 

WHERE DEPTNO1 = 101;

---

문제 2. 

STUDENT 테이블에서 1전공이 101번인 학생의 이름과 연락처와 지역을 출력하세요, 단 지역번호가 02는 'SEOUL' 031은 'GYEONGGI', 051은 'BUSAN' 052는 'ULSAN', 055는 'GYEONGNAM' 으로 출력하세요.

 

SELECT NAME, TEL, DECODE( SUBSTR( TEL, 1, INSTR(TEL, ')' ,1) - 1 ), 02, 'SEOUL', 031, 'GYEONGGI', 051, 'BUSAN', 052, 'ULSAN', 055, 'GYEONGNAME') "ETC" 
FROM STUDENT 
WHERE DEPTNO1 = 101;