【Oracle】ストアドプロシージャ入門

# はじめに

前回の[【Oracle】PL/SQL入門](https://qiita.com/nkojima/items/93a9c01741965f11bb8c)の記事に続いて、ストアドプロシージャについて復習した結果を記事としてまとめてみました。

ちなみに[こちらの資料](https://www.oracle.com/technetwork/jp/ondemand/database/db-basic/b-11-plsql-1448399-ja.pdf)によると、ストアドプロシージャとは「PL/SQLで記述した一連の処理手続きに名前をつけ、Oracle Databaseに格納したもの」と定義されています。

# 使用した環境

* Oracle社が提供している[Oracle Live SQL](https://livesql.oracle.com/)でOracle19cを利用しました。

# パラメータ・モード

* ストアドプロシージャ（ストアドファンクション）のパラメータ・モードには、以下の3タイプが存在します。
* 何でも出来る`INOUT`を常用すれば良さそうな気もしますが、変数ごとの役割およびスコープを明確にした方が保守性が良くなるので、基本は`IN`もしくは`OUT`のいずれかを使うのが良さそうです。

|パラメータ・モード|説明|
|---|---|
|IN|ストアドプロシージャ側で値を受け取るだけの変数。|
|OUT|ストアドプロシージャ側から呼び出し元へ値を渡すだけの変数。|
|INOUT|呼び出し元でセットされた値を、ストアドプロシージャ側で書き換えて呼び出し元へ値を渡せる変数。|

# 作成したストアドプロシージャ

## 最も基本的な構文

* 以下のコードは、引数のないストアドプロシージャとなります。
* `CREATE... プロシージャ名`でプロシージャ名を設定します。
 * `OR REPLACE`は既存のプロシージャを更新する際に付けます。
* `IS`から始まる宣言部において、処理部で使用する変数を宣言します。

```sql:最も基本的な構文
-- 日付を出力するストアドプロシージャ
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:実行用SQL
EXEC print_date;
```

```text:実行結果
28-MAR-20
```

## 引数のあるストアドプロシージャ1

* 以下はパラメータ・モードがINのストアドプロシージャです。
* For文中のループ変数（カウンタ変数、ループカウンタ）は暗黙的に宣言されるので、`IS`から始まる宣言部で変数を宣言していません。
 * [【Oracle】PL/SQL入門 - FOR文](https://qiita.com/nkojima/items/93a9c01741965f11bb8c#for%E6%96%87) 

```sql:引数のあるストアドプロシージャ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:実行用SQL
EXEC fizz_buzz(20);
```

```text:実行結果
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ブロック」を使用しています。

```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:実行用SQL
-- 宣言部
DECLARE
  tax NUMBER;
-- 処理部
BEGIN
  calc_consumption_tax(200, tax);
  DBMS_OUTPUT.PUT_LINE(tax);
END;
```

```text:実行結果
20
```

# その他

* ストアドプロシージャ/ストアドファンクションは作成時や実行時に自動的にコンパイルされるため、手動でコンパイルする必要はありません。
* 手動でコンパイルする場合は、以下の構文でコンパイルすることができます。

```sql:手動でのコンパイル
-- ストアドプロシージャの場合
ALTER PROCEDURE {プロシージャ名} COMPILE;

-- ストアドファンクションの場合
ALTER FUNCTION {ファンクション名} COMPILE;
```

# 参考URL

* [SHIFT the Oracle - ストアドプロシージャの定義と実行](https://www.shift-the-oracle.com/plsql/user-function-procedure.html)
* [ここからはじめよう Oracle PL/SQL入門](https://www.oracle.com/technetwork/jp/ondemand/database/db-basic/b-11-plsql-1448399-ja.pdf)
* [オラクルちょこっとリファレンス - PROCEDURE（プロシージャ）](https://oracle-chokotto.com/ora_procedure.html)
* [SHIFT the Oracle - プロシージャのコンパイル](https://www.shift-the-oracle.com/sql/alter-compile.html)
* [Oracle ストアド・プロシージャの呼び出し方と OUT パラメータの使い方](http://everything-you-do-is-practice.blogspot.com/2017/11/oracle-out.html)