ユーザー定義関数

ユーザー定義関数

関数は、ユーザー自身が作成（定義）することもできます。このような関数をユーザー定義関数

（UDF：User Defined Function）と呼んでいます。

ユーザー定義関数を作成するには、「CREATE FUNCTION」というステートメントを使用します。

構文は、次のとおりです。

CREATE FUNCTION 関数名 ( [@パラメーター名 データ型], ･･･ ) RETURNS 戻り値のデータ型

[AS]

BEGIN

ステートメント RETURN 戻り値 END

パラメーターと AS は省略可能です。BEGIN ～ END で囲まれた領域の中へ処理したい内容を記 述し、“RETURN” に続けて戻り値を記述します。

Let's Try

それでは、ユーザー定義関数を作成してみましょう。

1. 次のように左右の半角スペースを除去する「TRIM」関数を作成します。

USE sampleDB go

CREATE FUNCTION TRIM ( @param1 varchar(100) ) RETURNS varchar(100)

AS BEGIN

RETURN RTRIM( LTRIM(@param1) ) END

名前が「dbo.TRIM」と、「dbo.」が付いていることに注目します。dboは、DataBase Owner の略で、データベースの所有者という意味です。SQL Server の管理者アカウントを利用して、

SQL Server へ接続している場合には、自動的に dbo としてデータベースを操作しています。

したがって、dbo が作成したTRIM 関数ということで「dbo.TRIM」となります。

3. 続いて、次のように入力して、作成したユーザー定義関数を使用してみましょう。

SELECT dbo.TRIM(' 小田 ')

結果は、左右の半角スペースが削除されることを確認できます。@param1 へ “ 小田 ” を与え、関数の中では「RTRIM(LTRIM(@param1))」が実行されているという形です。

4. 続いて、「emp」テーブルの「empname」列に対して、「dbo.TRIM」関数を実行してみま しょう。

SELECT dbo.TRIM(empname) + 'さん', * FROM emp

作成したユーザー定義関数 今回作成した関数は1つの 値を返すので「スカラー値

関数」と呼ばれる

char(50) で定義された「empname」列の右側の余分な空白が、TRIM 関数によって削除で きていることを確認できます。

Note： .NET Framework 言語を使ったユーザー定義関数（SQLCLR）

ユーザー定義関数（UDF）は、Visual Basic や C# などの .NET Framework 言語を使っても作成することがで きます。たとえば、Transact-SQL の ROUND 関数は四捨五入（算術型丸め）ですが、.NET Framework 言語に は、「銀行型丸め」を実装している「Math.Round」メソッドが用意されているので、このメソッドを呼び出すよう なユーザー定義関数も簡単に作成することができます。

なお、.NET Framework 言語で作成した UDF は「SQLCLR 関数」とも呼ばれます。

Goal！

以上ですべての操作が完了です。最後までこの自習書の内容を試された皆さま、いかがでしたでし ょうか？ 今回は、入門編ということで、Transact-SQL の基本的な操作方法のご紹介のみになり ました。動的 SQL（sp_executesql）やストアド プロシージャの作成方法、トランザクション、

エラー処理といった応用的な利用方法については、本自習書シリーズの「開発者のための Transact-SQL 応用」で説明していますので、ぜひチャレンジしてみてください