Duritz's Fun Life

---- rank 함수
select deptno "부서번호", ename "성명", sal "급여",
    rank() over(order by sal desc) "등수", -- 해당 column의 등수를 준다.
    dense_rank() over(order by sal desc) "서열" --  서열을 나타내 준다.
from emp;

select deptno "부서번호", ename "성명", sal "급여",
    rank() over(partition by deptno order by sal desc) "부서등수",
    -- partition by 는 해당 컬럼별 순위를 나타낸다.
      rank() over(order by sal desc) "등수" -- 해당 column의 등수를 준다.
from emp;

select deptno "부서번호", ename "이름", sal "급여",
    rank() over(partition by deptno order by sal desc) "부서등수",
    rank() over(order by sal desc) "전체등수",
    dense_rank() over(partition by job order by coalesce(sal*12+comm, sal*12, 0) desc) "직종별등수",
    dense_rank() over(order by coalesce(sal*12+comm, sal*12, 0) desc) "전체서열"
from emp
order by 1;

select deptno "부서번호", ename "성명",
    coalesce(sal*12+comm, sal*12, 0) "연봉",
    rank() over(order by coalesce(sal*12+comm, sal*12, 0) desc) "등수"
from emp;

select deptno "부서번호", ename "성명", sal "급여",
    rank() over(order by sal desc) "등수" -- 해당 column의 등수를 준다.
from emp
where rank() over(order by sal desc) <= 5; -- rank 함수는 where절에 사용이 안된다.

--- ○ 변환형 함수
--    ↙-- to_number -┐↙--- to_char --┐
-- number            char              date
--   └-- to_char ---↗└-- to_date ---↗

select sysdate,
    to_char(sysdate,'year'),  -- two thousnad seven
    to_char(sysdate,'yyyy'),  -- 2007
    to_char(sysdate,'month'), -- february
    to_char(sysdate,'mon'),   -- feb
    to_char(sysdate,'mm'),    -- 02
    to_char(sysdate,'dd'),    -- 14
    to_char(sysdate,'hh am'), -- 03 오후 
    to_char(sysdate,'hh24'),  -- 15
    to_char(sysdate,'mi'),    -- 39
    to_char(sysdate,'ss'),    -- 43
    to_char(sysdate,'sssss')  -- 56383 : 현재날짜의 0시0분0초로부터 현재까지 지나간 초를 나타낸다.
from dual;

select to_char(sysdate,'day') -- 요일명 표시
from dual;
select to_char(sysdate,'ddd') -- 현재 년도의 첫째일부터 현재날짜까지 지나온 일수를 표시
from dual;
select to_char(sysdate,'dd') -- 현재 달의 첫째일로부터 현재날짜까지 지나온 일수를 표시
from dual;
select to_char(sysdate,'d') -- 현재 주의 첫째일로부터 현재날짜까지 지나온 일수를 표시
from dual;

select to_char(sysdate, 'q') -- 현재 년도의 분기를 표시한다
from dual;

select to_char(sysdate, 'fmyyyy-mm-dd') from dual; -- fm을 표시하면 앞에 숫자0이 포함되면 나타지않는다.
--------- 연습문제 : 오늘의 날짜를 다음 같이 나타내시오. (2007년 2월 14일 수요일)
select to_char(sysdate, 'yyyy') || '년 ' ||  to_char(sysdate, 'fmmm') || '월 ' ||
    to_char(sysdate, 'fmdd') || '일 ' ||to_char(sysdate, 'day') "오늘의 날짜"
from dual;
--------- 연습문제 : 내가 태어난 요일명은 무엇일까?
select to_char(to_date('1981-08-09','yyyy-mm-dd'), 'day') "내가태어난 요일" from dual;

---- 숫자 -> 문자 변환 기타
select 1234567, to_char(1234567, 'L9,999,999') -- 'L9,999,999' 이렇게 입력하면 해당국가의 통화표시가 나온다.
from dual;

select 1234567, to_char(1234567, 'L9,999,999.99') -- 소숫점 표시도 가능하다.
from dual;

