SQL Serverでストアドファンクションのスカラー関数を作成する方法
2022-07-01
azblob://2022/11/11/eyecatch/2022-07-01-sql-server-stored-function-scalar-000.jpg

こんにちは、あおいです。
業務でSQL Serverを触っているので、たまにはT-SQLの記事も書こうと思ったり。

さて、T-SQL (Transact-SQL) とは Microsoft と Sybaseによって 開発されたスタンダードの SQL (Structured Query Language) を拡張した言語で、Microsoft SQL Server で使われています。

T-SQLには集計関数・日付関数・文字列操作の関数など便利な関数が用意されています。
Transact-SQL リファレンス | Microsoft Docs
Microsoft SQL データベース関数 | Microsoft Docs


また、ユーザー定義で独自の関数、ストアドファンクションを作成することも可能です。

ストアドファンクションでは、入力パラメータに対して複雑な処理を行って、戻り値を結果として返します。
ストアドファンクションには一つの値を返すスカラー関数とテーブルを返すテーブル関数があります。

利点として、複数クエリの呼び出しをまとめることでカプセル化が可能、構文解析を完了させた状態のため実行速度が速い、等が挙げられます。

そこで、今回はSQL Serverでストアドファンクションのスカラー関数を作成する方法を紹介したいと思います。

まず、以下のスクリプトを実行します。

DROP TABLE IF EXISTS Customer

CREATE TABLE Customer(
	CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Name NVARCHAR(20),
	Job NVARCHAR(20),
	InvestmentHistory INT
)

INSERT INTO Customer
	(Name, Job, InvestmentHistory)
VALUES
(N'佐藤一郎', N'公務員', 1),
(N'鈴木二郎', N'会社員', 3),
(N'高橋三郎', N'大学生', 0),
(N'田中四郎', N'エンジニア', 5),
(N'伊藤五郎', N'経営者', 20)





SELECT文でテーブルを確認します。





スカラー関数の基本構文は以下の通りです。
戻り値を返すので、必ず最後にRETURNをします。

CREATE FUNCTION [ファンクション名 ] (
    [@パラメータ名][パラメータ名のデータ型]
)  
RETURNS [戻り値のデータ型]
AS
BEGIN

    [処理内容]

    RETURN [戻り値]

END





上記の基本構文を参考に、以下のスカラー関数を作成します。
以下のスカラー関数(getBestFinancialProducts)は顧客IDのパラメータを渡して、CASE文で投資歴の年数に応じた金融商品の値を返します。

CREATE FUNCTION getBestFinancialProducts (
	@CustomerID INT
)  
RETURNS NVARCHAR(20)
AS
BEGIN
	
	DECLARE @BestFinancialProducts NVARCHAR(20),
			@InvestmentHistory INT
	
	SELECT @InvestmentHistory = InvestmentHistory
	FROM Customer
	WHERE CustomerID = @CustomerID

	SET @BestFinancialProducts = 
		CASE WHEN @InvestmentHistory = 0 THEN 'FUND'
		     WHEN @InvestmentHistory >= 1 AND @InvestmentHistory <= 5 THEN 'STOCK'
			 WHEN @InvestmentHistory >= 6 AND @InvestmentHistory <= 10 THEN 'CFD'
			 WHEN @InvestmentHistory > 10 THEN 'FX'
			 ELSE ''
		END

    RETURN @BestFinancialProducts

END





スクリプトを実行後、[Database]⇒[Programmability]⇒[Functions]⇒[Scalar-valued Functions]を確認すると、ユーザー定義のスカラー関数が作成されています。




試しに、作成したスカラー関数を使ってみます。





また、既存のスカラ―関数を変更したい場合、ALTER FUNCTIONのクエリを流します。
変更したいスカラ―関数を右クリックで[Modify]を選択すると、クエリが画面に表示されます。





削除したい場合、DROP FUNCTIONのクエリを流します。

DROP FUNCTION [ファンクション名]




今回はSQL Serverでストアドファンクションのスカラー関数を作成する方法を紹介させていただきました。
注意点として、ストアドファンクションではテーブルの値を挿入・変更・削除等のスコープ外のリソースを変更するような処理はできません。
スコープ外のリソースの変更処理をしたい場合、ストアドプロシージャを使いましょう。

次回はB-Treeインデックス、あるいはカーソルの記事でも書こうかな。

参考記事
ユーザー定義関数 | Microsoft Docs
ストアドプロシージャ | Microsoft Docs