Oracle 11강 - 날짜함수

Whatever·2021년 9월 23일

기초 ORACLE

목록 보기

10/27

6)REMAINDER(n,c)

주어진 수 n을 c로 나눈 나머지를 반환
MOD와 비슷한 기능이나 나머지의 값의 크기에 따라 다른 결과반환
(내부처리가 다름)
(1) MOD
나머지 = 분모 - 분자*FLOOR(분모/분자)
(2) REMAINDER
나머지 = 분모 - 분자*ROUND(분모/분자)
ex)
MOD(13,7)= 13 - 7*FLOOR(13/7) --우리가 알고있는 나머지연산자 / 주어진 값을 초과하지 않는 최대정수
= 13 - 7*FLOOR(1.857..)
= 13- 7*1
= 6
REMAINDER(13,7) = 13 - 7*ROUND(13/7) --소수점 이하가 0.5가 넘어서면 올려줌
= 13 - 7*ROUND(1.857...)
= 13 - 7*2
= -1
MOD(15,7)= 15 - 7*FLOOR(15/7)
= 15 - 7*FLOOR(2.14...)
= 15 - 7*2
= 1
REMAINDER(15,7) = 15 - 7*ROUND(15/7)
= 15 - 7*ROUND(2.14...)
= 15 - 7*2
= 1

7)WIDTH_BUCKET(n,min,max,b)

주어진 값(min~max)을 b개의 구간으로 나누었을 때 n이 속한 구간의 순번(인덱스)을 반환

사용예)회원테이블에서 회원들이 보유한 마일리지(100~20000)를 10개의 구간으로 나누고
각 회원들이 그 중 어느구간에 속하는지를 조회하시오
Alias는 회원번호,회원명,마일리지,구간값

  SELECT MEM_ID AS 회원번호,
         MEM_NAME AS 회원명,
         MEM_MILEAGE AS 마일리지,
         WIDTH_BUCKET(MEM_MILEAGE,20000,100,10) AS 등급 
         --max값을 초과하면 b+1값, min보다 작으면 0 (등급으로 하려면 max와 min의 위치 바꾸기)
    FROM MEMBER;

사용예)회원들의 마일리지를 (100-25000)을 3개의 구간으로 구분하고 각 회원들이 속한 구간값이 1이하이면 '새싹회원', 2 구간에 속하면
'정상활동회원', 그 이상이면 'VIP회원'을 비고난에 출력하시오
Alias는 회원번호,회원명,마일리지,구간값,비고

  SELECT MEM_ID AS 회원번호,
         MEM_NAME AS 회원명,
         MEM_MILEAGE AS 마일리지,
         WIDTH_BUCKET(MEM_MILEAGE,100,25000,3) AS 구간값,
         CASE WHEN WIDTH_BUCKET(MEM_MILEAGE,100,25000,3)<=1 THEN '새싹회원'
              WHEN WIDTH_BUCKET(MEM_MILEAGE,100,25000,3)=2 THEN '정상활동회원'
              ELSE 'VIP회원' 
         END AS 비고
    FROM MEMBER;

날짜함수

1)SYSDATE

시스템에서 제공하는 날짜 및 시간정보 반환
'+', '-'연산의 대상

날짜타입은 LIKE연산자 쓰지말고 BETWEEN연산자를 쓰는 게 좋음.
ROUND(MONTHS_BETWEEN(SYSDATE,MEM_BIR)/12) -- 나이를 구할 수 있음

2)ADD_MONTHS(d, n)

주어진 날짜자료 d에 정수 n만큼을 더한 n월을 더한 날짜 반환

사용예)사원테이블에서 입사일자가(HIRE_DATE)수습시작일이라 가정하고 정식 발령일자(3개월 후)를 조회하여
이번달에 입사한 사원을 조회하시오.
Alias는 사원번호,사원명,수습일자,발령일

  SELECT EMPLOYEE_ID AS 사원번호,
         EMP_NAME AS 사원명,
         HIRE_DATE AS 수습일자,
         ADD_MONTHS(HIRE_DATE,3) AS 발령일
    FROM EMP
   WHERE EXTRACT(MONTH FROM ADD_MONTHS(HIRE_DATE,3)) = EXTRACT(MONTH FROM SYSDATE);

3)NEXT_DAY(d, c), LAST_DAY(d)

NEXT_DAY : 주어진 날짜 d이후의 날짜 중 가장 빠른 c요일('월','월요일','화','화요일',...)의 날짜를 반환
LAST_DAY : 주어진 날짜 d에 포함된 월의 마지막일자를 나타내는 날짜반환

