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

SQL Server
データベース

公開日時: 2020-03-30 　更新日時: 2022-06-13

この記事でわかること

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

システム エンジニア: ストアドプロシージャをEntity Frameworkから呼び出すことができたら、データ取得などストアドプロシージャサイドでコーディングする選択肢も増えますね。でもどうやって実装すれば良いのでしょうか？

プロジェクト リーダー: 実装の選択肢が増えることはとても良いことですね。しかし、ストアドプロシージャを呼び出すためには、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.まずは使用するストアドプロシージャをデータベース内に作成しましょう。


USE [DB_TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[sp_Test001]
          @Id                nvarchar(10)
         ,@City     nvarchar(10)
 
AS
BEGIN
 
        INSERT INTO [dbo].[City]
                           ([Id]
                           ,[CityName])
                 VALUES
                           (@Id
                           ,@City)
 
END;

USE [DB_TEST]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[sp_Test001]

@Id nvarchar(10)

,@City nvarchar(10)

BEGIN

INSERT INTO [dbo].[City]

([Id]

,[CityName])

VALUES

(@Id

,@City)

END;

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を設定するようにします。


using System;
using System.Data;
using System.Windows.Forms;
using System.Data.EntityClient;

namespace WindowsFormsApp9
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            using (EntityConnection conn = new EntityConnection("name = DB_TESTEntities"))
            {
                //開く
                conn.Open();
                using (EntityCommand cmd = conn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = "DB_TESTEntities.sp_Test001";
                        cmd.CommandType = CommandType.StoredProcedure;
                        EntityParameter param = new EntityParameter();
                        param.Value = "002";
                        param.ParameterName = "Id";
                        EntityParameter param2 = new EntityParameter();
                        param2.Value = "Osaka";
                        param2.ParameterName = "City";

                        //ストアドプロシージャに渡すパラメータを追加する
                        cmd.Parameters.Add(param);
                        cmd.Parameters.Add(param2);

                        // ストアドプロシージャ実行
                        using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                        {

                        }

                    }
                    catch (EntityException ex)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                }

                //閉じる
                conn.Close();
                //破棄
                conn.Dispose();
            }
        }
    }
}

using System;

using System.Data;

using System.Windows.Forms;

using System.Data.EntityClient;

namespace WindowsFormsApp9

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

using (EntityConnection conn = new EntityConnection("name = DB_TESTEntities"))

{

//開く

conn.Open();

using (EntityCommand cmd = conn.CreateCommand())

{

try

{

cmd.CommandText = "DB_TESTEntities.sp_Test001";

cmd.CommandType = CommandType.StoredProcedure;

EntityParameter param = new EntityParameter();

param.Value = "002";

param.ParameterName = "Id";

EntityParameter param2 = new EntityParameter();

param2.Value = "Osaka";

param2.ParameterName = "City";

//ストアドプロシージャに渡すパラメータを追加する

cmd.Parameters.Add(param);

cmd.Parameters.Add(param2);

// ストアドプロシージャ実行

using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))

{

}

catch (EntityException ex)

{

Console.WriteLine(ex.ToString());

}

//閉じる

conn.Close();

//破棄

conn.Dispose();

}

実行画面

実行結果前

実行結果後

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

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

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

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

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

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

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

FEnet.NETナビ・.NETコラムは株式会社オープンアップシステムが運営しています。

株式会社オープンアップシステムはこんな会社です

秋葉原オフィスには株式会社オープンアップシステムをはじめグループのIT企業が集結！
数多くのエンジニアが集まります。

スマホアプリから業務系システムまで

スマホアプリから業務系システムまで開発案件多数。システムエンジニア・プログラマーとしての多彩なキャリアパスがあります。
充実した研修制度

毎年、IT技術のトレンドや社員の要望に合わせて、カリキュラムを刷新し展開しています。社内講師の丁寧なサポートを受けながら、自分のペースで学ぶことができます。
資格取得を応援

スキルアップしたい社員を応援するために資格取得一時金制度を設けています。受験料(実費)と合わせて資格レベルに合わせた最大10万円の一時金も支給しています。
東証プライム上場企業グループ

オープンアップシステムは東証プライム上場「株式会社オープンアップグループ」のグループ企業です。

安定した経営基盤とグループ間のスムーズな連携でコロナ禍でも安定した雇用を実現させています。

株式会社オープンアップシステムに興味を持った方へ

株式会社オープンアップシステムでは、開発系エンジニア・プログラマを募集しています。

年収をアップしたい！スキルアップしたい！大手の上流案件にチャレンジしたい！
まずは話だけでも聞いてみたい場合もOK。お気軽にご登録ください。

株式会社オープンアップシステムへ応募する
会員登録不要! カンタン45秒!

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

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

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