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

ストアドプロシージャの定義と実行

ストアドプロシージャ（ファンクション）とは、Oracle データベースに格納(ストアド)されたプロシージャやファンクションのこと。 主に手続きのある処理を高速に処理するための手段である。
すべて(ストアド・パッケージも)をひっくるめて、単に ストアドプロシージャ （以下 ストアド・プロシージャ）と呼ばれている事も多い。

ストアド・プロシージャはクライアントとサーバーを繋ぐネットワークという遅い通信経路に SQL と結果が何度も行き交うような処理に対して非常に有効である。
一方、サーバーのシステム日付を取得するような手続きや繰り返しの無い処理には高速化の期待はできない (処理のカプセル化などという面では役に立つ)。

ストアド・プロシージャを機能別に分類しまとめたものを PL/SQL パッケージという。標準関数は SYS スキーマの STANDARD パッケージ に含まれている。

掛け算の内部定義の例

function '*' (LEFT NUMBER, RIGHT NUMBER) return NUMBER;

プロシージャ／ファンクションを定義してみる。

変数宣言と代入、条件制御、ループ処理、例外処理などがあるが、ここでは省略する。

作業は SQLPlus での作業を前提とする。

2 つの数値パラメータを受け取り、その積を求めるストアドファンクションの作成

この PL/SQL ブロックをコピー＆ペーストして SQL*Plus で実行する。(上書き防止のためプログラムは RIVUS ユーザーで定義されている)

ストアド・ファンクションを実行してみる。

SQL> SELECT PRODUCT(5,6) FROM DUAL ;
PRODUCT(5,6)
------------
         30

この関数の仕様上で許可されていない数値を指定してみる。

SQL> SELECT PRODUCT(5,-1),PRODUCT(-1,5) FROM DUAL ;
PRODUCT(5,-1) PRODUCT(-1,5)
------------- -------------
            0            -5

SQL> SELECT PRODUCT(10,10) FROM DUAL ;
SELECT PRODUCT(10,10) FROM DUAL
       *
行1でエラーが発生しました。:
ORA-06502: PL/SQL: 数値または値のエラー:
数値の精度が大きすぎます。が発生しました
ORA-06512: "RIVUS.PRODUCT", 行13

このサンプルプログラムでは戻り値を簡単に表示させるために DUAL 表 を使用しているが 純粋に ストアド・プロシージャ を実行する構文がある。

プロシージャ、ファンクションを実行する

ストアド・プロシージャを実行する

CALL procedure_name( [param1 [, param2 [, ...]]] ) 
または
EXECUTE プロシージャ名(パラメータ) 

ストアド・ファンクションの場合には

変数名 := ファンクション名(パラメータ) ;
または
CALL function_name( [param1 [, param2 [, ...]]] ) INTO :host_variable 

CALL は ストアド・プロシージャ を実行するための SQL コマンド(, 〜)であり、EXECUTE は SQL*Plus における PL/SQL ステートメントを実行するための SQL*Plus コマンド である。⇒ CALL と EXECUTE の違い

呼び出すパラメータの指定方法には 位置表記法と名前表記法 がある。（上の例は位置表記法による書き方）

変数に代入して使用する場合、結果を画面表示するには DBMS_OUTPUT パッケージ を使用する必要がある。 大抵は面倒なので簡易なテストの場合には DUAL 表を使用することが多い。 DUAL 表は、単純だが大変便利な表である。

ファンクション 詳説

説明のパート毎にリンクになっています。

CREATE OR REPLACE FUNCTION RIVUS.PRODUCT
(
   P_NUM1 IN NUMBER,
   P_NUM2 IN NUMBER
)
RETURN NUMBER
IS
  vSum    NUMBER(2) DEFAULT 0;
  -- vSum   NUMBER(2) := 0;
BEGIN
   FOR i IN 1..P_NUM2
   LOOP
     vSum := vSum + P_NUM1;
   END LOOP;
   RETURN vSum;
END;

ファンクション名の宣言

