Oracle SQL

Oracle ストアド・プロシージャとストアド・ファンクションの基本と違い

ストアド (Stored) とは？

Oracle データベースには「ストアド・プロシージャ」と「ストアド・ファンクション」という機能があります。「ストアド (Stored)」というのはサーバー上に保管(ストア)されるという意味です。つまり Oracle データベースサーバーに保管できる処理を作ることが出来る機能です。

またデータベースサーバー内で処理が実行されますので、実行速度が非常に高速になります。

なおストアド・プロシージャ及びストアド・ファンクション共に、PL/SQL という Oracle 独自の言語でプログラムを記述します。

なぜストアド処理が必要なのか？

処理の共通化

Oracle データベースサーバーに、あらかじめデータベースを操作する複数の処理を「ストアド・プロシージャ」または「ストアド・ファンクション」として保存しておき、様々な端末やアプリケーションから同じ処理を呼び出すことが出来るようになります。

実際の業務での実例を挙げると、各システム毎に消費税を計算するようになっているとします。消費税率を８％から１０％に変更する場合、すべてのシステムで消費税率を変更する必要があります。これは非常に大変な作業です。

しかしストアド・プロシージャ又はストアド・ファンクションとしてサーバー側で消費税を計算するようにしておくことで、ストアド・プロシージャ又はストアド・ファンクションを修正するだけですべてのシステムの消費税率が８％から１０％に変わります。このように共通処理をまとめておくという場面で非常に重宝します。

複数の SQL 実行速度の向上

ストアド・プロシージャとストアド・ファンクションのもう１つの利点は、複数の SQL をサーバー側で一括して実行できることです。ストアド・ファンクションやストアド・プロシージャを使わないと、次のように毎回１つの SQL をクライアントからサーバーに送信して結果を受信し、次の SQL を送信し結果を受信・・・という動きになります。

毎回クライアントから SQL を実行して結果をネットワーク経由で受信すると処理全体が非常に遅くなります。このような場面でストアド・ファンクションあるいはストアド・プロシージャを使えば、一度のストアド・プロシージャやストアド・ファンクションの呼び出しだけで済み、一連の SQL は全てサーバー内で実行されて結果も処理されます。最終的な結果のみをクライアントは受け取ることが出来ます。

次にストアド・プロシージャとストアド・ファンクションで出来ることと出来ないこと、そして基本的な例をそれぞれ見ていきます。

ストアド・ファンクション (Stored Function) とは？

RETURN で戻り値を設定することができる。
SQL 内で呼び出すことが出来る。

以下にストアド・ファンクションの簡単な例を記載します。ここでは２つの数値を加算して、結果を返してくれる例です。

--
-- ストアド・ファンクション例:
-- ２つの数値を加算して結果を RETURN で返却する
--
CREATE OR REPLACE FUNCTION FUNC_SUM
(
  P_1 IN NUMBER,
  P_2 IN NUMBER
)
RETURN NUMBER
IS
  V_SUM NUMBER(3) DEFAULT 0;
BEGIN
  V_SUM := P_1 + P_2;
  RETURN V_SUM;
END;

このストアド・ファンクションは次のようにして SQL の中で呼び出すことが出来ます。通常の Oracle 関数と同様です。

-- ストアド・ファンクションは SQL の中で呼び出せる
SQL> SELECT FUNC_SUM(2, 6) FROM DUAL;

FUNC_SUM()
------------
           8

ストアド・プロシージャ (Stored Procedure) とは？

RETURN で戻り値を設定することができない。
OUT パラメータを設定することで戻り値を設定可能。
SQL 内で呼び出すことが出来ない。
呼び出すには CALL という命令を使う必要がある。

以下にストアド・プロシージャの簡単な例を記載します。こちらも２つの数値を加算して、結果を返してくれる例です。

--
-- ストアド・プロシージャ例:
-- ２つの数値を加算して結果を OUT パラメータとして返却する
--
CREATE OR REPLACE PROCEDURE PROC_SUM
(
  I_1     IN  NUMBER,
  I_2     IN  NUMBER,
  O_TOTAL OUT NUMBER
)
IS
BEGIN
  O_TOTAL := I_1 + I_2;
  RETURN;
END;

Oracle ではストアド・プロシージャを呼び出す時に、IN パラメータと OUT パラメータを設定できます。IN パラメータは、ストアド・プロシージャを呼び出す時に設定する値で、OUT パラメータは実行結果を呼び出し元に戻すためのものとなっています。つまりストアド・プロシージャを呼び出す際には、IN パラメータのみ設定し、OUT パラメータには呼び出し元で結果を受け取れる変数を用意しておく必要があるのです。

このストアド・プロシージャは、以下のように CALL 命令を使って呼び出す必要があります。(SQL*Plus にて実行する必要があります。Oracle SQL Developer では実行できません)

-- CALL 命令を使ってストアド・プロシージャを呼び出す (SQL*Plus で実行)
SQL> VARIABLE RESULT NUMBER;        -- OUT パラメータの結果を受け取る変数を定義
SQL> CALL proc_sum(1, 2, :RESULT);  -- プロシージャ呼び出し
SQL> PRINT RESULT;                  -- OUT パラメータの中身を画面表示

Call completed.

    RESULT
----------
         3

なおストアド・プロシージャは、ストアド・ファンクションのように SQL の中では呼び出せません。エラーになります。

-- SQL の中ではプロシージャは認識されない
SQL> SELECT proc_sum() FROM DUAL;

SELECT proc_sum() FROM DUAL
       *
行1でエラーが発生しました。:
ORA-00904: "PROC_SUM": 無効な識別子です。

まとめ

ストアド・プロシージャやストアド・ファンクションを利用することにより、複数のアプリケーションで同じ処理をまとめておくことができます。さらにサーバー側で SQL を一括して実行することが出来ますので実行速度が非常に高速です。

またストアド・プロシージャやストアド・ファンクションを書くための言語である PL/SQL 言語にはループや IF 文等、様々な制御処理を記述することが出来ますので、とても柔軟なデータベース・プログラムを記述することが出来ます。

Everything you do is practice

Oracle ストアド・プロシージャとストアド・ファンクションの基本と違い