mysql

mysql で ストアドプロシージャ を登録・変更・削除する

2021年6月30日

ストアドプロシージャは サーバーに格納できる一連の SQL です。これが一度登録されると クライアントは個々のステートメントを繰り返し発行す必要はなくなります。

ユーザー作成

ユーザーに権限を付与する

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `database`.* TO 'user'@'10.1.%';

ひとつひとつ説明する。

SELECT, INSERT, UPDATE, DELETE これは普通の DML なので省略する。

CREATE ROUTINE … ストアドプロシージャを作成する権限

ALTER ROUTINE … ストアドプロシージャを削除・変更する権限

EXECUTE … ストアドプロシージャを実行する権限

ストアドプロシージャを登録する

ストアドプロシージャーを実行する際、 delimiter を一時的に変更すると登録しやすい。

delimiter // 
create procedure sample()
    -> begin
    -> select count(*) from user;
    -> end
    -> //

delimiter ;

ストアドプロシージャを確認する

SHOW PROCEDURE STATUS;

SHOW PROCEDURE STATUS;
+------------------+---------+-----------+--------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db               | Name    | Type      | Definer            | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------------+---------+-----------+--------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| database | sample  | PROCEDURE | root@%             | 2021-06-29 14:47:44 | 2021-06-29 14:47:44 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8mb4_general_ci |
| database | sample1 | PROCEDURE | user@10.1.% | 2021-06-29 14:57:08 | 2021-06-29 14:57:08 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8mb4_general_ci |
+------------------+---------+-----------+--------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

ストアドプロシージャを実行する

call sample1();
+----------+
| count(*) |
+----------+
|   188376 |
+----------+
1 row in set (0.00 sec)