ITコーディネータのシュウです。
夜の浦和駅で撮った写真です。サッカーは好きなので、やはりどうしても地元の浦和レッズに関心が行きますね。昨年は第1ステージは優勝でしたが、年間では3位、ナビスコ杯はベスト8、天皇杯やゼロックス杯は準優勝と、頑張ってはいるんですが、あと一歩、なかなか最後勝ちきれずに、悔しい思いをしたファンが多かったのではないでしょうか。今年は、是非頑張ってほしいです！
今日は、ORACLEのパッケージについて取り上げてみたいと思います。今まで何度か例で使用してきたDBMS_OUTPUTパッケージや、DBMS_LOCKパッケージなど、ORACLE側で事前に用意されているユーティリティ・パッケージもそれに該当しますが、そのパッケージについて詳しく見てみたいと思います。
＜本日の題材＞
パッケージ（ORACLE）
パッケージは、複数のサブプログラムを1つにまとめるためのオブジェクトです。プロシージャやファンクションとは異なり、仕様部と本体を別々に作成します。仕様部には本体に含めているプログラム名などをまとめて記述し、本体には各プログラムのソースコードを個別に記述していきます。
●パッケージの構造
パッケージ仕様部
PROCEDURE proc_1 (para_1 VARCHAR2);
FUNCTION func_1 (para_2 NUMBER);
パッケージ本体
PROCEDURE proc_1 (para_1 VARCHAR2)
IS
BEGIN
…
FUNCTION func_1 (para_2 NUMBER)
IS
BEGIN
…
パッケージの場合、この仕様部さえ定義できていれば、パッケージ本体が未完成であっても、コンパイルは正常になされます。このプロシージャ proc_1 を呼び出すプログラムは、proc_1 のパッケージ本体を直接参照するのではなく、proc_1を実行するのに必要な情報をパッケージの仕様部から得ることができるので、プロシージャ proc_1 の本体部が作成されていなくてもコンパイルできるわけです。
パッケージの仕様部の作成は以下のようになります。
CREATE [ OR REPLACE ] PACKAGE <パッケージ名>
{ IS | AS }
<仕様部>
END [ <パッケージ名> ] ;
例）
CREATE OR REPLACE PACKAGE pack_test
IS
PROCEDURE customer_month_purchase(年月 VARCHAR2);
PROCEDURE customer_total_purchase;
END;
/
この仕様部の作成の処理を実行すると、
パッケージの仕様部は作成できました。
次に、パッケージの本体を作成してみます。本体の作成は以下のようになります。
CREATE [ OR REPLACE ] PACKAGE BODY <パッケージ名>
{ IS | AS }
<本体>
END [ <パッケージ名> ] ;
先ほど仕様部を作成したパッケージの本体を作成します。パッケージ名は、仕様部と本体で一致している必要があります。
CREATE OR REPLACE PACKAGE BODY pack_test
IS
PROCEDURE customer_month_purchase(V年月 VARCHAR2)
IS
BEGIN
DELETE FROM 顧客月別購入履歴
WHERE 年月 = V年月;
INSERT INTO 顧客月別購入履歴
(顧客NO, 年月, 購入回数, 購入金額)
SELECT 顧客NO, TO_CHAR(出荷日,'YYYYMM'),
COUNT(DISTINCT 売上NO), SUM(売上金額)
FROM 売上TBL
WHERE TO_CHAR(出荷日, 'YYYYMM') = V年月
GROUP BY 顧客NO, TO_CHAR(出荷日,'YYYYMM');
END customer_month_purchase;
PROCEDURE customer_total_purchase
IS
BEGIN
DELETE FROM 顧客購入履歴;
INSERT INTO 顧客購入履歴
(顧客NO, 累計購入回数, 累計購入金額)
SELECT 顧客NO, COUNT(DISTINCT 売上NO), SUM(売上金額)
FROM 売上TBL
GROUP BY 顧客NO;
END customer_total_purchase;
END;
/
これは、売上TBLのデータから、指定した年月についての顧客の月別購入回数、金額を抽出して、「顧客月別購入履歴」テーブルに登録する「customer_month_purchase」というプロシージャと、売上TBLから顧客の過去のトータルの購入回数、金額を抽出して、「顧客購入履歴」テーブルに登録する「customer_total_purchase」というプロシージャを定義しています。（どちらも一旦データを削除してから登録します）
これを実行すると、
パッケージ本体も作成されました。
では、実際に実行してみます。
最初に、パッケージの中の「customer_total_purchase」プロシージャを実行します。
BEGIN
pack_test.customer_total_purchase;
END;
/
処理結果を確認するため、顧客購入履歴テーブルを抽出します。
SELECT * FROM 顧客購入履歴
ORDER BY 顧客NO;
次に、パッケージの中の「customer_month_purchase」プロシージャを実行します。
BEGIN
pack_test.customer_month_purchase('201601');
END;
/
処理結果を確認するため、顧客月別購入履歴テーブルを抽出します。
SELECT * FROM 顧客月別購入履歴
WHERE 年月 = '201601'
ORDER BY 顧客NO;
データが作成されていることが確認できました。
※なお、パッケージの利点、使用する理由については、ここでは詳しくは記述しませんが、日本オラクル社のOTNのサイトに記載があります。（ORACLE 11g2のマニュアルにそのような説明がありました）
https://docs.oracle.com/cd/E16338_01/appdev.112/b56260/packages.htm#i2408
にほんブログ村