오라클 SQL, 일반 함수 및 조건식, NVL, NVL2, DECODE, CASE, COALESCE, GREATEST, LEAST, USER, VSIZE

오라클 SQL, 일반 함수 및 조건식, NVL, NVL2, DECODE, CASE, COALESCE, GREATEST, LEAST, USER, VSIZE

​

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=613

 

오라클 SQL, 일반 함수 및 조건식, NVL, NVL2, DECODE, CASE, COALESCE, GREATEST, LEAST, USER, VSIZE

오라클 SQL, 일반 함수 및 조건식, NVL, NVL2, DECODE, CASE, COALESCE, GREATEST, LEAST, USER, VSIZE4.4 일반 함수 및 조건식(General Functions and Conditional Expressions)NVL(expr1, expr2) : IF expr1 IS NULL expr2 ELSE expr1…

ojc.asia

4.4 일반 함수 및 조건식(General Functions and Conditional Expressions)

NVL(expr1, expr2) : IF expr1 IS NULL expr2 ELSE expr1, expr1과 expr2의 DataType이 다르면 expr2를 비교전 expr1로 변환 한다.

NVL2(expr1, expr2, expr3): IF expr1 IS NULL THEN expr3 ELSE expr2, expr1은 어떠한 DataType 이라도 가능하며 expr2 및 expr3의 경우 LONG을 제외한 DataType이면 된다. 만약 expr2와 espr3가 DataType이 다르다면 비교전에 expr3을 expr2로 변환 한다.

