MySQL管理与优化(9)_MySQL

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)

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

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 <= 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)