ユーザ定義関数の作成について
仕事でSQLServerのストアド、ユーザ定義関数を使用することになりました。
SQLServerは、もう2年以上触っていなかったので復習をしたいと思います。
今日はユーザ定義関数の作成の仕方から。
1 CREATE FUNCTION [dbo].[ユーザ定義関数名] (引数) 2 RETURNS 戻り値 AS 3 4 BEGIN 5 -- このBEGIN 〜 END までにプログラム本文を記述する 6 END
説明
1行目
ユーザ定義関数名
⇒ 関数実行する際に登録される関数名を記述します。
1行目
引数
⇒ 名称 型名, 名称2 型名2,… といった形式で記述。
記述例 id1 varchar(10), id2 varchar(20), stardate DATETIME
引数無しの場合は()でOK。
2行目
戻り値の設定
⇒ int, varchar等、通常の型の場合は型を記述。
本文でsql発行した結果を返したい場合はTABLE型を使用する。
記述例1 int 型を返却する場合
RETURNS int
記述例2 TABLEを返却する場合
RETURNS @a table(name varchar(10), title varchar(10))
5行目
本文
ここに実行するSQL等実際のプログラムを記述します。
なお、2行目のASはおまじないのようなもの(と思ってください)です。
おそらく必ず必要。
BEGIN〜ENDがプログラム本文になります。
それでは実際の作成したユーザ定義関数例です。
現在 データベースdbTestにtbDataというテーブルがあります。
id | name | now_date ---------------------------- a | AAA |2008/02/01 01:00:00 b | bbb |2008/02/01 02:00:00
という内容です。
(型は id=varchar(10), name = varchar(20), now_date = datetime)
これから指定したidの行を出力する
ユーザ定義関数は
1 CREATE FUNCTION [dbo].[userFuncA] (@p_id varchar(10)) 2 RETURNS @tb table(id2 varchar(10), name2 varchar(20), date2 datetime ) as 3 BEGIN 4 insert into @tb select * from tbData where id = @p_id 5 return END
となります。
ポイントとしては戻り値の@tbにselect文の結果をinsert intoしなければならないことくらいですね。
実際にユーザ定義関数を実行する場合は
SELECT * FROM [dbTest].[dbo].[userFuncA]('a')
となります。
以下が実行結果です。
id2 | name2 | date2 ---------------------------- a | AAA |2008/02/01 01:00:00
以上、参考までに。