Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

ストアドプロシージャとは

ストアドプロシージャとはデータベースに格納(Stored) されているユーザープログラム(Procedure) の一般な俗称？(※1)である。

オラクルのマニュアル上では SQL/PSM(※2) 相当の機能を

• PL/SQL プロシージャ／ストアド・プロシージャ (CREATE PROCEDURE 〜)
• PL/SQL ファンクションストアド・ファンクション (CREATE FUNCTION 〜)
• PL/SQL パッケージ／ストアド・パッケージ (CREATE PACKAGE 〜 & CREATE PACKAGE BODY 〜)

に分類している。すべてをまとめて「ストアド・サブプログラム」を呼ぶ。
また、ストアド・プロシージャとストアド・ファンクションを「スタンドアロン・サブプログラム」と定義している。

(※1) 標準 SQL/PSM (PSM:Persistent Stored Modules) だから正式な名称は「永続格納モジュール」的な名前かもしれない。しかし、ほとんど聞かない。

(※2) SQL/PSM は比較的最近の規格。(SQL99)

ストアド・サブプログラム（ストアド・プロシージャ）とは

Oracle においてのストアドプロシージャとは PL/SQL というオラクル独自の「手続き型」言語で記述した＊名前付き＊の機能モジュールである。

少々乱暴な例で説明すると、

1. 入力パラメータを受け取る。
2. データベースのデータにアクセス（参照）する。
3. 内部の変数を使って計算したり、文字列処理したりする。
4. データベースにアクセス（更新、追加、削除）する。
5. 出力パラメータを設定する、または、戻り値を返す。

といった一連の処理を行なうプログラムを作成し

1. 作成したソースコード、または、不明瞭化したコード をデータベースに登録する。
2. プログラムをユーザーがコンパイル、または、Oracle が自動的に再コンパイルして実行可能な状態にする。
3. コンパイルされたプログラムをデータベースサーバー上のリソース（CPUなど）を使って実行。
4. 実行結果をクライアントに戻す。

といった機能をもつ Oracle DBMS に格納された実行モジュールである。

Oracle データベースには PL/SQL のソースコードをコンパイルするコンパイラと PL/SQL を実行する PL/SQL 実行エンジンが組み込まれている。最近のバージョンでは条件付コンパイル(Oracle 10g R2) や中間コードではなくネイティブコンパイルする環境も用意されている。(Oracle 9i)

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

ファンクションはプロシージャの機能を内包している。プロシージャ自体に戻り値をもたせたものがファンクションであると言ってよい。(※3)

戻り値を持つことでプロシージャとは異なり SELECT expr FROM tablename の expr の部分に記述することができる。
プロシージャを SELECT の式リストに記述すると・・・

SQL> select func_dummy() from dual;   -- ファンクションは呼び出せる
FUNC_DUMMY()
------------
           1
 
SQL> select proc_dummy() from dual;   -- プロシージャは認識しない
select proc_dummy() from dual
       *
行1でエラーが発生しました。:
ORA-00904: "PROC_DUMMY": 無効な識別子です。

プロシージャは自身に戻り値はないが呼び出し元に値を「出力」することはできる。戻り値という形式ではなくパラメータに OUT 属性を設定する。これは OUT 属性 または IN OUT 属性を使ってパラメータ変数経由で結果を返却するという方法である。

(※3) ストアドファンクションの特殊な形態として 表関数 がある。表関数は SELECT の列で参照するようなものではなく、 FROM に記述し関数の出力結果群を 「データソース」 として利用するための仕組みである。

スタンドアロン・サブプログラムとストアド・パッケージの違い

ストアド・パッケージはストアドプロシージャとストアドファンクションのライブラリ集である。しかし、パッケージという箱に詰めただけのシロモノではない。モジュールの共同住宅、マンションのようなものである。

パッケージがプロシージャの集まりと比べて明らかに優れている点としては

• マンションのように共有できるスペースをもつことができる。
• パッケージ内では共有利用はできるが外部には公開しない変数、タイプ、プロシージャ、ファンクション、ユーザー定義例外などを宣言できる。
• 同一セッションであれば別パッケージやプロシージャに公開されるパブリックな変数、プロシージャ、ユーザー定義例外を宣言することができる。
• パッケージ内のサブプログラムに オーバーロード を使用することができる。
• パッケージが 共有プール から追い出されないように DBMS_SHARED_POOL パッケージ で KEEP 設定することができる。
• パッケージによりモジュール間の依存関係がシンプルになる。（システムにも人間にも管理が容易になる）
• スタンドアロン・サブプログラムより共有プールのエージアウトと再ローディングが発生しにくい。共有メモリの断片化などを防止することに一役買う。

一方でパッケージ単位にしか設定できないプラグマ宣言などもあり、あらゆる点でパッケージが優れているというわけでもない。多くのケースでメンテナンス性もよいパッケージで管理するのが PL/SQL を使うプロジェクトの定石といえる。

---

PL/SQL の言語体系

PL/SQL は Oracle が開発した固有の言語で Ada をベースにしている。

関連事項

• ローカル・サブプログラム
• ストアドプロシージャの難読化ユーティリティ