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

2018.03.20

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

  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

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

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

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

例２. ストアドファンクションを作成する（変数 値渡し）

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の結果を変数に入れる方法は２つあります。

SELECT結果を変数に渡す（パターン1）

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

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

SELECT結果を変数に渡す（パターン2）

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

参照：CREATE FUNCTION (Transact-SQL)

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

コメント