【ストアドプロシージャ入門】SQL Server での使い方について｜サンプルも紹介

>この記事では、《SQL Server のストアドプロシージャ》について、
業務を通して学習した内容を、備忘録としてまとめています。

- __『ストアドプロシージャ』の概念__
- __『ストアドプロシージャ』の使い方サンプル__

こういった内容についてまとめています。

__※本記事は、自分で学習したことのまとめ用として書いています。
尚、解説で誤った点があれば、スローして頂ければ喜んでキャッチしますのでお願い致します。__

## 『ストアドプロシージャ』&ensp;の概念

![rpa_3.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/255884/376ce1be-012d-107b-b1b2-cfd5ea126431.jpeg)


『ストアドプロシージャ』とは…

__データベースに対する一連の手続き（Procedure）をコンパイルして１つにまとめ、データベース側にオブジェクトとして保存（Stored）したもの__です。

この一連の手続きには、さまざまな SQL文に加え、通常のSQLでは扱えない入出力のパラメータや、条件分岐や繰り返しといったプログラミング機能を含めることができます。

クライアントは作成された『ストアドプロシージャ』を呼び出すだけで、事前に登録された一連の処理を何度でも確実に実行することができます。

そのため・・・

決められた処理を繰り返し実行する必要がある場合、それを『ストアドプロシージャ』として登録しておくと非常に便利です。

## 『ストアドプロシージャ』&ensp;の構成

### 作成方法

~~~sql
CREATE PROCEDURE [ ストアドプロシージャ名 ] 
    [ @入力パラメータ1 ] [ 入力パラメータ1の型 ]
    [ @入力パラメータ2 ] [ 入力パラメータ2の型 ] [ OUTPUT ]
AS
BEGIN
    [ 処理 ]
END
~~~

### パラメータ

- （デフォルト）<br>
➡︎&ensp;値渡し
- __OUTPUT__<br>
➡︎&ensp;参照渡し

### パラメータの型

| 型名 | 詳細 |
| :---: | :---: |
| varchar | 文字列型 |
| int | 数値型 |
| decimal | 小数型 |
| date | 日付型 |
| time | 時刻型 |

### 変数の宣言

~~~sql
DECLARE [ @変数 ] [ 変数の型 ]
~~~

### 変数への代入

~~~sql
SET [ @変数 ] = [ 代入する値 ]
~~~

### 条件分岐&ensp;と&ensp;繰り返し処理

<table>
<tr>
  <th></th>
  <th>SQL</th>
  <th>概要</th>
</tr>
<tr>
  <td rowspan="2">条件分岐</td>
  <td>CASE</td>
  <td>条件の等価性を評価し、<br>それぞれの条件に当てはまる処理を実行。</td>
</tr>
<tr><td>IF</td>
  <td>条件の真偽を評価し、<br>それぞれの条件に当てはまる処理を実行。</td>
</tr>
<tr>
  <td>繰り返し処理</td>
  <td>WHILE</td>
  <td>指定された条件が真と評価されている間は、文または文のリストを繰り返し実行。<br>（指定されている条件が真になった場合のみ、繰り返しを実行）</td>
</tr>
</table>

#### ＜&ensp;CASE文&ensp;＞

`CASE`は`WHEN`リストした条件を上から順番にチェックしていき、最初に`TRUE`となる条件の結果を返します。

どれも合致せず、`ELSE`が指定されている場合は`ELSE`の結果を返します。

どれも合致せず、`ELSE`が指定されていない場合は`NULL`を返します。

##### ---&ensp;書き方&ensp;---

~~~sql
CASE 'xxx'
    WHEN 'A' THEN
        .....
    WHEN 'B' THEN
        .....
    ELSE
        .....
END
~~~

#### ＜&ensp;IF文&ensp;＞

##### ---&ensp;書き方&ensp;---

~~~sql
IF [ 条件文 ]
    .....
ELSE IF [ 条件文 ]
    .....
ELSE
    .....
~~~

#### ＜&ensp;WHILE文&ensp;＞

`WHILE`を使うと、指定した条件文が`TRUE`を返す間、ステートメントやステートメントブロックを繰り返すことができます。

途中でループを抜けたい時には`BREAK`を使って抜けることができます。

途中で続きのステートメントを実行せず`WHILE`の条件チェックに戻りたい時には`CONTINUE`を使います。

##### ---&ensp;書き方&ensp;---

~~~sql
WHILE [ 条件文 ]
BEGIN

   [ 繰り返したいステートメントブロック / BREAK / CONTINUE ]

END
~~~

## 【例題１】&ensp;ストアドプロシージャ作成｜変数&ensp;値渡し

