데이터베이스 질문과 답변
ORA-06503: PL/SQL: 함수가 값을 리턴하지 못했습니다 에러관련 질문입니다.
0
1
4,780
by 어떠닝히 [Oracle 에러코드] [2018.07.25 16:21:11]
create or replace FUNCTION FN_GET_ON_PRESENT_MILEAGE
(
I_CCD IN VARCHAR2 -- 회원코드
,I_SPCODE IN VARCHAR2 -- 상품코드
)
RETURN VARCHAR2
IS
V_RETURN VARCHAR2(1000);
BEGIN
SELECT
(CASE WHEN (SELECT BRANDCD FROM BOS.V_BM_ORDER_BASKET WHERE CCD = I_CCD AND SPCODE IN (SELECT COLUMN_VALUE AS SPCODE FROM TABLE(BOS.FN_SPLIT(I_SPCODE,','))) GROUP BY BRANDCD) = '109100' THEN
(SELECT ROUND((TO_NUMBER(SUBSTR (BOS.FN_GET_SALEPRICE_CH(BSK.SPCODE, I_CCD, '','N','ko','','KRW'), 31, 10))* BSK_QTY) * 0.13 ) FROM BOS.V_BM_ORDER_BASKET BSK WHERE 1=1 AND BSK.CCD = I_CCD AND SPCODE= I_SPCODE)
ELSE
CASE WHEN MAX(CMST.ON_PRESENT_MILEAGE) < 0 THEN 0 ELSE MAX(CMST.ON_PRESENT_MILEAGE) END --보유적립금
END)
AS ON_PRESENT_MILEAGE
into V_RETURN
FROM BOS.V_BM_CUSTMST CMST WHERE CMST.CCD = I_CCD GROUP BY CMST.ON_PRESENT_MILEAGE;
EXCEPTION
WHEN OTHERS THEN
V_RETURN := NULL;
RETURN V_RETURN;
END FN_GET_ON_PRESENT_MILEAGE;
제가 위와같은 함수를 생성했는데요
실행하면 아래 오류메세지가 납니다.
ORA-06503: PL/SQL: 함수가 값을 리턴하지 못했습니다
ORA-06512: "BOS.FN_GET_ON_PRESENT_MILEAGE", 28행
ORA-06512: 9행
실행을 하면 아래처럼 리턴받지를 못합니다. 쿼리자체에 이상은 없는데
도움좀 주시면 감사드리겠습니다.
감사합니다.!
by 야신
[2018.07.28 10:35:45]
create or replace FUNCTION FN_GET_ON_PRESENT_MILEAGE ( I_CCD IN VARCHAR2 -- 회원코드 ,I_SPCODE IN VARCHAR2 -- 상품코드 ) RETURN VARCHAR2 IS V_RETURN VARCHAR2(1000); BEGIN SELECT (CASE WHEN (SELECT BRANDCD FROM BOS.V_BM_ORDER_BASKET WHERE CCD = I_CCD AND SPCODE IN (SELECT COLUMN_VALUE AS SPCODE FROM TABLE(BOS.FN_SPLIT(I_SPCODE,','))) GROUP BY BRANDCD) = '109100' THEN (SELECT ROUND((TO_NUMBER(SUBSTR (BOS.FN_GET_SALEPRICE_CH(BSK.SPCODE, I_CCD, '','N','ko','','KRW'), 31, 10))* BSK_QTY) * 0.13 ) FROM BOS.V_BM_ORDER_BASKET BSK WHERE 1=1 AND BSK.CCD = I_CCD AND SPCODE= I_SPCODE) ELSE CASE WHEN MAX(CMST.ON_PRESENT_MILEAGE) < 0 THEN 0 ELSE MAX(CMST.ON_PRESENT_MILEAGE) END --보유적립금 END) AS ON_PRESENT_MILEAGE into V_RETURN FROM BOS.V_BM_CUSTMST CMST WHERE CMST.CCD = I_CCD GROUP BY CMST.ON_PRESENT_MILEAGE ; RETURN V_RETURN; -- 이 부분이 없음 EXCEPTION WHEN OTHERS THEN V_RETURN := NULL; RETURN V_RETURN; END FN_GET_ON_PRESENT_MILEAGE;
) 버튼을 클릭하여 작성 하시면 됩니다.