CREATE OR REPLACE FUNCTION RIVUS.PRODUCT

CREATE [ OR REPLACE] FUNCTION という DDL でファンクションを作成する。OR REPLACE のキーワードはオプションで既存のファンクションが存在したら置き換えるという意味。
CREATE だけで実行した場合、その名前が既に同一ネームスペース で使用済の場合にエラーとなる。

ファンクション名は、PRODUCT である。RIVUS. は定義する スキーマ の指定でオプション指定項目である(デフォルトは自分)。 スキーマとファンクション名の間にはドット(.) を書く。

• 関連事項
  ⇒ ネームスペース

パラメータの定義

引数に関する定義。引数がない場合には省略することが可能

(
  P_NUM1 IN NUMBER,
  P_NUM2 IN NUMBER
)

パラメータの「名前」、「入出力タイプ」、「データ型」 を指定する。
入出力には { IN | OUT | IN OUT } の 3 種類がある。データの精度を指定することはできない。（Oracle 12c 時点）

P_NUM1 IN NUMBER(1) … NG

呼び出し側をコンパイル時にエラーにはできないが、データの精度を指定した変数を宣言しその変数にパラメータを代入することで 実行時例外 を発生させるという方法は可能。

• パラメータ・モード ⇒ IN | OUT | IN OUT について詳しく知る

戻り値の定義 ( RETURN 句 )

戻り値のデータ型を宣言する。 パラメータの定義同様、データの精度を指定することはできない。

RETURN NUMBER(2) … NG

入力パラメータ同様、戻り値の変数を用意して代入時の実行時例外を利用する方法は可能。

関数のプロトタイプ宣言部の終了と変数宣言部の開始

IS または AS で終了する(どちらでもよい)。ここでは IS を使用している。

IS 〜 BEGIN

変数の宣言や内部関数の定義部

   vSum  NUMBER(2) [ DEFAULT  0 ] ;
-- vSum  NUMBER(2) [ :=  0 ];

vSum という変数名を宣言している。 NUMBER(2) の 2 は整数部の桁数で 正の上限は 99 になる。後続の DEFAULT 句にて初期値を設定している。 DEFAULT の記述がない場合 NULL が初期値となる。 DEFAULT 0 と := 0 は同じ意味。ただし、先頭に '--' を使用して、2 行目は コメント にしている。

関数本体 ( BEGIN 〜 END )

関数の本体を BEGIN 〜 END の間に記述する。
BEGIN 〜 END で囲まれた部分は ブロック（PL/SQL ブロック） と呼ばれる。 END には どの関数の終了かを明確にする為に、以下のように関数の名前を併記することも多い。

BEGIN 
 ....
END PRODUCTION;

ループ処理 FOR LOOP 〜 END LOOP

繰り返し処理の 1 つ

FOR i IN 1..P_NUM2
LOOP
  ....
END LOOP;

FOR i IN 1..P_NUM2 は 1 〜 P_NUM2 まで、ステップ 1 で ループ処理を行なう。
ここで i はループ変数で、ループの中でのみ有効な変数である。この変数は定義部に宣言する必要はない。 仮に宣言してもループ内部では別ものとして扱われる。ループで使用する変数名は他と混同させないように注意する。

P_NUM2 が 0 以下になるような場合にはループ処理はスキップされる。

計算処理

変換をともなう加算処理

vSum := vSum + P_NUM1;

vSum は NUMBER(2) 、 P_NUM1 は NUMBER 型、計算結果は vSum の整数 2 桁に丸め処理がされる。

• 関連事項
  ⇒ 暗黙変換

戻り値 RETURN ( RETURN 文 )

戻り値を返す、ファンクションのときのみに必要。RETURN で終了しない場合や 戻り値の定義 で 指定したデータ型に 暗黙変換できない場合、そのファンクションは実行時エラーになる。

RETURN vSum ;

メモ： 戻り値の定義は 「RETURN 句」 、戻り値の RETURN は 「RETURN 文」 と呼び方も区別されている。

---