[ORACLE] 함수 221202-01

❗함수(User Defined Function : Function)

 - 특징은 procedure와 유사하며, 반환 값이 존재함
 - 보통 OUT 매개변수는 사용하지 않음

(사용형식)
  CREATE [OR REPLACE] PROCEDURE 프로시져명[( 
    매개변수 [IN|OUT|INOUT] 데이터타입[:=디폴트 값],
            :
    매개변수 [IN|OUT|INOUT] 데이터타입[:=디폴트 값]) 
    RETURN 데이터타입 -- 세미콜론(;) 없음
  IS|AS 
    선언영역
  BEGIN
    실행영역
    (반드시 하나이상의 
      RETURN expr; 이 존재해야 함) --SELECT문의 SELECT절이나 WHERE절에서 사용, 동일한 데이터타입을 사용해야 함
  END;

  . 하나의 함수는 하나의 값만 반환 가능
  . 함수의 호출을 SELECT문의 SELECT절, WHERE절 등에서 수행

 

✔ 사용예) 회원번호를 입력받아 2020년 7월 구매현황을 조회하시오. 

CREATE OR REPLACE FUNCTION fn_sum_member(
    p_mid IN MEMBER.MEM_ID%TYPE)
    RETURN NUMBER --;x
  IS
    L_SUM NUMBER:=0; --구매금액을 계산해서 보관할 방, 값이없으면 NULL
  BEGIN
    SELECT SUM(A.CART_QTY*B.PROD_PRICE) INTO L_SUM
      FROM CART A, PROD B
     WHERE A.CART_PROD=B.PROD_ID
       AND A.CART_MEMBER=p_mid
       AND A.CART_NO LIKE '202007%';
    RETURN L_SUM;
  END;  
  
[실행] 
  SELECT MEM_ID AS 회원번호,
         MEM_NAME AS 회원명,
         NVL(fn_sum_member(MEM_ID),0) AS 구매금액
    FROM MEMBER     
   WHERE MEM_ID IN(SELECT CART_MEMBER
                     FROM CART
                    WHERE CART_NO LIKE '202007%'); 
  -- => OUTER JOIN 결과

✔사용예) 장바구니테이블에 매출정보를 저장하는 프로시져를 작성하시오
                입력자료는 회원번호,회원명,날짜,상품번호,수량이며 장바구니번호는 함수를 이용하여 
                작성하시오

CREATE OR REPLACE PROCEDURE insert_cart_proc(
    p_mid IN MEMBER.MEM_ID%TYPE,
    p_date IN DATE,
    p_pid IN PROD.PROD_ID%TYPE,
    p_qty IN NUMBER)
  IS 
   
  BEGIN
   
    
    INSERT INTO CART VALUES(p_mid,create_cart_no(p_date,p_mid),p_pid,p_qty);
    COMMIT;
  END;
  
--장바구니번호 생성 함수
  CREATE OR REPLACE FUNCTION create_cart_no(
    p_date IN DATE, p_mid IN MEMBER.MEM_ID%TYPE)
    RETURN CHAR
  IS
    L_CART_NO CART.CART_NO%TYPE; --장바구니번호 잠시보관
    L_MID MEMBER.MEM_ID%TYPE;
    L_COUNT NUMBER:=0; --행의수
    L_FLAG CHAR(9):=TO_CHAR(p_date,'YYYYMMDD')||'%';
  BEGIN
    --해당일자의 장바구니테이블에 자료존재 판단 
    SELECT COUNT(*) INTO L_COUNT
      FROM CART
     WHERE CART_NO LIKE L_FLAG;
    IF L_COUNT=0 THEN -- 첫 고객
       L_CART_NO:=TO_CHAR(p_date,'YYYYMMDD')||TRIM('00001');
    ELSE
      --해당 날짜의 최대장바구니번호
      SELECT MAX(CART_NO) INTO L_CART_NO
        FROM CART
       WHERE CART_NO LIKE L_FLAG; 
      -- 최대장바구니 번호를 보유한 회원번호 
      SELECT DISTINCT CART_MEMBER INTO L_MID 
        FROM CART
       WHERE CART_NO = L_CART_NO; 
       
      IF L_MID!=p_mid THEN
         L_CART_NO:=L_CART_NO+1;
      END IF;
    END IF; 
  
    RETURN L_CART_NO;
  END;

SELECT create_cart_no(TO_DATE('20200414'),'f001')
  FROM DUAL;
  /*
    p_mid IN MEMBER.MEM_ID%TYPE,
    p_date IN DATE,
    p_pid IN PROD.PROD_ID%TYPE,
    p_qty IN NUMBER)
  */
  
  EXECUTE insert_cart_proc('b001',SYSDATE,'P201000010',5);
  EXECUTE insert_cart_proc('b001',SYSDATE,'P302000016',10);
  EXECUTE insert_cart_proc('t001',SYSDATE,'P101000003',2);
 DELETE FROM CART WHERE CART_NO LIKE '2022%';
 COMMIT;