MySQLでストアドプロシージャを使用する方法

  • 390 ビュー
  • 15分の読み物
ハディ・バハドリ
  • 2024年7月29日
0 株式
0
0
0
0
0
株式
0
0
0
0

導入

通常、リレーショナルデータベースを操作する場合、SELECT、INSERT、UPDATE、DELETEなどの構造化照会言語（SQL）ステートメントをアプリケーションコード内から直接発行して、データの取得や操作を行います。これらのステートメントは、基盤となるデータベーステーブルを直接操作します。同じステートメントまたはステートメントのグループが、同じデータベースにアクセスする複数のアプリケーションで使用される場合、それらは個々のアプリケーション間で繰り返し使用されることがよくあります。.

MySQLは、他の多くのリレーショナルデータベース管理システムと同様に、ストアドプロシージャの使用をサポートしています。ストアドプロシージャは、1つまたは複数のSQL文を共通の名前を付けてグループ化し、再利用しやすくするとともに、共通のビジネスロジックをデータベース自体にカプセル化するのに役立ちます。ストアドプロシージャは、データベースにアクセスするアプリケーションから呼び出して、一貫した方法でデータを取得または操作することができます。.

ストアド プロシージャを使用すると、複数のアプリケーションで使用される共通タスクの再利用可能なルーチンを作成したり、データを検証したり、データベース ユーザーが基礎となるテーブルに直接アクセスして任意のクエリを発行することを制限することで、データ アクセス セキュリティの追加レイヤーを提供したりできます。.

このチュートリアルでは、ストアド プロシージャとは何か、また、データを返し、入力および出力パラメータを使用する基本的なストアド プロシージャを作成する方法について学習します。.

前提条件

このガイドに従うには、SQLベースのリレーショナルデータベース管理システム（RDBMS）が動作するコンピュータが必要です。このガイドの手順と例は、以下の環境で検証されています。

  • Ubuntu 20.04 を実行しているサーバー、管理者権限を持つ非ルートユーザー、および UFW で構成されたファイアウォール
  • MySQL はサーバー上にインストールされ、保護されています。
  • データベースからデータを取得するためのSELECTクエリの実行に関する基本的な紹介

注：多くのRDBMSは独自のSQL実装を使用しており、ストアドプロシージャの構文は公式のSQL標準には含まれていないことにご注意ください。このチュートリアルで紹介されているコマンドは他のRDBMSでも動作するかもしれませんが、ストアドプロシージャはデータベース固有のものであるため、MySQL以外のシステムでテストした場合、正確な構文や出力が異なる場合があります。.

ストアドプロシージャを使用してテーブルを作成できる空のデータベースも必要です。MySQLサーバーへの接続と、このガイドの例で使用するテストデータベースの作成方法については、以下の「MySQLへの接続とサンプルデータベースの設定」セクションをご覧ください。.

MySQLへの接続とサンプルデータベースの設定

このセクションでは、このガイドの例を使用できるように、MySQL サーバーに接続してサンプル データベースを作成します。.

このガイドでは、架空の車のコレクションを使用します。現在所有している車の詳細情報（メーカー、モデル、製造年、価格など）を保存します。.

SQL データベース システムがリモート サーバー上で実行されている場合は、ローカル マシンからサーバーに SSH で接続します。

ssh sammy@your_server_ip

次に、MySQL サーバー プロンプトを開き、Sami を MySQL ユーザー名に置き換えます。

mysql -u sammy -p

procedures という名前のデータベースを作成します。

CREATE DATABASE procedures;

データベースが正常に作成された場合、次の出力が表示されます。

Output
Query OK, 1 row affected (0.01 sec)

プロシージャ データベースを選択するには、次の USE ステートメントを実行します。

USE procedures;

次のような出力が得られます。

Output
Database changed

データベースを選択したら、その中にインスタンステーブルを作成できます。machinesテーブルには、データベース内のマシンに関する簡略化されたデータが格納されます。このテーブルには以下の列が含まれます。

  • メーカー: この列は、所有する各自動車を表し、最大 100 文字の varchar データ型を使用して表現されます。.
  • モデル: この列には、最大 100 文字の varchar データ型を使用して表現された車のモデル名が含まれます。.
  • year: この列には、数値を保持する int データ型で自動車の製造年が格納されます。.
  • 値: この列には、最大 10 桁、小数点以下 2 桁の 10 進データ型を使用して車の値が格納されます。.

次のコマンドでサンプル テーブルを作成します。

CREATE TABLE cars (
make varchar(100),
model varchar(100),
year int,
value decimal(10, 2)
);

