MySQL 5.6 Developer試験対策 7 MySQLのストアド・プログラムの設計、作成および使用
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
- トリガーについて説明する。トリガーを使用する
- ストアド・プロシージャを作成する。ストアド・プロシージャを実行する
- ストアド・ファンクションを作成する。ストアド・ファンクションを使用する
- 【補】ストアドルーチン共通
- ストアド・プロシージャ内にエラー処理を実装する
MySQLのストアド・プログラムの設計、作成および使用
トリガーについて説明する。トリガーを使用する
トリガーの構文と例
CREATE DATABASE IF NOT EXISTS sample; USE sample; CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
- mysql データベースだと駄目だった
ERROR 1465 (HY000): Triggers can not be created on system tables
mysql> SELECT @sum FROM DUAL; +------+ | @sum | +------+ | NULL | +------+ 1 row in set (0.00 sec)
- ユーザ変数を初期化
mysql> SET @sum = 0; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @sum FROM DUAL; +------+ | @sum | +------+ | 0 | +------+ 1 row in set (0.00 sec)
INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); SELECT @sum;
+---------+ | @sum | +---------+ | 1852.48 | +---------+ 1 row in set (0.00 sec)
- TRIGGERに直接ロジックを書くのではなく、ストアドプロシージャに逃したほうが簡単になることも
トリガーのメタデータ
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G; *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: sample TRIGGER_NAME: ins_sum EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: sample EVENT_OBJECT_TABLE: account ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: SET @sum = @sum + NEW.amount ACTION_ORIENTATION: ROW ACTION_TIMING: BEFORE ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: NULL SQL_MODE: NO_ENGINE_SUBSTITUTION DEFINER: root@localhost CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci 1 row in set (0.00 sec) ERROR: No query specified
SHOW CREATE TRIGGER
文- 特定のトリガーを定義した
CREATE TRIGGER
文の確認等
- 特定のトリガーを定義した
SHOW CREATE TRIGGER ins_sum\G; *************************** 1. row *************************** Trigger: ins_sum sql_mode: NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) ERROR: No query specified
SHOW TRIGGERS
文- triggerの一覧
SHOW TRIGGERS\G; *************************** 1. row *************************** Trigger: ins_sum Event: INSERT Table: account Statement: SET @sum = @sum + NEW.amount Timing: BEFORE Created: NULL sql_mode: NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) ERROR: No query specified
ストアド・プロシージャを作成する。ストアド・プロシージャを実行する
mysql> CREATE PROCEDURE dorepeat(p1 INT) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END; CREATE PROCEDURE dorepeat(p1 INT) -> BEGIN -> SET @x = 0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 mysql> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPEAT SET @x = @x + 1' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNTIL @x > p1 END REPEAT' at line 1 mysql> END; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
CREATE PROCEDURE dorepeat(p1 INT) -> BEGIN -> SET @x = 0;
- BEGIN-ENDの中のボディの
;
でCREATE PROCEDURE
文が断ち切られてしまう - ので、一時的にデリミタを再定義する
\
はやめよう
delimiter // CREATE PROCEDURE dorepeat(p1 INT) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END// delimiter ;
- PostgreSQLと異なり、オーバーロードはできない
delimiter // CREATE PROCEDURE dorepeat(p1 INT, p2 INT) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END// delimiter ;
ERROR 1304 (42000): PROCEDURE dorepeat already exists
- PROCEDUREはCALLで呼ぶ
mysql> CALL dorepeat(1000); CALL dorepeat(1000); Query OK, 0 rows affected (0.01 sec) mysql> SELECT @x FROM DUAL; SELECT @x FROM DUAL; +------+ | @x | +------+ | 1001 | +------+ 1 row in set (0.00 sec)
ストアドプロシージャのメタデータ
SHOW CREATE PROCEDURE dorepeat\G; *************************** 1. row *************************** Procedure: dorepeat sql_mode: NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `dorepeat`(p1 INT) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) ERROR: No query specified
mysql> SHOW PROCEDURE STATUS LIKE 'dorepeat'\G; SHOW PROCEDURE STATUS LIKE 'dorepeat'\G; *************************** 1. row *************************** Db: sample Name: dorepeat Type: PROCEDURE Definer: root@localhost Modified: 2020-06-03 17:05:38 Created: 2020-06-03 17:05:38 Security_type: DEFINER Comment: character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) ERROR: No query specified
SHOW PROCEDURES
とかはない
ストアド・ファンクションを作成する。ストアド・ファンクションを使用する
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN CONCAT('Hello, ',s,'!');
mysql> SELECT hello('world'); +----------------+ | hello('world') | +----------------+ | Hello, world! | +----------------+ 1 row in set (0.00 sec)
- テーブルとかは返せない
- テーブルのSELECTクエリ結果の表示だけならPROCEDUREでできる
delimiter // CREATE PROCEDURE hoge(num int) BEGIN SELECT num FROM DUAL; END// delimiter ;
- 再帰関数にはできない
メタデータ
SHOW CREATE FUNCTION hello\G; *************************** 1. row *************************** Function: hello sql_mode: NO_ENGINE_SUBSTITUTION Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s CHAR(20)) RETURNS char(50) CHARSET latin1 DETERMINISTIC RETURN CONCAT('Hello, ',s,'!') character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) ERROR: No query specified
SHOW FUNCTION STATUS LIKE 'hello'\G; *************************** 1. row *************************** Db: sample Name: hello Type: FUNCTION Definer: root@localhost Modified: 2020-06-03 17:08:11 Created: 2020-06-03 17:08:11 Security_type: DEFINER Comment: character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) ERROR: No query specified
SHOW FUNCTIONS
とかはない
【補】ストアドルーチン共通
- EXECUTE 権限必要
- SQL SECURITY特性
- DEFINER / INVOKER
- 定義側のコンテキストで実行するか、呼び出し元のコンテキストで実行するか
- DEFINER / INVOKER
メタデータ
SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G; *************************** 1. row *************************** SPECIFIC_NAME: dorepeat ROUTINE_CATALOG: def ROUTINE_SCHEMA: sample ROUTINE_NAME: dorepeat ROUTINE_TYPE: PROCEDURE DATA_TYPE: CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2020-06-03 17:05:38 LAST_ALTERED: 2020-06-03 17:05:38 SQL_MODE: NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT: DEFINER: root@localhost CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci *************************** 2. row *************************** SPECIFIC_NAME: hello ROUTINE_CATALOG: def ROUTINE_SCHEMA: sample ROUTINE_NAME: hello ROUTINE_TYPE: FUNCTION DATA_TYPE: char CHARACTER_MAXIMUM_LENGTH: 50 CHARACTER_OCTET_LENGTH: 50 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: latin1 COLLATION_NAME: latin1_swedish_ci DTD_IDENTIFIER: char(50) ROUTINE_BODY: SQL ROUTINE_DEFINITION: RETURN CONCAT('Hello, ',s,'!') EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: YES SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2020-06-03 17:08:11 LAST_ALTERED: 2020-06-03 17:08:11 SQL_MODE: NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT: DEFINER: root@localhost CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci 2 rows in set (0.00 sec) ERROR: No query specified
ストアド・プロシージャ内にエラー処理を実装する
- 死なずに続行するか(
CONTINUE
)、BEGIN-ENDブロックを離脱するか(EXIT
)UNDO
もパースできるがサポートされていない
CREATE TABLE sample.t (s1 INT, PRIMARY KEY (s1)); delimiter // CREATE PROCEDURE handlerdemo () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; INSERT INTO sample.t VALUES (1); SET @x = 2; INSERT INTO sample.t VALUES (1); SET @x = 3; END// delimiter ; CALL handlerdemo(); SELECT @x;
+------+ | @x | +------+ | 3 | +------+ 1 row in set (0.00 sec)
CONTINUE
ハンドラがないと主キー重複でエラーになる
CREATE TABLE sample.t (s1 INT, PRIMARY KEY (s1)); delimiter // CREATE PROCEDURE handlerdemo () BEGIN -- DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; INSERT INTO sample.t VALUES (1); SET @x = 2; INSERT INTO sample.t VALUES (1); SET @x = 3; END// delimiter ; CALL handlerdemo(); SELECT @x;
+------+ | @x | +------+ | 2 | +------+ 1 row in set (0.00 sec)
SHOW ERRORS; +-------+------+---------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------+ | Error | 1062 | Duplicate entry '1' for key 'PRIMARY' | +-------+------+---------------------------------------+ 1 row in set (0.00 sec)