パッケージの作成
あとで編集
・パッケージヘッダの定義
--パッケージ定義
CREATE OR REPLACE PACKAGE BOOKS_LEARNING AS
PROCEDURE HIGH_PRICE(
RET_PRICE_SUM OUT NUMBER,
RET_CD OUT NUMBER
);
PROCEDURE INSERT_WORK(
RET_CD OUT NUMBER
);
PROCEDURE MARGE_BOOK(
RET_CD OUT NUMBER
);
END;
パッケージボディの定義
--パッケージボディ定義
CREATE OR REPLACE PACKAGE BODY BOOKS_LEARNING AS
PROCEDURE HIGH_PRICE(
RET_PRICE_SUM OUT NUMBER,
RET_CD OUT NUMBER
) IS
CURSOR CUR001 IS
SELECT
URIMEI.BOOKID AS BOOKID,
URIMEI.SLIPNO AS SLIPNO,
URIMEI.PRICE AS PRICE
FROM
URIMEIDTL_TBL URIMEI
INNER JOIN
BOOK_MST BOOK
ON
URIMEI.SLIPNO = BOOK.CMNSLIPNO
AND URIMEI.BOOKID = BOOK.BOOKID
-- AND BOOK.BOOKID IN ('001014')
ORDER BY
URIMEI.BOOKID, URIMEI.SLIPNO;
PRICE_MAX NUMBER:=0;
BEGIN
-- DBMS_OUTPUT.PUT_LINE('TEST');
FOR URIMEI IN CUR001
LOOP
DBMS_OUTPUT.PUT_LINE('URIMEI : ' || URIMEI.BOOKID || ' ' || URIMEI.PRICE);
INSERT INTO LEARNING_WORK
VALUES (URIMEI.SLIPNO, URIMEI.PRICE, URIMEI.BOOKID,SYSDATE,'');
END LOOP;
END;
PROCEDURE INSERT_WORK(
RET_CD OUT NUMBER
) IS
PROCEDURE MARGE_BOOK(
RET_CD OUT NUMBER
) IS
CURSOR CUR002 IS
SELECT
URIMEI.SLIPNO AS SLIPNO,
URIMEI.BOOKNM AS BOOKNM,
URIMEI.PRICE AS PRICE
FROM
URIMEIDTL_TBL URIMEI
INNER JOIN
BOOK_MST BOOK
ON
URIMEI.SLIPNO = BOOK.CMNSLIPNO
AND
URIMEI.BOOKID = BOOK.BOOKID
ORDER BY
URIMEI.BOOKID, URIMEI.SLIPNO;
PRICE_MAX NUMBER:=0;
BEGIN
/*
FOR URIMEI IN CUR002
LOOP
INSERT INTO LEARNING_WORK
VALUES (URIMEI.SLIPNO, URIMEI.PRICE, URIMEI.BOOKNM, SYSDATE, SYSDATE );
END LOOP;
COMMIT;
*/
MERGE INTO LEARNING_WORK LW
USING
(
SELECT
UT.SLIPNO,UT.PRICE,UT.BOOKNM
FROM
URIMEIDTL_TBL UT
INNER JOIN
BOOK_MST BM
ON
UT.SLIPNO = BM.CMNSLIPNO
AND UT.BOOKID = BM.BOOKID
) UM
ON (UM.SLIPNO= LW.SLIPNO)
WHEN MATCHED THEN
UPDATE SET
PRICE = UM.PRICE,
BOOKNM = UM.BOOKNM,
UPD_DATE = SYSDATE
WHEN NOT MATCHED THEN
INSERT
( SLIPNO, PRICE, BOOKNM, INS_DATE, UPD_DATE )
VALUES
( UM.SLIPNO,
UM.PRICE,
UM.BOOKNM,
SYSDATE,
SYSDATE
);
END;
END;
・パッケージの実行
--パッケージ実行
DECLARE
RET_CD NUMBER:=0;
BEGIN
/*
BOOKS_LEARNING.HIGH_PRICE(RET_PRICE_SUM,RET_CD) ;
DBMS_OUTPUT.PUT_LINE(RET_PRICE_SUM) ;
DBMS_OUTPUT.PUT_LINE(RET_CD ) ;
*/
BOOKS_LEARNING.INSERT_WORK(RET_CD);
--BOOKS_LEARNING.MARGE_BOOK(RET_CD);
END;