次の出力が印刷された場合、テーブルは作成されています。

Output
Query OK, 0 rows affected (0.00 sec)

次に、次の INSERT INTO 操作を実行して、Cars テーブルにサンプル データをロードします。

INSERT INTO cars
VALUES
('Porsche', '911 GT3', 2020, 169700),
('Porsche', 'Cayman GT4', 2018, 118000),
('Porsche', 'Panamera', 2022, 113200),
('Porsche', 'Macan', 2019, 27400),
('Porsche', '718 Boxster', 2017, 48880),
('Ferrari', '488 GTB', 2015, 254750),
('Ferrari', 'F8 Tributo', 2019, 375000),
('Ferrari', 'SF90 Stradale', 2020, 627000),
('Ferrari', '812 Superfast', 2017, 335300),
('Ferrari', 'GTC4Lusso', 2016, 268000);

INSERT INTO 操作により、ポルシェ5台とフェラーリ5台を含む10台のサンプルスポーツカーがテーブルに追加されます。以下の出力は、5行すべてが追加されたことを示しています。

Output
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

これで、ガイドの残りの部分に従って、SQL でストアド プロシージャを使い始める準備が整いました。.

ストアドプロシージャの概要

MySQLをはじめとする多くのリレーショナルデータベースシステムにおけるストアドプロシージャは、データベースから呼び出されると順番に実行される1つ以上の命令を含むオブジェクトと呼ばれます。最も基本的な例として、ストアドプロシージャは、頻繁に使用されるフィルターを使ってデータベースからデータを取得するなど、再利用可能なルーチンの下に共通の式を保存できます。例えば、過去数か月間にオンラインストアで注文した顧客を取得するストアドプロシージャを作成できます。最も複雑なシナリオでは、ストアドプロシージャは、強力なアプリケーションのための複雑なビジネスロジックを記述する大規模なプログラムを表すことができます。.

ストアドプロシージャ内の命令セットには、SELECTクエリやINSERTクエリなど、データを返したり操作したりする一般的なSQL文を含めることができます。さらに、ストアドプロシージャでは以下のものを使用できます。

  • パラメータはストアド プロシージャに渡されるか、ストアド プロシージャから返されます。.
  • 取得したデータをプロシージャ コード内で直接処理するために宣言された変数。.
  • 条件ステートメント。IF ステートメントや CASE ステートメントなどの特定の条件に応じて、ストアド プロシージャ コードのセクションを実行できます。.
  • WHILE、LOOP、REPEAT などのループを使用すると、取得したデータセットの各行など、コードのセクションを複数回実行できます。.
  • プロシージャへのアクセス権を持つデータベース ユーザーにエラー メッセージを返すなどのエラー処理手順。.
  • データベース内の他のストアド プロシージャを呼び出します。.

注: MySQL がサポートする広範な構文により、強力なプログラムを作成し、ストアドプロシージャを使用して複雑な問題を解決することが可能になります。このガイドでは、ストアドプロシージャ本体で囲まれた SQL 文と入出力パラメータを使用したストアドプロシージャの基本的な使用方法のみを説明します。条件付きコードの実行、変数の使用、ループ、カスタムエラー処理については、このガイドの範囲外です。ストアドプロシージャの詳細については、MySQL の公式ドキュメントをご覧ください。.

 

プロシージャが名前で呼び出されると、データベース エンジンは定義どおりに命令ごとにプロシージャを実行します。.

データベースユーザーは、指定されたプロシージャを実行するための適切な権限を持っている必要があります。この権限要件により、データベースへの直接アクセスを禁止しながらも、安全に実行できる個々のプロシージャへのアクセスを許可することで、セキュリティが強化されます。.

ストアド プロシージャはデータベース サーバー上で直接実行され、すべての計算をローカルで実行し、完了した場合にのみ呼び出し元のユーザーに結果を返します。.

プロシージャの動作を変更したい場合は、データベース内のプロシージャを更新できます。すると、そのプロシージャを使用するアプリケーションは自動的に新しいバージョンを受け取ります。すべてのユーザーは、アプリケーションを調整することなく、すぐに新しいプロシージャコードの使用を開始できます。.

ストアド プロシージャを作成するために使用される SQL コードの一般的な構造は次のとおりです。

DELIMITER //
CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)
BEGIN
instruction_1;
instruction_2;
. . .
instruction_n;
END //
DELIMITER ;

