データベース千夜一夜第32回

ユーザー定義関数 長谷川裕行
有限会社 手國堂

スカラ値関数

テーブルの処理はストアドプロシージャにしてしまっても構わないものが多いので、ユーザー定義関数の多くはスカラ値関数となります。


- スカラ値関数の書式 -

スカラ値関数の書式は以下のようになります。

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命令に続いて<戻り値>を指定します。ローカル変数でもその他の式でも構いません。最初に宣言した戻り値の型と矛盾しないように注意しましょう。


- 消費税額を加算する関数 -

スカラ値関数の例として、商品の販売価格に消費税(5%)を加算する処理を作ってみます。販売価格を引数にしてそれに0.05(5%)を乗算し、小数点以下を切り捨てて返す――という、単純な処理です。

リスト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



- 税率を変更できるようにする -

ここで、実務に携わっている人は「消費税率が5%固定はおかしいんじゃないか?」と思われるはずです。増税は困ったものですが、実務レベルではそれも考慮しておかなくてはなりません。

税率の変更に備えて、引数に「販売単価」と「消費税率」の2つの値を取る関数を作ってみましょう。先に作った“CalcTax”と同じ名前の関数とし、中の処理だけを置き換えることにしましょう。

ユーザー定義関数だけではなく、ストアドプロシージャやテーブルなどCreate命令で生成できるオブジェクトは、Drop命令で削除できる他にAlter命令で中身を「置き換える」ことができます。

ストアドプロシージャのところでは、既存のオブジェクトを置き換える場合、一旦Dropして新たにCreateしていましたが、Alterを使えば簡単に上書きできます。Create、Drop、Alterの各命令については、回を追って紹介しましょう。ここではAlterの使い方だけ覚えておいてください。

さて、既存の“CalcTax”関数を2つの引数を取るものに置き換えるには、リスト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消費税計算」というビューが追加され、開けば各商品の販売単価に5%の消費税額を加算した「税込価格」フィールドが表示されます。


- スカラ値関数を呼び出せる場所 -

スカラ値関数は通常の「値」を返すため、値を利用できる場所ならどこでも呼び出せます。

上の例で示したSELECT文の式フィールド(式を用いて値を生成する新たなフィールド)の他、WHERE句やGROUP BY句の条件式、UPDATE命令のSET句に用いる代入式(右辺値)、INSERT命令のVALUES句で設定する値などに利用できます。



トップページ
ユーザー定義関数とは?
スカラ値関数
スカラ値関数の書式
消費税額を加算する関数
SELECT文で実行する
消費税を算出する関数
税率を変更できるようにする
テーブル値関数からビューを作る
スカラ値関数を呼び出せる場所
インラインテーブル値関数
複数ステートメントのテーブル値関数
あとがき
Copyright © GrapeCity inc. All rights reserved.