.net column
.NET開発者のためのブログメディア

ストアドプロシージャを作成する意味とは?ストアドプロシージャを呼び出す方法

 
ストアドプロシージャを作成する意味とは?ストアドプロシージャを呼び出す方法

データベースにSQL Serverを導入していれば、Transact-SQLという言語を使用してデータベースサイドでプログラミングを行うことが可能です。これをストアドプロシージャと呼びます。ストアドプロシージャは多くの現場で活用されていますので、Entity Frameworkから呼び出せると作業の幅も広がります。ここではEntity Frameworkとストアドプロシージャについて確認していきましょう。

SE
ストアドプロシージャをEntity Frameworkから呼び出すことができたら、データ取得などストアドプロシージャサイドでコーディングする選択肢も増えますね。でもどうやって実装すれば良いのでしょうか?
PL
実装の選択肢が増えることはとても良いことですね。しかし、ストアドプロシージャを呼び出すためには、Visual Studioでの作業が少しばかり必要です。早速その手順を一緒に確認していきましょう。

そもそもストアドプロシージャとは何か


ストアドプロシージャとは、簡潔に言うと、データベースに対する幾つもの処理を1つにまとめた関数のことを言います。1つの関数にまとめることで関数を1度呼び出すだけで複数の処理を実行できるようになります。ストアドプロシージャは「戻り値の無い」関数であることもポイントです。その逆で「戻り値のある」ものはストアドファンクションと言います。

ストアドプロシージャを作成する意味とは?


複雑な条件によるSQL問い合わせを、アプリでは無くデータベース側で実行することで、クライアントとデータベース間の通信回数・通信量を抑えることでレスポンス時間を短縮させることが出来ます。また、ビジネスロジックをアプリと分離することで保守性を向上させることも出来ます。

ストアドプロシージャを作成する3つのメリット


ストアドプロシージャを作成するメリットで、「セキュリティの向上に繋げること」「パフォーマンスの向上に繋げること」「保守性の向上に繋げること」の3つがあります。詳しく解説します。

1:セキュリティの向上に繋げることが可能

ストアドプロシージャにすることで、ユーザがデータベース内のテーブルを直接操作することを制限することが出来ます。そのため、不用意なデータへの操作を防ぐことができセキュリティ向上に繋げることが出来ます。

2:パフォーマンスの向上に繋げることが可能

「データ検索」や「データ更新」を行おうとした際、埋め込みSQLの場合、「データ検索」と「データ更新」を行うのにデータベースとの通信を2度する必要があります。それに対してストアドプロシージャを採用した場合、「データ検索」と「データ更新」をひとまとめに処理するため、データベースへの通信は1度で済み、パフォーマンスの向上に繋がります。

3:保守性の向上に繋げることが可能

ストアドプロシージャは、データベースに対する手続きをアプリと別で管理することが出来ます。そうすることで、業務改善によりビジネスロジックへ変更を行う場合、対象のストアドプロシージャのみを変更するだけで対応が可能なため、アプリの保守性が向上します。

ストアドプロシージャの作成をするSQL文の例5つ


ストアドプロシージャ内で使う条件分岐である「IF」文と「CASE」文の2つと、繰り返し処理である「LOOP」文、「WHILE」文、「REPEAT」文の3つを解説していきます。

1:if

条件の真偽を評価します。条件に当てはまる処理を実行します。

2:case

条件の等価性を評価します。条件に当てはまる処理を実行します。

3:while

指定された条件が真(true)の間は、繰り返し処理します。(指定されている条件が真(true)になった場合のみ、最初とその後の繰り返しを処理します。)

4:LOOP

定義されている繰り返し文の中に組み込まれている1つまたは複数の文を繰り返し実行します。

5:repeat

指定された条件が真と評価されるまで、文または文のリストを繰り返し実行します。(最初の繰り返しは無条件で実行します。)

