단일행 함수 - 기타 함수

★ 단일행 함수의 종류 ★

1. 문자 함수
2. 숫자 함수
3. 날짜 함수
4. 변환 함수
5. 기타 함수

---

5. 기타 함수

1. case when then else end

select case 조건 -- 조건이
		when 결과값1 then '문자열1' 또는 숫자1
        -- 조건값이 결과값1과 동일하면 '문자열1' 또는 숫자1 출력
        when 결과값2 then '문자열2' 또는 숫자2 
        -- 조건값이 결과값2와 동일하면 '문자열2' 또는 숫자2 출력
        else '문자열3' 또는 숫자3 
        -- when 에 적합한 값이 없으면 '문자열3'또는 숫자3 출력
        end as "별칭"
        -- end 로 마무리 한다.
        
 -- 또는
 
 select case	-- case 뒤에 조건이 없을 시
 		when 조건1 then '문자열1' 또는 숫자1
     -- when 뒤에 오는 조건1이 참이면 '문자열1' 또는 숫자1 출력
        when 조건2 then '문자열2' 또는 숫자2
     -- when 뒤에 오는 조건2이 참이면 '문자열2' 또는 숫자2 출력 
        else '문자열3' 또는 숫자3
        -- when 에 참이 없으면 '문자열3'또는 숫자3 출력
        end as "별칭"
        -- end 로 마무리 한다.

2. decode

select decode(조건, 결과값1, '문자열1'또는 숫자1
-- 조건값이 결과값1 이라면 '문자열1' 또는 숫자1
					,결과값2, '문자열2'또는 숫자2
                    -- 결과값2 이라면 '문자열2' 또는 숫자2
                    		, '문자열'또는 숫자) as "별칭"
  		-- 결과값1과 결과값2가 조건의 값에 해당하지 않는 경우
  		-- '문자열3' 또는 숫자3

---

3. table view

종류 : inline view, stored view

3-1. inline view

( ) 안에 필요한 컬럼이 있으면 만드는 것, 1회성
FROM( ) 문자 -> 문자는 아무문자나 상관없다.

만나이

=> 올해 생일이 미래이면 현재년도 - 태어난년도 - 1
=> 올해 생일이 오늘이거나 과거이면 현재년도-태어난년도

현재년도 => extract(year from sysdate)
태어난년도 => case when substr(jubun,7,1) in('1','2')
		then '19' || substr(jubun,1,2)
        else '20' || substr(jubun,1,2)
        end
올해생일 => to_date(to_char(sysdate,'yyyy') 
					|| substr(jubun,3,4),'yyyymmdd')

정년퇴직일(만 63세 기준)

• 3월 ~ 8월 생일 8월 말일 퇴사
• 9월 ~ 2월 생일 2월 말일 퇴사
  => 생일에 따라 구분 월지정하기 -> 퇴직년도 구하기
  -> 년도+월 -> 해당년도에 해당 월 마지막 날짜 구하기

생일에 따라 구분하기
=> case when substr(jubun,3,2) between '03' and '08'
    then '0801'
    else '0201'
    end
퇴직년도 구하기 => add_months(sysdate,(63-age)*12)

---

Ex) 만나이, 정년퇴직일

SELECT fullname as 사원명
-- fullname 은 A 안에 있는 컬럼으로 A.fullname 과 같다.
-- 동일한 컬럼명이 없을 경우, 'A.'은 생략이 가능하다.
	, rpad(substr(jubun,1,6),13,'*') as 주민번호
    , age as "현재 나이"
    , last_day(to_date
    (
    to_char(add_months(sysdate,(63-age)*12),'yyyy')
    ||
    case when substr(jubun,3,2) between '03' and '08'
    then '0801'
    else '0201'
    end
    ,'yyyy-mm-dd')) as retire_day	-- 정년퇴직일
