MySQL管理与优化(9)_MySQL

存储过程和函数

• 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。

存储过程或函数的相关操作

创建，修改存储过程或函数

• 相关语法

CREATE    [DEFINER = { user | CURRENT_USER }]    PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_bodyCREATE    [DEFINER = { user | CURRENT_USER }]    FUNCTION sp_name ([func_parameter[,...]])    RETURNS type    [characteristic ...] routine_bodyproc_parameter:    [ IN | OUT | INOUT ] param_name typefunc_parameter:    param_name typetype:    Any valid MySQL data typecharacteristic:    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: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci1 row in set (0.01 sec)-- 查看存储过程或函数的定义mysql> SHOW CREATE PROCEDURE cityname_by_id/G*************************** 1. row ***************************Procedure: cityname_by_idsql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONCreate 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;    ENDcharacter_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci1 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 INTOSELECT .. 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_statementhandler_type:     CONTINUE | EXIT | UNDOcondition_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_insertmysql> 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                   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, WHILE。
• 它们具体的用法可参考：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

不吝指正。