Oracle 정리 ( 함수(숫자, 날짜, 데이터 형 변환))

숫자

---

 

• round(숫자, 출력을 원하는 자리수), trunc(숫자, 출력을 원하는 자리수)
• round()는 숫자를 반올림 하는 함수, trunc()는 버림을 하는 함수

select  round(123.456,2) as r1,
        round(123.456,0) as r2,
        round(123.456,-1) as r3
from dual;

 

[결과]

 

 

• mod(숫자, 나눌 숫자), ceil(숫자), floor(숫자)
• mod() 함수는 나머지 값을 구하는 함수, ceil() 함수는 주어진 숫자와 가장 가까운 큰 정수를 구하는 함수, floor() 함수는 주어진 숫자와 가장 가까운 작은 정수를 구하는 함수

select  mod(10,3) mod,
        ceil(0.3333) ceil,
        floor(1.3333) floor
from dual;

 

[결과]

 

 

날짜

---

 

• sysdate (OS로 부터 현재시간을 가져옴)

select  sysdate
from dual;

 

[결과]

 

• months_between(날짜, 날짜) (날짜 사이의 개월 수를 출력하는 함수)
• 큰 날짜를 먼저 써야 양수 반환
• 두 날짜가 같은 달에 속해있으면 특정 규칙으로 계산된 값 반환

select  sysdate,
        months_between(sysdate,hire_date) months_between
from employees
where department_id=110;

 

[결과]

 

• add_months(날짜,숫자) (날짜에 숫자 만큼의 달을 추가하는 함수)

select  hire_date, 
        add_months(hire_date,6) add_months
from employees
where department_id = 110;

 

[결과]

 

• next_day(날짜,요일) (날짜 요일의 가장 최근에 돌아오는 같은 요일의 날짜 반환)

select  sysdate,
        next_day(sysdate,'월') next_day
from dual;

 

[결과]

 

 

• last_day(날짜) (날짜가 속한 달의 가장 마지막 날 반환)

select   sysdate,
        last_day(sysdate)
from dual;

 

[결과]

 

 

• 날짜의 round(날짜), trunc(날짜) (round() 함수는 12:00을 기준으로 클 경우 다음 날짜의 12:00 반환, trunc() 함수는 무조건 당일의 12:00 반환)

select  sysdate,
        round(sysdate),
        trunc(sysdate)
from dual;

 

[결과]

 

데이터 형 변환

---

숫자 ==(to_char)==> 문자 <==(to_char)== 날짜

숫자 <==(to_number)== 문자 ==(to_date)==> 날짜

 

• to_char() (숫자 -> 문자)