사용예)2005년 2월 제품별 매입합계를 조회하시오 --날짜가 요구사항에 있을 때 :조건이 됨
상품코드,상품명,매입수량합계,매입금액합계

  SELECT A.BUY_PROD AS 상품코드,
         B.PROD_NAME AS 상품명,
         SUM(A.BUY_QTY) AS 매입수량합계,
         SUM(A.BUY_QTY*A.BUY_COST) AS 매입금액합계
    FROM BUYPROD A, PROD B
   WHERE B.PROD_ID=A.BUY_PROD --조인조건 
     AND A.BUY_DATE BETWEEN TO_DATE('20050201') AND 
         LAST_DAY(TO_DATE('20050201'))
   GROUP BY A.BUY_PROD, B.PROD_NAME 
   --상품코드가 같은 것끼리 모아라 / GROUP BY는 GROUP BY 다음에 나오는 컬럼의 같은 값끼리 모으는 것
   ORDER BY 1;

4)MONTHS_BETWEEN(d1,d2)

두 날짜자료 사이의 달수를 반환 --한 달이 차지 않는 경우 소수점 이하로 나올 수 있음

사용예)회원테이블에서 회원들의 생년월일을 이용하여 경과된 개월 수를 조회하시오

  SELECT MEM_ID AS 회원번호,
         MEM_NAME AS 회원명,
         MEM_BIR AS 생년월일,
         ROUND(MONTHS_BETWEEN(SYSDATE,MEM_BIR)) AS 월수 -- /12하면 나이를 구할 수 있음
    FROM MEMBER;

UPDATE 테이블명 SET 바꿀 컬럼명

5)EXTRACT(fmt FROM d)

주어진 날짜 d에서 'fmt'로 정의된 요소 값을 반환
fmt는 'YEAR','MONTH','DAY','HOUR','MINUET','SECOND'
반환 값의 타입은 숫자형임

** MEMBER 테이블에서 다음 자료를 수정하시오.
회원번호 : 'i001'
이름 : '최지현'
MEM_REGNO1 : '741220'=>'011220'
MEM_REGNO2 : '2384719'=>'4384719'
MEM_BIR : '1974/12/20'=>'2001/12/20'

   UPDATE MEMBER
   SET MEM_REGNO1='011220',
       MEM_REGNO2='4384719',
       MEM_BIR=TO_DATE('2001/12/20')
    WHERE MEM_ID='i001';      

   SELECT MEM_ID,MEM_NAME,MEM_REGNO1,MEM_REGNO2,MEM_BIR
   FROM MEMBER
  WHERE MEM_ID='i001'; 

  회원번호 : 't001'
     이름 : '성원태'
MEM_REGNO1 : '760506'=>'000506'
MEM_REGNO2 : '1454731'=>'3454731'
MEM_BIR : '1976/05/06'=>'2000/05/06'  

   UPDATE MEMBER
   SET MEM_REGNO1='000506',
       MEM_REGNO2='3454731',
       MEM_BIR=TO_DATE('2000/05/06')
    WHERE MEM_ID='t001';   

  SELECT MEM_ID,MEM_NAME,MEM_REGNO1,MEM_REGNO2,MEM_BIR
   FROM MEMBER
  WHERE MEM_ID='t001';

  회원번호 : 'e001'
     이름 : '이혜나'
MEM_REGNO1 : '750501'=>'020501'
MEM_REGNO2 : '2406017'=>'4406017'
MEM_BIR : '1975/05/01'=>'2002/05/01'  

    UPDATE MEMBER
   SET MEM_REGNO1='020501',
       MEM_REGNO2='4406017',
       MEM_BIR=TO_DATE('2002/05/01')
     WHERE MEM_ID='e001'; 

     SELECT MEM_ID,MEM_NAME,MEM_REGNO1,MEM_REGNO2,MEM_BIR
   FROM MEMBER
  WHERE MEM_ID='e001';

  COMMIT;

사용예)회원테이블에서 이번달 생일인 회원을 추출하시오
Alias는 회원번호,회원명,생년월일,마일리지

  SELECT MEM_ID AS 회원번호,
         MEM_NAME AS 회원명,
         MEM_BIR AS 생년월일,
         MEM_MILEAGE AS 마일리지
    FROM MEMBER
   WHERE EXTRACT(MONTH FROM MEM_BIR) = EXTRACT(MONTH FROM SYSDATE)+1 --다음달이 생일인 사람

사용예)사원테이블에서 근속년수를 계산하여 근속년수가 20년 이상인 사원을 조회하시오
Alias는 사원번호,사원명,입사일,근속년수이다

  SELECT EMPLOYEE_ID AS 사원번호,
         EMP_NAME AS 사원명,
         HIRE_DATE AS 입사일,
         EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) AS 근속년수
    FROM EMP
   WHERE EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE)>=20;

