SQL ユーザー定義関数としての SQL プロシージャーの再作成

データベース管理システム内のパフォーマンスを最大化するために、単純な SQL プロシージャーを SQL ユーザー定義関数として書き直すことができます。

このタスクについて

プロシージャーと関数とでは、ルーチン本体が SQL PL を含められるコンパウンド・ブロックでインプリメントされているという事実が共通しています。両方とも、同じ SQL PL ステートメントは、BEGIN および END キーワードで区切られるコンパウンド・ブロック内に組み込まれます。

手順

SQL プロシージャーを SQL 関数に変換する場合には、いくつかの注意事項があります。

これを実行する主なそして唯一の理由は、ロジックのみがデータを照会する場合のルーチンのパフォーマンスを向上させることです。
スカラー関数では、関数の出力パラメーターには値を直接割り当てることができないという事実に対処するために、戻り値を保持する変数を宣言することが必要になる場合があります。ユーザー定義のスカラー関数の出力値は、関数の RETURN ステートメントでのみ指定されます。
SQL 関数がデータを変更する場合、MODIFIES SQL 節を使用して明示的に作成し、データを変更する SQL ステートメントを含めることができるようにします。

例

続く例では、論理的に同等の SQL プロシージャーおよび SQL スカラー関数を示しています。これら 2 つのルーチンは、同じ入力値が指定されていれば機能的には同じ出力値を提供します。ただしそれぞれはいくらか異なる方法でインプリメントされ、呼び出されます。


  CREATE PROCEDURE GetPrice (IN Vendor CHAR(20), 
                             IN Pid INT, 
                             OUT price DECIMAL(10,3)) 
  LANGUAGE SQL 
  BEGIN 
  
    IF Vendor = 'Vendor 1' 
     THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid); 
    ELSE IF Vendor = 'Vendor 2' 
     THEN SET price = (SELECT Price FROM V2Table 
	                   WHERE Pid = GetPrice.Pid); 
    END IF; 
  END

このプロシージャーは 2 つの入力パラメーター値を取り、入力パラメーター値に基づいて条件付きで決定された出力パラメーター値を戻します。これは IF ステートメントを使用しています。この SQL プロシージャーは、CALL ステートメントを実行して呼び出します。例えば、CLP から以下を実行できます。


  CALL GetPrice( 'Vendor 1',  9456, ?)

この SQL プロシージャーは、以下のように論理的に同等な SQL 表関数として再作成できます。


  CREATE FUNCTION GetPrice (Vendor CHAR(20), Pid INT)  
      RETURNS  DECIMAL(10,3) 
  LANGUAGE SQL  MODIFIES SQL
  BEGIN 
    DECLARE price DECIMAL(10,3); 

    IF Vendor = 'Vendor 1' 
      THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid); 
    ELSE IF Vendor = 'Vendor 2' 
      THEN SET price = (SELECT Price FROM V2Table 
      			      WHERE Pid = GetPrice.Pid); 
    END IF; 
  
    RETURN price; 
  END

この関数は 2 つの入力パラメーターを取り、入力パラメーター値に基づいて条件付きで決定された単一のスカラー値を戻します。関数が戻されるまで、戻される値を保持するために、これには price というローカル変数の宣言および使用が必要です。ただし SQL プロシージャーは出力パラメーターを変数として使用できます。機能的にはこれら 2 つのルーチンは同じロジックを実行します。

当然のことですが、それぞれのルーチンの実行インターフェースは異なるものです。 SQL プロシージャーを単に CALL ステートメントで呼び出す代わりに、SQL 関数は式が許可されている SQL ステートメント内で呼び出す必要があります。たいていの場合これは問題ではなく、ルーチンによって戻されるデータを即時に操作することを意図している場合は、実際には利点があります。ここで SQL 関数を呼び出す 2 つ方法の例を示します。

以下のように VALUES ステートメントを使用して呼び出すことができます。


  VALUES (GetPrice('Vendor 1', 9456))

例えば値を表から選択し、関数の結果に基づいて行をフィルター処理する場合などは、以下のように SELECT ステートメントで呼び出すこともできます。


  SELECT VName FROM Vendors WHERE GetPrice(Vname, Pid) < 10