select 1234567, to_char(1234567, '09999999') -- 해당 숫자앞에 0을 넣을수도 있다.
from dual;

select ename, to_char(sal, '$9,999') sal
from emp;

select sysdate,
    to_char(sysdate,'yyyy'),  -- to_char 는 문자형태로 보여지므로 왼쪽정렬
    to_char(sysdate,'mm'),
    to_char(sysdate,'dd'),
    extract(year from sysdate),  -- extract는 숫자형태로 보여지므로 오른쪽정렬
    extract(month from sysdate), -- 시, 분, 초는 표시되지 않는다.
    extract(day from sysdate)
from dual;

---- 날짜연산
select sysdate, sysdate + 1, sysdate + 1/24, sysdate + 1/(24*60*60)
from dual;

select '2007-02-14' - '2007-02-12' -- 이러한 형식은 문자로 인식하기 때문에 실행이 안된다.
from dual;
select to_date('2007-02-14','yyyy-mm-dd') -  -- to_date('입력값','x') :  해당값은 X라는 형식의 날짜라고 표시한다.
    to_date('2007-02-12','yyyy-mm-dd')
from dual;
---- months_between 함수 :
select months_between('2007-2-12','2007-01-01') -- 문자 형식이라도 잠정적으로 날짜로 인식한다.
    -- months_between(a,b): a와b의 차이를 달(month)수로 나타낸다.
from dual;

---- add_months 함수
select add_months(sysdate, 1) -- add_months(a,b) : a날짜로부터 b달만큼 더한다.
from dual;
----------- 예제문제 : 지금부터 1달 2일 3시간 4분 5초후는?
select sysdate, add_months(sysdate,1) + 2 + 3/24 + 5/(24*60*60)
from dual;  -- oracle 8i 까지 써왔던 함수.
----------- 예제문제 : 지금부터 1년 1달 2일 3시간 4분 5초후는?
select sysdate, sysdate + to_yminterval('01-01') + to_dsinterval('002 03:04:05')
from dual; -- oracle 9i 부터 나온 시간계산함수

---- next_day 함수 : next_day(날짜,'요일') - 해당날짜에서 가장빨리 돌아오는 요일을 나타낸다.
select next_day(sysdate,'금'),   -- 리눅스,유닉스 기반에서는 요일이 한글이 아닌 영어라 안될수 있다.
       next_day(sysdate,'월'),
       next_day(sysdate,'토요일'),
       next_day(sysdate,'화요일')
from dual;

select to_char(sysdate,'day'), to_char(sysdate,'dy') -- 해당함수는 날짜에대한 요일을 알려준다.
from dual;  -- 유닉스나 리눅스는 한글이 아닌 영어로 나타난다.

select to_char(sysdate, 'd')
from dual;
------ 1, 2, 3, 4, 5, 6, 7
------ 월 화 수 목 금 토 일 로 매핑이 된다.

---- 미리배워보는 (case, when, then, end) 문
select case 3-1                       -- oracle 8i부터 생성된 함수
    when 1 then '3-1=1 입니다.'
    when 2 then '3-2=1 입니다.'
    else '3-1=몰라요'
    end
from dual;

select case
    when 3 > 4 then '3 > 4 입니다.'
    when 3 > 2 then '3 > 2 입니다.'
    else '몰라요'
    end
from dual;

select decode(3-1, 1, '3-1=1 ok', 2, '3-1=2 ok', '3-1= I don`t know')
from dual;   -- 오라클 8i 이전에 사용하던 함수
----------------------------------------------------------------------------------------
---- case~ 함수
select case to_char(sysdate, 'd')
    when '1' then '일요일'
    when '2' then '월요일'
    when '3' then '화요일'
    when '4' then '수요일'
    when '5' then '목요일'
    when '6' then '금요일'
    when '7' then '토요일'
    end "오늘의 요일명"
from dual;

select decode(to_char(sysdate, 'd')
           ,'1','일요일'
           ,'2','월요일'
           ,'3','화요일'
           ,'4','수요일'
           ,'5','목요일'
           ,'6','금요일'
           ,'7','토요일') "오늘의 요일명"
