[SQL Server] ユーザ定義関数について

SQL Serverでは、ユーザ定義関数と予め組み込まれたシステム関数がサポートされます。
今日の開発備忘録は、ユーザ定義関数です。

ユーザ定義関数は、もう少し細かく分けることができ、スカラー値関数とテーブル値関数と呼ばれています。
機能は、読んで字の如く「スカラー値」を返す関数と、「テーブル値」を返す関数となります。

どちらの関数も、CREATE FUNCTION句で関数名を宣言し、RETURNS句で戻り値を宣言し、RETURNで値を返します。
少しですが違うので具体的に見ていきます。

1)スカラー値関数
スカラー値関数は、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

2)テーブル値関数
ユーザー定義テーブル値関数は、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