MySQL ストアドプロシージャの基本

はじめに

この実験では、MySQL ストアドプロシージャの基本を学びます。目標は、MySQL データベース内でデータを管理するために、ストアドプロシージャを作成、呼び出し、変更する方法を理解することです。

まず、データベースと employees という名前のテーブルを作成します。次に、employees テーブルにデータを挿入するための insert_employee という名前のストアドプロシージャを作成します。CALL ステートメントを使用してこのプロシージャを呼び出す方法と、プロシージャに入力パラメータを追加する方法を学びます。最後に、DROP PROCEDURE ステートメントを使用してプロシージャを削除する方法を学びます。

注意： この実験では、最初に MySQL シェルに一度だけ接続し、最後に終了すれば十分です。以降のステップのすべての SQL コマンドは、同じ MySQL セッション内で実行する必要があります。ステップ間に MySQL への接続や切断を繰り返す必要はありません。

データの挿入を行うプロシージャを作成する

このステップでは、テーブルにデータを挿入する MySQL ストアドプロシージャの作成方法を学びます。ストアドプロシージャは、データベース内に保存された事前コンパイル済みの SQL ステートメントです。名前で実行できるため、パフォーマンスとセキュリティを向上させることができます。

まず、ターミナルを開き、以下のコマンドを使用して MySQL サーバーに接続します。

sudo mysql -u root

このコマンドは、root ユーザーとして MySQL サーバーに接続します。以降のすべてのステップで、この MySQL セッションを開いたままにしてください。

接続すると、MySQL シェルに入ります。次に、セットアップ中に作成された testdb データベースに切り替えます。

USE testdb;

正しいデータベースに入ったので、employees テーブルにデータを挿入するストアドプロシージャを作成しましょう。ストアドプロシージャは CREATE PROCEDURE ステートメントを使用して作成されます。新しい従業員レコードを挿入する insert_employee という名前のプロシージャを定義します。

以下は、ストアドプロシージャの SQL コードです。

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255))
BEGIN
    INSERT INTO employees (name, department) VALUES (employee_name, employee_department);
END //
DELIMITER ;

このコードの内訳を見てみましょう。

DELIMITER //: これは、ステートメントの区切り文字を ; から // に変更します。プロシージャ自体にセミコロンが含まれているため、プロシージャ定義全体を 1 つのステートメントとして扱うように MySQL に指示する必要があるため、これは必要です。
CREATE PROCEDURE insert_employee: これは、insert_employee という名前のストアドプロシージャの作成を宣言します。
(IN employee_name VARCHAR(255), IN employee_department VARCHAR(255)): これは、プロシージャの入力パラメータを定義します。employee_name と employee_department はパラメータの名前であり、VARCHAR(255) はそれらのデータ型です。IN キーワードは、これらが入力パラメータであることを示します。
BEGIN ... END: このブロックには、プロシージャが呼び出されたときに実行される SQL ステートメントが含まれています。
INSERT INTO employees (name, department) VALUES (employee_name, employee_department);: これは、入力パラメータとして渡された値を使用して、employees テーブルに新しい行を挿入する SQL ステートメントです。
DELIMITER ;: これは、ステートメントの区切り文字を ; にリセットします。

このコードを実行するには、MySQL シェルに直接コピーして貼り付けます。

コードを実行した後、MySQL シェルで次のコマンドを実行して、プロシージャが作成されたことを確認できます。

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

このコマンドは、名前、データベース、作成日を含む insert_employee プロシージャに関する情報を表示します。

これで、employees テーブルにデータを挿入するストアドプロシージャを正常に作成しました。次のステップでは、このプロシージャを呼び出す方法を学びます。

CALL ステートメントでプロシージャを呼び出す

前のステップでは、insert_employee という名前のストアドプロシージャを作成しました。このステップでは、CALL ステートメントを使用してこのプロシージャを呼び出す方法を学びます。

リマインダー: まだ MySQL シェルにいて、testdb データベースを使用しているはずです。そうでない場合は、以下のコマンドで切り替えてください。

USE testdb;

CALL ステートメントは、ストアドプロシージャを実行するために使用されます。構文は次のとおりです。

CALL procedure_name(argument1, argument2, ...);

この場合、プロシージャ名は insert_employee であり、従業員の名前と部署の 2 つの引数を受け取ります。

insert_employee プロシージャを呼び出して、「Engineering」部署に「Alice Smith」という名前の新しい従業員を挿入してみましょう。

CALL insert_employee('Alice Smith', 'Engineering');

このステートメントは、指定された引数で insert_employee プロシージャを実行します。

データが正しく挿入されたことを確認するには、MySQL シェルで employees テーブルをクエリできます。

SELECT * FROM employees;

テーブルに、「Alice Smith」という名前と「Engineering」という部署を持つ新しい行が表示されるはずです。id は自動的に割り当てられます。

次に、「Marketing」部署に「Bob Johnson」という名前の別の従業員を挿入しましょう。

