SQL - 날짜함수, 변환함수, 조건함수

날짜 함수

 

select *
from NLS_SESSION_PARAMETERS;

create table exam
(a date, b date);

insert into exam (a, b)
values (to_date('15/01/01','RR/MM/DD'), to_date('15/01/01','YY/MM/DD'));

select to_char(a, 'RRRR'), to_char(b, 'YYYY')
from exam;

-- 시스템의 년도를 1995년도 변경
insert into exam (a, b)
values (to_date('15/01/01','RR/MM/DD'), to_date('15/01/01','YY/MM/DD'));

select to_char(a, 'RRRR'), to_char(b, 'YYYY')
from exam;

가) SYSDATE 함수

현재 날짜 반환
형식은 RR/MM/DD

 

SELECT SYSDATE 
FROM DUAL;

SELECT SYSDATE, systimestamp
FROM dual;

-- 연산 가능
SELECT SYSDATE 오늘, SYSDATE+1 내일, SYSDATE-1 어제
FROM dual;

-- 근무 년수 계산
SELECT ename, hiredate, TRUNC((SYSDATE - HIREDATE)/365)년 FROM EMP ORDER BY 3 DESC;
SELECT ename, hiredate, FLOOR((SYSDATE - HIREDATE)/365)년 FROM EMP ORDER BY 3 DESC;

변환 함수

 

자동 형변환

 

-- 문자가 숫자로 자동변환
SELECT last_name, salary
FROM employees
WHERE salary = '17000';

--문자가 날짜로 자동변환
SELECT last_name, salary
FROM employees
WHERE HIRE_DATE = '03/06/17';

명시적 형변환 (날짜 -> 문자)

TO_CHAR( number | date, 'format' )

select sysdate, to_char(sysdate,'YYYY'), to_char(sysdate,'MM')
from dual;

select sysdate, to_char(sysdate,'YYYY/MM/DD MON DAY DY')
from dual;

--" "을 주의해서 포맷변경 
select sysdate, to_char(sysdate,'YYYY"년" MM"월" DD"일"')
from dual;

select sysdate, to_char(sysdate,'AM HH HH24 MI SS')
from dual;

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD,(AM) DY HH24:MI:SS')
FROM dual;

-- 날짜에서 특정년도만 추출, 월만 추출, 일만 추출
select sysdate, to_char(sysdate, 'YYYY'), to_char(sysdate, 'MM')
from dual;


-- extract 함수 이용해 추출하기
select sysdate, extract(year from sysdate), 
                extract(month from sysdate),
                extract(day from sysdate),
                extract(hour from systimestamp),
                extract(minute from systimestamp),
                extract(second from systimestamp)
from dual;

-- 입사 날짜가 9월인 사원들의 정보를 출력
SELECT last_name,hire_date, salary
FROM employees
WHERE TO_CHAR(hire_date, 'MM')='09';


-- extract로 변경하기
SELECT last_name,hire_date, salary
FROM employees
WHERE extract (month from hire_date)='09';

명시적 형변환 (수치 -> 문자)

SELECT last_name, salary, 
TO_CHAR(salary, '$999,999') 달러,
TO_CHAR(salary, 'L999,999') 원화
FROM employees;

-- 자릿수에 따라 포맷을 늘려주어야한다.
select to_char(98765432,'L999,999,999')
from dual;

명시적 형변환 (문자 -> 수치)

TO_NUMBER( str )

SELECT TO_NUMBER('123') + 100 
FROM dual;

SELECT TO_NUMBER('123,456','999,999') + 100 
FROM dual;

SELECT TO_NUMBER('123,456','$999,999') + 100 
FROM dual;

명시적 형변환 (문자 -> 날짜)

TO_DATE( str, 'format' )

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

SELECT TO_DATE( '20170802181030' , 'YYYYMMDDHH24MISS' )
FROM dual;

SELECT TO_DATE( '2017년08월02일' , 'YYYY"년"MM"월"DD"일"' )
FROM dual;

-- 현재 날짜에서 2017/01/01을 뺀 결과를 출력
SELECT SYSDATE, SYSDATE-TO_DATE( '20170801' , 'YYYYMMDD' )
FROM dual;

• 실제 DB에 날짜를 저장한다면?
• 가. 20170802 (권장)
• 나. 2017년08월02일 => 저장크기 더 필요함. 출력시 포맷변경이 제한적임. Locale(지역화)

조건 함수

DECODE 함수

조건이 반드시 일치하는 경우에 사용하는 함수

DECODE ( 컬럼, 비교값1, 결과값1,
 비교값2, 결과값2,
 ...
 비교값n, 결과값n,
 기본결과값 ) 

SELECT last_name,salary,
         DECODE(salary,24000, salary*0.3,
                       17000, salary*0.2,
                       salary) as 보너스
FROM employees
ORDER BY 2 desc;

-- 사원들의 입사년도 별로 사원들의 인원수를 구하는 SQL문
SELECT COUNT(*) "총인원수",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2001, 1, 0)) "2001",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2002, 1, 0)) "2002",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2003, 1, 0)) "2003",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2004, 1, 0)) "2004",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2005, 1, 0)) "2005",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2006, 1, 0)) "2006",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2007, 1, 0)) "2007",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2008, 1, 0)) "2008"
FROM employees;

CASE 함수

여러 조건에 대해서 선택적으로 SQL문을 실행시킬 수 있는 함수. CASE 함수는 다양한 비교 연산자를 이용하여 조건을 설정할 수 있다.

CASE 컬럼 WHEN 비교값1 THEN 결과값1
     WHEN 비교값2 THEN 결과값2
     ELSE 결과값n
END

CASE WHEN 조건1 THEN 결과값1
     WHEN 조건2 THEN 결과값2
     ELSE 결과값n
END

-- ANSI SQL, 동등비교
SELECT last_name,salary,
 CASE salary WHEN 24000 THEN salary*0.3
            WHEN 17000 THEN salary*0.2
            ELSE salary 
 END 보너스 FROM employees
ORDER BY 2 desc;

-- ANSI SQL, 부등비교
SELECT last_name,salary,CASE WHEN salary >=20000 THEN 1000
                            WHEN salary >=15000 THEN 2000
                            WHEN salary >=10000 THEN 3000
                            ELSE 4000 
                        END 보너스 
 FROM employees
ORDER BY 2 desc;

SELECT last_name,salary, CASE WHEN salary BETWEEN 20000 AND 25000 THEN '상'
                              WHEN salary BETWEEN 10000 AND 20001 THEN '중'
                              ELSE '하'
                          END as 등급 
FROM employees
ORDER BY 2 desc;

SELECT last_name,salary, CASE WHEN salary IN ( 24000, 17000 , 14000) THEN '상'
                              WHEN salary IN ( 13500, 13000) THEN '중'
                              ELSE '하'
                         END as 등급 
FROM employees
ORDER BY 2 desc;