ストアドプロシージャとは？概要やメリットとデメリットを解説

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

ストアドプロシージャとは、リレーショナルデータベース管理システム（RDBMS）のアクセス手順を、事前にまとめて格納するための機能を指します。手順を格納することから、英語では「Stored Procedure」と名付けられています。

ここでは、ストアドプロシージャの概要やメリット、デメリット、書き方などを学んでいきます。

ストアドプロシージャは、RDBMSのアクセスの手続きを事前登録したものです。複数のSQL命令をまとめてグルーピングした手続きを格納しておき、効率的に呼び出すことができます。プログラミング言語における関数・サブルーチンを定義する手順と同じようにデータベースアクセスを行うことができます。

初心者でもわかるSQL基礎：基本構文や学習方法、書き方を解説

ストアドプロシージャは、あらかじめ格納しておくことでSQL文が実行可能な形式で保存されます。ストアドプロシージャを使わない場合と比較して、実行速度が速い特徴があります。また、実行時に都度解釈させる場合と比較して、オーバーヘッドが低減し、通信やサーバ負荷が削減されます。

加えて、プログラミング言語の関数同様に特定の手順をまとめて定義できるため、保全性の向上と特定処理に限定したセキュリティ面での維持も期待できます。

ストアドプロシージャは、複数のSQLステートメントを1つの呼び出しで実行することができます。

コードの保全性が高まる半面、そのストアドプロシージャの実装部分については、業務要件の知識やSQLの知識が求められるため、むしろ保全の難易度が高まる場合があります。

このことで、安易な修正は不具合を誘発してしまい、不具合の修正のつもりが問題あるコードを増やすリスクがあります。

またストアドプロシージャは、RDBMSによって実装の違いがあり、書き方も異なります。そのためストアドプロシージャを多用した処理は、他のRDBMSへの移行性を低下させる要因になります。

ストアドプロシージャの書き方

ストアドプロシージャの書き方は、リリースされているRDBMSによって違いがあります。ここでは、主要なRDBMSであるSQL Server、MySQL、PostgreSQLについて、それぞれのストアドプロシージャの構文を確認していきます。

SQL ServerはMicrosoftが提供するRDBMSで、Windows、Linuxで動作します。Transact-SQLやMicrosoft .NET Frameworkで使用します。

書き方は、「CREATE PROC」または「CREATE PROCEDURE」でストアドプロシージャを作成し、「BEGIN」～「END」の間に必要なSELECT文などを登録します。

CREATE { PROC | PROCEDURE }
    プロシージャ名
    [ { @パラメータ名 パラメータのデータ型 }
        [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ]
AS { [ BEGIN ] 登録するSQLステートメント [;] [ ...n ] [ END ] }

「OUT」または「OUTPUT」が指定されている場合は、プロシージャの呼び出し元にパラメータの値を返します。指定がない場合は、入力パラメータ「IN」となります。パラメータが変更できない場合は、「READONLY」を指定します。

格納されたストアドプロシージャは、「EXEC」あるいは「EXECUTE」でパラメータを指定し、実行することができます。

【参考】：SQL Server 【参考】：SQL Server: ストアド プロシージャ (データベース エンジン) 【参考】：SQL Server: ストアド プロシージャの作成 【参考】：SQL Server: ストアド プロシージャの実行

SQL Serverとは？使い方や構築方法をわかりやすく解説！

MySQLはオープンソースのRDBMSで、Windows、Linux、macOSのマルチプラットフォームで動作します。MySQLでは、式で呼び出すストアドファンクションと「CALL」ステートメントで呼び出すストアドプロシージャが用意されています。

ストアドプロシージャの書き方は、「CREATE PROCEDURE」で作成し、「BEGIN」～「END」の間に必要なSELECT文などを登録します。

CREATE PROCEDURE
    プロシージャ名 ([ IN | OUT | INOUT ] パラメータ名 パラメータのデータ型 [,..])
BEGIN
    SQLステートメント[;,,]
END

「IN」はプロシージャへ値を渡し、「OUT」は値が呼び出し元に戻ります。「INOUT」は入出力両方です。ストアドプロシージャは、「CALL」ステートメントで実行することができます。

【参考】：MySQL 【参考】：MySQL: ストアドオブジェクト 【参考】：MySQL: CREATE PROCEDURE ステートメントおよび CREATE FUNCTION ステートメント 【参考】：MySQL: CALL ステートメント

MySQL入門！世界で最も普及しているRDBMSを学んでみよう

PostgreSQLもフリーでオープンなRDBMSであり、マルチプラットフォームで動作します。標準SQLに対応するとともに、独自の拡張がされています。

PostgreSQLのストアドプロシージャは、標準SQLや独自拡張のPL/pgSQL（Procedural Language/PostgreSQL Structured Query）を用いて行います。ストアドファンクションとストアドプロシージャに対応しています。

ストアドプロシージャの書き方は、「CREATE PROCEDURE」で作成し、「AS $$」～「$$」または「BEGIN」～「END」の間に必要なSELECT文などを登録します。

CREATE PROCEDURE
    プロシージャ名 ([ IN | OUT | INOUT | VARIADIC ] パラメータ名 パラメータのデータ型 [,..])
LANGUAGE SQL
AS $$
    SQLステートメント[;,,]
$$;

CREATE PROCEDURE
    プロシージャ名 ([ IN | OUT | INOUT | VARIADIC ] パラメータ名 パラメータのデータ型 [,..])
LANGUAGE SQL
BEGIN ATOMIC
    SQLステートメント[;,,]
END;

パラメータの指定がない場合のデフォルトモードは、入力パラメータ「IN」となります。「OUT」または「OUTPUT」が指定されている場合は、プロシージャの呼び出し元にパラメータの値を返します。

「INOUT」は「IN」と「OUT」の両方で、「VARDIADIC」は数が定義されていない同じ型の入力を示します。ストアドプロシージャを実行するには、「CALL」ステートメントを用います。

【参考】：PostgreSQL 【参考】：PostgreSQL: CREATE PROCEDURE 【参考】：PostgreSQL: CALL 【参考】：PostgreSQL: PL/pgSQL — SQL手続き言語

PostgreSQL入門、複数のRDBMSを習得して更なる活躍を

標準SQLとSQLの歴史！SQLの互換性と規格化の流れを解説

ストアドプロシージャを理解して使用しよう

ストアドプロシージャは、複雑なSQL文をあらかじめまとめて登録することで、高速化や保全性の向上などが期待できます。その反面、ストアドプロシージャを多用すると、実装の違いによる修正間違いなどが誘発されたり、異なるRDBMSへの移行性を損なうリスクがあります。

そのため、ストアドプロシージャを利用するには、そのメリットとデメリットを理解したうえで、効率的に使用するのがおすすめです。