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

はじめに

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

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

Skills Graph

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

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

まず、操作対象となるシンプルなテーブルを作成しましょう。ターミナルを開き、以下のコマンドを使用して MySQL サーバーに接続します。

mysql -u root -p

root パスワードの入力を求められます。パスワードを入力し、Enter キーを押します。

次に、testdb という名前のデータベースを作成します。

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

次に、以下の構造を持つ employees という名前のテーブルを作成します。

CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(255)
);

テーブルができたので、そこにデータを挿入するストアドプロシージャを作成しましょう。ストアドプロシージャは 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 //: これは、文の区切り文字を ; から // に変更します。プロシージャ自体にセミコロンが含まれているため、MySQL に対してプロシージャ定義全体を 1 つの文として扱うように指示する必要があります。
• 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 ターミナルに直接コピーして貼り付けることができます。

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

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

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

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

CALL 文を使用してプロシージャを呼び出す

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

まず、MySQL サーバーに接続し、testdb データベースを使用していることを確認してください。まだ接続していない場合は、以下のコマンドを使用します。

mysql -u root -p

パスワードの入力を求められたら入力してください。

USE testdb;

CALL 文は、ストアドプロシージャを実行するために使用されます。構文は以下の通りです。

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

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

「Alice Smith」という名前の新しい従業員を「Engineering」部署に追加するために、insert_employee プロシージャを呼び出しましょう。

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

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

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

SELECT * FROM employees;

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

もう 1 人の従業員「Bob Johnson」を「Marketing」部署に追加しましょう。

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

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

SELECT * FROM employees;

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

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

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

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

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

まず、既存のプロシージャを削除する必要があります。削除しない場合、同じ名前のプロシージャを作成しようとするとエラーが発生します。まだ MySQL に接続していない場合は、接続します。

mysql -u root -p

パスワードの入力を求められたら入力してください。

USE testdb;

既存のプロシージャを削除します。

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) VALUES (employee_name, employee_department);
    -- 挿入後に給与を更新する
    UPDATE employees SET salary = employee_salary WHERE name = employee_name AND department = employee_department;
END //
DELIMITER ;

変更点を分解して説明しましょう。

• プロシージャ定義に新しい入力パラメータ IN employee_salary DECIMAL(10, 2) を追加しました。DECIMAL(10, 2) は給与のデータ型で、最大 10 桁で小数点以下 2 桁までを許容します。
• employees テーブルに新しい列 salary を追加しました。この列は、以下の SQL 文を使用して手動で追加する必要があります。

ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

• 新しく挿入された従業員の給与を更新する UPDATE 文を追加しました。INSERT 文では直接給与を設定できないため、まず従業員を挿入し、その後名前と部署に基づいて給与を更新します。

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

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

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

SELECT * FROM employees;

テーブルには、名前が「Charlie Brown」、部署が「Finance」、給与が 60000.00 の新しい行が表示されるはずです。

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

プロシージャを削除する

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

まず、MySQL サーバーに接続し、testdb データベースを使用していることを確認してください。まだ接続していない場合は、以下のコマンドを使用します。

mysql -u root -p

パスワードの入力を求められたら入力してください。

USE testdb;

DROP PROCEDURE 文は、ストアドプロシージャを削除するために使用されます。構文は以下の通りです。

DROP PROCEDURE [IF EXISTS] procedure_name;

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

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

DROP PROCEDURE IF EXISTS insert_employee;

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

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

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 でのストアドプロシージャの作成、呼び出し、修正、削除に関する実験演習は終了です。

まとめ

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

この実験ではまた、ストアドプロシージャの入力パラメータを定義する方法、つまりそれらの名前とデータ型を指定する方法についても説明しました。これにより、プロシージャを呼び出す際に値を渡すことができ、より柔軟で再利用可能なプロシージャを作成できます。