CALL insert_employee('Bob Johnson', 'Marketing');

再度、employees テーブルをクエリして挿入を確認します。

SELECT * FROM employees;

テーブルには、「Alice Smith」と「Bob Johnson」の 2 行が表示されるはずです。

これで、CALL ステートメントを使用してストアドプロシージャ insert_employee を正常に呼び出し、データが正しく挿入されたことを確認できました。これは、ストアドプロシージャを使用して SQL ロジックをカプセル化し、再利用する方法を示しています。

プロシージャに入力パラメータを追加する

これまでのステップで、insert_employee という名前のストアドプロシージャを作成し、employee_name と employee_department という 2 つの入力パラメータを持つように呼び出しました。このステップでは、プロシージャに別の入力パラメータを追加する方法を学びます。

insert_employee プロシージャに employee_salary パラメータを追加しましょう。これにより、新しいレコードを挿入する際に従業員の給与を指定できるようになります。

まず、既存のプロシージャを削除する必要があります。削除しないと、同じ名前のプロシージャを作成しようとしたときにエラーが発生します。MySQL シェルで以下を実行します。

DROP PROCEDURE IF EXISTS insert_employee;

次に、新しい入力パラメータを持つ変更されたストアドプロシージャを作成しましょう。

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255), IN employee_salary DECIMAL(10, 2))
BEGIN
    INSERT INTO employees (name, department, salary) VALUES (employee_name, employee_department, employee_salary);
END //
DELIMITER ;

変更点の詳細を見てみましょう。

プロシージャ定義に新しい入力パラメータ IN employee_salary DECIMAL(10, 2) を追加しました。DECIMAL(10, 2) は給与のデータ型で、小数点以下 2 桁で最大 10 桁までを許可します。
INSERT ステートメントを変更して、salary 列と employee_salary パラメータを含めました。

次に、変更された insert_employee プロシージャを呼び出して、「Finance」部署に「Charlie Brown」という名前の新しい従業員を 60000.00 の給与で挿入しましょう。

CALL insert_employee('Charlie Brown', 'Finance', 60000.00);

データが正しく挿入されたことを確認するには、MySQL シェルで employees テーブルをクエリできます。

SELECT * FROM employees;

テーブルに、「Charlie Brown」という名前、「Finance」という部署、および 60000.00 という給与を持つ新しい行が表示されるはずです。

これで、ストアドプロシージャ insert_employee に入力パラメータを正常に追加し、データが正しく挿入されたことを確認できました。これは、ストアドプロシージャを変更して新しい要件に対応する方法を示しています。

プロシージャを削除する

この最後のステップでは、データベースからストアドプロシージャを削除（ドロップ）する方法を学びます。プロシージャを削除すると、データベースから削除され、実行できなくなります。

リマインダー: まだ MySQL シェルにいて、testdb データベースを使用しているはずです。

DROP PROCEDURE ステートメントは、ストアドプロシージャを削除するために使用されます。構文は次のとおりです。

DROP PROCEDURE [IF EXISTS] procedure_name;

IF EXISTS 句はオプションですが、推奨されます。これにより、プロシージャが存在しない場合にエラーが発生するのを防ぐことができます。

この場合、プロシージャ名は insert_employee です。プロシージャを削除しましょう。

DROP PROCEDURE IF EXISTS insert_employee;

このステートメントは、testdb データベースから insert_employee プロシージャを削除します。

プロシージャが削除されたことを確認するには、MySQL シェルでプロシージャのステータスを再度表示してみてください。

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

このコマンドは空の結果セットを返すはずです。これは、プロシージャが存在しなくなったことを示しています。

または、プロシージャを呼び出そうとするとエラーが発生します。

CALL insert_employee('Test', 'Test', 1000);

これにより、ERROR 1305 (42000): PROCEDURE testdb.insert_employee does not exist のようなエラーメッセージが表示されます。

これで、ストアドプロシージャ insert_employee を正常に削除しました。

これで、MySQL シェルを終了するには、次のように入力します。

exit

これで、MySQL でストアドプロシージャを作成、呼び出し、変更、および削除する実験演習は終了です。

まとめ

この実験では、employees という名前のデータベースとテーブルを作成することから始め、MySQL ストアドプロシージャの基本を学びました。次に、CREATE PROCEDURE ステートメントを使用して insert_employee という名前のストアドプロシージャを定義し、employees テーブルにデータを挿入しました。DELIMITER コマンドは、プロシージャ定義内のセミコロンを処理するために使用されました。

この実験では、ストアドプロシージャの入力パラメータを定義し、その名前とデータ型を指定する方法もカバーしました。これにより、プロシージャが呼び出されたときに値を渡すことができ、より柔軟で再利用可能になります。CALL ステートメントを使用してストアドプロシージャを呼び出す練習をし、データの挿入を確認しました。最後に、DROP PROCEDURE ステートメントを使用してストアドプロシージャを削除する方法を学びました。