SQL Serverのストアドプロシージャを作成する

2018.03.20

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

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

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

ストアドプロシージャ

プロシージャを作成する

SQL Serverのストアドプロシージャを作成するには「CREATE PROCEDURE~」を使用します。Oracleの「OR REPLACE」は使えないのが不便ですね。

-- プロシージャを作成する 
CREATE PROCEDURE {プロシージャ名}(@パラメータ {型} {OUTPUT})
AS
BEGIN
  ｛処理｝
END

パラメータ

  • （デフォルト）・・・値渡し
  • OUTPUT・・・参照渡し

型

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

変数の宣言

  • DECLARE @変数 型

代入

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

 

ストアドプロシージャを作成するCREATE PROCEDURE

ストアドプロシージャにはパラメータに値だけを渡す「値渡し」と「参照渡し」があります。この２種類の作成例を紹介します。

例１. ストアドプロシージャを作成する（変数 値渡し）

-- プロシージャを作成する 
CREATE PROCEDURE dbo.proA(@iEno int)
AS
BEGIN
  --変数宣言
  DECLARE @iCnt int;
  SET @iCnt = @iEno + 1;

　--UPDATE実行
  UPDATE emp 
  SET ord_no = 0
  WHERE empno = @iCnt;
END
GO

ここでは受け取った値の条件に合致するレコードをUPDATEするシンプルなストアドプロシージャを作成しました。

パラメータ

パラメータ「@iEno」を受け取り、empテーブルをUPDATEするストアドプロシージャ「proA」を作成しました。

変数

変数は「DECLARE」で宣言します。変数名の先頭に「@」をつけます。「DECLARE @iCnt int」でint型の変数「@iCnt 」を宣言しました。

代入

変数の代入は「SET」以降に記述します。ここでは「SET @iCnt = @iEno + 1;」とし、「@iEno + 1」を「@iCnt」に代入しました。

例２. ストアドプロシージャを作成する（変数 参照渡し）

-- プロシージャを作成する 
CREATE PROCEDURE dbo.proA
(@iEno int, @vEname varchar(30) OUTPUT)
AS
BEGIN
  --SELECT実行（パターン1）
  SELECT @vEname = empname 
  FROM emp
  WHERE empno = @iEno;

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

ここではパラメータ（変数）「iEno」に値を渡して、それをストアドプロシージャが受け取って、SELECT結果を「@vEname」に返すストアドプロシージャを作成しました。

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

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

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

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

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

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

 

SQL Serverのストアドプロシージャとして、値渡しと参照渡しの２種類を紹介しました。

参照：CREATE PROCEDURE (Transact-SQL)

以上、SQL Serverのストアドプロシージャ(PROCEDURE)を作成するSQLでした。

コメント