はじめに
前回の【Oracle】PL/SQL入門の記事に続いて、ストアドプロシージャについて復習した結果を記事としてまとめてみました。
ちなみにこちらの資料によると、ストアドプロシージャとは「PL/SQLで記述した一連の処理手続きに名前をつけ、Oracle Databaseに格納したもの」と定義されています。
使用した環境
- Oracle社が提供しているOracle Live SQLでOracle19cを利用しました。
パラメータ・モード
- ストアドプロシージャ（ストアドファンクション）のパラメータ・モードには、以下の3タイプが存在します。
- 何でも出来る
INOUTを常用すれば良さそうな気もしますが、変数ごとの役割およびスコープを明確にした方が保守性が良くなるので、基本はINもしくはOUTのいずれかを使うのが良さそうです。
| パラメータ・モード | 説明 |
|---|---|
| IN | ストアドプロシージャ側で値を受け取るだけの変数。 |
| OUT | ストアドプロシージャ側から呼び出し元へ値を渡すだけの変数。 |
| INOUT | 呼び出し元でセットされた値を、ストアドプロシージャ側で書き換えて呼び出し元へ値を渡せる変数。 |
作成したストアドプロシージャ
最も基本的な構文
- 以下のコードは、引数のないストアドプロシージャとなります。
-
CREATE... プロシージャ名でプロシージャ名を設定します。-
OR REPLACEは既存のプロシージャを更新する際に付けます。
-
-
ISから始まる宣言部において、処理部で使用する変数を宣言します。
最も基本的な構文
-- 日付を出力するストアドプロシージャ
CREATE OR REPLACE PROCEDURE print_date
-- 宣言部
IS
date_time VARCHAR2(50);
-- 処理部
BEGIN
SELECT SYSDATE
INTO date_time
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(date_time);
END;
実行用SQL
EXEC print_date;
実行結果
28-MAR-20
引数のあるストアドプロシージャ1
- 以下はパラメータ・モードがINのストアドプロシージャです。
- For文中のループ変数（カウンタ変数、ループカウンタ）は暗黙的に宣言されるので、
ISから始まる宣言部で変数を宣言していません。
引数のあるストアドプロシージャ1
-- FizzBuzz問題を解く。
-- endNumber: FizzBuzzの終了値
CREATE OR REPLACE PROCEDURE fizz_buzz(endNumber IN NUMBER)
IS
BEGIN
FOR i IN 1..endNumber LOOP
IF (MOD(i, 3) = 0 AND MOD(i, 5) = 0) THEN
DBMS_OUTPUT.PUT_LINE('Fizz Buzz');
ELSIF (MOD(i, 3) = 0) THEN
DBMS_OUTPUT.PUT_LINE('Fizz');
ELSIF (MOD(i, 5) = 0) THEN
DBMS_OUTPUT.PUT_LINE('Buzz');
ELSE
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;
実行用SQL
EXEC fizz_buzz(20);
実行結果
1
2
Fizz
4
Buzz
Fizz
7
8
Fizz
Buzz
11
Fizz
13
14
Fizz Buzz
16
17
Fizz
19
Buzz
引数のあるストアドプロシージャ2
- 以下はパラメータ・モードがINとOUTの2種類が存在するストアドプロシージャです。
- 消費税額をパラメータ・モードが
OUTの変数に入れて呼び出し元に返しています。
- 消費税額をパラメータ・モードが
- 実行用SQLは「無名PL/SQLブロック」を使用しています。
引数のあるストアドプロシージャ1
-- 消費税10%時の消費税額を求める。
-- price: 商品の税抜き価格。
-- tax: 消費税額。小数点以下の端数は切り捨てとする。
CREATE OR REPLACE PROCEDURE calc_consumption_tax(price IN NUMBER, tax OUT NUMBER)
-- 宣言部
IS
-- 処理部
BEGIN
SELECT TRUNC(price / 10)
INTO tax
FROM DUAL;
END;
実行用SQL
-- 宣言部
DECLARE
tax NUMBER;
-- 処理部
BEGIN
calc_consumption_tax(200, tax);
DBMS_OUTPUT.PUT_LINE(tax);
END;
実行結果
20
その他
- ストアドプロシージャ/ストアドファンクションは作成時や実行時に自動的にコンパイルされるため、手動でコンパイルする必要はありません。
- 手動でコンパイルする場合は、以下の構文でコンパイルすることができます。
手動でのコンパイル
-- ストアドプロシージャの場合
ALTER PROCEDURE {プロシージャ名} COMPILE;
-- ストアドファンクションの場合
ALTER FUNCTION {ファンクション名} COMPILE;