국비교육 24일차 Oracle : 날짜함수, 변환함수, 기타함수, VIEW
1. 날짜함수
1) add_months(날짜, 숫자)
- 숫자가 양수이면 날짜에서 숫자 개월 수 만큼 더해준 날짜를 나타내는 것이고, 숫자가 음수이면 날짜에서 숫자 개월 수 만큼 빼준 날짜를 나타내는 것이다. 여기서 숫자의 단위는 개월수이다.
select sysdate, to_char(add_months(sysdate, 1), 'yyyy-mm-dd')
from dual;
- 2022-08-042) months_between(날짜1,날짜2)
- 날짜1 에서 날짜2 를 뺀 값으로 그 결과는 숫자가 나오는데, 결과물 숫자의 단위는 개월 수이다. 즉, 두 날짜의 개월차이를 구할 때 쓰인다.
select months_between(add_months(sysdate,3),sysdate)
from dual;
-- 3- 날짜1 - 날짜2 = 숫자
날짜1에서 날짜2를 뺀 값으로 숫자가 나오는데 결과물 숫자의 단위는 일수이다.
즉, 두 날짜의 일수 차이를 구할 때 사용한다.
select add_months(sysdate, 1) - sysdate
from dual;
-- 313) last_day(특정날짜)
- 특정 날짜가 포함된 달력에서 맨 마지막날짜를 알려주는 것이다.
select last_day(sysdate)
from dual;
-- 22/07/314) next_day(특정 날짜, '요일')
- 특정 날짜로부터 다음번에 돌아오는 가장 빠른 '일'~'토'의 날짜를 알려주는 것이다.
select sysdate
, next_day(sysdate, '금')
, next_day(sysdate, '월')
from dual;
-- 22/07/04 22/07/08 22/07/115) extract
- 날짜에서 연, 월, 일을 숫자형태로 추출해주는 것이다.
select sysdate
, to_char(sysdate, 'yyyy'), extract(year from sysdate)
, to_char(sysdate, 'mm'), extract(month from sysdate)
, to_char(sysdate, 'dd'), extract(day from sysdate)
from dual;
-- 22/07/04 2022 2022 07 7 04 4- extract 사용하면 오른쪽 맞춤으로 숫자형태로 추출된다.
2. 변환함수
1) to_char(날짜, '형태'), to_char(숫자, '형태')
-
날짜와 숫자를 '형태' 모양의 문자형태로 변환시켜준다.
-
날짜를 문자형태로 변환하기
select to_char(sysdate, 'yyyy') AS 년도
, to_char(sysdate, 'mm') AS 월
, to_char(sysdate, 'dd') AS 일
, to_char(sysdate, 'hh24') AS "24시간"
, to_char(sysdate, 'am hh') AS "12시간"
, to_char(sysdate, 'pm hh') AS "12시간"
, to_char(sysdate, 'mi') AS 분
, to_char(sysdate, 'ss') AS 초
, to_char(sysdate, 'q') AS 분기
-- 1월~3월 => 1, 4월~6월 => 2, 7월~9월 => 3, 10월~12월 => 4
, to_char(sysdate, 'day') AS 요일명
-- 월요일(Windows) , Monday(Linux)
, to_char(sysdate, 'dy') AS 줄인요일명
-- 월(Windows) , Mon(Linux)
, to_char(sysdate, 'd') AS 요일명(숫자로 출력)
-- '일'(1) ~ '토'(7)
-- ==> 숫자로 출력되는 결과값을 case when 등으로 요일명으로 변경
, to_char(sysdate, 'ddd') AS 올해 1월 1일부터 얼마나 지났는지
, to_char(sysdate, 'sssss') AS 오늘 0시0분0초부터 몇초가 지났는지
from dual;- 숫자를 문자형태로 변환하기
select 1234567890
, to_char(1234567890, '9,999,999,999')
, to_char(1234567890, '$9,999,999,999')
, to_char(1234567890, 'L9,999,999,999') -- L은 그 나라의 화폐기호
from dual;
-- 1234567890 1,234,567,890 $1,234,567,890 ￦1,234,567,890select 100
, to_char(100, '999.0') -- 자릿수 맞출 때
, to_char(100, '999.00')
, 96.7
, to_char(96.7, '999.0')
, to_char(96.7, '999.00')
from dual;
-- 100 100.0 100.00 96.7 96.7 96.702) to_date(문자, '날짜형태')
- 문자를 '날짜형태' 모양의 날짜형태로 변환시켜준다.
select to_date('2022-07-04','yyyy-mm-dd')
, to_date('2022/07/04','yyyy/mm/dd')
, to_date('20220704','yyyymmdd')
from dual;3) to_number(문자)
- 숫자모양을 가지는 문자를 숫자형태로 변환시켜주는 것이다.
select '12345', to_number('12345')
from dual;
-- 12345(문자) 12345(숫자)- to_number는 문자와 숫자타입의 사칙연산에서 자동형변환이 발생한다.
3. 기타함수
1) greatest, least
select greatest(10, 90, 100, 80) -- 나열되어진 것들 중에 가장 큰 값을 알려준다.
, least(10, 90, 100, 80) -- 나열되어진 것들 중에 가장 작은 값을 알려준다.
from dual;
-- 100 10- 문자 나열도 가능하다.
2) rank, dense_rank
- rank(등수(석차)구하기), dense_rank(서열구하기)
-- hr 테이블
-- 월급을 몇번째로 많이 버는지 구하기
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, nvl(salary + (salary * commission_pct), salary) AS 월급
, rank() over(order by nvl(salary + (salary * commission_pct), salary) desc) AS 월급등수
, dense_rank() over(order by nvl(salary + (salary * commission_pct), salary) desc) AS 월급서열
from employees;- rank() over()에서 over()안에 있는 월급을 내림차순으로 정렬 - partition 추가
select department_id AS 부서번호
, employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, to_char( nvl(salary + (salary * commission_pct), salary), '999,999' ) AS 월급
, rank() over(order by nvl(salary + (salary * commission_pct), salary) desc) AS 전체월급등수
, rank() over(partition by department_id -- 부서번호를 기준으로 구분하여라.
order by nvl(salary + (salary * commission_pct), salary) desc) AS 부서내월급등수
from employees
order by 1;- partition by로 부서 번호를 기준으로 부서 내에서 구분3) lag, lead 함수
- 게시판에서 특정 글을 조회할 때 많이 사용된다.
create table tbl_review
(boardno number -- 글번호
,subject varchar2(4000) -- 글제목
);
insert into tbl_review(boardno, subject) values(1, '안녕');
insert into tbl_review(boardno, subject) values(2, '오라클');
insert into tbl_review(boardno, subject) values(3, '복습');
insert into tbl_review(boardno, subject) values(4, 'SQL');
insert into tbl_review(boardno, subject) values(5, 'REVIEW');
commit;
select *
from tbl_review;
select lag(boardno, 1) over(order by boardno desc) AS 이전글번호
-- boardno(글번호)의 내림차순으로 정렬했을 때 위쪽으로 1칸 올라간 행에서 boardno 컬럼의 값을 가져온다.
, lag(subject, 1) over(order by boardno desc) AS 이전글제목
-- boardno(글번호)의 내림차순으로 정렬했을 때 위쪽으로 1칸 올라간 행에서 subject 컬럼의 값을 가져온다.
, boardno AS 글번호
, subject AS 글제목
, lead(boardno, 1) over(order by boardno desc) AS 다음글번호
-- boardno(글번호)의 내림차순으로 정렬했을 때 아래쪽으로 1칸 내려간 행에서 boardno 컬럼의 값을 가져온다.
, lead(subject, 1) over(order by boardno desc) AS 다음글제목
-- boardno(글번호)의 내림차순으로 정렬했을 때 아래쪽으로 1칸 내려간 행에서 subject 컬럼의 값을 가져온다.
from tbl_review;
-- lag(boardno, 1) 에서 숫자 1은 생략이 가능하다.
/*
이전글번호 이전글제목 글번호 글제목 다음번호 다음제목
null null 5 REVIEW 4 SQL
5 REVIEW 4 SQL 3 복습
4 SQL 3 복습 2 오라클
3 복습 2 오라클 1 안녕
2 오라클 1 안녕 null null
*/
-- lag는 1칸(지정된 숫자가 1임)만큼 방향이 위쪽으로 올라가고 lead는 방향이 아래쪽으로 움직임.
-- 1(지정된 숫자)만큼 위 아래의 숫자가 나오는 것
-- order by 기준으로 어떻게 정렬하여서 여기를 기준으로 얼마만큼 위에 있는지를 모여주는 것4. VIEW
(1) VIEW란? 테이블은 아니지만 select 되어진 결과물을 마치 테이블 처럼 보는것(간주하는 것)이다.
(2) VIEW는 inline view와 stored view 2가지가 있고, inline view 는 1회성이고, stored view는 언제든지 불러내서 재사용이 가능하다.
1) Inline view
- select 구문을 괄호( )를 쳐서 별칭(예 : V)을 부여한 것을 말한다.
-- hr 테이블
-- employees 테이블에서 월급등수가 1등 부터 10등까지 사원들만
-- 사원번호 , 사원명 , 월급 , 월급등수 을 나타내세요.
select V.*
from
(
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, nvl(salary + (salary * commission_pct), salary) AS 월급
, rank() over(order by nvl(salary + (salary * commission_pct), salary) desc) AS 월급등수
from employees
) V -- V는 소괄호() 속의 select 되어져 나온 결과물을 V 라는 이름을 가지는 테이블로 간주한다.
where V.월급등수 <= 10
-- select 되어져 나온 결과물을 하나의 table로 본다. 사원번호, 사원명, 월급, 월급등수 등을 하나의 컬럼명을 본다.
-- 이 select 테이블의 결과물을 v라고 한다. v라는 테이블로 본다.
-- 이때의 V를 인라인뷰라고 한다. inline View는 sql문 속에 v라는 테이블로 간주하겠다는 뜻이다.
-- 큰 틀은 select from where이나 from으로 불러오는 테이블이 테이블로 간주되어지는 View이다.
-- 이렇게 sql문 속에 포함된 것이 inline view라 한다. 2) Stored view
- 복잡한 SQL을 저장하여 select 문을 간단하게 사용하고자 할 때 쓰인다.
- Stored View(저장된 뷰) 생성하기
/*
create or replace view 뷰명
--> 뷰명 으로 되어진 view 가 없으면 create(생성) 하고,
만약에 뷰명 으로 되어진 view 가 이미 존재한다라면
이전에 정의해둔 view를 없애버리고 select 문장 으로 replace(수정)해라는 말이다.
as
select 문장;
*/create or replace view view_employee_rank
-- view_employee_rank 이름의 view가 없다면 만들고
-- 있으면 아까 select문 없애고 이 select문으로 교체하라.
as
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, nvl(salary + (salary * commission_pct), salary) AS 월급
, rank() over(order by nvl(salary + (salary * commission_pct), salary) desc) AS 월급등수
from employees;
-- View VIEW_EMPLOYEE_RANK이(가) 생성되었습니다.
select *
from view_employee_rank;
-- 테이블과 유사하게 사용하여도 오류가 나지 않는다. 
이런 저런 기록들