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