--> 아래 예문은 NVL함수 안의 표현식의 형이 다르므로 오류가 발생 한다. SQL> select ename, nvl(comm, 'NOT APPLICABLE') "COMMISSION" 2 from emp 3 where deptno=30; select ename, nvl(comm, 'NOT APPLICABLE') "COMMISSION" * 1행에 오류: ORA-01722: 수치가 부적합합니다 SQL> 1 select ename, nvl(to_char(comm), 'NOT APPLICABLE') "COMMISSION" SQL> / ENAME COMMISSION ---------- ---------------------------------------- ALLEN 300 WARD 500 MARTIN 1400 BLAKE NOT APPLICABLE TURNER 0 JAMES NOT APPLICABLE 6 개의 행이 선택되었습니다. SQL> select ename, sal, comm, nvl2(to_char (comm), ‘sal and comm', ‘sal’) income 2 from emp 3 where deptno = 20; ENAME SAL COMM INCOME ---------- ---------- ---------- ------------ SMITH 800 96 SAL AND COMM JONES 2975 SAL SCOTT 3000 SAL ADAMS 1100 SAL FORD 3000 SAL --10번 부서의 사원 중 수당을 받는 사람은 급여(sal)와 수당(comm)을 더한 값을 출력하고 수당을 받지 않는 사원은 급여만 총액으로 출력하라. SQL> select ename, sal, comm, nvl2(comm, sal+comm ,sal) "total" from emp 2 where deptno = 10; ENAME SAL COMM total ---------- ---------- ---------- ---------- CLARK 2450 2450 KING 5000 5000 MILLER 1300 1300

DECODE (column|expr1, search1, result1 [,search2,result2,,,][,default return]) : column1이 search1과 같으면 result1, search2와 같으면 result2 아무것도 같지 않으면 default return을 돌려 주며 4개 이상의 아규먼트로 이루어 진다.

CASE: DECODE 함수와 기능이 비슷하지만 좀더 직관적인 함수이다.

SQL> select ename, decode(deptno, 10, 'Accounting', 2 20, 'Research', 3 30, 'Sales', 4 40, 'Operations', 5 'Unknown') department 6 from emp 7 / --> 아래의 CASE 함수와 동일한 결과가 나타난다. SQL> select ename, 2 (case deptno 3 when 10 then 'Accounting' 4 when 20 then 'Research' 5 when 30 then 'Sales' 6 when 40 then 'Operations' 7 else 'Unknown' 8 end) department 9 from emp 10 / ENAME DEPARTMENT ---------- ---------- SMITH Research ALLEN Sales …… 16 개의 행이 선택되었습니다. --> 10번 부서의 급여 평균이 800보다 크면 ‘H’, 작으면 ‘L’을 출력해 보자. SQL> select case 2 when (select avg(sal) from emp where deptno=10) >= 800 then 'H' 3 else 'L' 4 end "급여수준" 5 from dual; - H with a as ( select avg(sal) avg from emp where deptno = 10 ) select DECODE(SIGN(a.avg - 800), 1, 'H', -1, 'L', 'UnKnown') from a; SQL> SELECT ename, 2 (CASE EXTRACT(YEAR FROM hiredate) 3 WHEN 1982 THEN '8 years service' 4 WHEN 1981 THEN '9 years service' 5 WHEN 1980 THEN '10 years service' 6 END) AS "Award for 2000" 7 FROM emp 8 WHERE EXTRACT(YEAR FROM hiredate) IN (1982,1981,1980) 9 ORDER BY hiredate 10 / ENAME Award for 2000 ---------- ---------------- SMITH 10 years service ALLEN 9 years service WARD 9 years service JONES 9 years service …… SQL> SELECT empno, ename,(CASE 2 WHEN sal >= 5000 THEN 'High Sal' 3 WHEN sal >= 3000 AND sal < 5000 THEN 'Middle Sal' 4 WHEN sal >= 1000 AND sal < 3000 THEN 'Average Sal' 5 WHEN sal < 1000 THEN 'Low Sal' 6 END) AS Sal_Category 7 FROM emp; EMPNO ENAME SAL_CATEGOR ---------- ---------- ----------- 7369 SMITH Low Sal 7499 ALLEN Average Sal 7521 WARD Average Sal 7566 JONES Average Sal ……

NULLIF (expr1, expr2) : expr1과 expr2가 같은면 NULL. 같지 않으면 expr1을 리턴 한다.

SQL> select nvl(nullif('ORACLE','oracle'),'널입니다')from dual; NVL(NULL -------- ORACLE - 수당이 NULL인 사원은 SAL를, NULL이 아니면 sal+comm을 nullif, nvl2를 이용하여 출력하시오. SQL> select sal, comm, nvl2(nullif(sal, nvl(comm, 0)+sal),sal+comm,sal) from emp; SAL COMM NVL2(NULLIF(SAL,NVL(COMM,0)+SAL),SAL+COMM,SAL) ---------- ---------- ---------------------------------------------- 800 800 1600 300 1900 1250 500 1750 2975 2975 1250 1400 2650 2850 2850 2450 2450 ……

COALESCE (expr1, expr2,,,,) : 여러 expr중 처음 NULL 아닌 요소를 리턴 한다.

GREATEST (expr1, expr2,,,,) : 여러 expr중 최대인 것을 리턴 한다.

LEAST (expr1, expr2,,,,) : 여러 expr중 최소인 것을 리턴 한다.

-- EMP테이블에서 COMM이 NULL이 아니면 COMM을 출력, COMM이 NULL이고 SAL가 NULL이 아니면 SAL 출력, SAL, COMM모두 NULL이면 0을 출력하시오. SQL> select ename, sal, comm, coalesce(comm, sal, 0) from emp; ENAME SAL COMM COALESCE(COMM,SAL,0) ---------- ---------- ---------- -------------------- SMITH 800 800 ALLEN 1600 300 300 WARD 1250 500 500 JONES 2975 2975 MARTIN 1250 1400 1400 BLAKE 2850 2850 CLARK 2450 2450 SCOTT 3000 3000 KING 5000 5000 TURNER 1500 0 0 ADAMS 1100 1100 SQL> select greatest(1,6,8,5,3,4) from dual; GREATEST(1,6,8,5,3,4) --------------------- 8 SQL> select greatest('B','T','W') from dual; G - W SQL> select least(1,6,8,5,3,4) from dual; LEAST(1,6,8,5,3,4) ------------------ 1 SQL> select least('B','T','W') from dual; L - B

USER : 현재 세션의 접속한 USER계정을 VARCHAR2 형식으로 리턴 한다.

SQL> select USER from dual; USER ------------------------------ SCOTT SQL> show user; USER은 "SCOTT"입니다

VSIZE(expr) : expr의 바이트 수를 리턴 한다.

SQL> select vsize(empno), vsize(ename) from emp; VSIZE(EMPNO) VSIZE(ENAME) ------------ ------------ 3 5 3 5 3 4