MySQLでストアドプロシージャを使用する方法
著者は、Write for Donationsプログラムの一環として、寄付を受けるためにDiversity in Tech Fundを選択しました。
はじめに
通常、リレーショナルデータベースを使用する場合、アプリケーションコード内から直接的にSELECT、INSERT、UPDATE、DELETEなどの個別のStructured Query Language（SQL）クエリを発行してデータを取得または操作します。これらの文は、基になるデータベーステーブル上で動作し、テーブルを直接操作します。同じ文または文のグループが、同じデータベースにアクセスする複数のアプリケーション内で使用される場合、通常、各アプリケーションで重複しています。
MySQLは、他の多くのリレーショナルデータベース管理システムと同様に、ストアドプロシージャの使用をサポートしています。ストアドプロシージャは、共通の名前で1つまたは複数のSQLステートメントをグループ化して再利用するために役立ち、データベース内の共通のビジネスロジックをカプセル化します。このような手続きは、データベースにアクセスするアプリケーションから呼び出され、一貫した方法でデータを取得または操作するために使用されます。
ストアドプロシージャを使用することで、共通のタスクに対して再利用可能なルーチンを作成することができます。これにより、複数のアプリケーション間で使用することができます。また、データの検証を行ったり、データベースユーザーが直接基になるテーブルにアクセスしたり自由なクエリを発行することを制限することにより、データアクセスのセキュリティの追加のレイヤーを提供することもできます。
このチュートリアルでは、ストアドプロシージャとは何か、データを返す基本的なストアドプロシージャの作成方法、および入力と出力パラメータの両方を使用する方法について学びます。
前提条件
このガイドに従うためには、SQLベースのリレーショナルデータベース管理システム（RDBMS）が動作しているコンピュータが必要です。このガイドの手順と例は、以下の環境で検証されました。
- A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
- MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.
- Basic familiarity with executing SELECT queries to retrieve data from the database as described in our How To SELECT Rows FROM Tables in SQL guide.
Note
また、ストアドプロシージャの使用例を示すための空のデータベースも必要です。このガイド全体で使用されるテスト用のデータベースの接続方法や設定方法の詳細については、MySQLサーバーへの接続とサンプルデータベースのセットアップセクションを参照することをお勧めします。
MySQLに接続し、サンプルデータベースの設定を行います。
このセクションでは、MySQLサーバーに接続し、このガイドの例に従うためのサンプルデータベースを作成します。
このガイドでは、架空の自動車コレクションを使用します。所有している車の詳細、メーカー、モデル、製造年、および価値を保存します。
もしSQLデータベースシステムがリモートサーバー上で実行されている場合、ローカルマシンからサーバーにSSH接続してください。
- ssh sammy@your_server_ip
その後、MySQLサーバープロンプトを開いて、MySQLユーザーアカウントの名前でsallyを置き換えます。
- mysql -u sammy -p
手続きという名前のデータベースを作成してください。
- CREATE DATABASE procedures;
データベースが正常に作成された場合、以下のような出力が表示されます。
Query OK, 1 row affected (0.01 sec)
手順のデータベースを選択するには、次のUSEステートメントを実行してください。
- USE procedures;
以下の出力結果を受け取ります。
Database changed
データベースを選択した後は、その中にサンプルのテーブルを作成することができます。車のテーブルでは、データベース内の車に関する簡易なデータを保持します。以下の列が含まれます。
- make: This column holds the make for each owned car, expressed using the varchar data type with a maximum of 100 characters.
- model: This column holds the car model name, expressed using the varchar data type with a maximum of 100 characters.
- year: This column stores the car’s build year with int data type to hold numerical values.
- value: This column stores the car’s value using the decimal data type with a maximum of 10 digits and 2 digits after the decimal point.
以下のコマンドでサンプルテーブルを作成してください。
- CREATE TABLE cars (
- make varchar(100),
- model varchar(100),
- year int,
- value decimal(10, 2)
- );
もし以下の出力が表示されたら、テーブルは作成されています。
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操作は、表に10台のサンプルスポーツカーを追加します。そのうちの5台がポルシェモデルで、5台がフェラーリモデルです。以下の出力は、5行全てが追加されたことを示しています。
Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0
それでは、あなたは残りのガイドに従い、SQLでストアドプロシージャを使用する準備が整いました。
ストアドプロシージャ入門
MySQLや他のリレーショナルデータベースシステムにおけるストアドプロシージャは、呼び出された際にデータベースによって順次実行される1つ以上の命令を含むオブジェクトです。最も基本的な例では、ストアドプロシージャはよく使用されるフィルターを使用してデータベースからデータを取得するなど、共通のステートメントを再利用可能な手続きとして保存することができます。たとえば、指定された期間内に注文をしたオンラインストアの顧客を取得するためのストアドプロシージャを作成することができます。最も複雑なシナリオでは、ストアドプロシージャは頑健なアプリケーションのための複雑なビジネスロジックを記述する包括的なプログラムを表すことができます。
格納された手続きの一連の命令には、データを返したり操作したりするような、SELECTやINSERTクエリのような一般的なSQL文を含めることができます。さらに、格納された手続きは以下の機能を利用することができます。
- Parameters passed to the stored procedure or returned through it.
- Declared variables to process retrieved data directly within the procedure code.
- Conditional statements, which allow the execution of parts of the stored procedure code depending on certain conditions, such as IF or CASE instructions.
- Loops, such as WHILE, LOOP, and REPEAT, allow executing parts of the code multiple times, such as for each row in a retrieved data set.
- Error handling instructions, such as returning error messages to the database users accessing the procedure.
- Calls to other stored procedures in the database.
Note
手続きがその名前で呼び出されると、データベースエンジンは定義された通りに、命令ごとに実行します。
データベースのユーザーは、与えられた手続きを実行するために適切な権限を持っている必要があります。この権限の要件は、直接的なデータベースアクセスを禁止しながら、安全に実行できる個別の手続きへのユーザーアクセスを提供するセキュリティの層を提供します。
ストアドプロシージャはデータベースサーバー上で直接実行され、全ての計算がローカルで行われ、処理が完了した後に結果が呼び出し元のユーザーに返されます。
もし手続きの動作を変更したい場合は、手続きをデータベースで更新することができます。それを使用しているアプリケーションは自動的に新しいバージョンを適用します。全てのユーザーは、アプリケーションの調整が不要で新しい手続きコードをすぐに使用することができます。
以下は、ストアドプロシージャを作成するために使用される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は意図を誤解する可能性があります。
この制限を回避するために、CREATE PROCEDURE呼び出しの間、DELIMITERコマンドを使用して一時的にデリミタを;から//に変更します。その後、ストアドプロシージャ本体内のすべてのセミコロンはそのままサーバーに渡されます。手続き全体が終了した後、最後のDELIMITER ;でデリミタは再び;に変更されます。
新しい手続きを作成するためのコードの核心は、CREATE PROCEDURE呼び出しと手続きの名前（例ではprocedure_name）です。手続きの名前の後には、手続きが受け入れる必要があるパラメーターのオプションのリストが続きます。最後の部分は手続き本体であり、BEGINとENDステートメントで囲まれています。その内側には、単一のSQL文（SELECTクエリなど）またはより複雑なコードを含む手続きコードがあります。
ENDコマンドは、通常のセミコロンではなく、一時的な区切り記号である//で終了します。
次のセクションでは、単一のクエリを囲むパラメーターのない基本的なストアドプロシージャを作成します。
パラメータのないストアドプロシージャの作成
このセクションでは、所有している車のリストを降順でメイクと価値に基づいて表示するために、最初のストアドプロシージャを作成します。
最初に使用するSELECT文を実行してください。
- SELECT * FROM cars ORDER BY make, value DESC;
データベースは、最初にメーカーで並び替えられ、同じメーカー内では価値が降順で並べられた車のリストを返します。
+———+—————+——+———–+ | 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ステートメントがそのまま書かれます。
Note
GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE on *.* TO ‘sammy’@’localhost’;
FLUSH PRIVILEGES;
ユーザー権限を更新した後は、rootとしてログアウトし、ユーザーとして再ログインして、CREATE PROCEDUREステートメントを再実行してください。
ストアドルーチンとMySQLの特権のドキュメントで、データベースユーザーに対するストアドプロシージャの権限の適用方法について詳しく学ぶことができます。
データベースは成功メッセージで応答します。
Query OK, 0 rows affected (0.02 sec)
「get_all_cars（ゲットオールカーズ）」手続きは現在、データベースに保存されており、呼び出されると保存されたステートメントがそのまま実行されます。
保存されたストアドプロシージャを実行するには、手続き名の後にCALL SQLコマンドを使用します。新しく作成した手続きを次のように実行してみてください。
- CALL get_all_cars;
手続き名「get_all_cars」を使用するだけで十分です。以前に使用したSELECT文の手動入力はもはや必要ありません。データベースは、以前に実行されたSELECT文の出力と同様に結果を表示します。
+———+—————+——+———–+ | 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)
あなたは今、特定の方法で並べ替えた車のテーブル内のすべての車を返すパラメータのないストアドプロシージャを成功裏に作成しました。このプロシージャは、複数のアプリケーションで使用できます。
次のセクションでは、ユーザーの入力に応じて手順の動作を変更するためのパラメータを受け入れる手順を作成します。
入力パラメータを使用してストアドプロシージャを作成する。
このセクションでは、ユーザーが手続きを実行する際にデータを渡すためのストアドプロシージャ定義に入力パラメータを含めます。たとえば、ユーザーはクエリフィルタを提供することができます。
以前に作成されたストアドプロシージャ「get_all_cars」は、常に「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節に表示され、車のテーブルを製造年でフィルタリングします。
データベースは再び成功のメッセージで応答します。
Query OK, 0 rows affected (0.02 sec)
前回と同様に、手続きをパラメータを渡さずに実行してみてください。
- CALL get_cars_by_year;
MySQLデータベースはエラーメッセージを返します。
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);
今、呼び出された手続きは正常に実行され、その年の車のリストが返されます。
+———+—————+——+———–+ | 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)
この例では、入力パラメータをストアドプロシージャに渡し、プロシージャ内のクエリで使用してフィルタリングオプションを提供する方法について学びました。
次のセクションでは、出力パラメータを使用して、一度の実行で複数の異なる値を返すプロシジャを作成します。
入力と出力パラメーターを持つストアドプロシージャを作成する。
以前の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)のタイプで定義されています（車のテーブルのvalue列に類似しています）。これらはコレクション内の最も安い車と最も高い車の情報、および一致する車の平均価格を返すために使用されます。
SELECT文は、SQLの数学関数を使用して、carsテーブルから4つの値をクエリーします。COUNT関数は車の総数を取得し、MIN、AVG、MAX関数はvalue列から最小、平均、最大の値を取得します。
Note
SQLで数学関数を使用する方法については、How To Use Mathematical Expressions and Aggregate Functions in SQLのチュートリアルを参照してください。
そのクエリの結果をデータベースに保存するように設定するために、新しいキーワード「INTO」が導入されます。INTOキーワードの後に、取得したデータに対応する4つの手続きパラメータの名前がリストアップされます。これにより、MySQLは、car_numberパラメータにCOUNT(*)の値を保存し、min_valueパラメータにMIN(value)の結果を保存します。
データベースは手続きの作成が成功したことを確認します。 (The database will confirm the successful creation of the procedure.)
Query OK, 0 rows affected (0.02 sec)
今、実行して新しい手順を実施してください。 (Ima, jikkō shite atarashī shidō o jisshi shite kudasai.)
- CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
新しい4つのパラメータは「@」記号で始まります。これらはMySQLコンソールのローカル変数名であり、一時的にデータを格納するために使用できます。作成したストアドプロシージャにこれらを渡すと、手続きはその変数に値を挿入します。
データベースは次のように応答します。
Query OK, 1 row affected (0.00 sec)
以前の動作とは異なり、結果が即座に画面に表示されるわけではありません。これは、ストアドプロシージャの結果がクエリ結果として返されず、出力パラメータに保存されたためです。結果へのアクセス方法は、MySQLのシェルで以下のように直接SELECTすることです。
- SELECT @number, @min, @avg, @max;
このクエリでは、手続きを再度呼び出すのではなく、ローカル変数から値を選択しています。格納された手続きはその変数に結果を保存し、データはシェルから切断するまで利用可能なままです。
Note
MySQLでユーザー定義変数の使用方法について詳しく知りたい場合は、ドキュメンテーションのユーザー定義変数のセクションを参照してください。アプリケーション開発で使用する場合、ストアドプロシージャから返されたデータにアクセスする方法は、異なるプログラミング言語やフレームワークごとに異なります。疑わしい場合は、選択した言語とフレームワークのドキュメンテーションを参照してください。
出力は、問い合わせられた変数の値を表示します。
+———+———-+———–+———–+ | @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;
データベースは、成功メッセージを表示して手続きの削除が成功したことを確認します。
Query OK, 0 rows affected (0.02 sec)
手順が削除されたことを確認するために、それを呼び出してみることで検証できます。実行してください。
- CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
今回は、データベースに手続きが存在しないというエラーメッセージが表示されます。
ERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not exist
このセクションでは、データベース内の既存のストアドプロシージャを削除する方法を学びました。
結論
このガイドに従うことで、MySQLでストアドプロシージャを利用して再利用可能なステートメントを名前付きの手続きに保存し、後で実行する方法を学びました。パラメータのないストアドプロシージャや入出力パラメータを使用してプロシージャを作成し、柔軟性を高めることができました。
ストアドプロシージャを使用すると、データへのアクセス方法を統一し、再利用可能なルーチンを作成することができます。また、個々のSQLクエリでは実現できない複雑な動作も実装することができます。このチュートリアルでは、ストアドプロシージャの基本的な使用方法のみをカバーしました。もっと詳しく学びたい場合は、MySQLのストアドプロシージャに関するドキュメンテーションを参照してください。
もしSQL言語やその使い方についてもっと学びたい場合は、『SQLの使い方シリーズ』の他のガイドを参照してみることをおすすめします。