변환함수

정의된 자료의 형을 일시적으로 변환하여 반환
CAST,TO_NUMBER,TO_DATE,TO_CHAR이 제공됨

1)CAST(expr AS 타입)
- 'expr'로 정의된 데이터 또는 컬럼의 값을 '타입'형으로 변환(일시적)
- '타입'은 오라클에서 사용될수 있는 데이터 타입

사용예)

  SELECT PROD_ID,
         PROD_NAME,
         PROD_COST,
         CAST(PROD_PRICE AS VARCHAR2(10)) --숫자 오른쪽정렬, 문자 왼쪽정렬
    FROM PROD
   WHERE PROD_COST>=100000;

2)TO_CHAR(data[,fmt])

data는 문자열(CHAR,CLOB=>VARCHAR2),숫자,날짜 타입의 자료 --문자열에서 문자열은 VARCHAR2형식으로 바꾸는 데 사용됨

'fmt'는 변환하려는 형식지정 문자열

(1)날짜타입 형식지정 문자열

 --------------------------------------------------------------------------
 형식지정문자열           의미             사용예
 --------------------------------------------------------------------------
  AD,BC,CC        서기,세기  SELECT TO_CHAR(SYSDATE,'BC CC') FROM DUAL;
  YYYY,YYY,YY,Y   년도       SELECT TO_CHAR(SYSDATE,'BC YYYY') FROM DUAL;
                            SELECT TO_CHAR(SYSDATE,'BC YYY') FROM DUAL; --오른쪽에서 세자리만  
                            SELECT TO_CHAR(SYSDATE,'BC Y') FROM DUAL; --오른쪽에서 한자리
  Q               분기      SELECT TO_CHAR(SYSDATE,'Q"분기"') FROM DUAL; --문자열 안에 쌍따옴표로 문자 추가 가능 
  MM,RM           월        SELECT TO_CHAR(SYSDATE,'YYYYMM RM') FROM DUAL; --RM : 로마식표현법
  MONTH, MON                SELECT TO_CHAR(SYSDATE,'YYYY MON') FROM DUAL;  
  W,WW,IW         주차      SELECT TO_CHAR(SYSDATE,'W WW IW') FROM DUAL; 
  --W는 주차(이번주에서 오늘이 몇 번째 날인지) / WW는 올해 첫 주부터 오늘이 몇 번째 주인지
  DD,DDD,J        일        SELECT TO_CHAR(SYSDATE,'DD DDD J') FROM DUAL; 
                  --DD는 해당 달 1일부터 지금까지 며칠이 경과되었는지 /DDD는 2021년에서 오늘까지 며칠이 경과되었는지 /
                    J는 기원전4712년부터 지금까지 경과된 일자
  D,DY,DAY        주의 요일  SELECT TO_CHAR(SYSDATE,'D DY DAY') FROM DUAL; 
  --D는 일요일을 기준으로 오늘이 몇번째 날인지 /DY는 요일의 약자 /DAY는 요일의 FULL NAME
  AM,PM,A.M.,P.M. 오전,오후  SELECT TO_CHAR(SYSDATE,'AM PM') FROM DUAL; 
                          --주어진 날짜를 기준으로 지금이 오전인지 오후인지 나타냄
  HH,HH12,HH24    시        SELECT TO_CHAR(SYSDATE,'HH HH24') FROM DUAL; --HH24는 24시간표시형식
  MI              분        SELECT TO_CHAR(SYSDATE,'HH24:MI') FROM DUAL; 
  SS,SSSSS        초        SELECT TO_CHAR(SYSDATE,'HH24:MI:SS SSSSS') FROM DUAL;
  --SSSSS는 오늘 0시0분0초부터 지금까지 경과된 시간을 초단위로 나타냄
  " "             기타 사용자 정의
                            SELECT TO_CHAR(SYSDATE,'YYYY"년" MM"월" DD"일" HH24:MI:SS') 
                                           --''안에서 ""안의 값이 문자열로 반환되어 출력
                              FROM DUAL;
 ----------------------------------------------------------------------------

Whatever

이전 포스트

Oracle 10강 - 문자열함수(REPLACE,INSTR), 숫자함수(GREATEST,ROUND,FLOOR,CEIL,MOD)

다음 포스트

Oracle 11강 - 날짜함수

기초 ORACLE

Oracle 10강 - 문자열함수(REPLACE,INSTR), 숫자함수(GREATEST,ROUND,FLOOR,CEIL,MOD)

Oracle 12강 - 날짜타입, Null처리 함수

0개의 댓글

관련 채용 정보