MySQL 5.6 Developer試験公式「試験内容チェックリスト」

[https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882:embed:cite]

MySQL 5.6 リファレンスマニュアル

[https://dev.mysql.com/doc/refman/5.6/ja/:embed:cite]

MySQLのストアド・プログラムの設計、作成および使用

[https://dev.mysql.com/doc/refman/5.6/ja/stored-programs-views.html:embed:cite]

トリガーについて説明する。トリガーを使用する

[https://dev.mysql.com/doc/refman/5.6/ja/triggers.html:embed:cite]

トリガーの構文と例

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

ストアド・プロシージャを作成する。ストアド・プロシージャを実行する

[https://dev.mysql.com/doc/refman/5.6/ja/stored-programs-defining.html:embed:cite]

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

mysqlクライアントでセミコロン含みのストアドプログラムを作成しようとすると問題がおきる

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
- 定義側のコンテキストで実行するか、呼び出し元のコンテキストで実行するか

メタデータ

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

ストアド・プロシージャ内にエラー処理を実装する

[https://dev.mysql.com/doc/refman/5.6/ja/declare-handler.html:embed:cite]

死なずに続行するか(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)

D.Horiyama's output

MySQL 5.6 Developer試験対策 7 MySQLのストアド・プログラムの設計、作成および使用

トリガーについて説明する。トリガーを使用する

トリガーの構文と例

トリガーのメタデータ

ストアド・プロシージャを作成する。ストアド・プロシージャを実行する

ストアドプロシージャのメタデータ

ストアド・ファンクションを作成する。ストアド・ファンクションを使用する

メタデータ

【補】ストアドルーチン共通

メタデータ

ストアド・プロシージャ内にエラー処理を実装する