사용자 정의 함수(Function)

사용자 정의 함수

[문법]

create or replace function 함수명
(파라미터변수명 IN 파라미터변수의타입)
return 리턴되어질타입
is
	변수선언;
begin
	실행문;
    return 리턴되어질값;
end 함수명;

---

성별 함수

create or replace function func_gender
(p_jubun in varchar2)	-- p_jubun 파라미터명, varcar2 유형
return varchar2
is
	v_result	varchar2(6);
begin
	select case when substr(p_jubun,7,1) in('1','3') 
    		then '남' else '여' end
    INTO
    v_result
    from dual;
    return v_result;
end func_gender;

---

사용방법

select func_gender('9010201234567')
from dual;

---

나이 함수 (1)

create or replace function func_age
(p_jubun in varchar2)
return number	-- 타입이 달라도 가능
is
	v_age	varchar2(6);
begin
	select case when to_date(to_char(sysdate, 'yyyy')
    	|| substr(p_jubun,3,4),'yyyymmdd')
        	- to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') > 0
       then extract(year from sysdate) 
            - (to_number(substr(p_jubun,1,2)) 
            + case when substr(p_jubun,7,1) in ('1','2')
            	then 1900 else 2000 end) - 1
       else extract(year from sysdate)
       		- (to_number(substr(p_jubun,1,2))
            + case when substr(p_jubun,7,1) in ('1','2')
            	then 1900 else 2000 end)
       end
	INTO
		v_age
	from dual;
	return v_age;
end func_age;

나이 함수 (2)

create or replace function func_age_2
(p_jubun in varchar2)
return varchar2
is
    v_age    varchar2(6);
begin
    v_age :=  case when to_date(to_char(sysdate, 'yyyy') 
    			|| substr(p_jubun, 3, 4), 'yyyymmdd') 
            	- to_date(to_char(sysdate,'yyyymmdd'), 'yyyymmdd') 
                	> 0 
              then extract(year from sysdate) 
              	- ( to_number(substr(p_jubun,1,2)) 
              	+ case when substr(p_jubun,7,1) in('1','2') 
              			then 1900 else 2000 end ) - 1 
              else extract(year from sysdate) 
              	- ( to_number(substr(p_jubun,1,2)) 
                + case when substr(p_jubun,7,1) in('1','2') 
                		then 1900 else 2000 end ) 
              end;
     return v_age;
end func_age_2;

---

정년퇴직일

create or replace function func_retirement_day
(p_jubun  IN  varchar2)
return date
is
	v_retirement_day date; -- 변수명
begin
    select last_day(to_date(to_char(add_months(sysdate
    						,(63-func_age(p_jubun))*12),'yyyy')
     -- 만 63세가 되어지는 오늘날 : add_months(sysdate,(63-현재나이)*12)
        || case when substr(p_jubun,3,2) between '03' and '08' 
        		then '0801' else '0201' end,'yyyy-mm-dd'))
    INTO v_retirement_day
    from dual;
    return v_retirement_day;
end func_retirement_day;

---

생성되어진 함수 소스 조회

select *
from user_source
where type = 'FUNCTION' and name = '함수명';
-- 이 때 함수명은 대문자로 작성

---

정리

-> local_hr에서작업한것