このコード スニペットの最初と最後の命令は、DELIMITER // と DELIMITER ; です。MySQL では通常、セミコロン (;) 文字を使用してステートメントを区切り、ステートメントの開始と終了を示します。MySQL コンソールでセミコロンで区切られた複数のコマンドを実行すると、それらは個別のコマンドとして扱われ、次々に独立して実行されます。ただし、ストアド プロシージャには、呼び出されたときに順番に実行される複数のコマンドを含めることができます。これにより、MySQL に新しいプロシージャを作成するように指示しようとすると問題が発生します。データベース エンジンは、ストアド プロシージャの本文でセミコロン文字を検出すると、コマンドの実行を停止する必要があると判断します。このような状況では、問題のコマンドはプロシージャ作成コード全体であり、プロシージャ自体の単一の命令ではないため、MySQL は意図を誤解します。.

この制限を回避するには、DELIMITERステートメントを使用して、CREATE PROCEDURE呼び出しの実行中、区切り文字を一時的に「;」から「//」に変更します。これにより、ストアドプロシージャ本体内のすべてのセミコロンがそのままサーバーに送信されます。プロシージャ全体が完了すると、最後の「DELIMITER ;」で区切り文字が「;」に変更されます。.

新しいプロシージャを作成するためのコードの核となるのは、CREATE PROCEDURE呼び出しと、それに続くプロシージャ名（例ではprocedure_name）です。プロシージャ名の後には、プロシージャが受け入れるパラメータのオプションリストが続きます。最後の部分は、BEGIN文とEND文で囲まれたプロシージャ本体です。プロシージャコード内には、SELECTクエリなどのSQL文や、より複雑なコードを含めることができます。.

END コマンドは、通常のセミコロンの代わりに、一時的な区切り文字である // で終了します。.

次のセクションでは、クエリをカプセル化する基本的なパラメーターなしのストアド プロシージャを作成します。.

パラメータなしのストアドプロシージャの作成

このセクションでは、ブランド別に所有されている車のリストとその値を降順で返す SQL SELECT ステートメントを含む最初のストアド プロシージャを作成します。.

まず、使用する SELECT ステートメントを実行します。

SELECT * FROM cars ORDER BY make, value DESC;

データベースは、Cars テーブルから車のリストを返します。まずブランド別に、次にブランド内で値の降順で返します。

Output
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso | 2016 | 268000.00 |
| Ferrari | 488 GTB | 2015 | 254750.00 |
| Porsche | 911 GT3 | 2020 | 169700.00 |
| Porsche | Cayman GT4 | 2018 | 118000.00 |
| Porsche | Panamera | 2022 | 113200.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
| Porsche | Macan | 2019 | 27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)

最も価値の高いフェラーリがリストの一番上にあり、最も価値の低いポルシェがリストの一番下にあります。.

このクエリが複数のアプリケーションまたは複数のユーザーによって繰り返し使用され、すべてのユーザーが同じ方法で結果を並べ替えられるようにしたいとします。そのためには、再利用可能な名前付きプロシージャの下に、その式を格納するストアドプロシージャを作成します。.

このストアド プロシージャを作成するには、次のコード スニペットを実行します。

DELIMITER //
CREATE PROCEDURE get_all_cars()
BEGIN
SELECT * FROM cars ORDER BY make, value DESC;
END //
DELIMITER ;

