MySQL--存储过程和函数
概述
1. 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
2. 存储过程和函数的区别
- 函数必须有返回值，而存储过程没有
- 存储过程的参数可以使用IN, OUT, INOUT 类型；而函数的参数只能是IN类型的
如果有函数从其他类型的数据库迁移到MySQL，那么就可能需要将函数改造成存储过程。
存储过程和函数的相关操作
|-创建、修改存储过程或者函数
1. 在对存储过程或函数进行操作时，需要首先确认用户是否具有相应的权限。例如
- 创建存储过程或者函数需要 CREATE ROUTING权限
- 修改或者删除存储过程或函数需要ALTER ROUTING权限
- 执行存储过程或函数需要EXECUTE权限
2. 创建存储过程或者函数
CREATE PROCEDURE sp_name ([proc_patameter[, ...]])
[characteristic ...] routine_body
CREATE FUNCTION sp_name ([func_patameter[, ...]])
RETURNS type
[characteristic ...] routine_body
说明： proc_parameter:
[IN|OUT|INOUT] param_name type
fun_patameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
|[NOT] DETERMINISTIC
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'
routine_body:
Valid SQL procedure statement or statements
3. 修改存储过程或函数
ALTER {PROCEDURE|FUNCTION} sp_name
[characteristic ...]
characteristic:
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'
4. 调用过程的语法
CALL sp_name ([parameter[, ...]])
5. MySQL的存储过程和函数中允许包含DDL语句，也允许在存储过程中执行提交（Commit，即确认之前的修改）或者回滚（Rollback，即放弃之前的修改），但是存储过程和函数中不允许执行LOAD DATA INFILE语句。
6. 存储过程和函数可以调用其他的过程或者函数
7. 和视图的创建语法稍有不同，存储过程和函数的CREATE语法不支持使用CREATE OR REPLACE对存储过程和函数进行修改，如果需要对已有的存储过程或者函数进行修改，需要执行ALTER语法。
8. characteristic说明
- LANGUAGE SQL: 说明下面过程的BODY是使用SQL语言编写，这条是系统默认的，为今后MySQL会支持的除SQL外的其他语言支持的存储过程而准备
- [NOT] DETERMINISTIC: DETERMINISTIC 确定的，即每次输入一样输出也一样的程序，NOT DITERMINISTIC非确定的，默认是非确定的。当前，这个特征值还没有被优化程序使用。
- {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}: 这些特征值提供子程序使用数据的内在信息，这些特征值目前只是提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况。CONTAINS SQL 表示子程序不包含读或写数据的语句。NO SQL 表示子程序不包含SQL语句。READS SQL DATA 表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA 表示子程序包含写数据的语句。如果这些特征没有明确定义，默认使用的值是CONTAINS SQL。
- SQL SECURITY {DEFINER|INVOKER}: 可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是DEFINER。
- COMMENT 'string': 存储过程或者函数的注释信息。
|-删除存储过程或者函数
1. 一次只能删除一个存储过程或者函数，删除存储过程或者函数需要有该过程或者函数的ALTER ROUTINE权限，具体语法如下
DROP {PROCEDURE|FUNCTION} [IF EXIST] sp_name
|-查看存储过程或者函数
1. 查看存储过程或者函数的状态
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']
2. 查看存储过程或者函数的定义
SHOW CREATE {PROCEDURE|FUNCTION} sp_name
3. 通过查看information_schema.Routines获得存储过程和函数的名称、类型、语法、创建人等信息
SELECT *
FROM routines
WHERE routine_name='sp_name'
|-变量的使用
1. 通过DECLARE可以定义一个局部变量，该变量的作用范围只能在BEGIN...END块中，可以用在嵌套的块中。
2. 变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。可以一次声明多个相同类型的变量。如果需要，可以使用DEFAULT赋默认值
3. 定义一个变量的语法
DECLARE var_name[, ...] type [DEFAULT value]
4. 变量可以直接赋值，或者通过查询赋值。直接赋值使用SET，可以赋常量或者赋表达式，具体语法如下
SET var_name = expr[, var_name = expr] ...
也可以通过查询将结果赋给变量，这要求查询返回的结果必须只有一行，具体语法如下
SELECT col_name[, ...]
INTO var_name[, ...]
table_expr
|-定义条件和处理
1. 条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤
2. 条件的定义
DECLARE condition_name
CONDITION FOR condition_value
condition_value:
SQLSTATE [VALUE] sqlstate_value
|msql_error_code
3. 条件的处理
DECLARE handler_type
HANDLER FOR condition_value[, ...] sp_statement
handler_type:
CONTINUE
|EXIT
|UNDO(目前还不支持)
condition_value:
SQLSTATE [VALUE] sqlstate_value
|condition_name
|SQLWARNING
|NOT FOUND
|SQLEXCEPTION
|mysql_error_code
handler_type现在还只支持CONTINUE和EXIT两种，CONTINUE表示继续执行下面的语句，EXIT表示执行终止，UNDO现在还不支持
condition_value的值可以是通过DECLARE定义的condition_name，可以是SQLSTATE的值或者mysql-error-code的值或者SQLWARNING、NOT FOUND、SQLEXCEPTION。
SQLWARNING是对所有以 01 开头的SQLSTATE代码的速记
NOT FOUND 是对所有以 02 开头的SQLSTATE代码的速记
SQLEXCEPTION 是对所有没有被SQLWARNING和NOT FOUND捕获的SQLSTATE代码的速记
|-光标的使用
1. 在存储过程和函数中，可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH、CLOSE
2. 声明光标
DECLARE cursor_name
CURSOR FOR select_statement
3. OPEN光标
OPEN cursor_name
4. FETCH光标
FETCH cursor_name
INTO var_name[, varname] ...
5. CLOSE光标
CLOSE cursor_name
6. 变量、条件、处理程序、光标都是通过DECLARE定义的，他们之间是有先后顺序要求的。变量和条件必须在最前面声明，然后才能是光标的声明，最后才可以是处理程序的调用。
|-流程控制
1. IF语句。IF实现条件判断，满足不同的条件执行不同的语句列表，具体语法如下
IF search_condition THEN statement_list
[ELSE search_condition THEN statement] ...
[ELSE statement_list]
END IF
2. CASE语句
CASE case_value
IF search_condition THEN statement_list
[ELSE IF search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
或者：
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
3. LOOP语句。LOOP实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用LEAVE语句实现，具体语法如下
[begin_label:] LOOP
statement_list
END LOOP [end label]
如果不在statement_list中增加退出循环的语句，那么LOOP语句可以用来实现简单的死循环
4. LEAVE语句。用来从标注的流程构造中退出，通常和 BEGIN...END 或者循环一起使用。
1 DROP PROCEDURE IF EXISTS insertDept; 2 3 DELIMITER $$ 4 5 CREATE PROCEDURE insertDept(IN startNo int, IN count int) 6 7 BEGIN 8 9 SET @x = startNo; 10 SET @e = @x + count; 11 ins: LOOP 12 IF @x >= @e THEN 13 LEAVE ins; 14 END IF; 15 INSERT INTO dept 16 (deptno, deptname) 17 VALUES 18 (@x, CONCAT('test', @x)); 19 SET @x = @x + 1; 20 END LOOP ins; 21 22 END; 23 24 $$ 25 26 DELIMITER ;
5. ITERATE语句。ITERATE语句必须用在循环中，作用是跳过当前循环的剩下的语句，直接进入下一轮循环。
6. REPEAT语句。有条件的循环控制语句，当满足条件的时候退出循环，具体语法如下：
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end label]
7. WHILE 语句。WHILE 语句实现的也是有条件的循环控制语句，即当满足条件时执行循环的内容，具体语法如下
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
8. WHILE 循环和 REPEAT循环的区别在于： WHILE 是满足条件才执行循环， REPEAT 是满足条件退出循环；WHILE 在首次循环执行之前就判断条件，所以循环最少执行0次，而 REPEAT 是在首次执行循环之后才判断条件，所以循环最少执行一次。
9. 时间调度器是MySQL 5.1 后新增的功能，可以将数据库按自定义的时间周期触发某种操作。
时间调度器创建
