DB/Oracle
[Oracle] 오라클 쿼리 SP_CONTRACT_VALUE_ITEM_INSERT& SP_CONTRACT_VALUE_ITEM_UPDATE
동탄FreeDiver
2022. 8. 26. 16:10
영업관리팀에서 잦은 CONTRACT_VALUE 테이블 데이터 일괄처리 요청으로 인하여 새 프로그램을 만들어 주었다.
프론트 = 파워빌더
백엔드 = 오라클(SP)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SP_CONTRACT_VALUE_ITEM_INSERT 쿼리
CREATE OR REPLACE PROCEDURE SP_CONTRACT_VALUE_ITEM_INSERT
(OLD_ITEM_CODE CONTRACT_VALUE.ITEM_CODE%TYPE
,NEW_ITEM_CODE CONTRACT_VALUE.ITEM_CODE%TYPE
,CHANGE_EMP CONTRACT_VALUE.LASTEMP%TYPE
,OLD_TERM_TO DATE
,NEW_TERM_FROM DATE
)
IS
BEGIN
INSERT INTO CONTRACT_VALUE
SELECT SALES_CODE
,CUST_CODE
,NEW_ITEM_CODE
,BUSINESS_GUBUN
,BASE_AMT
,ORDER_AMT
,LOWEST_AMT
,CAMT_PRCTYPE
,CONTRACT_AMT
,RND_RATE
,NEW_TERM_FROM
,TERM_TO
,SYSDATE
,CONTRACT_GUBUN
,MON_SOMO
,DRAFT_DATE
,DRAFT_NO
,USE_YN
,CHANGE_EMP
,SYSDATE
,CHANGE_EMP
,SYSDATE
,CONTRACT_TYPE_1
,CONTRACT_TYPE_2
,CONTRACT_TYPE_3
,CONTRACT_TYPE_4
,CONTRACT_TYPE_5
,CONTRACT_TYPE_6
,CONTRACT_TYPE_7
FROM CONTRACT_VALUE
WHERE TERM_TO = OLD_TERM_TO
AND ITEM_CODE = OLD_ITEM_CODE;
COMMIT;
END;-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SP_CONTRACT_VALUE_ITEM_UPDATE 쿼리
CREATE OR REPLACE PROCEDURE SP_CONTRACT_VALUE_ITEM_UPDATE
(OLD_ITEM_CODE CONTRACT_VALUE.ITEM_CODE%TYPE
,CHANGE_EMP CONTRACT_VALUE.LASTEMP%TYPE
,OLD_TERM_TO DATE
,NEW_TERM_TO DATE
)
IS
BEGIN
UPDATE CONTRACT_VALUE
SET TERM_TO = NEW_TERM_TO
,CHANGE_DATE = SYSDATE
,LASTEMP = CHANGE_EMP
,LASTYMD = SYSDATE
WHERE TERM_TO = OLD_TERM_TO
AND ITEM_CODE = OLD_ITEM_CODE;
COMMIT;
END;