MySQL 함수 올바르게 작성했으나 반환값이 정상적이지 않은 경우

작성일자: 2023년 7월 12일

✔결론

👀문제 정의

GET_ORGN_PAY_AMT(함수)

✔원인 & 해결방법

✔결론

---

함수 내부에서 정의한 변수에 SELECT ... INTO 구문으로 값을 넣어줄 때 컬럼명과 변수명이 동일한지 확인해보자. 이 경우에 값을 제대로 넣어주지 못함. 둘이 충돌나지 않게 변수의 이름을 컬럼 이름과 다르게 지정해주면 된다.

SELECT BNEF_TP, CPN_BNEF
INTO BNEF_TP, CPN_BNEF
FROM COUPON
WHERE CPN_ID = v_CPN_ID;

SELECT BNEF_TP, CPN_BNEF
INTO V_BNEF_TP, V_CPN_BNEF
FROM COUPON
WHERE CPN_ID = P_CPN_ID;

👀문제 정의

GET_ORGN_PAY_AMT 는 쿠폰아이디를 주면 주어진 금액(V_ORD_AMT)에 대해서 쿠폰할인을 적용한 금액을 반환해주는 함수다.

쿠폰 아이디 2번을 주어서 5%할인이 적용되어 428640원이 반환되어야 정상이나 내가 입력한 451200원이 그대로 나옴

함수 코드 자체는 잘못된 부분이 없음

GET_ORGN_PAY_AMT(함수)

CREATE
    DEFINER = syur1@`%` FUNCTION GET_ORGN_PAY_AMT(v_ORD_AMT double, v_CPN_ID int) RETURNS double DETERMINISTIC
BEGIN
    DECLARE ORGN_PAY_AMT DOUBLE; -- 원결제금액
    DECLARE BNEF_TP CHAR(2); -- 혜택타입(쿠폰할인율/금액)
    DECLARE CPN_BNEF DOUBLE; -- 쿠폰혜택

    -- 1. 쿠폰아이디로 혜택타입과 쿠폰혜택을 조회해온다.
    SELECT BNEF_TP, CPN_BNEF
    INTO BNEF_TP, CPN_BNEF
    FROM COUPON
    WHERE CPN_ID = v_CPN_ID;

    -- 2. 주문금액에서 쿠폰을 적용한 금액을 계산한다.
    IF BNEF_TP = '01' THEN -- 할인율인 경우
        SET ORGN_PAY_AMT = v_ORD_AMT - (v_ORD_AMT * (CPN_BNEF / 100));
    ELSEIF BNEF_TP = '02' THEN -- 할인 금액인 경우
        SET ORGN_PAY_AMT = v_ORD_AMT - CPN_BNEF;
    ELSE
        SET ORGN_PAY_AMT = v_ORD_AMT;
    END IF;

    RETURN TRUNCATE(ORGN_PAY_AMT, 0);
END;

✔원인 & 해결방법

함수 코드 자체에는 문제가 없어서 함수내에서 사용하는 값들을 반환해보면서 디버깅을 해보고 있었는데 BNEF_TP를 반환했을 때 NULL로 반환이 되어서 혹시나하고 테이블의 컬럼명과 겹치치않게 변수명을 바꿔보니 제대로 동작했다.

---

함수 내부에서 정의한 변수에 SELECT ... INTO 구문으로 값을 넣어줄 때 컬럼명과 변수명이 동일할 경우 값을 제대로 넣어주지 못함. 둘이 충돌나지 않게 변수의 이름을 컬럼 이름과 다르게 지정해주면 된다.

SELECT BNEF_TP, CPN_BNEF
INTO BNEF_TP, CPN_BNEF
FROM COUPON
WHERE CPN_ID = v_CPN_ID;

SELECT BNEF_TP, CPN_BNEF
INTO V_BNEF_TP, V_CPN_BNEF
FROM COUPON
WHERE CPN_ID = P_CPN_ID;

CREATE
    DEFINER = syur1@`%` FUNCTION GET_ORGN_PAY_AMT(P_ORD_AMT double, P_CPN_ID int) RETURNS double DETERMINISTIC
BEGIN
    DECLARE V_ORGN_PAY_AMT DOUBLE; -- 원결제금액
    DECLARE V_BNEF_TP char(2); -- 혜택타입(쿠폰할인율/금액)
    DECLARE V_CPN_BNEF int; -- 쿠폰혜택

    -- 1. 쿠폰아이디로 혜택타입과 쿠폰혜택을 조회해온다.
    SELECT BNEF_TP, CPN_BNEF
    INTO V_BNEF_TP, V_CPN_BNEF
    FROM COUPON
    WHERE CPN_ID = P_CPN_ID;

    -- 2. 주문금액에서 쿠폰을 적용한 금액을 계산한다.
    IF V_BNEF_TP = '01' THEN -- 할인율인 경우
        SET V_ORGN_PAY_AMT = P_ORD_AMT - (P_ORD_AMT * (V_CPN_BNEF / 100));
    ELSEIF V_BNEF_TP = '02' THEN -- 할인 금액인 경우
        SET V_ORGN_PAY_AMT = P_ORD_AMT - V_CPN_BNEF;
    ELSE
        SET V_ORGN_PAY_AMT = P_ORD_AMT;
    END IF;

    RETURN TRUNCATE(V_ORGN_PAY_AMT, 0);
END;

---

2시간 삽질한 원인이 이거였다니 허무하긴 한데.. 그래도 덕분에 함수 내부에서 사용할 변수명 접두사 규칙도 정하게되었다!

1. 매개변수 접두사: 'P_'
   • 예시: P_ORD_AMT, p_CPN_ID

2. 함수 내부 변수 접두사: 'V_'
   • 예시: V_ORGN_PAY_AMT, V_BNEF_TP, V_CPN_BNEF

3. 함수 내부 상수 접두사: 'C_'
   • 예시: C_DISCOUNT_RATE, C_MAX_AMOUNT

Uploaded by N2T