ユーザー定義関数を作成するストアドファンクション：SQL実践講座（23）

» 2002年09月28日 00時00分公開

　今回は、前回（「ストアドプロシージャによる繰り返し処理」）までに作成したストアドプロシージャを基に、ユーザー定義関数（ストアドファンクション）の作成に挑戦します。ストアドプロシージャは、一連の複数の処理を実行し、その結果をSELECT文の実行結果と同様にクライアントに返すことが目的でした。ユーザー定義関数は、ストアドプロシージャと同様に一連の複数の処理を実行して、その結果を戻り値として返すことが可能な機能です。

　では早速、例題を実行しながら、SQLの確認をしていきましょう。

今回登場するストアドプロシージャ／ストアドファンクションのコマンド群は、SQL Server 2000を題材に解説しています

州ごとの顧客数を求めるストアドファンクションの作成

　前々回（「条件分岐のあるストアドプロシージャ」）の例題として紹介した「州ごとの顧客リストを求めるストアドプロシージャ」を基に、「州ごとの顧客数を求めるユーザー定義関数」を作成してみましょう。州を引数に取り、その州に存在する顧客の数を求めて、ユーザー定義関数の戻り値として返します。クエリアナライザから下記のSQLを実行し、fnNumOfContactsByRegionというユーザー定義関数を作成してみましょう。

【例1】（リストをクリックすると、別ウィンドウで表示します）

　では早速、ユーザー定義関数を実行してみます。ユーザー定義関数を呼び出す一番簡単な方法は、SELECT文を使用する方法です。下記の例題を実行してみましょう。

【例2】

SELECT dbo.fnNumOfContactsByRegion('WA') AS 'NumOfContacts'

画面1　fnNumOfContactsByRegionの実行結果（画面をクリックすると拡大表示します）

　fnNumOfContactsByRegionを、引数を 'WA' として指定して実行すると、Customersテーブルに登録されている顧客の中で、州が 'WA' に指定されている顧客の数をCount関数で求めて、ユーザー定義関数の結果として返します。このように、ユーザー定義関数はほかのシステム関数と同様に、SELECT文の中で用いたり、WHEREの条件式として指定したりすることが可能です。呼び出し方で1つ特徴的なのは、ユーザー定義関数名の前に「dbo.」とユーザー定義関数のオーナーを指定しているところです。これは仕様で、ユーザー定義関数の呼び出しの際は、最低でもオーナーを指定する必要があります。dbo.を省略すると関数名が見つからないとのエラーメッセージが表示されますので、注意してください。

fnNumOfContactsByRegionユーザー定義関数の解説

　では、fnNumOfContactsByRegionユーザー定義関数の定義を見ていきましょう。

　まず、1行目ではCREATE FUNCTION句でユーザー定義関数の名称を指定しています。例1ではfnNumOfContactsByRegionが名称です。次に (　) でくくった中に引数を指定しています。ストアドファンクションの場合には (　) が必要ありませんでしたが、ユーザー定義関数は「関数」であるため、(　) を指定することが必須となっています。引数がない場合も (　) の省略はできません。例1では、名称が「@Region」でデータ型がNVARCHAR(15)の引数を1つ指定しています。

　2行目は、RETURNS句で戻り値のデータ型を指定しています。例1では、戻り値としてINT型を指定しています。戻り値のデータ型として指定できるのは、標準のデータ型に加えて、次の例題で紹介するテーブル型を指定することが可能です。

　4行目から22行目が、ユーザー定義関数の本体になります。

　5行目では、ユーザー定義関数内で使用するローカル変数を定義しています。6行目では、引数の@Regionを検査して、NULLの場合とそうでない場合で大きく処理を分けています。それぞれのIF文のブロック内では、サブクエリを利用してSELECT文の実行結果を@NumContacts変数に代入しています。

　IF文の実行が終わった20行目では、＠NumContactsに代入された顧客数を、fnNumOfContactsByRegionユーザー定義関数の戻り値として返すために、RETURN文で＠NumContactsローカル変数を指定しています。

　RETURN句を実行するとそれ以降の処理は実行されませんので、RETURN文はユーザー定義関数の処理の最後に指定する必要があります。

ユーザー定義関数の制約

　このように、ユーザー定義関数はTransact-SQLでSQL Serverの機能を拡張させることが可能です。ユーザー定義関数はあらかじめ定義しておくことで、システム関数と同様に使用することができます。また、何段にも渡る複雑なサブクエリが必要になるSQL文では、いくつかの部分に細分化をし、それぞれをユーザー定義関数として定義をした方が、クエリの可読性が高くなる場合も少なくありません。

　ストアドプロシージャと比べて大きな制約の1つは、テーブルの構造や値の変更を伴う操作を記述できない点です。例えば、ユーザー定義関数の中でテーブルの作成（CREATE）をしたり、値の更新（UPDATE）をしたりすることはできません。このような処理が必要な場合は、ストアドプロシージャとして定義する部分とユーザー定義関数として定義する部分を分けることで、ある程度柔軟に構成が可能です。

誕生日の月ごとの従業員名リストを求める関数

