ユーザ定義関数について
SQL Serverでは、ユーザ定義関数と予め組み込まれたシステム関数がサポートされます。
今日の開発備忘録は、ユーザ定義関数です。
ユーザ定義関数は、もう少し細かく分けることができ、スカラー値関数とテーブル値関数と呼ばれています。
機能は、読んで字の如く「スカラー値」を返す関数と、「テーブル値」を返す関数となります。
どちらの関数も、CREATE FUNCTION句で関数名を宣言し、RETURNS句で戻り値を宣言し、RETURNで値を返します。
少しですが違うので具体的に見ていきます。
１）スカラー値関数
スカラー値関数は、MSDNではスカラー関数と記載されているのスカラー関数が正しいのかもしれません。
この関数の戻り値には、text、ntext、image、cursor、および timestamp 以外の任意のデータ型を指定できます。
多くは、数値(int)だったり名称(varchar)だったりだと思います。
先ほども書きましたがこの関数は、RETURNS 句で返す型を定義します。
RETURNで定義した型の単一のデータ値を返します。
具体的には、以下のような感じです。
--スカラー関数FN_TEST1が有れば消す if OBJECT_ID (N'FN_TEST1',N'FN') is NOT NULL DROP FUNCTION FN_TEST1 go --テスト用テーブルT_FN_TESTが有れば消す if OBJECT_ID (N'T_FN_TEST',N'U') is NOT NULL Drop table T_FN_TEST go --テスト用テーブルT_FN_TEST作成＆データセット create table T_FN_TEST( paramID int, v_num int ) insert into T_FN_TEST values (1,1) insert into T_FN_TEST values (2,1) insert into T_FN_TEST values (3,1) insert into T_FN_TEST values (4,1) insert into T_FN_TEST values (5,1) go --スカラー関数FN_TEST1を作成 CREATE FUNCTION FN_TEST1 ( @param1 int ) RETURNS int AS BEGIN DECLARE @ret int; SELECT @ret = SUM(v_num) FROM T_FN_TEST t WHERE t.paramID >= @param1 IF (@ret IS NULL) SET @ret = 0 RETURN @ret END go --スカラー関数FN_TEST1の呼び出しテスト declare @rv int select @rv = [dbo].[FN_TEST1](3) print @rv go --テスト用テーブルT_FN_TESTを消す drop table T_FN_TEST --スカラー関数FN_TEST1を消す DROP FUNCTION FN_TEST1
２）テーブル値関数
ユーザー定義テーブル値関数は、table データ型を返します。
この関数もRETURNS句で戻り値を宣言しますが、
・インライン テーブル値関数
・複数のステートメントのテーブル値関数
で宣言とロジックが少し異なります。
ちなみに、table データ型の値を返すこの関数は、ビューとストアードプロシージャの中間のようなツールです。
ビューに比べると、まず同じようにFROM句に使用できる。ビューよりも高度SQLロジックが組める。
ストアードプロシージャに比べると、ストアードプロシージャはFROM句に使用できないので決定的に違います。
ただ、テーブル値関数は、既存のデータに関してUPDATE句は使用できませんしCREATE句も使用できません。
あくまでも参照用ってことですね。関数内で宣言したRETURNS句テーブルに関してはUPDATEもできます。
では、具体的な宣言方法へ
A.インライン テーブル値関数
RETURNS TABLEと宣言し、SELECT SQLを書くだけです。
この場合、SELECTで抽出したテーブルの定義がそのまま値として返ります。
--スカラー関数FN_TEST2が有れば消す if OBJECT_ID (N'FN_TEST2',N'IF') is NOT NULL DROP FUNCTION dbo.FN_TEST2 go --テスト用テーブルT_FN_TESTが有れば消す if OBJECT_ID (N'T_FN_TEST',N'U') is NOT NULL Drop table T_FN_TEST go --テスト用テーブルT_FN_TEST作成＆データセット create table T_FN_TEST( paramID int, v_num int ) insert into T_FN_TEST values (1,1) insert into T_FN_TEST values (2,1) insert into T_FN_TEST values (3,1) insert into T_FN_TEST values (4,1) insert into T_FN_TEST values (5,1) go --テーブル値関数FN_TEST2の作成 CREATE FUNCTION dbo.FN_TEST2 ( @param1 int ) RETURNS TABLE AS RETURN ( SELECT * from T_FN_TEST t where t.paramID > @param1 ) go --スカラー関数FN_TEST2の呼び出しテスト select * from dbo.FN_TEST2(2) select sum(v_num) from dbo.FN_TEST2(2) --テスト用テーブルT_FN_TESTを消す drop table T_FN_TEST --スカラー関数FN_TEST2を消す DROP FUNCTION FN_TEST2
B.複数のステートメントのテーブル値関数
複数の処理で返す値を整えていきます。
テーブルAから10行入れて、テーブルBから更に10行追加して、最後に一部の項目を更新するなどなど。
以下のように宣言します。
--スカラー関数FN_TEST3が有れば消す if OBJECT_ID (N'FN_TEST3',N'TF') is NOT NULL DROP FUNCTION dbo.FN_TEST3 go --テスト用テーブルT_FN_TESTが有れば消す if OBJECT_ID (N'T_FN_TEST',N'U') is NOT NULL Drop table T_FN_TEST go --テスト用テーブルT_FN_TEST作成＆データセット create table T_FN_TEST( tableID int, R1 int, R2 int, R3 int, R4 int, R5 int, R6 int ) insert into T_FN_TEST values (1,1,1,1,1,1,1) insert into T_FN_TEST values (2,1,1,1,1,1,1) insert into T_FN_TEST values (3,1,1,1,1,1,1) insert into T_FN_TEST values (4,1,1,1,1,1,1) insert into T_FN_TEST values (5,1,1,1,1,1,1) go --テーブル値関数FN_TEST3の作成 CREATE FUNCTION dbo.FN_TEST3 ( @param1 int, @param2 int, @param3 int, @param4 int ) RETURNS @rtnTable TABLE ( R1 int NOT NULL, R2 int, R3 int, R4 int, R5 int, R6 int ) AS begin insert into @rtnTable SELECT t1.tableID, t1.R2, t1.R3, t1.R4, null, null from T_FN_TEST t1 where t1.tableID > @param1 update @rtnTable set R5=R2 * @param2 insert into @rtnTable SELECT t2.tableID, t2.R2, t2.R3, t2.R4, null, null from T_FN_TEST t2 where t2.tableID > @param3 update @rtnTable set R6=R3 * @param4 return end go --スカラー関数FN_TEST3の呼び出しテスト select * from dbo.FN_TEST3(1,2,3,4) select sum(R2) from dbo.FN_TEST3(1,2,3,4) --テスト用テーブルT_FN_TESTを消す drop table T_FN_TEST --スカラー関数FN_TEST3を消す DROP FUNCTION FN_TEST3