Conversion Functions(변환함수)
TO_CHAR
위 함수는 DATE형, NUMBER형을 문자타입으로 변환하는 함수
TO_CHAR(number),TO_CHAR(number,format),TO_CHAR(date,format)형식으로 사용가능
Number Format의 변환예제
NUMBER 타입의 데이터를 아래와 같이 특정 형식의 문자 타입으로 변환 할 수 있다
--- 콤마 예제
SELECT TO_CHAR(12345678, '999,999,999') comma FROM DUAL;
--- 소숫점 예제
SELECT TO_CHAR(123.45678, '999,999,999.99') period FROM DUAL;
--- $표시 예제
SELECT TO_CHAR(12345678, '$999,999,999') dollar FROM DUAL;
-- Local 화폐 표시 예제(한국의 경우 원화표시로 자동 변환됨)
SELECT TO_CHAR(12345678, 'L999,999,999') local FROM DUAL;
-- 왼쪽에 0을 삽입
SELECT TO_CHAR(123,'09999') zero FROM DUAL;
-- 16진수로 변환
SELECT TO_CHAR(123,'XXXX') hexadecimal FROM DUAL;
Date Format의 변환예제
DATE 타입의 데이터를 아래와 같이 특정 형식의 문자타입으로 변환할 수 있다.
---년,월,일,시,분,초 예제
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "sysdate" FROM DUAL;
--- 365일 중 몇 일째인지 조회
SELECT TO_CHAR(SYSDATE, 'DDD') "Day of year" FROM DUAL;
-- 53주 중 몇 주차인지 조회
SELECT TO_CHAR(SYSDATE ,'IW') "Week of year" FROM DUAL;
-- 해당 월의 이름 조회
SELECT TO_CHAR(SYSDATE, 'MONTH') "name of month" FROM DUAL;
"WW"와 "IW" 모두 1년의 몇 주차(1~53)로 조회하는 포맷이지만 "WW" 포맷은 무조건 1일에서 7일까지가 1주차로 시작을 하며, "IW" 포맷은 실제 달력에 맞게 주차가 계산된다.
TO_DATE
TO_DATE 함수는 CHAR,VARCHAR2 형을 DATE타입으로 변환
TO_DATE(char,format)형식으로 사용할 수 있다;.
주요 Date Format Elements에서 'W','WW' Format을 제외한 나머지는 TO_DATE함수의 Format으로 사용할 수있다.
--- DATE타입으로 변환하는 예제
SELECT TO_DATE('2011-01-01','RRRR-MM-DD') FROM DUAL;TO_NUMBER
TO_NUMBER 함수는 CHAR,VARCHAR2의 데이터 타입을 숫자형식으로 변환함
TO_NUMBER(char)형식으로 사용할 수 있다.
--- 문자를 숫자로 변환하는 간단한 예제이다
SELECT TO_NUMBER('01210616') FROM DUAL;기타함수들
DUMP
DUMP는 바이트 크기와 해당 데이터 타입 코드를 반환함
-- 16대신 8을 넣으면 8진수로, 10을 넣으면 10진수로 변환이 된다
-- Len은 ename의 해당 byte수이다
SQL> SELECT ename, DUMP(ename,16) "16진수"
FROM emp
WHERE ename= 'ALLEN';
ename 16진수
------ ----------------------------
ALLEN Typ=1 LEN=5: 41,4c,4c,45,4eGREATEST
GREATEST함수는 검색 값 중에서 가장 큰값을 반환함
SQL> SELECT GREATEST(10,100,5,-7) FROM DUAL;
GREATEST(10,100,5,-7)
---------------------
100LEAST
LEAST함수는 GREATEST 함수와 반대로 가장 작은 값을 반환함
UID,USER
UID는 현재 사용자의 유일한 ID번호로 반환하고, USER는 현재 오라클 사용자를 VARCHAR2형식으로 반환 함
SQL> SELECT USER,UID FROM DUAL;
USER UID
------------ --------
SCOTT 32USERENV
USERNV함수는 현재 세션의 환경정보를 반환함
- ENRTYID: 사용 가능한 Auditing entry Identifier를 반환
- LABEL : 현재 세션의 Lable을 반환
- LANGUAGE: 현재 세션에서 사용중인 언어와 테리토리 값을 반환
- SESSIONID: Auditing(감사) Session ID를 반환
- TERMINAL : 현재 세션 터미널의 OS ID를 반환
SQL> SELECT USERNV('LANGUAGE') FROM DUAL;
USERNV('LANGUAGE')
-----------------------
KOREAN_KOREA.KO16KSC5601VSIZE
해당 문자의 BYTE 수를 반환, 해당 문자가 NULL이면 NULL값을 반환
SQL> SELECT VSIZE(ename), ename
FROM emp
WHERE deptno =30;
VSIZE(ENAME) ENAME
------------- -----------
5 ALLEN
4 WARD
6 MARTIN
5 BLAKE
DECODE와 CASE
DECODE와 CASE함수는 SQL문장에서 조건에 해당하는 값을 추출하고자 할때 주로 사용
DECODE
DECODE함수는 조건에 따라 데이터를 다른 값이나 컬럼값으로 추출할 수 있다.
DECODE(VALUE,IF1,THEN1,IF2,THEN2...)형태로 사용할 수 있다.
VALUE값이 IF1일 경우 THEN1을반환, VALUE2도 동일하다
DECODE함수안에 DECODE함수를 중첩으로 사용가능
----부서번호가 10이면 ACCOUNTING,20이면 RESEARCH,30이면 SALES,
----나머지는 OPERATIONS를 출력하는 예제
SELECT deptno, DECODE(deptno, 10, 'ACCOUNTING',20,'RESEARCH',30,'SALES','OPERATIONS') name
FROM dept;
DEPTNO NAME
------- ---------
10 ACCOUNTING
20 RESEARCHING
30 SEALSE
40 OPERATIONSDECODE함수에서 집계함수를 사용한 에제
-- depno가 10인 경우 급여합계를 계산, 20이면 최대값, 30은 최소값을 출력하는 예제
SELECT depno, DECODE(deptno,10,SUM(sal),
20,MAX(sal),
30,MIN(sal)) sal
FROM emp
GROUP BY deptno;
DEPTNO SAL
-------- -------
30 950
20 3000
10 8750
DECODE함수는 집계함수와 함께 통계 데이터를 추출할 때 많이 사용, 아래는 부서별로 급여합계를 조회하는예
---부서별로 급여 합계를 출력함
SELECT deptno, NVL(SUM(DECODE(deptno,10,sal)),0) deptno10,
NVL(SUM(DECODE(deptno,20,sal)),0) deptno20,
NVL(SUM(DECODE(deptno,30,sal)),0) deptno30,
NVL(SUM(DECODE(deptno,40,sal)),0) deptno40
FROM emp
GROUP BY deptno;아래 부서별 급여합계 예를 보면 일반적인 집계함수를 사용할 때는 급여 합계가 행으로 조회되지만 DECODE와 MAX함수를 사용하면 열로 값을 표시할 수있다.
--- 부서별로 급여 합계를 행으로 출력함
SELECT d.deptno, NVL(SUM(e.sal),0) sal
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
GROUP BY d.deptno;
DEPTNO SAL
--------- -------------
10 8750
20 10875
30 9400
40 0
-- 부서별로 급여 합계를 열로 출력한다.
SELECT MAX(NVL(SUM(DECODE(deptno, 10, sal)),0)) deptno10,
MAX(NVL(SUM(DECODE(deptno, 20, sal)),0)) deptno20,
MAX(NVL(SUM(DECODE(deptno, 30, sal)),0)) deptno30,
MAX(NVL(SUM(DECODE(deptno, 40, sal)),0)) deptno40
FROM emp
GROUP BY deptno;
DEPTNO10 DEPTNO20 DEPTNO30 DEPTNO40
--------- ---------- ---------- ----------
8750 10875 9400 0
CASE
CASE함수는 DECODE함수가 제공하지 못하는 비교 연산의 단점을 해결 할 수 있는 함수
DECODE함수에 비교연산을 수행하기 위해서는 GREATEST,LEAST등의 함수를 사용해야 하지만, CASE함수에서는 조건 연산자를 모두 사용 할 수 있다.
CASE 함수는 IF...THEN...ELSE 구문과 비슷하다. WHEN절 다음에 여러 조건이 올수 있다.
--- DECODE 예제를 CASE로 변변환한 예
SELECT deptno,
CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
ELSE 'OPERATIONS'
END as "Dept Name"
FROM dept;
DEPTNO Dept Name
------- -------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS---WHEN절 다음에 연산자가 오는 예제
SELECT ename,
CASE
WHEN sal < 1000 THEN sal+(sal*0.8)
WHEN sal BETWEEN 1000 AND 2000 THEN sal+(sal*0.5)
WHEN sal BETWEEN 2001 AND 3000 THEN sal+(sal*0.3)
ELSE sal+(sal*0.1)
END sal
FROM emp;NVL,NVL2,NULLIF,COALESCE
NVL
NVL함수는 NULL 값을 다른 값으로 바꿀때 사용 모든 타입에 적용 가능
---매니저가 없는 값을 0으로 바꿔서 출력
SELECT empno, NVL(mgr,0) mgr
FROM emp
WHERE deptno=10;
EMPNO MGR
------- -------
7782 7839
7839 0
7934 7782NVL2
NVL2라는 함수는 NVL함수에 DECODE함수의 개념을 합쳤다고 생각하면 된다.
- NVL2(expr,expr1,expr2)
- expr의 값이 NULL이아닐경우 expr1의 값을 반환 NULL인경우 expr2의 값을 반환
--- 매니저가 있는경우 1, 없는경우 0을 출력
SELECT empno, NVL2(mgr,1,0) mgr
FROM emp
WHERE deptno = 10;
EMPNO MGR
-------- ------
7782 1
7839 0
7934 1NULLIF
- NULLIF(exp1,exp2)
- exp1값과 exp2값이 동일하면 NULL 그렇지않으면 exp1을 반환- CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END와 동일
COALESCE
- COALESCE(expr1,expr2,expr3,...)
- expr1이 NULL이아니면 expr1값을 expr1이 NULL이면 COALESCE(expr2,expr3,...)값을 반환
NVL 함수와 비슷하다
--- 아래의 문장을 실행
SELECT COALESCE(comm,1), comm
FROM emp;
COALSECE(COMM,1)
1 NULL COMM
---------------------------
1 NULL
300 300
1 NULL
1 NULL
1 NULL
0 0
1 NULL