下記は・・・

__受け取った値の条件に合致するレコードを`UPDATE`する__

ストアドプロシージャです。

~~~sql
CREATE PROCEDURE dbo.procedureA
    @inputValue int
AS
BEGIN
    -- 変数宣言
    DECLARE @variable int;
    SET @variable = @inputValue + 1;

    -- UPDATE実行
    UPDATE
        table_xxx
    SET
        number = 0
    WHERE
        id = @variable;
END
GO
~~~

### パラメータ

パラメータ`@inputValue`を受け取り、table_xxxを`UPDATE`するストアドプロシージャ`procedureA`を作成しています。

### 変数

- 変数は`DECLAREで宣言
- 変数の先頭に`@`を付ける
- `DECLARE @variable int`でint型の変数`@variable`を宣言

### 変数への代入

変数への代入は`SET`以降に記述します。

ここでは・・・

`SET @variable = @inputValue + 1`とし、

`@inputValue + 1`を`@variable`に代入しています。

## 【例題２】&ensp;ストアドプロシージャ作成｜変数&ensp;参照渡し

下記は・・・

__パラメータ`@inputValue`をストアドプロシージャに渡して、`SELECT`した結果を`@name`に代入する__

ストアドプロシージャです。

~~~sql
CREATE PROCEDURE dbo.procedureA
    @inputValue int
    @name varchar(30)OUTPUT
AS
BEGIN
    -- SELECT実行（パターン①）
    SELECT
        @name = name
    FROM
        table_xxx
    WHERE
        age = @inputValue;
        
    -- SELECT実行（パターン②）
    SET @name = (
        SELECT
            name
        FROM
            table_xxx
        WHERE
            age = inputValue;
    )
END
GO
~~~

そして・・・

SELECTの結果を変数に入れる方法は、２つあるので、簡単に解説していきます。

### SELECTした結果を変数に渡す（パターン①）

パターン１は、SQLの中に埋め込む方法です。

~~~sql
SELECT
    @name = name
FROM
    table_xxx
WHERE
    age = @inputValue;
~~~

上記のように記述します。

ここでは・・・

`@name = name`とし、`name`の結果を変数`@name`に代入しています。

### SELECTした結果を変数に渡す（パターン②）

パターン２は、変数の代入と同じように`SET`を使います。

`SELECT`した結果を`@name`に代入しています。

~~~sql
SET @name = (
    SELECT
        name
    FROM
        table_xxx
    WHERE
        age = inputValue;
)
~~~

## 『ストアドプロシージャ』のメリット【５つ】

![shells_script_3.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/255884/cf448f77-cd8f-e6d3-5d40-fb14fed1ee37.jpeg)

1. &ensp;__パフォーマンスの向上__
2. &ensp;__ネットワークトラフィックの低減__
2. &ensp;__アプリケーション保守性の向上__
3. &ensp;__セキュリティの強化__
4. &ensp;__一貫性のある処理の実行__

### メリット①：&emsp;パフォーマンスの向上

既定では、初回実行時にプロシージャがコンパイルされ、以降の実行時に再利用される実行プランが作成されます。 

クエリプロセッサは新しいプランを作成する必要がないため、通常のプロシージャの実行はそれほど時間がかかりません。

### メリット②：&emsp;ネットワークトラフィックの低減

『ストアドプロシージャ』は、クライアントからの１回のCALL文でデータベースに登録されているカプセル化された複数のロジックを実行できます。

これにより・・・

ネットワークではプロシージャを実行するための呼び出しのみが送信されるため、サーバーとクライアント間でのネットワークトラフィックを大幅に低減できます。 

>プロシージャによって提供されるコードのカプセル化が存在しない場合は、ネットワークでコードの各行を送信する必要があります。

### メリット③：&emsp;アプリケーション保守性の向上
 
『ストアドプロシージャ』でロジックをカプセル化することによって、データベースに対する定型的な処理をアプリケーションから隔離することができます。

ロジックを変更する場合は、関連する『ストアドプロシージャ』のみ更新することで対応できるため、アプリケーションの保守性と開発の生産性が向上します。

### メリット④：&emsp;セキュリティの強化

ユーザーがデータベースに直接アクセスすることを避け、『ストアドプロシージャ』へのアクセスに制限することで、不用意なデータの削除や情報漏えいを防ぐことが可能です。

### メリット⑤：&emsp;一貫性のある処理の実行

あらゆるクライアント環境から、データベース内に格納された一貫性のある共通化されたロジックを繰り返して実行できるため、データの不整合や結果のばらつきを防止できます。