この記事では、《SQL Server のストアドプロシージャ》について、
業務を通して学習した内容を、備忘録としてまとめています。
- 『ストアドプロシージャ』の概念
- 『ストアドプロシージャ』の使い方サンプル
こういった内容についてまとめています。
※本記事は、自分で学習したことのまとめ用として書いています。
尚、解説で誤った点があれば、スローして頂ければ喜んでキャッチしますのでお願い致します。
『ストアドプロシージャ』 の概念
『ストアドプロシージャ』とは…
__データベースに対する一連の手続き（Procedure）をコンパイルして１つにまとめ、データベース側にオブジェクトとして保存（Stored）したもの__です。
この一連の手続きには、さまざまな SQL文に加え、通常のSQLでは扱えない入出力のパラメータや、条件分岐や繰り返しといったプログラミング機能を含めることができます。
クライアントは作成された『ストアドプロシージャ』を呼び出すだけで、事前に登録された一連の処理を何度でも確実に実行することができます。
そのため・・・
決められた処理を繰り返し実行する必要がある場合、それを『ストアドプロシージャ』として登録しておくと非常に便利です。
『ストアドプロシージャ』 の構成
作成方法
CREATE PROCEDURE [ ストアドプロシージャ名 ]
[ @入力パラメータ1 ] [ 入力パラメータ1の型 ]
[ @入力パラメータ2 ] [ 入力パラメータ2の型 ] [ OUTPUT ]
AS
BEGIN
[ 処理 ]
END
パラメータ
- （デフォルト）
➡︎ 値渡し -
OUTPUT
➡︎ 参照渡し
パラメータの型
| 型名 | 詳細 |
|---|---|
| varchar | 文字列型 |
| int | 数値型 |
| decimal | 小数型 |
| date | 日付型 |
| time | 時刻型 |
変数の宣言
DECLARE [ @変数 ] [ 変数の型 ]
変数への代入
SET [ @変数 ] = [ 代入する値 ]
条件分岐 と 繰り返し処理
| SQL | 概要 | |
|---|---|---|
| 条件分岐 | CASE | 条件の等価性を評価し、 それぞれの条件に当てはまる処理を実行。 |
| IF | 条件の真偽を評価し、 それぞれの条件に当てはまる処理を実行。 |
|
| 繰り返し処理 | WHILE | 指定された条件が真と評価されている間は、文または文のリストを繰り返し実行。 （指定されている条件が真になった場合のみ、繰り返しを実行） |
＜ CASE文 ＞
CASEはWHENリストした条件を上から順番にチェックしていき、最初にTRUEとなる条件の結果を返します。
どれも合致せず、ELSEが指定されている場合はELSEの結果を返します。
どれも合致せず、ELSEが指定されていない場合はNULLを返します。
--- 書き方 ---
CASE 'xxx'
WHEN 'A' THEN
.....
WHEN 'B' THEN
.....
ELSE
.....
END
＜ IF文 ＞
--- 書き方 ---
IF [ 条件文 ]
.....
ELSE IF [ 条件文 ]
.....
ELSE
.....
＜ WHILE文 ＞
WHILEを使うと、指定した条件文がTRUEを返す間、ステートメントやステートメントブロックを繰り返すことができます。
途中でループを抜けたい時にはBREAKを使って抜けることができます。
途中で続きのステートメントを実行せずWHILEの条件チェックに戻りたい時にはCONTINUEを使います。
--- 書き方 ---
WHILE [ 条件文 ]
BEGIN
[ 繰り返したいステートメントブロック / BREAK / CONTINUE ]
END
【例題１】 ストアドプロシージャ作成｜変数 値渡し
下記は・・・
受け取った値の条件に合致するレコードをUPDATEする
ストアドプロシージャです。
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に代入しています。
【例題２】 ストアドプロシージャ作成｜変数 参照渡し
下記は・・・
パラメータ@inputValueをストアドプロシージャに渡して、SELECTした結果を@nameに代入する
ストアドプロシージャです。
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の中に埋め込む方法です。
SELECT
@name = name
FROM
table_xxx
WHERE
age = @inputValue;
上記のように記述します。
ここでは・・・
@name = nameとし、nameの結果を変数@nameに代入しています。
SELECTした結果を変数に渡す（パターン②）
パターン２は、変数の代入と同じようにSETを使います。
SELECTした結果を@nameに代入しています。
SET @name = (
SELECT
name
FROM
table_xxx
WHERE
age = inputValue;
)
『ストアドプロシージャ』のメリット【５つ】
- パフォーマンスの向上
- ネットワークトラフィックの低減
- アプリケーション保守性の向上
- セキュリティの強化
- 一貫性のある処理の実行
メリット①： パフォーマンスの向上
既定では、初回実行時にプロシージャがコンパイルされ、以降の実行時に再利用される実行プランが作成されます。
クエリプロセッサは新しいプランを作成する必要がないため、通常のプロシージャの実行はそれほど時間がかかりません。
メリット②： ネットワークトラフィックの低減
『ストアドプロシージャ』は、クライアントからの１回のCALL文でデータベースに登録されているカプセル化された複数のロジックを実行できます。
これにより・・・
ネットワークではプロシージャを実行するための呼び出しのみが送信されるため、サーバーとクライアント間でのネットワークトラフィックを大幅に低減できます。
プロシージャによって提供されるコードのカプセル化が存在しない場合は、ネットワークでコードの各行を送信する必要があります。
メリット③： アプリケーション保守性の向上
『ストアドプロシージャ』でロジックをカプセル化することによって、データベースに対する定型的な処理をアプリケーションから隔離することができます。
ロジックを変更する場合は、関連する『ストアドプロシージャ』のみ更新することで対応できるため、アプリケーションの保守性と開発の生産性が向上します。
メリット④： セキュリティの強化
ユーザーがデータベースに直接アクセスすることを避け、『ストアドプロシージャ』へのアクセスに制限することで、不用意なデータの削除や情報漏えいを防ぐことが可能です。
メリット⑤： 一貫性のある処理の実行
あらゆるクライアント環境から、データベース内に格納された一貫性のある共通化されたロジックを繰り返して実行できるため、データの不整合や結果のばらつきを防止できます。