MySQL管理と最適化(9):ストアド・プロシージャと関数

ストアド・プロシージャと関数

ストアド・プロシージャおよび関数は、事前にコンパイルされ、データベースに格納されたSQL文のセットです.

ストアド・プロシージャまたは関数に関するアクション
ストアド・プロシージャまたは関数の作成、変更

関連構文

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

例

DELIMITER //

--       
mysql> CREATE PROCEDURE cityname_by_id(IN cid INT, OUT total INT)
    -> READS SQL DATA
    -> BEGIN
    ->  SELECT id, city FROM city WHERE id=cid;
    ->
    ->  SELECT FOUND_ROWS() INTO total;
    -> END //
Query OK, 0 rows affected (0.06 sec)

--       
mysql> CALL cityname_by_id(2, @res);
+----+----------+
| id | city     |
+----+----------+
|  2 | NeiJiang |
+----+----------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> SELECT @res;
+------+
| @res |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

ストアド・プロシージャまたは関数の削除

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

クエリー・ストアド・プロシージャまたは関数

mysql> SHOW PROCEDURE status like 'cityname_by_id'\G
*************************** 1. row ***************************
                  Db: mysqltest
                Name: cityname_by_id
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2014-06-17 15:22:11
             Created: 2014-06-17 15:22:11
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

--             
mysql> SHOW CREATE PROCEDURE cityname_by_id\G
*************************** 1. row ***************************
Procedure: cityname_by_id
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `cityname_by_id`(IN cid INT, OUT total INT)
    READS SQL DATA
    BEGIN
        SELECT id, city FROM city WHERE id=cid;

        SELECT FOUND_ROWS() INTO total;
    END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

またはシステムテーブルからinformation_schema.routinesで検索:

mysql> SELECT * FROM information_schema.routines WHERE ROUTINE_NAME='cityname_by_id'\G

変数の使用

変数の定義:BEGINのみ...ENDブロックでは、構文は

です.

DECLARE var_name[,...] type [DEFAULT_VALUE]

DECLARE last_month_start DATE;

変数の付与:直接付与またはクエリー付与

SET var_name = expr [, var_name = expr] ...

#      
SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)

# SELECT INTO
SELECT .. FROM .. INTO var_name

定義条件および処理

--      
DECLARE condition_name CONDITION FOR condition_value
 
condition_value:
    SQLSTATE [VALUE] 
    sqlstate_value| 
    mysql_error_code

--      
DECLARE handler_type HANDLER FOR condition_value[, ...] sp_statement

handler_type:
     CONTINUE | EXIT | UNDO
condition_value:
    SQLSTATE [VALUE] 
    condition_name| 
    SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

例:

--       
mysql> CREATE PROCEDURE city_insert()
    -> BEGIN
    ->  INSERT INTO city VALUES (200, 'Beijing');
    ->  INSERT INTO city VALUES (200, 'Beijing');
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)
--       ，      
mysql> CALL city_insert()//
ERROR 1062 (23000): Duplicate entry '200' for key 'PRIMARY'

--       ，      
DROP PROCEDURE IF EXISTS city_insert
mysql> CREATE PROCEDURE city_insert()
    -> BEGIN
    ->  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x = 1;
    ->  INSERT INTO city VALUES (300, 'ShangHai');
    ->  INSERT INTO city VALUES (300, 'ShangHai');
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

--     ，       
mysql> CALL city_insert()//
Query OK, 0 rows affected, 1 warning (0.09 sec)

カーソルの使用

結果セットは、記憶プロセスおよび関数においてカーソルを用いてループ処理することができる.

--     
DECLARE cursor_name CURSOR FOR select_statement

-- OPEN   
OPEN cursor_name

-- FETCH   
FETCH cursor_name INTO var_name [, var_name]

-- CLOSE   
CLOSE cursor_name

例

--       
mysql> CREATE PROCEDURE city_stat()
    -> BEGIN
    ->  DECLARE cid INT;
    ->  DECLARE cname VARCHAR(20);
    ->  DECLARE cur_city CURSOR FOR SELECT * FROM city;
    ->  DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_city;
    ->
    ->  SET @x1 = 0;
    ->  SET @x2 = 0;
    ->
    ->  OPEN cur_city;
    ->
    ->  REPEAT
    ->          FETCH cur_city INTO cid, cname;
    ->          IF cid <= 4 THEN
    ->                  SET @x1 = @x1 + cid;
    ->          ELSE
    ->                  SET @x2 = @x2 + cid * 2;
    ->          END IF;
    ->  UNTIL 0 END REPEAT;
    ->
    ->  CLOSE cur_city;
    ->
    -> END;
    -> //
Query OK, 0 rows affected (0.06 sec)

--       
mysql> SELECT * FROM city;
+-----+----------+
| id  | city     |
+-----+----------+
|   2 | NeiJiang |
|   3 | HangZhou |
|  10 | ChengDu  |
| 200 | Beijing  |
| 300 | ShangHai |
+-----+----------+
5 rows in set (0.00 sec)

mysql> CALL city_stat();
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @x1, @x2;
+------+------+
| @x1  | @x2  |
+------+------+
|    5 | 1020 |
+------+------+
1 row in set (0.00 sec)

変数、条件、プロセッサ、カーソルの宣言には順序があり、変数と条件は一番前に宣言し、カーソルの宣言、最後にプロセッサの生命である必要があります.

プロセス制御

具体的なフロー制御文は、IF、CASE、LOOP、LEAVE、ITERATE、REPEAT、WHILEEである.

これらの具体的な使用法は、次のように参照できます.http://dev.mysql.com/doc/refman/5.7/en/flow-control-statements.html

詳細については、以下を参照してください.
http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
指摘を惜しまない.