1. Procedure에서 Exception 처리
DECLARE
V_EXCEPTION EXCEPTION;
V_NUM NUMBER := 0;
BEGIN
FOR V_ROW IN (SELECT LEVEL AS SEQ FROM DUAL CONNECT BY LEVEL <= 10)
LOOP
BEGIN
V_NUM := MOD(V_ROW.SEQ, 5);
IF V_NUM = 0 THEN RAISE V_EXCEPTION; END IF;
DBMS_OUTPUT.PUT_LINE('출력 : ' || V_ROW.SEQ || ' : ' || V_NUM);
EXCEPTION
WHEN V_EXCEPTION THEN
DBMS_OUTPUT.PUT_LINE('1. 예외 : ' || V_ROW.SEQ || ' : ' || V_NUM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('2. 예외 : ' || V_ROW.SEQ || ' : ' || V_NUM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('3. 예외 : ' || SQLERRM);
END;
/
2. Procedure에서 RAISE_APPLICATION_ERROR 처리
BEGIN
FOR V_IDX IN 1 .. 10
LOOP
BEGIN
IF V_IDX = 5 THEN
RAISE_APPLICATION_ERROR(-20000, '에러!');
END IF;
DBMS_OUTPUT.PUT_LINE(V_IDX);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(V_IDX || ' : ' || SQLERRM);
END;
END LOOP;
END;
1
2
3
4
5 : ORA-20000: 에러!
6
7
8
9
10
3. Function에서 RAISE_APPLICATION_ERROR 처리
CREATE OR REPLACE TYPE ROW_ERP_PO_INFO AS OBJECT
(
EBELN VARCHAR2(4000 BYTE) -- ESPPOHD.ERP_PO_NO
, EBELP VARCHAR2(4000 BYTE) -- ESPPOHD.ERP_PO_LINE_NO
, EXTROW VARCHAR2(4000 BYTE) -- ESPPOHD.ERP_PO_SERVICE_NO
, ZSUBPO VARCHAR2(4000 BYTE) -- ESPPOHD.PO_NO
, ZSUBSEQ NUMBER -- ESPPOHD.PO_MOD_CNT
, STATS VARCHAR2(4000 BYTE) -- 상태 [생성:C, 갱신:R]
, TXZ01 VARCHAR2(4000 BYTE) -- ESBPJHD.PJT_TIT
, KTEXT1 VARCHAR2(4000 BYTE) -- ESPPOHD.PO_TIT
, MENGE NUMBER -- 1
, MEINS VARCHAR2(4000 BYTE) -- 'AU'
, NETWR NUMBER -- ESPPOHD.PO_TOT_AMT_LOC
, TBTWR NUMBER -- ESPPOHD.PO_TOT_AMT_LOC
, WAERS VARCHAR2(4000 BYTE) -- 'KRW'
, SAKTO VARCHAR2(4000 BYTE) -- ESPPOHD.GL_ACCOUNT
, WBSID VARCHAR2(4000 BYTE) -- ESPPOHD.WBSID
);
CREATE OR REPLACE FUNCTION GET_ERP_PO_INFO ( P_SYS_ID IN VARCHAR2
, P_PO_NO IN VARCHAR2) RETURN ROW_ERP_PO_INFO
IS
V_ERP_PO_NO VARCHAR2(4000);
V_ERP_PO_INFO ROW_ERP_PO_INFO := ROW_ERP_PO_INFO(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
-- 1. ERP_PO_NO 조회
-- 하나의 프로젝트내에 같은 업체가 2건이상의 공사에 참여할 수 있다.
-- 그러므로 다건의 ERP_PO_NO가 있을 수 있으나, 같은 업체에 대해서는 같은 ERP_PO_NO가 보장되므로 1건만 취하면 된다.
SELECT ERP_PO_NO
INTO V_ERP_PO_NO
FROM ESBPJEPC
WHERE (SYS_ID
,PJT_NO
,VD_CD) IN (SELECT PO.SYS_ID
, PO.PJT_NO
, OO.ORG_CD
FROM ESPPOHD PO
JOIN ESAOOMG OO ON OO.SYS_ID = PO.SYS_ID
AND OO.OPER_ORG_SN = PO.OPER_ORG_SN
WHERE PO.SYS_ID = P_SYS_ID
AND PO.PO_NO = P_PO_NO)
AND ROWNUM = 1;
IF V_ERP_PO_NO IS NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'ESBPJEPC.ERP_PO_NO에 데이터가 없습니다.');
END IF;
-- 2. Line No. : 30 ~ 99990 : (Service No. == 990 => +10)
-- Service No. : 10 ~ 990 : (각 PO 마다 => +10)
-- 동일 Line No. 내에서 금액이(현재 보낼 금액을 포함하여) 500억을 넘으면 Line No. 를 10을 증가하여 다음 Line번호로 현재 보낼 정보를 I/F한다.
-- CREATE INDEX ESPPOHD_IDX_ERP ON ESPPOHD(SYS_ID, ERP_PO_NO, ERP_PO_LINE_NO, ERP_PO_SERVICE_NO);
SELECT NVL(T01.EBELN, T02.EBELN)
, TO_CHAR(NVL(T01.EBELP, DECODE(SIGN(T01.NETWR + LINE_AMT - 50000000000), 1, DECODE(T02.EXTROW, 10, T02.EBELP , T02.EBELP + 10), T02.EBELP)) , 'FM00000') -- 500억이하면 발번된 Line No. 그대로, 초과면 +10 근데 최초 발번(10)이라면 그대로
, TO_CHAR(NVL(T01.EXTROW, DECODE(SIGN(T01.NETWR + LINE_AMT - 50000000000), 1, DECODE(T02.EXTROW, 10, T02.EXTROW, 10) , T02.EXTROW)), 'FM0000000000') -- 500억이하면 발번된 Service No. 그대로, 초과면 10 근데 최초 발번(10)이라면 그대로
, ZSUBPO
, ZSUBSEQ
, STATS
, TXZ01
, KTEXT1
, MENGE
, MEINS
, NETWR
, TBTWR
, WAERS
, SAKTO
, WBSID
INTO V_ERP_PO_INFO.EBELN
, V_ERP_PO_INFO.EBELP
, V_ERP_PO_INFO.EXTROW
, V_ERP_PO_INFO.ZSUBPO
, V_ERP_PO_INFO.ZSUBSEQ
, V_ERP_PO_INFO.STATS
, V_ERP_PO_INFO.TXZ01
, V_ERP_PO_INFO.KTEXT1
, V_ERP_PO_INFO.MENGE
, V_ERP_PO_INFO.MEINS
, V_ERP_PO_INFO.NETWR
, V_ERP_PO_INFO.TBTWR
, V_ERP_PO_INFO.WAERS
, V_ERP_PO_INFO.SAKTO
, V_ERP_PO_INFO.WBSID
FROM (SELECT POH.ERP_PO_NO AS EBELN
, POH.ERP_PO_LINE_NO AS EBELP
, POH.ERP_PO_SERVICE_NO AS EXTROW
, POH.PO_NO AS ZSUBPO
, POH.PO_MOD_CNT AS ZSUBSEQ
, NVL2(POH.ERP_PO_NO, 'R', 'C') AS STATS
, SUBSTR(PJT.PJT_TIT, 1, 40) AS TXZ01
, SUBSTR(POH.PO_TIT, 1, 40) AS KTEXT1
, 1 AS MENGE
, 'AU' AS MEINS
, POH.PO_TOT_AMT_LOC AS NETWR
, POH.PO_TOT_AMT_LOC AS TBTWR
, 'KRW' AS WAERS
, POH.GL_ACCOUNT AS SAKTO
, POH.WBSID AS WBSID
FROM ESPPOHD POH
JOIN ESBPJHD PJT ON PJT.SYS_ID = POH.SYS_ID
AND PJT.PJT_NO = POH.PJT_NO
WHERE POH.SYS_ID = P_SYS_ID
AND POH.PO_NO = P_PO_NO) T01
JOIN (SELECT EBELN AS EBELN
, EBELP + DECODE(SIGN(EXTROW - 990), -1, 0, 10) AS EBELP -- 990이 될때까지 +0 하고 990일때 +10 한다.
, EXTROW + DECODE(SIGN(EXTROW - 990), -1, 10, -980) AS EXTROW -- 990이 될때까지 +10 하고 990일때 -980한다.
FROM (SELECT /*+ INDEX_DESC(ESPPOHD ESPPOHD_IDX_ERP) */
NVL(MAX(ERP_PO_NO), V_ERP_PO_NO) AS EBELN
, NVL(MAX(ERP_PO_LINE_NO), 30) AS EBELP
, NVL(MAX(ERP_PO_SERVICE_NO), 0) AS EXTROW
FROM ESPPOHD
WHERE SYS_ID = P_SYS_ID
AND ERP_PO_NO = V_ERP_PO_NO
AND ROWNUM = 1)) T02 ON 1 = 1
JOIN (SELECT NVL(SUM(PO_TOT_AMT_LOC), 0) AS LINE_AMT
FROM ESPPOHD
WHERE (SYS_ID
,ERP_PO_NO
,ERP_PO_LINE_NO) IN (SELECT /*+ INDEX_DESC(ESPPOHD ESPPOHD_IDX_ERP) */
SYS_ID
, ERP_PO_NO
, ERP_PO_LINE_NO
FROM ESPPOHD
WHERE SYS_ID = P_SYS_ID
AND ERP_PO_NO = V_ERP_PO_NO
AND ROWNUM = 1)) T03 ON 1 = 1;
IF V_ERP_PO_INFO.SAKTO IS NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'ESPPOHD.GL_ACCOUNT에 데이터가 없습니다.');
END IF;
IF V_ERP_PO_INFO.WBSID IS NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'ESPPOHD.WBSID에 데이터가 없습니다.');
END IF;
RETURN V_ERP_PO_INFO;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('PO_NO : ' || P_PO_NO || ' => ' || SQLERRM);
RAISE_APPLICATION_ERROR(-20000, 'PO_NO : ' || P_PO_NO || ' => ' || SQLERRM); <-- 이구문이 있으면 그냥 실행으로도(DBMS출력 없이)Exception Throw한다.
RETURN NULL;
END;