IT 지식 쌓기

날짜 관련된 명령어정리 하기

SYSDATE 

현재 날짜를 가져옴

SELECT 

SYSDATE 

FROM DUAL;

=======================================================================

형식에 맞춰서 가져오기 

TO_CHAR

DATE 타입의 데이터를 CHAR타입으로 변환

SELECT

TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS 테스트1,

TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') AS 테스트2,

TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS 테스트2,

TO_CHAR(SYSDATE, 'YYYY') 연도,

TO_CHAR(SYSDATE, 'MM') 월,

TO_CHAR(SYSDATE, 'HH24') 시간

FROM DUAL;

시간 출력 HH <<  기본값이 12 시간 단위이니 24 시간 단위가 필요하면 HH24 <<

TO_CHAR 은 문자형으로 변환시키는 함수이므로 날짜에만 국한되지 않는다.

TO_DATE

CHAR 타입의 데이터를 DATE 타입으로 변환

SELECT

TO

TO_DATE(20150316, 'YYYYMMDD')

FROM DUAL;

=======================================================================

ADD_MONTHS

 ± 월 데이터를 변환시 사용되는 함수

SELECT

TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') AS "한달 전",

TO_CHAR(ADD_MONTHS(SYSDATE, 0), 'YYYY-MM-DD') AS "이번 달",

TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYY-MM-DD') AS "한달 후",

TO_CHAR(ADD_MONTHS(SYSDATE, 12), 'YYYY-MM-DD') AS "1년 후",

TO_CHAR(ADD_MONTHS(SYSDATE, 120), 'YYYY-MM-DD') AS "10년 후"

FROM DUAL;

* 기준이 되는 날짜가 해당 월의 말일이 되는 경우 변환되는 날짜가 말일로 표시 될수 있다.

 예) 8월 31일을 기준으로 잡아서 한달 후를 출력할수 9월 30일이 나온다.

=======================================================================

LAST_DAY

날짜 데이터의 마지막 일을 구해서 출력

SELECT

LAST_DAY(SYSDATE) 테스트1,

TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD') 테스트2

FROM DUAL;

=======================================================================

NULL 값을 변환하기

NVL(컬럼명, 반환값)

컬럼 값이 NULL 이면 반환값이 나옴

 예)

SELECT

NVL(COMM, 0)

FROM EMP;

COMM 이 NULL 값이면 0을 반환.

NVL2(컬럼명, 반환값1, 반환값2)

컬럼값이 NULL이면 반환값1 아니면 반환값2

 예)

SELECT

NVL2(COMM, 0, 1)

FROM EMP;

COMM이 NULL 값이면 0을 반환 아닐시 1을 반환

DECODE

SELECT

DECODE(COMM, NULL, '널', 300, '삼백', '조건없음')

FROM EMP;

COMM 이 NULL 이면 널 300 이면 삼백 앞의 조건에 속하지 않다면 조건없음이 출력된다.

NULL 처리를 원할시 NVL, NVL2  OR  DECODE 명령어를 이용하여 NULL값을 치환한다.

DECODE

- DECODE 함수는 조건에 따라 데이터를 다른 값이나 컬럼값으로 추출 할 수 있다.

- DECODE(VALUE, IF1, THEN1, IF2, THEN2...) 형태로 사용 할 수 있다.

- VALUE 값이 IF1일 경우에 THEN1 값을 반환하고, VALUE 값이 IF2일 경우에는 THEN2 값을 반환한다.

- DECODE 함수 안에 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 RESEARCH

     30 SALES

     40 OPERATIONS

=======================================================================

아래는 DECODE 함수에서 집계 함수를 사용한 예제이다

-- 10부서는 급여합계를, 20부서는 최대값을, 30부서는 최소값을 출력하는 예제

SELECT deptno, 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; 

DEPTNO   DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40

------- --------- --------- ---------- ----------

     30         0         0       9400          0

     20         0     10875          0          0

     10      8750         0          0          0 

=======================================================================

아래 부서별 급여합계 예를 보면 일반적인 집계함수를 사용할 때는 급여 합계가 행으로 조회가 되지만, 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함수로 변환한 예이다.

--위의  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; 

출처 : 구루비