はじめに
この実験では、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 ステートメントを使用してストアドプロシージャを削除する方法を学びました。
