[oracle] 프로시저 생성

CREATE OR REPLACE PROCEDURE HGIS.SP_MAKE_VMSDATA
IS
V_MSG VARCHAR2(200) :='';
BEGIN

DELETE FROM VMSCURRENTMSG;
INSERT INTO VMSCURRENTMSG SELECT VMS_ID, VMS_DESC, SPHASE, SMSG1, SMSG2, SMSG3, SMSG4, SMSG5, SMSG6, SMSG7, SMSG8, SMSG9, SMSG10, IPHASE, IMSG1, IMSG2, MSG_TYPE, IMSG_LINE1, IMSG_LINE2, EQ_STATUS FROM VMSCURRENTMSG@FRWIS;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_MSG := SUBSTR(SQLERRM, 1, 100);
INSERT INTO BATCH_RESULT@UPOC
VALUES ('HGIS FTMS 데이타 JOB', 'SP_MAKE_FTMSDATA', 0,'에러발생', V_MSG, SYSDATE );
COMMIT;
END SP_MAKE_VMSDATA;

=============== 좀 더 자세하게 ===============

자판기테이블의 판매현황을 하루 한번 이상 저장가능 하도록 만든 프로시저.

자판기id와 날짜가 기본키인 sale_vend_daily 테이블에 하루에 한번(또는 여러번) 모든 합계 데이터를 넣는 프로시저.

sale_vend_daily(판매날짜,자판기id,자판기 이름, 총판매가,총판매량)

- 커서에 SQL을 조회해서 조회된 row들을 모두 insert 해준다. row가 없을때까지.

- %TYPE

형식=> 변수명 테이블명.칼럼명%TYPE ; (기존 테이블의 칼럼에 선언된 데이터타입과 크기를 참조하여 변수를 선언
테이블 칼럼의 제약조건은 적용되지 않음.)

- %ROWTYPE
형식=> 변수명 테이블명%ROWTYPE ;

- 기존 테이블의 각 칼럼에 정의된 데이터타입과 크기를 참조
- 칼럼 수와 동일한 복수 개의 변수가 선언,
- 각 기억 장소의 구분은 “변수명.칼럼명”으로 구분

CREATE OR REPLACE PROCEDURE VENDER_SALE_DAILY_TEMP(P_ACT_DATE VARCHAR2) IS

V_SALE_DATE SALE_INFO.SALE_DATE%TYPE; --
V_VEND_ID SALE_INFO.VEND_ID%TYPE;
V_VEND_NAME SALE_INFO.VEND_NAME%TYPE;
V_SALE_AMT NUMBER(10);
V_SALE_CNT NUMBER(10);

CURSOR SALE_CUR IS

SELECT
SALE_DATE
,VEND_ID ,MAX(VEND_NAME) VEND_NAME
,SUM(SALE_PRICE) SALE_AMT ,COUNT(*) SALE_CNT
--INTO V_SALE_DATE, V_VEND_ID, V_VEND_NAME,
-- V_SALE_AMT, V_SALE_CNT
FROM SALE_INFO
WHERE SALE_DATE = P_ACT_DATE
GROUP BY SALE_DATE, VEND_ID;

S SALE_CUR%ROWTYPE;

BEGIN

DELETE FROM SALE_VEND_DAILY WHERE SALE_DATE = P_ACT_DATE;
COMMIT;

OPEN SALE_CUR;
LOOP

FETCH SALE_CUR INTO S ;
EXIT WHEN SALE_CUR%NOTFOUND;

V_SALE_DATE :=S.SALE_DATE;
V_VEND_ID :=S.VEND_ID;
V_VEND_NAME :=S.VEND_NAME;
V_SALE_AMT :=S.SALE_AMT;
V_SALE_CNT :=S.SALE_CNT;
--V_SALE_CNT := S.SALE_CNT*10;

dbms_output.put_line('SALE_DATE=====> ' || V_SALE_DATE);
dbms_output.put_line('SALE.VEND_ID=====> ' || V_VEND_ID);
dbms_output.put_line('SALE.SALE_AMT=====> ' || V_SALE_AMT);
dbms_output.put_line('SALE.SALE_CNT=====> ' || V_SALE_CNT);



insert into sale_vend_daily
( SALE_DATE
,VEND_ID
,VEND_NAME
,SALE_AMT
,SALE_CNT
)
values
( V_SALE_DATE
,V_VEND_ID ,V_VEND_NAME
,V_SALE_AMT ,V_SALE_CNT
);
COMMIT;


END LOOP;

CLOSE SALE_CUR;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END VENDER_SALE_DAILY_TEMP;