はじめに
以下の記事に続いて、ストアドパッケージについて復習した結果を記事としてまとめてみました。
使用した環境
- Oracle社が提供しているOracle Live SQLでOracle19cを利用しました。
ストアドパッケージとは
- ストアドプロシージャやストアドファンクションをまとめたものをストアドパッケージ（以下、パッケージ）と呼びます。
- パッケージは「単にストアドプロシージャやストアドファンクションをまとめたもの」ではなく、スコープがパッケージ内に限られる変数やユーザー定義例外などを利用することもできます。
- パッケージの構造は、以下の2つに大別されます。
- 仕様部：外部公開するオブジェクトの定義やサブプログラムの仕様を記述します。
- 本体部：パッケージ内部で使用される変数の定義やサブプログラムの本体（実体）などを記述します。
作成したSQL
- 以下のSQLは、消費税に関する処理をまとめたパッケージです。
- ファンクションの
get_tax_rateでは、日付に応じた消費税率（8% or 10%）を返す処理となっています。 - プロシージャの
calc_tax_includedでは、価格と販売日に応じて「消費税込み額」を返す処理となっています。 - プロシージャやファンクションの末尾が
ENDではなく、END {プロシージャ名}やEND {ファンクション名}となっている点に注意が必要です。
pkg_tax.sql
-- 仕様部
CREATE OR REPLACE PACKAGE pkg_tax AS
--ファンクション
FUNCTION get_tax_rate(sales_date IN DATE) RETURN NUMBER;
--プロシージャ
PROCEDURE calc_tax_included(price IN NUMBER, sales_date IN DATE, tax_included OUT NUMBER);
END;
/
-- 本体部
CREATE OR REPLACE PACKAGE BODY pkg_tax AS
-- グローバル変数の宣言部
-- 現在の消費税率
current_tax_rate NUMBER := 10;
-- 以前の消費税率
previous_tax_rate NUMBER := 8;
-- 消費税率を返す。
-- sales_date: 販売日。
-- return: 消費税率。
FUNCTION get_tax_rate(sales_date IN DATE) RETURN NUMBER
-- 宣言部
IS
tax_rate NUMBER;
-- 処理部
BEGIN
IF (sales_date >= TO_DATE('2019/10/01', 'yyyy/mm/dd')) THEN
tax_rate := current_tax_rate;
ELSE
tax_rate := previous_tax_rate;
END IF;
RETURN tax_rate;
END get_tax_rate;
-- 消費税込額を求める。
-- price: 商品の税抜き価格。
-- sales_date: 販売日。
-- tax_included: 消費税込額。小数点以下の端数は切り捨てとする。
PROCEDURE calc_tax_included(price IN NUMBER, sales_date IN DATE, tax_included OUT NUMBER)
-- 宣言部
IS
tax_rate NUMBER;
-- 処理部
BEGIN
-- 消費税率の取得
SELECT
get_tax_rate(sales_date)
INTO
tax_rate
FROM
DUAL;
-- 税込額の計算
tax_included := TRUNC(price * (100 + tax_rate) / 100);
END calc_tax_included;
END pkg_tax;
/
実行用SQL（ストアドファンクション）
SELECT
-- 2019年9月30日における消費税率を求める。
pkg_tax.get_tax_rate(TO_DATE('2019/09/30', 'yyyy/mm/dd'))
FROM
DUAL;
実行結果（ストアドファンクション）
8
実行用SQL（ストアドプロシージャ）
-- 宣言部
DECLARE
tax_included NUMBER;
-- 処理部
BEGIN
-- 2019年10月1日における、パソコン（59800円）の税込価格を求める。
pkg_tax.calc_tax_included(59800, TO_DATE('2019/10/01', 'yyyy/mm/dd'), tax_included);
DBMS_OUTPUT.PUT_LINE(tax_included);
END;
実行結果（ストアドプロシージャ）
65780