今回の記事では、SQL Serverで初めて「ユーザー定義関数」を作成する人向けに、関数の作り方や構文などを紹介していきます。
尚、SQL Serverにおける「ユーザー定義関数」も幾つか種類がありますが、当記事では「スカラー値関数」を例に解説します。
SQL Serverの「スカラー値関数」とは
まず「関数」について簡単におさらいをしたうえで、「スカラー値」やSQL Serverの「スカラー値関数」と他の関数について解説します。
「関数」とは数学やプログラミングで使われる概念であり仕組みです。
プログラミングにおける「関数」は、ある特定のタスクを行うための手続きが定義された単一のプログラムです。
同じ処理を繰り返し実行したい場合や、規模の大きいプログラムを分割する場合に作成します。
プログラミング言語ごとに予め用意されている「組み込み関数」と、プログラミング言語の利用者が自身で作る「ユーザー定義関数」があります。
関数では、プログラムを実行するための「パラメーター」を入力値として受け取れるようになっており、そのパラメーターを使用して関数内に定義された処理を実行し、その結果を出力します。
尚、入力値自体は必須ではなく、パラメーターを必要としない関数も作れます。
但し、「関数」では必ず何らかの値を出力し、値を出力しないプログラムは、関数ではなく「サブルーチン」など別の名前で呼ばれます。
また、関数が受け付ける入力値を「引数」、関数が出力する値を「戻り値」とも呼びます。
「関数」のイメージ図としては以下になります。
「スカラー値」についても簡単に説明します。
「スカラー値」は、言い換えると「単一の値」です。
この「単一」と表現しているのは、配列や辞書型の値と比べた場合における単一であり、この値は文字列や数値、真偽値など様々な形式の値を含みます。
SQL Serverで作成できる関数では、主に「スカラー値関数」と「テーブル定義関数」があります。
「スカラー値関数」は、名前の通り何らかの単一の値を返す関数です。
「テーブル値関数」は、パラーメータを元に関数内で戻り値で使用するテーブルを定義し、そのテーブルに対してデータのINSERTなどを行ったうえで、テーブルとそのデータを戻り値として返します。
例えば、パラメーターの値によって動的にテーブル定義まで変えたテーブルとそのデータを返す仕組みが必要となった場合は「テーブル値関数」が必要になります。
一般的に、利用する機会が多く初心者の人にも理解しやすいのは「スカラー値関数」です。
「スカラー値関数」の特徴と「ストアドプロシージャ」との違い
「スカラー値関数」の作り方は、一見しただけだと「ストアドプロシージャ」とも非常によく似ています。
両方とも一般的なプログラミング言語と同様に「制御構文」が使用できるため、IF文などによる処理の分岐やループなどの繰り返し処理が実装できます。
尚、SQLはISO(国際標準化機構)によって標準SQLが定められており、実行するRDBMSが異なっていてもSELECTなどの多くのステートメントは同じ構文で利用できますが、今回の記事で紹介するスカラー値関数などでも使われる制御構文の種類や仕様は、OracleやSQL ServerなどのRDBMSごとで独自にSQLの機能を拡張して実装しており標準化はされていません。
Microsoftが独自に拡張したSQL言語は「Transact-SQL(T-SQL)」と呼ばれます。
SQL Serverの「スカラー値関数」と「ストアドプロシージャ」はどちらもこの「Transact-SQL」を使用します。
当項では、「スカラー値関数」と「ストアドプロシージャ」の違いを簡単に説明しておきます。
- 何らかの単一の値を取得する目的で使用し、必ず値を戻り値にセットすることが必要。
- SELECTステートメントなどのSQL文に組み込むことで呼び出すことができ、スカラー値関数単体で実行することはできない。
- スカラー値関数内でUPDATEやINSERTなど、データの更新する処理は実装できない。
- 複数の処理をまとめたプロシージャ。
- 戻り値はセットは必須ではないが、戻り値に値をセットすることもできる。
- EXECUTEステートメントを使用し、単体で実行することができる。
- ストアドプロシージャ内ではUPDATEやINSERTなどのデータ更新処理も実装できる。
スカラー値関数は必ずSQL文に組み込んで使用され、関数内でデータの更新処理を実装することはできませんが、ストアドプロシージャはそれ単体で実行することができて、データの更新処理も実装できるのが大きな違いです。
ストアドプロシージャはバッチ処理などでもよく利用されます。
当ブログでは、SQL Serverでのストアドプロシージャの作り方を記事にしております。
興味があればこちらもご参照ください。
SQLServerにおける「スカラー値関数」の作り方
当項では、当記事の主題である「スカラー値関数」の作り方を解説します。
尚、当記事ではSQL Server Managemet Studio(以降「SSMS」と呼称)を使って作成する手順で説明していきます。
当記事で使用しているSSMSのバージョンは「v18.6」です。
SSMSで「スカラー値関数」を新規作成
SSMSを起動し、画面左側の「オブジェクト エクスポローラー」から対象となるデータベースを選択し、[プログラミング]→[関数]と開いていき、[スカラー値関数]を右クリックして[新しいスカラー値関数]を選択します。
すると、以下のようにCREATE FUNCTIONステートメントのテンプレートが読み込まれた状態でクエリエディタが開きます。
読み込まれたテンプレートをブロックごとに説明していきます。
①説明
テンプレートで読み込まれた云々の説明がコメントで書かれています。
このブロックを使って、作成するスカラー値関数の概要などを記載しても良いですし、丸っと削除していただいても結構です。
当記事では丸っと削除します。
②スカラー値関数の設定
このブロックでは作成するスカラー値関数の設定を指定します。
まず、以下の設定が自動的に読み込まれます。
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
「ANSI_NULLS」はNULLの扱いに関する設定です。
ANSI_NULLSがONであれば、ISOで定めた標準SQLの挙動になり、NULLは値ではなくNULLとして扱われます。
詳しい仕様については以下のリンクをご参照ください。
SET ANSI_NULLS (Transact-SQL) – Microsoft Lean
「QUOTED_IDENTIFIER」はダブルクォーテーションやシングルクォーテーションで文字列を囲んだときの挙動に関する設定です。
QUOTED_IDENTIFIERがONであれば、ISOで定めた標準SQLの挙動になります。
詳しい仕様については以下のリンクをご参照ください。
SET QUOTED_IDENTIFIER (Transact-SQL) – Microsoft Lean
③署名など
このブロックでは、スカラー値関数の作成者の署名や作成日付、関数の説明などを記述します。
もちろんこのブロックを消してしまっても問題はありませんが、データベースを運用していく場合、各オブジェクトの作成者や作成日時、備考などの情報を残しておかないと支障があります。
なるべく書き残すようにしましょう。
尚、当記事では丸っと削除します。
④CREATE FUNCTIONステートメント
スカラー値関数を作成する場合は、「CREATE FUNCTION」ステートメントを使用します。
このブロックでは関数名を定義しています。
尚、本来はCREATE FUNCTIONステートメントの構成上、④から⑦のブロックが必要ですが、当記事ではそれぞれ分けて説明していきます。
関数名を定義する構文は以下です。
CREATE FUNCTION [スキーマ名].[関数名]
スキーマ名を指定しない場合は、SQL Server既定のスキーマである「dbo」配下に作成されます。
また、アルファベットだけではなく、日本語を使うことも可能です。
ユーザー定義関数は自由に名前を付けることができますが、名前一つとっても保守性や使い勝手が変わってきます。
もし名付けで困る場合は、以下の命名規則がおススメです。
fn_[動詞]_[対象]
動詞では”Get”や”Count”など。
対象では”ElapsedTime”や”AverageAge”など。
日本語で命名する場合も上記の命名規則を意識されると良いかと思います。
⑤パラメーター(引数)
このブロックでパラメーター(引数)を定義します。
パラメーターを定義するブロックは丸括弧で括ります。
読み込まれたテンプレートでは以下のような記述が入っています。
CREATE FUNCTION
(
-- Add the parameters for the function here
<@Param1, sysname, @p1>
)
テンプレートの記述はぱっと見はわかり難いのですが、以下のようにパラメーター名とデータ型を定義してください。
[@パラメーター名] [データ型]
例えば以下のように記述します。
CREATE FUNCTION fn_Get_Test
(
@paraTest1 INT
)
複数パラメーターを指定する場合は以下のようにカンマで区切って記述します。
@paraTest1 INT,
@paraTest2 DATETIME
また、パラメーターを定義しなければパラメーターの無い関数になります。
その場合も丸括弧で括ったブロック自体は必要です。
以下のように記述することでパラメーター部分のブロックを省略できます。
CREATE FUNCTION dbo.fn_Get_TestFunction()
RETURNS ・・・
・・・
⑥RETURNS 戻り値の定義
スカラー値関数では必ず戻り値が必要であり、その戻り値のデータ型を定義します。
以下のように記述します。
RETURNS INT
⑦処理ブロック
BEGINから始まり、ENDで終わっているブロックのなかでこの関数の処理内容を記述します。
テンプレートでは、このBEGINからENDまでのブロックのなかに以下の三つの行が読み込まれています。
DECLARE <@ResultVar, sysname, @Result>
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
RETURN <@ResultVar, sysname, @Result>
これらについても簡単に説明しておきます。
「DECLARE」ステートメントは変数の宣言で使用します。
一般的にはブロック内の先頭で変数の宣言を行いますが、Transact-SQLの構文上はこのブロックのなかならどこで宣言しても構いません。
また、宣言をした変数に対して値を代入する場合は「SET」ステートメントを使用します。
変数の宣言及び値の代入は以下のように記述します。
-- 変数を宣言
DECLARE @variableNum INT,
DECLARE @variableDate DATETIME
-- 変数に値を代入
SET @variableNum = 12345;
SET @variableDate = '2024/12/31'
「SELECT」ステートメントも読み込まれていますが、こちらは例として読み込んできているだけであり、ブロック内で必ずSELECT文を使用しないといけないわけではありません。
関数の処理内容によってはブロック内でSELECT文を実行して、特定のテーブルから条件にあるレコードの値を取得して変数に格納したり、CURSOR(カーソル)を使用して行単位で繰り返し処理を実装することができます。
SELECT文を実行して取得したレコードの値を変数に入れる場合は以下のように記述します。
DECLARE @MaxNum
SELECT @MaxNum = MAX(age) FROM Customer
SELECT文の取得結果を代入する場合は、「SET」を指定せずに代入します。
「RETURN」ステートメントは関数の戻り値を指定します。
ユーザー定義関数の特性上、通常は変数の値を指定することが多いですが、条件分岐処理で分岐した後、固定値を入れる実装をする場合もあります。
また、戻り値に指定する値は、CREATE FUNCTIONステートメントの冒頭の「RETURNS」で定義したデータ型に合わせておくことが必要です。
変数を戻り値に指定する場合の記述例は以下です。
DECLARE @MaxNum
SELECT @MaxNum = MAX(age) FROM Customer
RETURN @MaxNum
尚、「Transact-SQL」の制御構文の使い方の詳しい解説は、「ストアドプロシージャの書き方」を過去に記事でまとめておりますため、以下のリンク先をご参照ください。
スカラー値関数の登録
必要な処理を定義して完成したら、CREATE FUNCTIONステートメントを実行して、スカラー値関数をSQL Serverのデータベース内に登録します。
「スカラー値関数」の作成例
当項では、実際のスカラー値関数を作ってみます。
まずは引数のない簡単な作成例です。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.fn_GetDate_Sample()
RETURNS DATETIME
AS
BEGIN
DECLARE @testVal DATETIME
SET @testVal = GETDATE();
RETURN @testVal
END
GO
この関数はSQL Serverの組み込み関数の「GetDate」の値を取得して返すだけの簡単なものです。
関数を呼び出す場合は以下のように使用します。
SELECT dbo.fn_GetDate_Sample()
次に、もう少しだけ複雑な関数も作成してみます。
CREATE FUNCTION [dbo].[fn_Convert_Sample]
(
@inputString nvarchar(100)
)
RETURNS nvarchar(100)
AS
BEGIN
DECLARE @processedString nvarchar(100) = '';
DECLARE @chkChar nvarchar(1) = '';
DECLARE @cnvChar nvarchar(1) = 'ア';
DECLARE @index int = 1;
WHILE @index <= LEN(@inputString)
BEGIN
-- パラメーターの文字列から1文字ずつ取り出して全文字数分ループします。
SET @chkChar = SUBSTRING(@inputString, @index, 1);
IF @chkChar = 'あ'
BEGIN
-- 置き換える。
SET @processedString = @processedString + @cnvChar;
END
ELSE
BEGIN
-- 置き換えしない。
SET @processedString = @processedString + @chkChar;
END
-- カウンタをインクリメントします。
SET @index = @index + 1;
END
RETURN @processedString;
END
この関数では、パラメーターで渡された文字列を一文字ずつチェックして、特定の文字の場合にその文字を置き換えて、その文字列を返します。
関数を呼び出す場合は以下のように使用します。
SELECT dbo.fn_Convert_Sample1('あいうえお')
この関数では、パラメーターとして渡された文字列を一文字ずつチェックして、特定の文字の場合は、別の文字に置き換えてその結果を返します。
上記の使用例であれば、「アいうえお」が返ります。
WHILEで繰り返し処理を実装しており、そのなかでIFを使って条件分岐をしています。
スカラー値関数でよくある実装例です。
後は、関数の処理のなかで別のテーブルをSELECTしてレコードの値を取得して分岐したり、レコードが取れたか否かで分岐するといったこともよくやります。
是非参考にしてください。
【おまけ】GOコマンドとセミコロン
テンプレートでは「GO」コマンドが読み込まれています。また、当記事のサンプルとして作成した処理では行の末尾にセミコロンが付与されています。
こちらについても簡単に説明しておきます。
「GO」コマンドは一連の処理の実行を指示するコマンドです。
SQLのステートメントではなく、SQL Serverで用意されている「コマンド」という扱いになります。
ローカル変数のスコープもGOが実行されたあとはスコープから外れます。
また「GO 2」などのようにGOの後ろに数字を指定すると同じ処理が複数回実行されますが、このような使い方はあまりされません。
スカラー値関数の作成においては、テンプレートで読み込んだ際にGOコマンドも記述されてきますが、よくわからなければそのまま残しておいてください。
「セミコロン」は、行の行の終わりを指定する記号です。
Microsoftの説明では「Transact-SQL ステートメントのターミネータを示します。」と説明されています。
C#やJavaでは、行の最後にセミコロンを置きますが、それと同じです。
SQL Serverではセミコロンは必須ではなく、セミコロンを末尾に記述しなくても動作しますが、プログラムの構造を視覚的にわかりやすくするためにはセミコロンを明示的に置いたほうが良いと思います。
最後に
今回の記事では、SQL Serverの「スカラー値関数」の作り方を紹介しました。
データベースやプログラミング初心者の人がSQL Server環境でSQLを十分に扱えるようになったら、次は今回紹介したスカラー値関数を作れるようにすると、さらにデータベースを効率良く扱えるようになります。
今回の記事を参考にして積極的にチャレンジしてもらえると嬉しいです。
今回も長々と読んでいただきましてありがとうございます。
それでは皆さまご機嫌よう！