종류	의미	예제	결과
9	9의 개수만큼 자리수	to_char(9876,'99999')	9876
0	빈자리를 0으로 채우기	to_char(9876,'099999')	009876
$	$ 표시를 붙여서 표시	to_char(9876,'$9999)	$9876
.	소수점 이하를 표시	to_char(9876,'9999.99'	9876.00
,	천 단위 구분기호를 표시	to_char(9876,'99,9999')	9,876

 

select  first_name,
        to_char(salary*12,'$999,999.99') sal
from employees
where department_id = 110;

 

[결과]

 

• to_char() (날짜 -> 문자)

종류	의미	예제
YYYY	연도를 4자리로 표현	2019
RRRR	Y2K 버그로 인해 등장한 날짜 표기법으로 연도 4자리 표기법	2019
YY	연도를 끝의 2자리만 표시	19
RR	연도를 마지막 2자리만 표시	19
year	연도의 영문 이름 전체를 표시	TWO THOUSAND NINE
MM	월을 숫자 2자리로 표현합니다.	06
MON	유닉스용 오라클에서 월을 뜻하는 영어 3글자로 표시 윈도우용 오라클은 MONTH와 동일	Jun
MONTH	월을 뜻하는 이름 전체를 표시합니다	June
DD	일을 숫자 2자리로 표시	17
DAY	요일에 해당하는 명칭을 표시 (윈도우는 한글, 유닉스는 영어)	월요일, Monday
DDTH	몇 번째 날인지를 표시	17th
HH24	하루를 24시간으로 표시	14
HH	하루를 12시간으로 표시	02
MI	분 표시	34
SS	초 표시	30

 

select  first_name,
        to_char(salary*12,'$999,999.99') sal,
        to_char(hire_date,'RRRR-MM-DD HH24-MI-SS') hiredate
from employees
where department_id = 110;

 

[결과]

 

• to_number( '아라비아 숫자' ) (문자 -> 숫자)

select  to_number('11')
from dual;

 

[결과]

 

일반 함수

---

 

• nvl(조사 할 컬럼, null일 경우 치환할 값), nvl2(조사 할 컬럼, null이 아닐때 치환할 값, null일 때 치환할 값)
• decode() (오라클에서만 사용되는 조건문 처리 함수)

select  first_name,
        department_id,
        decode(department_id,'10','Team-1','20','Team-2','30','Team-3','N/A') TEAM
from employees
where department_id < 50;

 

[결과]

 

• case문 (decode()에서 비교하기 어려운 연산도 처리가 가능하기 때문에 아주 요긴하게 사용되는 방법

case 컬럼명 when 조건1 then 출력1
	   [when 조건2 then 출력2] ...
           else 출력 3
           
end 컬럼명(Alias)           

 

select first_name, department_id,
        case department_id when 10 then 'Team-1'
                           when 20 then 'Team-2'
                           when 30 then 'Team-3'
                           else 'N/A'
        end team
from employees
where department_id < 50;

 

[결과]

 

 

그룹 함수

---

 

• count(컬럼명) (데이터의 총 row 수를 반환, null 제외)

select  count(*),
        count(commission_pct)
from employees;

 

[결과]

 

• sum(컬럼명) (데이터들의 합계 반환)

select  count(*),
        sum(salary)
from employees;

 

[결과]

 

• max(컬럼명), min(컬럼명) (max()는 데이터들 중 가장 큰 값 반환, min()은 데이터들중 가장 작은 값 반환)
• 데이터를 정렬 한 후 그 중에서 최대/최소값을 추출

select  max(salary),
        min(salary),
        max(hire_date),
        min(hire_date)
from employees;

 

[결과]

 

그룹함수 특정 조건으로 세부적인 결과값 출력(group by)

---

 

[예제]

select  department_id,
        job_id,
        count(*),
        sum(salary)
from    employees
group by department_id,
         job_id
order by 1,2; 

[결과]

 

주의사항

1. select 절에 사용된 그룹함수 이외의 컬럼이나 표현식은 반드시 group by 절에 사용되어야 함
2. group by 절에 사용된 컬럼은 select 절에 사용되지 않아도 됨
3. group by 절에는 반드시 컬럼명이 사용되어야 하며 컬럼 Alias는 사용하면 안됩니다.
4. where 절은 그룹함수가 올 수 없음

그룹 함수 결과를 조건으로 사용하기(having 절)

---

 

• 그룹 함수를 조건으로 사용하고 싶을 경우에는 where 대신에 having 절을 사용하면 해결됨
• where 절의 조건이 먼저 실행됨

 

[예제]

select  department_id,
        count(*),
        sum(salary)
from employees
having sum(salary) > 20000
group by department_id;

 

[결과]

 

 

• 데이터들을 모아서 합계(집계)를 해주는 함수
• rollup(), cube() (rollup()은 데이터들의 소계를 구해줌, cube()는 주어진 데이터들의 전체 총계까지 구해줌)

rollup(컬럼명들)

 

[예제]

select  department_id,
        job_id,
        count(*),
        sum(salary)
from employees
where department_id > 50
group by rollup(department_id,job_id);

 

[결과]

 

cube(컬럼명)

 

[예제]

select  department_id,
        job_id,
        count(*),
        sum(salary)
from employees
where department_id > 50
group by cube(department_id,job_id);

 

[결과]

 

grouping sets(컬럼명들)

 

[예제]

select  department_id,
        manager_id,
        count(*)
from employees
where department_id > 100
group by grouping sets(department_id,manager_id);

 

[결과]