SQL Serverのストアドファンクションを作成する

SQL Serverのストアドには2種類あります。

  1. ストアドファンクション(FUNCTION)・・・戻り値あり
  2. ストアドプロシージャ(PROCEDURE)・・・実行のみ

戻り値があるのがファンクションで、戻り値がないのがプロシージャです。

ストアドファンクション

ファンクションを作成する

SQL Serverでストアドファンクションを作成するには「CREATE FUNCTION~」を使用します。Oracleの「OR REPLACE」は使えないのが不便ですね。

-- ファンクションを作成する 
CREATE FUNCTION {ファンクション名}(@パラメータ {型}) RETURN {型}
AS
BEGIN
  {処理}
END

パラメータ

  • (デフォルト)・・・値渡し
  • OUTPUT・・・参照渡し

  • varchar・・・文字列型
  • int・・・数値型
  • decimal・・・小数型
  • date・・・日付型

変数の宣言

  • DECLARE @変数 型

代入

  • SET {代入される値} = {代入する値}

 

ストアドファンクションを作成するCREATE FUNCTION

例1. ストアドファンクションを作成する

-- ファンクションを作成する 
CREATE FUNCTION dbo.funA RETURN int 
AS
BEGIN
  --変数宣言
  DECLARE @iCnt int;
  SET @iCnt := 1;
  RETURN @iCnt;
END
GO

ここでは「1」を返すシンプルなストアドファンクションを作成しました。

例2. ストアドファンクションを作成する(変数 値渡し)

CREATE FUNCTION dbo.funA
(@iEno int) RETURN varchar(30)
AS
BEGIN
  --変数宣言
 @vEname varchar(30);

  --SELECT実行(パターン1)
  SELECT @vEname = empname 
  FROM emp
  WHERE empno = @iEno;
 
 --SELECT実行 (パターン2)
  SET @vEname = (SELECT empname FROM emp WHERE empno = @iEno);

  RETURN @vEname;
END
GO

ここではパラメータ(変数)「iEno」に値を渡して、それをストアドファンクションが受け取って、SELECT結果を「@vEname」に返し、これを戻り値とするストアドファンクションを作成しました。

SELECTの結果を変数に入れる方法は2つあります。

SELECT結果を変数に渡す(パターン1)

パターン1はSQLの中に埋め込む方法。「SELECT @変数 = 列1 FROM テーブル」と記述します。

ここでは「@vEname = empname」とし、「empname」の結果を変数「@vEname」に入れました。

SELECT結果を変数に渡す(パターン2)

パターン2は変数の代入と同じように「SET」を使います。SELECTした結果を「@vEname」に入れました。

参照:CREATE FUNCTION (Transact-SQL)

以上、SQL Serverのストアドファンクション(FUNCTION)を作成するSQLでした。

コメント