from dual;

---- last_day 함수 : 해당 날짜에 맨 마지막 날을 표시한다.
select last_day(sysdate)
from dual;

---- log 함수 : 상용로그 값을 구한다.
select log(10,100)
from dual;

---- sign 함수 : 결과물이 음수이면(-)로 양수면(+)로 표시한다.
select sign(2-5), sign(2-2), sign(5-2)
from dual;
------ 참고
select sin(90), cos(90), tan(90), asin(180), acos(180), atan(180)
from dual;

----chr 함수
select ascii('A'), chr(65) -- chr(숫자) : 해당숫자를 아스키값으로 인식하여 그값에 대한 문자를 출력
from dual;

---- mod, floor 함수
select 5/2, mod(5,2),
    floor(5/2),
    ceil(5/2)
from dual;

---- power 함수 : 거듭제곱값을 구한다.
select power(2,5)
from dual;

---- sqrt 함수 : 제곱근을 구한다. (루트)
select sqrt(5)
from dual;

---- round, trunc 함수
select 45.195, round(45.195), round(45.195,0),
    round(45.195,1), round(45.195,2), round(45.195,-1),
    round(45.195,-2), round(54.195,-2)
    -- round(숫자, x) : 해당숫자의 X자리까지만 나타낸다. 사라지는 부분은 반올림되어진다.
from dual;

select 45.195, trunc(45.195), trunc(45.195,0),
    trunc(45.195,1), trunc(45.195,2), trunc(45.195,-1),
    trunc(45.195,-2), trunc(54.195,-2)
    -- trunc(숫자, x) : 해당숫자의 X자리까지만 나타낸다. 사라지는 부분은 절삭한다.
from dual;

---- translate, replace 함수
select translate('oracle','abcdefghijklnmopqrstuvwxyz','ABCDEFGHIJKLNMOPQRSTUVWXYZ')
    -- translate('문장', 'x', 'y') : 문장에서 X라는 문자를 Y로 바꾼다. 단, X와Y는 1:1 매핑이 되야한다.
from dual;

select translate('일금 2560 만원','0123456789','영일이삼사오육칠팔구')
from dual;

select 'oracle orahome', replace('oracle orahome','ora','오라')
    -- replace('문장','x','y') : 문장에서 X라는 문자를 검색하여 Y로 변경한다.
from dual;

---- ltrim, rtlim 함수 : 왼쪽이나 오른쪽에 공백이 존재할때 공백을 제거하기위해 많이 사용
select 'oraoracle server',
    ltrim('oraoracle server','ora'),
    -- ltrim('문장','지울문자') : 문장에서 왼쪽부터 지정한 지울문자의 순서에 상관없이
    --                            연속적으로 포함되면를 연속되는것이 끝날때까지 삭제해서 출력한다.
      rtrim('oraoracle serverver','ver')
      -- rtrim('문장','지울문자') : 오른쪽 부터 삭제
from dual;

select 'oraoracle server',
    ltrim('orroaracle server','ora'),
      rtrim('oraoracle serverveer','ver')
from dual;

select 'oracle' || '                 sever',
    'oracle' || ltrim('                 sever'), -- 지울문자를 쓰지 않으면 해당 공백을 지우게 된다.
    'oracle                 ' || 'server',
    rtrim('oracle                 ') || 'server'
from dual;

---- lpad, rpad 함수
select 'oracle',
    lpad('oracle', 10, '*'),
    -- lpad('문자', x, '특수문자') : 문자를 X수만큼 늘리되 모자라는 부분은 왼쪽부터 특문으로 체운다.
    rpad('oracle', 10, '*')
    -- rpad('문자', x, '특수문자') : 모자라는 부분을 오른쪽부터 체운다.
from dual;

select 'oracle',
    lpad('오라클서버', 14, '♣'), -- byte로 적용되기 때문에 한글은 1글자당 2byte가 된다.
    rpad('오라클서버', 14, '♣')  -- 한글자음으로 만드는 특수문자는 한글처럼 2byte가 된다.
from dual;