　もう1つ例題を紹介しましょう。前回の例題として紹介をした誕生日の月ごとの従業員リストを求めるストアドプロシージャを基に、その結果のリストを戻り値として返すユーザー定義関数を作成してみます。引数は前回と同様に取りませんが、呼び出すと、誕生月とその誕生月の従業員名の表を戻り値として返します。クエリアナライザから下記のSQLを実行し、fnSelectBirthdayByMonthというユーザー定義関数を作成してみましょう。

【例3】（リストをクリックすると、別ウィンドウで表示します）

　では早速、ユーザー定義関数を実行してみましょう。例3で作成したfnSelectBirthdayByMonthユーザー定義関数は、「テーブル」を戻り値として返すユーザー定義関数であるため、例1の呼び出し方とは違います。一番簡単な呼び出し方は、SELECT文のFROM句として指定する方法です。下記の例題を実行してみましょう。

【例4】

SELECT * FROM fnSelectBirthdayByMonth()

画面2　fnSelectBirthdayByMonthの実行結果（画面をクリックすると拡大表示します）

　テーブルを戻り値に返すfnSelectBirthdayByMonth ユーザー定義関数は、例4に示したとおり、FROM句の様な物理的なテーブルを指定することが可能な場所に記述ができます。例4の結果としては、fnSelectBirthdayByMonth の戻り値をSELECTですべて表示するよう指定をしたため、結果としては、SelectBirthdayByMonth ストアドプロシージャとまったく同様な結果が表示されたと思います。

　fnSelectBirthdayByMonthがテーブルと同様に使用できることを確認するために、もう1つ例題を実行してみましょう。今度は、WHERE句でBirthMonthを12月に制限してみます。

【例5】

SELECT * FROM fnSelectBirthdayByMonth() WHERE BirthMonth = 12

画面3　条件を付与してfnSelectBirthdayByMonthを実行したところ（画面をクリックすると拡大表示します）

fnSelectBirthdayByMonthユーザー定義関数の解説

　では、fnSelectBirthdayByMonthユーザー定義関数の定義を見ていきましょう。

　まず、1行目ではCREATE FUNCTION句でユーザー定義関数の名称を指定しています。例3ではfnSelectBirthdayByMonthが名称です。引数はありませんが、省略不可能である (　) を関数名の後に指定しています。

　2行目から4行目は、RETURNS句で戻り値の指定をしています。例1では、戻り値としてINT型を指定していましたが、ここではテーブル型の戻り値を指定するために「TABLE」句を指定しています。TABLE句の前に指定しているのは、戻り値の変数名です。ここで指定した変数名は、ユーザー定義関数の中ではテーブルとして使用することができます。前回の例題で使用した一時テーブルと同様な扱いをすることができます。そして、このテーブルに保存された内容が、ユーザー定義関数の戻り値として返されます。例3では、@retCalendarを変数名として指定しています。

　TABLE句に引き続き、そのテーブルの定義を記述しています。記述の方法は、CREATE文で指定する方法と同様です。列の名称と、そのデータ型を指定します。定義の内容は、前回の例で使用した#Calendar一時テーブルと同様です。

　6行目以降が、fnSelectBirthdayByMonthユーザー定義関数の本体です。fnSelectBirthdayByMonthユーザー定義関数は、前回のSelectBirthByMonthストアドプロシージャと同様、大きく4つの部分で構成されています。

(1) 変数とカーソルの宣言
ユーザー定義関数内で使用するローカル変数とカーソルを宣言します

(2) 初期化処理
変数や一時テーブルの初期化を実行します

(3) メイン処理
ユーザー定義関数内のメイン処理を実行します

(4) 終了処理
ユーザー定義関数内の終了前処理を実行します

変数とカーソルの宣言

　7行目から14行目は、ローカル変数とカーソルの宣言です。前回のSelectBirthByMonthストアドプロシージャと同様です。

　16行目から18行目では、テーブル型のローカル変数を宣言しています。これは、前回使用した一時テーブルと同様に使用することができるメモリ上のテーブルですが、SQL Server上での取り扱いは「変数」です。また、ユーザー定義関数の定義に一時テーブルを使用することはできませんので、メモリ上のテーブルが必要な場合は、テーブル型のローカル変数を使用します。書式は、以下のとおりです。

DECLARE （変数名） TABLE（テーブルの定義）

　例3では、@Calendarというテーブル型変数を定義しています。テーブルの定義は、CREATE文での指定と同様です。@Calendarテーブル型変数の定義の内容は、前回のSelectBirthByMonthストアドプロシージャで作成した#Calendar一時テーブルと同様です。

初期化処理

　前回、SET NOCOUNT ONとして実行環境の変更を行いましたが、ユーザー定義関数では実行環境の変更を行うことができませんので、指定をしていません。

　21行目から27行目は、@Calenderテーブル型変数への初期化処理です。前回の、#Calender一時テーブルへの初期化処理と同様です。

メイン処理

　30行目から44行目は、カーソルから一行ずつ取得をして、@Calenderテーブル型変数へ値をセットしていく処理です。前回の、#Calenderへの処理と同様です。

　46行目から47行目で、@Calendarテーブル型変数にセットされた処理結果を、一括して@retCalenderテーブル型変数へ移しています。直接@retCalender変数へ値をセットすることも可能ですが、今回はテーブル型変数の例題を示すと同時に、前回の例題との相違がなるべく少なくなるよう、一度@Calenderテーブル型変数へセットした値を@retCalender変数へSELECT INTO文で値のコピーをしています。