[DBMS/Oracle] 함수(User Defined function)

[DBMS/Oracle] 함수(User Defined function)

함수(User Defined function)

- 반환 값이 존재하는 서브프로그램(SELECT문의 SELECT절 WHERE절, INSERT문의 VALUES, WHERE절 
    UPDATE문의 SET절, WHERE절에서 사용가능
- 그 밖의 특징은 프로시져와 동일

(사용형식)
   CREATE [OR REPLACE] PROCEDURE 함수명[(
     매개변수 [IN|OUT|INOUT] 타입 [:=|DEFAULT 값][,]
                   :
     매개변수 [IN|OUT|INOUT] 타입 [:=|DEFAULT 값])]
     
     RETURN 타입명
  IS|AS- 
    선언영역
  BEGIN
    실행영역
    RETURN expr;
    
    [EXCEPTION
      예외처리]
  END;

- 'RETURN 타입명' : 반환할 타입명만 기술
- 'RETURN expr' : 실행영역에서 반드시 하나 이상의 RETURN 문에 의하여 값을 반환해야 함
- FUNCTION에서는 OUT매개변수를 사용하지 않음

 

사용예) 2020년 6월 판매된 상품코드를 입력받아 해당 상품의 매출금액을 반환하는 함수를 작성하시오.

  CREATE OR REPLACE FUNCTION FN_CART01(P_PID PROD.PROD_ID%TYPE)
    RETURN NUMBER
  IS 
    L_SUM NUMBER:=0; --매출금액
  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_PROD=P_PID;
    RETURN L_SUM; 
  END;

(실행)
  SELECT DISTINCT A.CART_PROD AS 상품코드,
                  B.PROD_NAME AS 상품명,
                  FN_CART01(A.CART_PROD) AS 매출액
    FROM CART A, PROD B
   WHERE A.CART_PROD=B.PROD_ID
     AND A.CART_NO LIKE '202006%'
  ORDER BY 1;

 

사용예) 기관과 상품코드를 입역 받아 매출 수량을 조회하여 재고수불테이블을 갱신하는 프로시져와 함수를 작성하시오.

(함수 : 기간 및 상품코드를 입력 받아 매출수량 집계) 
  CREATE OR REPLACE FUNCTION FN_SUM_CART01(P_PERIOD IN VARCHAR2,P_PID IN PROD.PROD_ID%TYPE)
    RETURN NUMBER
  IS
    L_QTY NUMBER:=0;
  BEGIN
    SELECT SUM(CART_QTY) INTO L_QTY
      FROM CART
     WHERE CART_PROD=P_PID
       AND CART_NO LIKE P_PERIOD||'%';
       
     RETURN L_QTY;
  END;

(프로시져 : 기간(년도 및 월)을 입력 받아 재고수불테이블을 갱신)
  CREATE OR REPLACE PROCEDURE PROC_RENAIN_UPDATE02(P_PERIOD IN VARCHAR2)
  IS
    L_PID PROD.PROD_ID%TYPE;
    L_QTY NUMBER:=0;
    L_LDATE DATE:=LAST_DAY(TO_DATE(P_PERIOD||'01'));
    
    CURSOR CUR_CART03 IS 
      SELECT DISTINCT CART_PROD
        FROM CART
       WHERE CART_NO LIKE P_PERIOD||'%';
  BEGIN
    OPEN CUR_CART03;
    LOOP
      FETCH CUR_CART03 INTO L_PID;
      EXIT WHEN CUR_CART03%NOTFOUND;
      
      UPDATE REMAIN A
        SET A.REMAIN_O=A.REMAIN_O + FN_SUM_CART01(P_PERIOD,L_PID),
            A.REMAIN_J_99=A.REMAIN_J_99 - FN_SUM_CART01(P_PERIOD,L_PID),
            A.REMAIN_DATE=L_LDATE
      WHERE A.PROD_ID=L_PID;
     COMMIT;
   END LOOP;
    
    EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류발생 : '||SQLERRM);
        ROLLBACK;
  END;

(실행)
  EXECUTE PROC_RENAIN_UPDATE02('202007');