사용자 정의 함수(USER DEFINED FUNCTION)

사용자 정의 함수(USER DEFINED FUNCTION)

• 반환(하나) 값이 있음
• 반환되는 위치는 함수 호출문의 위치
• 사용형식은 PROCEDURE와 유사
• 사용형식

CREATE [OR REPLACE] FUNCTION 함수명[(
   매개변수 IN|OUT|INOUT(모드) 타입명[:=default 값],
                        :
   매개변수 IN|OUT|INOUT(모드) 타입명[:=default 값])   
   RETURN 타입명
   IS|AS
     변수 선언부 -- number타입은 반드시 초기화
   BEGIN --반드시 하나이상의 타입이 와야함.
     RETURN 변수|값|수식; --실제 반환은 여기서 함.
   END;

예시) 회원번호를 입력받아 나이를 반환하는 함수를 작성하시오.

create or replace function fn_mem01(
  p_mem_id member.mem_id%type)
  return number
is
  v_age number:=0; --여기서는 반드시 초기화 해야됨 선언부
begin
  select extract(year from sysdate)-extract(year from mem_bir)
         into v_age
    from member
   where mem_id = p_mem_id;
   return v_age;
   exception when others then 
      dbms_output.put_line('오류발생 : '||SQLERRM);
      return null;  --함수에서 예외의 null반환하는것 염두해두기
end;
 ▶ 실행 
select mem_name as 회원명,
       fn_mem01(mem_id) as 나이,
       mem_job as 직업,
       mem_mileage as 마일리지
  from member
 where fn_mem01(mem_id) >= 50;

예시) 상품코드와 년도를 입력받아 매출액집계를 반환하는 함수를 작성하시오.
단, Alias는 상품코드, 상품명, 매출액합계

(얻고자 하는것)   
 select a.cart_prod as 상품코드,
        b.prod_name as 상품명,
        sum(a.cart_qty*b.prod_price) as 매출액합계
 --sum(a.cart_qty*b.prod_price)를 함수로 만들겠다.
   from cart a, prod b
  where a.cart_prod = b.prod_id
    and a.cart_no like '2005%'
  group by a.cart_prod, b.prod_name;

(함수)
 create or replace function fn_cart01(
    p_id prod.prod_id%type,
    p_year char)
    return number
 is 
    v_sum number(15):= 0; 
      -- number타입 변수는 자리수는 명시안해도 초기화는 꼭해야한다.
    v_year char(5) := p_year||'%';
 begin
    select sum(cart_qty*prod_price)
           into v_sum
      from cart, prod
     where cart_prod = prod_id
       and cart_prod = p_id
       and cart_no like v_year;
    return v_sum;
 end;

(사용)
 select prod_id as 상품코드,
        prod_name as 상품명,
        fn_cart01(prod_id,'2005') as 매출액합계
 --sum(a.cart_qty*b.prod_price)를 함수로 만들겠다.
   from prod;