[오라클] 사용자 정의 함수

사용자 정의 함수 작성

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

실습

create or replace function func_gender 
(p_jubun  IN  varchar2) -- 매개변수 IN 변수타입
return varchar2         -- varchar2(6)와 같이 자리수를 쓰면 안됨(오류)
is
 v_result varchar2(6); -- 변수선언 시에는 자리수를 써야 한다.
begin
 
 select case when substr(p_jubun, 7, 1) in('1','3') then '남' else '여' end -- 이 값을
        INTO
        v_result -- v_result에 넣겠다. 즉 변수선언 해야한다.
 from dual;
 
return v_result; // v_result의 타입이 선언부(is)에서 선언한 타입과 같아야 한다
end func_gender;

-- Function FUNC_GENDER이(가) 컴파일되었습니다.


select 
    FUNC_GENDER('9010201234567'),
    FUNC_GENDER('9010202234567'),
    FUNC_GENDER('9010203234567'),
    FUNC_GENDER('9010204234567')
from dual;

select 
    employee_id AS 사원번호,
    first_name || ' ' || last_name AS 사원명,
    jubun AS 주민번호,
    FUNC_GENDER(jubun) AS 성별
from employees
order by 1;


-- function을 자유자재로 만들게 되면 

select *
from employees
where FUNC_GENDER(jubun) = '여'; -- 어차피 gender는 남,여만 나오므로

-- 이렇게 여성만 해당하는 경우만 골라서 볼 수 있다
-- function을 자유자재로 만드는 능력이 중요하다.


---- 또는 

create or replace function func_gender_2
(p_jubun  IN  varchar2) -- varchar2(13) 와 같이 자리수를 쓰면 안됨(오류)
return varchar2         -- varchar2(6)와 같이 자리수를 쓰면 안됨(오류)
is
 v_result varchar2(6); -- 변수선언 시에는 자리수를 써야 한다.
begin
     v_result := case when substr(p_jubun, 7, 1) in('1','3') then '남' else '여' end; 
     return v_result; -- select문을안쓰고 막바로 변수속에 넣어버린다.  
return v_result;
end func_gender_2;

select 
    FUNC_GENDER_2('9010201234567'),
    FUNC_GENDER_2('9010202234567'),
    FUNC_GENDER_2('9010203234567'),
    FUNC_GENDER_2('9010204234567')
from dual;


select 
    employee_id AS 사원번호,
    first_name || ' ' || last_name AS 사원명,
    jubun AS 주민번호,
    FUNC_GENDER(jubun) AS 성별1,
    FUNC_GENDER_2(jubun) AS 성별2
from employees
order by 1;


----  주민번호를 입력받아서 나이를 알려주는 함수 func_age(주민번호)을 생성해보세요. ----




create or replace function func_age
(p_jubun  IN  varchar2) -- varchar2(13) 와 같이 자리수를 쓰면 안됨(오류)
return number         -- varchar2(6)와 같이 자리수를 쓰면 안됨(오류)
is
 v_age number(3); -- !! 변수선언 시에는 자리수를 써야 한다. !!
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 -- v_result에 넣겠다. 즉 변수선언 해야한다.
from dual;
return v_age;
end func_age;
-- Function FUNC_AGE이(가) 컴파일되었습니다.


select 
    func_age('9010201234567'),
    func_age('9010202234567'),
    func_age('9010203234567'),
    func_age('9010204234567')
from dual;


select 
    employee_id AS 사원번호,
    first_name || ' ' || last_name AS 사원명,
    jubun AS 주민번호,
    FUNC_GENDER(jubun) AS 성별1,
    FUNC_GENDER_2(jubun) AS 성별2,
    func_age(jubun) AS 만나이
from employees
order by 1;


-------------- 또는

create or replace function func_age_2
(p_jubun  IN  varchar2) -- varchar2(13) 와 같이 자리수를 쓰면 안됨(오류)
return number         -- varchar2(6)와 같이 자리수를 쓰면 안됨(오류)
is
 v_age number(3); -- !! 변수선언 시에는 자리수를 써야 한다. !!
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;
-- Function FUNC_AGE_2이(가) 컴파일되었습니다.



select 
    func_age('9010201234567'),
    func_age('9010202234567'),
    func_age('9010203234567'),
    func_age('9010204234567')
from dual;


select 
    employee_id AS 사원번호,
    first_name || ' ' || last_name AS 사원명,
    jubun AS 주민번호,
    func_age(jubun) AS 만나이,
    func_age_2(jubun) AS 만나이
from employees
order by 1;

-- employees 테이블에서 나이가 20대 여자와 40대인 남자 사원들만 
 -- 사원번호, 사원명, 주민번호, 성별, 나이를 나타내세요.
 
select 
    employee_id AS 사원번호,
    first_name || ' ' || last_name AS 사원명,
    jubun AS 주민번호,
    FUNC_GENDER(jubun) AS 성별,
    func_age(jubun) AS 만나이
from employees
where (trunc(func_age(jubun),-1) = 20 AND FUNC_GENDER(jubun) = '여')
    OR  (trunc(func_age(jubun),-1) = 20 AND FUNC_GENDER(jubun) = '남')
order by 성별,만나이;

-- 함수를 만들어서 사용하면 깔끔하기 때문에 개발자들은 함수를 많이 만든다!