存储过程和函数

• 存储过程和函数是事先经过编译并存储在数据库中的一段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

查询存储过程或函数

1mysql> SHOW PROCEDURE status like 'cityname_by_id'\G
2*************************** 1. row ***************************
3                  Db: mysqltest
4                Name: cityname_by_id
5                Type: PROCEDURE
6             Definer: root@localhost
7            Modified: 2014-06-17 15:22:11
8             Created: 2014-06-17 15:22:11
9       Security_type: DEFINER
10             Comment:
11character_set_client: utf8
12collation_connection: utf8_general_ci
13  Database Collation: utf8_general_ci
141 row in set (0.01 sec)
15
16-- 查看存储过程或函数的定义
17mysql> SHOW CREATE PROCEDURE cityname_by_id\G
18*************************** 1. row ***************************
19Procedure: cityname_by_id
20sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
21Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `cityname_by_id`(IN cid INT, OUT total INT)
22    READS SQL DATA
23    BEGIN
24        SELECT id, city FROM city WHERE id=cid;
25
26        SELECT FOUND_ROWS() INTO total;
27    END
28character_set_client: utf8
29collation_connection: utf8_general_ci
30  Database Collation: utf8_general_ci
311 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

范例：

1-- 创建存储过程
2mysql> CREATE PROCEDURE city_insert()
3    -> BEGIN
4    ->  INSERT INTO city VALUES (200, 'Beijing');
5    ->  INSERT INTO city VALUES (200, 'Beijing');
6    -> END;
7    -> //
8Query OK, 0 rows affected (0.00 sec)
9-- 调用存储过程，第二句时报错
10mysql> CALL city_insert()//
11ERROR 1062 (23000): Duplicate entry '200' for key 'PRIMARY'
12
13-- 修改存储过程，支持异常处理
14DROP PROCEDURE IF EXISTS city_insert
15mysql> CREATE PROCEDURE city_insert()
16    -> BEGIN
17    ->  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x = 1;
18    ->  INSERT INTO city VALUES (300, 'ShangHai');
19    ->  INSERT INTO city VALUES (300, 'ShangHai');
20    -> END;
21    -> //
22Query OK, 0 rows affected (0.00 sec)
23
24-- 再次调用，将不会抛出错误
25mysql> CALL city_insert()//
26Query 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, 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

不吝指正。