テーブルの処理はストアドプロシージャにしてしまっても構わないものが多いので、ユーザー定義関数の多くはスカラ値関数となります。
- スカラ値関数の書式 -
スカラ値関数の書式は以下のようになります。
Create Function <関数名> (@<引数名> As <データ型>)
Returns <戻り値のデータ型>
As
Begin
:(処理)
Return <戻り値>
End
<関数名>で関数の名前を定義し、続いて（必要なら）“@<引数名> As <データ型>”で引数名とそのデータ型を明らかにします。引数や変数には、名前の先頭に@を付けます。
<戻り値のデータ型>では、関数の戻り値のデータ型を記述します。SQL Serverで利用できるデータ型を指定できます。但し、text、ntext、image、timestamp型は使えません。一般的なint、money、float、datetime型、char、varchar型などが使えます。
Begin～Endの間に、関数本体の処理を記述します。ローカル変数はDeclare命令で宣言できます。この場合も、変数名の先頭に@を付けます。処理の中ではコメント記号“--”が使えます。
最後にReturn命令に続いて<戻り値>を指定します。ローカル変数でもその他の式でも構いません。最初に宣言した戻り値の型と矛盾しないように注意しましょう。
- 消費税額を加算する関数 -
スカラ値関数の例として、商品の販売価格に消費税(５％)を加算する処理を作ってみます。販売価格を引数にしてそれに0.05(５％)を乗算し、小数点以下を切り捨てて返す――という、単純な処理です。
リスト1：消費税を加算するユーザー定義スカラ値関数“AddTax”(ex01.sql)
Create Function AddTax (@Value money)
Returns money
As
Begin
Declare @TaxValue money -- ローカル変数を宣言
-- 小数点以下を切り捨てる
SET @TaxValue = Floor(@Value * 0.05) -- 変数に戻り値を代入
Return (@Value + @TaxValue) -- 合計額を返す
End
このソースをクエリアナライザで実行すると、データベースの「関数」に新しいオブジェクト“AddTax”が追加されます。
- SELECT文で実行する -
ユーザー定義スカラ関数は、一般的なSQLの関数と同じようにSELECT文などのSQL文の中で使えます。また、ストアドプロシージャのようにEXECUTE命令で実行することも可能です。以下のようにすれば、テーブル「商品_mr」の「販売単価」の値に消費税を加算した金額を「税込単価」として表示できます(ex02.sql)。
SELECT 商品ID, 品名, dbo.AddTax(販売単価) AS 税込単価
FROM 商品_mr
ユーザー定義スカラ関数を呼び出す場合、単に関数名だけではなく先頭に「所有者（オーナー）名」を付けます。
<所有者名>.<関数名>
一般に所有者名は“dbo”（すべての権限を実行できる暗黙のユーザー名）となりますが、異なる所有者名でログインしてユーザー定義スカラ値関数を作った（Createした）場合、その所有者名を使う必要があります。
なお、テーブル値関数を呼び出す場合には、関数名の先頭に所有者名を付ける必要はありません。
- 消費税を算出する関数 -
スカラ値関数の例をいくつか挙げておきましょう。
先の“AddTax”では「販売価格」に消費税額を加算した値を返しましたが、今度は消費税額だけを返すようにしてみましょう。こちらの方が実用的です。
消費税額を計算するだけですから、処理は“AddTax”より単純になります。引数にmoney型の@Valueを取り、それに0.05を乗算して小数点以下を切り捨てるだけです。“AddTax”を書き換えてリスト2のようにすることも考えられますが、リスト3のようにすればローカル変数が不要になります。
※ サンプルのクエリファイルには、リスト3を“ex03.sql”として収録しています。リスト2のソースコードはサンプルには含まれません
リスト2：消費税だけを返すユーザー定義関数“CalcTax”
Create Function CalcTax (@Value money)
Returns money
As
Begin
Declare @TaxValue money
-- 小数点以下を切り捨てる
SET @TaxValue = Floor(@Value * 0.05)
Return (@TaxValue)
End
リスト3：変数宣言をなくして簡潔にした“CalcTax”(ex03.sql)
Create Function CalcTax (@Value money)
Returns money
As
Begin
Return (Floor(@Value * 0.05))
End
SELECT文の中で実行してみましょう。以下のようなSQLを実行すると、消費税額と税込み単価の両方を表示できます(ex04.sql)。このような処理でビューを作っておけば、消費税額の変更に柔軟に対応できます。
SELECT 商品ID, 品名, 販売単価,
dbo.CalcTax(販売単価) AS 消費税,
販売単価 + dbo.CalcTax(販売単価) AS 税込単価
FROM 商品_mr
- 税率を変更できるようにする -
ここで、実務に携わっている人は「消費税率が５％固定はおかしいんじゃないか？」と思われるはずです。増税は困ったものですが、実務レベルではそれも考慮しておかなくてはなりません。
税率の変更に備えて、引数に「販売単価」と「消費税率」の２つの値を取る関数を作ってみましょう。先に作った“CalcTax”と同じ名前の関数とし、中の処理だけを置き換えることにしましょう。
ユーザー定義関数だけではなく、ストアドプロシージャやテーブルなどCreate命令で生成できるオブジェクトは、Drop命令で削除できる他にAlter命令で中身を「置き換える」ことができます。
ストアドプロシージャのところでは、既存のオブジェクトを置き換える場合、一旦Dropして新たにCreateしていましたが、Alterを使えば簡単に上書きできます。Create、Drop、Alterの各命令については、回を追って紹介しましょう。ここではAlterの使い方だけ覚えておいてください。
さて、既存の“CalcTax”関数を２つの引数を取るものに置き換えるには、リスト4のようなソースを記述します。
リスト4：消費税も引数にするよう書き直した“CalcTax”(ex05.sql)
Alter Function CalcTax (@Value money, @TaxRate int)
Returns money
As
Begin
Declare @TaxValue money
-- 小数点以下を切り捨てる
SET @TaxValue = Floor(@Value * @TaxRate / 100)
Return (@TaxValue)
End
- テーブル値関数からビューを作る -
実行方法は、引数にフィールド名「販売単価」に続けて消費税額の「5」を「，」で区切って与えるだけです。
以下のSQL文を実行した結果は画面4のようになります。
SELECT 商品ID, 品名, 販売単価,
販売単価 + dbo.CalcTax(販売単価, 5) AS 税込単価
FROM 商品_mr
この処理をビューにするには、Create View命令を使います(ex06.sql)。
Create View vw消費税計算
AS
SELECT 商品ID, 品名, 販売単価,
販売単価 + dbo.CalcTax(販売単価, 5) AS 税込単価
FROM 商品_mr
これで、データベースに「vw消費税計算」というビューが追加され、開けば各商品の販売単価に５％の消費税額を加算した「税込価格」フィールドが表示されます。
- スカラ値関数を呼び出せる場所 -
スカラ値関数は通常の「値」を返すため、値を利用できる場所ならどこでも呼び出せます。
上の例で示したSELECT文の式フィールド（式を用いて値を生成する新たなフィールド）の他、WHERE句やGROUP BY句の条件式、UPDATE命令のSET句に用いる代入式（右辺値）、INSERT命令のVALUES句で設定する値などに利用できます。
 |