前のセクションで説明したように、最初と最後のステートメント (DELIMITER // と DELIMITER 😉) は、プロシージャの作成中にステートメント区切り文字としてのセミコロン文字を無視するように MySQL に指示します。.

CREATE PROCEDURE SQL文の後にプロシージャ名 get_all_cars が続きます。これは、プロシージャの機能を最もよく表すように定義できます。プロシージャ名の後には、パラメータを追加するための括弧 () があります。この例では、プロシージャはパラメータを使用しないため、括弧は空です。次に、プロシージャのコードブロックの開始と終了を定義する BEGIN 文と END 文の間に、先ほど使用した SELECT 文をそのまま記述します。.

データベースは成功メッセージで応答します:

Output
Query OK, 0 rows affected (0.02 sec)

get_all_cars プロシージャはデータベースに保存され、呼び出されると、保存されているステートメントがそのまま実行されます。.

ストアドプロシージャを実行するには、CALL SQLコマンドに続けてプロシージャ名を指定します。新しく作成したプロシージャを次のように実行してみましょう。

CALL get_all_cars;

このプロシージャを使用するには、プロシージャ名「get_all_cars」だけが必要です。これまで使用していたSELECT文の一部を手動で入力する必要はなくなりました。データベースはSELECT文の出力通りに結果を実行します。

Output
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso | 2016 | 268000.00 |
| Ferrari | 488 GTB | 2015 | 254750.00 |
| Porsche | 911 GT3 | 2020 | 169700.00 |
| Porsche | Cayman GT4 | 2018 | 118000.00 |
| Porsche | Panamera | 2022 | 113200.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
| Porsche | Macan | 2019 | 27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

これで、Carsテーブルにあるすべての車を特定の順序で返す、パラメータなしのストアドプロシージャが作成されました。このメソッドは複数のアプリケーションで使用できます。.

次のセクションでは、パラメータを受け入れて、ユーザー入力に応じてプロシージャの動作を変更するプロシージャを作成します。.

入力パラメータを持つストアドプロシージャの作成

このセクションでは、ストアドプロシージャ定義に入力パラメータを追加し、プロシージャを実行するユーザーがデータをプロシージャに渡せるようにします。例えば、ユーザーはクエリフィルターを指定できます。.

前述のストアドプロシージャ get_all_cars は、常に cars テーブルからすべての車を取得します。では、特定の製造年の車を検索する別のプロシージャを作成しましょう。これを行うには、プロシージャ定義に get_all_cars というパラメータを定義します。.

次のコードを実行します。

DELIMITER //
CREATE PROCEDURE get_cars_by_year(
IN year_filter int
)
BEGIN
SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

前のセクションのプロシージャ作成コードにはいくつかの変更があります。.

まず、名前は get_cars_by_year です。これは、製造年に基づいて車を取得するというメソッドを説明しています。.

先ほどの空の括弧にパラメータ定義が追加されました。IN year_filter int です。IN キーワードは、パラメータが呼び出し元ユーザーからプロシージャに渡されることをデータベースに伝えます。year_filter はパラメータの任意の名前です。プロシージャコード内でパラメータを参照する際に使用します。最後に、int はデータ型です。この場合、製造年は数値で表されます。.

プロシージャ名の後に定義された year_filter パラメータは、SELECT ステートメントの WHERE year = year_filter 句に表示され、自動車のテーブルを製造年でフィルタリングします。.

データベースは再び成功メッセージで応答します:

Output
Query OK, 0 rows affected (0.02 sec)

前と同じように、パラメータを渡さずにプロシージャを実行してみてください。

CALL get_cars_by_year;

MySQL データベースから次のエラー メッセージを返します:

Error message
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0

今回は、ストアドプロシージャはパラメータの指定を期待していますが、パラメータが指定されていません。パラメータ付きのストアドプロシージャを呼び出すには、プロシージャが期待する順序と同じ順序で、括弧内にパラメータ値を指定します。2017年に製造された自動車を取得するには、次のコマンドを実行します。

CALL get_cars_by_year(2017);

呼び出されたプロシージャは正しく実行され、その年の自動車のリストを返します。

Output
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
+---------+---------------+------+-----------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

この例では、入力パラメータをストアド プロシージャに渡し、プロシージャ内のクエリでそれらを使用してフィルタ オプションを提供する方法を学習しました。.

次のセクションでは、出力パラメータを使用して、1 回の実行で複数の異なる値を返すプロシージャを作成します。.

入力パラメータと出力パラメータを持つストアドプロシージャの作成

これまでの2つの例では、作成したストアドプロシージャがSELECT文を呼び出して結果セットを返していました。しかし、場合によっては、クエリに対して単一の結果セットではなく、複数の異なる値をまとめて返すストアドプロシージャが必要になることがあります。.

コレクション内の自動車の数や市場価値 (最小、最大、平均) など、特定の年の自動車に関する概要情報を提供するプロシージャを作成するとします。.

これを実現するには、新しいストアドプロシージャを作成する際にOUTパラメータを使用します。INパラメータと同様に、OUTパラメータにも名前とデータ型が関連付けられています。ただし、ストアドプロシージャにデータを渡す代わりに、ストアドプロシージャからデータを代入して呼び出し元ユーザーに値を返すことができます。.

出力パラメータを使用して、指定された製造年の自動車の概要データを返す get_car_stats_by_year プロシージャを作成します。

DELIMITER //
CREATE PROCEDURE get_car_stats_by_year(
IN year_filter int,
OUT cars_number int,
OUT min_value decimal(10, 2),
OUT avg_value decimal(10, 2),
OUT max_value decimal(10, 2)
)
BEGIN
SELECT COUNT(*), MIN(value), AVG(value), MAX(value)
INTO cars_number, min_value, avg_value, max_value
FROM cars
WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

今回は、製造年で車をフィルタリングするINパラメータyear_filterに加えて、括弧ブロック内に4つのOUTパラメータが定義されています。cars_numberパラメータはintデータ型で表され、セット内の車の台数を返すために使用されます。min_value、avg_value、max_valueパラメータは市場価格を表し、decimal型(10, 2)で定義されています（carsテーブルのvalue列に似ています）。これらは、セット内の最も安い車と最も高い車の情報、および一致するすべての車の平均価格を返すために使用されます。.

SELECT ステートメントは、SQL 数学関数を使用して cars テーブルから 4 つの値を検索します。COUNT を使用して車の合計数を取得し、MIN、AVG、MAX を使用して値列から最小値、平均値、最大値を取得します。.

クエリの結果をストアドプロシージャの出力パラメータに格納するようデータベースに指示するために、INTOという新しいキーワードが導入されました。INTOキーワードの後には、取得したデータに関連する4つのプロシージャパラメータ名を指定します。これにより、MySQLはCOUNT(*)の値をcars_numberパラメータに、MIN(value)の結果をmin_valueパラメータに格納するなど、同様の処理を行います。.

データベースは、プロシージャが正常に作成されたことを確認します。

Output
Query OK, 0 rows affected (0.02 sec)

次のコマンドを実行して、新しいルーチンを実行します。

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

4つの新しいパラメータは@記号で始まります。これらはMySQLコンソール内のローカル変数の名前であり、一時的にデータを保存するために使用できます。作成したストアドプロシージャにこれらのパラメータを渡すと、ストアドプロシージャはこれらの変数に値を挿入します。.

データベースは次のように応答します。

Output
Query OK, 1 row affected (0.00 sec)

これは、結果がすぐに画面に表示されていた以前の動作とは異なります。これは、ストアドプロシージャの結果が出力パラメータに保存され、クエリ結果として返されないためです。結果にアクセスするには、次のようにMySQLシェルで直接選択できます。

SELECT @number, @min, @avg, @max;

このクエリでは、プロシージャを再度呼び出すのではなく、ローカル変数から値を選択します。ストアドプロシージャは結果をこれらの変数に保存し、シェルを終了するまでデータは利用可能です。.

注：MySQLでのユーザー定義変数の使用に関する詳細は、ドキュメントの「ユーザー定義変数」セクションをご覧ください。アプリケーション開発で使用する場合、ストアドプロシージャから返されるデータへのアクセス方法は、プログラミング言語やフレームワークによって異なります。ご不明な点がある場合は、選択した言語とフレームワークのドキュメントを参照してください。.

出力にはクエリ変数の値が表示されます。

Output
+---------+----------+-----------+-----------+
| @number | @min | @avg | @max |
+---------+----------+-----------+-----------+
| 2 | 48880.00 | 192090.00 | 335300.00 |
+---------+----------+-----------+-----------+
1 row in set (0.00 sec)

これらの値は、2017 年に生産された自動車の数、およびこの生産年の自動車の市場価値の最小値、平均値、最大値に対応しています。.

この例では、出力パラメータを使用して、ストアドプロシージャ内から複数の異なる値を返す方法を学習しました。これらの値は後で使用するために使用できます。次のセクションでは、作成したプロシージャを削除する方法を学習します。.

ストアドプロシージャを削除する

このセクションでは、データベース内のストアド プロシージャを削除します。.

作成したプロシージャが不要になる場合があります。また、プロシージャの動作を変更したい場合もあります。MySQLでは、プロシージャを作成した後に定義を変更することはできません。そのため、変更するには、まずプロシージャを削除し、必要な変更を加えて再作成するしかありません。.

最後のプロシージャ「get_car_stats_by_year」を削除してみましょう。これを行うには、DROP PROCEDURE文を使用します。

DROP PROCEDURE get_car_stats_by_year;

データベースは、成功メッセージでプロシージャの削除が成功したことを確認します。

Output
Query OK, 0 rows affected (0.02 sec)

プロシージャが削除されたかどうかを確認するには、以下のコマンドを実行してみます。

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

今回は、データベースにプロシージャが存在しないというエラー メッセージが表示されます。

Error message
ERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not exist

このセクションでは、データベースからストアド プロシージャを削除する方法を学習しました。.

結果

このガイドでは、ストアドプロシージャとは何か、そしてMySQLでストアドプロシージャを使用して再利用可能なステートメントを名前付きプロシージャに保存し、後で実行する方法について学びました。パラメータのないストアドプロシージャと、入出力パラメータを使用してより柔軟なストアドプロシージャを作成しました。.

この記事の内容:
,
投稿者:
フォローする
コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

前の記事
次の記事
あなたも気に入るかもしれない