読者です 読者をやめる 読者になる 読者になる

ぬるすぺいす遍在

仕事がらみや物書きの勉強したことや、日々雑感

パッケージの作成

PL/SQL

あとで編集

・パッケージヘッダの定義

--パッケージ定義

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;