FROM
(
  SELECT fullname, jubun
      , case when birthday_this_year
      > to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd')
      then extract(year from sysdate) - birthyear - 1
      else extract(year from sysdate) - birthyear
      end as age	-- 만나이
  FROM
  (
      select first_name || ' ' || last_name as fullname
          , jubun

          , to_date(to_char(sysdate,'yyyy')
       || substr(jubun,3,4),'yyyymmdd')
       as birthday_this_year		-- 올해생일
          , case when substr(jubun,7,1) in('1','2')
              then '19' else '20'
              end || substr(jubun,1,2) 
              as birthyear -- 태어난 년도
      from EMPLOYEES
  ) A
) B;

---

- WITH 절을 사용한 inline view ( 만나이 )

WITH
A AS
(
    select first_name || ' ' || last_name as fullname
          , jubun

          , to_date(to_char(sysdate,'yyyy')
       || substr(jubun,3,4),'yyyymmdd')
       as birthday_this_year		-- 올해생일
          , case when substr(jubun,7,1) in('1','2')
              then '19' else '20'
              end || substr(jubun,1,2) 
              as birthyear -- 태어난 년도
      from EMPLOYEES
)
SELECT fullname, jubun
      , case when birthday_this_year
      > to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd')
      then extract(year from sysdate) - birthyear - 1
      else extract(year from sysdate) - birthyear
      end as age	-- 만나이
FROM A;

---

3-2. stored view

create or replace view 뷰명 as select 문장
-> 뷰명으로 되어진 view 가 없으면 create, 있으면 기존 내용을 없애고, 내용을 select 문장으로 수정

create or replace view 뷰명	
-- 이때 테이블명이나 뷰명은 접두어로 사용하려고 하기
-- Ex) TBL_이름~~, VIEW_이름~~
as
select ~~
from ~~;

---

4. greatest(1,2,3,4)
   나열되어진 값들 중 가장 큰 값

5. least(1,2,3,4)
   나열되어진 값들 중 가장 작은 값

select greatest(1,2,3), greatest('김유신','허준','고수')
	, greatest(to_date('2024-01,01','yyyy-mm-dd')
    	, to_date('2024-02-29','yyyy-mm-dd')
        , to_date('2024-12-31','yyyy-mm-dd'))
 from dual;
 -- 결과
 -- 	3	|	허준		|	2024-12-31
 -- 숫자크기	| ㄱ~ㅎ,ㅏ~ㅣ |	 날짜순서

---

6. rank() over(partition by 컬럼명1 order by 구하고 싶은 값 asc/desc)

7. dense_rank() over(order by 구하고 싶은 값 asc/desc)

• partition by 컬럼명1 = 기준이 컬럼명1
  즉, 컬럼명1 내에서 구하고 싶은 값의 오름차순/내림차순
  -> 기준이 없을 시 생략 가능하다.
• desc : 1등부터 나열

★ 주의
rank 나 dense_rank 는 where 절에 사용할 수 없다!
=> 사용하고 싶을 경우 inline view 를 이용하여 사용

SELECT *
FROM
(
    select  employee_id as 사원번호
        , first_name || ' ' || last_name as 사원명
, to_char(nvl(salary + (salary * commission_pct), salary),
   											'99,999') as 월급
, rank() over(
	order by nvl(salary + (salary * commission_pct), salary)
    										desc) as 월급등수
    from employees
) V
WHERE 월급등수 <= 10;

---

8. lag(컬럼명1, 양수인 숫자) over(order by 컬럼명2 asc/desc)
   컬럼명2를 asc/desc 한 기준으로 숫자만큼 위에 나열된 컬럼명1의 값을 나타낸다.

9. lead(컬럼명1, 양수인 숫자) over(order by 컬럼명2 asc/desc)
   컬럼명2를 asc/desc 한 기준으로 숫자만큼 아래에 나열된 컬럼명1의 값을 나타낸다.

select lag(boardno) over(order by boardno desc) 
		as 이전글번호
        , lag(subject) over(order by boardno desc) 
        as 이전글제목
        , boardno as 글번호
        , subject as 글제목
        , content as 글내용
        , lead(boardno) over(order by boardno desc) 
        as 다음글번호
        , lead(subject) over(order by boardno desc) 
        as 다음글제목
from tbl_board;

---

정리

-> local_hr에서작업한것