[oracle] 업데이트 프로시저 생성

CREATE OR REPLACE PROCEDURE BM.BMC_BPJAEGO1
( I_GIGYANCODE IN VARCHAR2,
PO_OUTMSG OUT VARCHAR2 )
IS
P_DIJAEGOSURYANG NUMBER:=0;
P_DIJAEGOGUMOAEK NUMBER:=0;

BEGIN

FOR RC IN
(SELECT GIGYANCODE, NYEONUEOL, BUPUMCODE,
JIJAEGOSURYANG, JIJAEGOGUMOAEK, DIIPGOSURYANG,
DIIPGOGUMOAEK, DICHULGOSURYANG, DICHULGOGUMOAEK,
DIJAEGOSURYANG, DIJAEGOGUMOAEK, PYEONGGYUNDANKA
FROM BMCJAGOD
WHERE GIGYANCODE = I_GIGYANCODE
--AND BUPUMCODE = I_BUPUM
ORDER BY GIGYANCODE, NYEONUEOL)
LOOP
BEGIN
SELECT DIJAEGOSURYANG, DIJAEGOGUMOAEK
INTO P_DIJAEGOSURYANG, P_DIJAEGOGUMOAEK
FROM BMCJAGOD
WHERE GIGYANCODE = RC.GIGYANCODE
AND BUPUMCODE = RC.BUPUMCODE
AND NYEONUEOL = ( SELECT MAX(NYEONUEOL)
FROM BMCJAGOD
WHERE GIGYANCODE = RC.GIGYANCODE
AND BUPUMCODE = RC.BUPUMCODE
AND NYEONUEOL < RC.NYEONUEOL);
IF SQL%ROWCOUNT != 0 THEN
UPDATE BMCJAGOD
SET JIJAEGOSURYANG = P_DIJAEGOSURYANG,
JIJAEGOGUMOAEK = P_DIJAEGOGUMOAEK,
DIJAEGOSURYANG = P_DIJAEGOSURYANG + RC.DIIPGOSURYANG - DICHULGOSURYANG,
DIJAEGOGUMOAEK = P_DIJAEGOGUMOAEK + RC.DIIPGOGUMOAEK - DICHULGOGUMOAEK
WHERE GIGYANCODE = RC.GIGYANCODE
AND BUPUMCODE = RC.BUPUMCODE
AND NYEONUEOL = RC.NYEONUEOL;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END;

END LOOP;

COMMIT;
PO_OUTMSG := '0'; --Success!

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
PO_OUTMSG := '2'; --'<Error!> 유일키에 대해 중복에러가 발생했습니다.';
WHEN INVALID_NUMBER THEN
ROLLBACK;
PO_OUTMSG := '3'; --'<Error!> 유효하지 않은 값이 입력되었습니다';
WHEN VALUE_ERROR THEN
ROLLBACK;
PO_OUTMSG := '4'; --'<Error!> 입력된 값이 잘못되었습니다';
WHEN NO_DATA_FOUND THEN
ROLLBACK;
PO_OUTMSG := '5'; --'<Error!> 해당 자료가 없습니다';
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
PO_OUTMSG := '6'; --'<Error!> 에러발생 - 작업이 취소되었습니다.';
END ;
/