SQLの制御文 概要
条件分岐 if 条件の真偽を評価します。条件に当てはまる処理を実行します。 IF ‘xxxx’ = 1 THEN …; ELSE….; END IF;
case 条件の等価性を評価します。条件に当てはまる処理を実行します。 CASE ‘xxxx’ WHEN ‘A’ THEN…; ELSE END CASE;
繰り返し処理 while 指令された条件が真と評価されている間は、文または文のリストを繰り返し実行。(指定されている条件が真になった場合のみ、最初の繰り返しとその後の繰り返しを実行) WHILE ‘XXXX’ > 0 DO ・ ・ END WHILE;
LOOP 定義されている繰り返し文の中に組み込まれている1つまたは複数の文を繰り返し実行。 L1: LOOP ・ ・ END LOOP L1;
repeat 指定された条件が真と評価されるまで、文または文のリストを繰り返し実行。(最初の繰り返しは無条件で実行) REPEAT ・ ・ UNTIL…. END REPEAT;

埋め込みSQLとの違いとは


埋め込みSQLはVB.NETやPHPなどで作成するプログラム内にSQL文を埋め込むやり方です。これによりクライアントアプリの中からSQLを実行することが可能になります。一方、ストアドプロシージャは、SQL文をさまざまな制御文や条件文と組み合わせてコンパイルしたものをデータベースに格納しそれを呼び出して使います。この点が埋め込みSQLとの違いになります。

ストアドプロシージャを呼び出すための前準備


1.まずは使用するストアドプロシージャをデータベース内に作成しましょう。

2.ここからはVisual Studioでの作業です。
ソリューションエクスプローラー内で右クリック→「追加」→「新しい項目」→「Data」タブ→「ADO.NET Entity Data Model」を選択します。

3.「データベースからEFDesigner」を選択します。

4.データベースの接続先を設定します。
入力できたらテスト接続で接続確認を実施し、確認ができたら「OK」ボタンを押下します。

5.「次へ」を押下します。

6.Entity Frameworkのバージョンを選択します。

7.ストアドプロシージャを含めて「完了」ボタンを押下します。

ストアドプロシージャを呼び出すサンプルプログラム

Entity Frameworkからストアドプロシージャを呼び出し、データの登録を行うサンプルプログラムです。Window Fromsでテスト実行を行います。ID:002, CityName:Osakaを設定するようにします。

実行画面

実行結果前

実行結果後

ストアドプロシージャを作成する時の注意点


ストアドプロシージャはプログラムのように処理を組めます。処理の中で別のストアドプロシージャを呼び出すこともできてしまいます。しっかりと設計せずに既存のストアドプロシージャを組み合わせてしまうとトランザクションを保持したまま、別のストアドプロシージャを呼び出し、その中でも更新処理を行うという場合に、想定されないエラーが発生してしまうことがあります。

なぜかと言うと、呼び出された側のトランザクションで、もしもエラーが起きてロールバックが発生した際に、その場で強制終了されます。しかし、呼び出した側のトランザクションが残ったままになるため、想定されないエラーが発生するという事象が起きてしまいます。

ストアドプロシージャを積極的に導入してみましょう

ストアドプロシージャは、データベースサイドでコンパイルされるので、C#サイドでコンパイルした後の本番環境へのDLLの置き換えなどが不要です。また、検索処理速度もC#サイドでSQLクエリをコーディングするよりスピードアップできますので、Entity Frameworkとストアドプロシージャの両方を活用できるプログラマーを目指してみても良いのではないでしょうか。

ストアドプロシージャについて理解しておこう


ストアドプロシージャのメリットや基本的な使い方について解説してきました。パフォーマンスやセキュリティや保守性など多くのメリットがある反面、色々できるためメリットを潰しかねない作りにもなってしまう可能性があります。ストアドプロシージャはデータベースへの処理がメインということを念頭に、正しい設計と実装を理解しながら開発を進めていってください。


.NET分野でのキャリアアップをお考えの方は、現在募集中の求人情報をご覧ください。

また、直接のエントリーも受け付けております。

エントリー(応募フォーム)

Search

Popular

reccomended

Categories

Tags