开发篇 - 存储过程和函数 - 《MySQL》

1.概述
2.相关操作

1.概述

事先通过编译并存储在数据库中的一段SQL语句的结合通过调用过程和函数简化应用开发人员的很多好处

储存过程没有返回值，参数可以使用 IN OUT INOUT类型
函数必须要有返回值，参数只能是IN类型

2.相关操作

首先需要有EXECUTE权限

2.1创建、修改储存过程或者函数

CREATE PROCEDURE sp_name([proc-paranete][,...])
    [characteristic ..] routine_body
#创建、修改储存过程
 CREATE FUNCTION sp_name([proc-paranete][,...])
     RETURNS type
    [characteristic ..] routine_body
#创建、修改函数

proc-paranete（参数）：
[IN|OUT|INOUT] param_name type
IN 输入参数
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
仅需要将数据传入存储过程，并不需要返回计算后的该值。
只能当做传入参数
OUT 输出参数
该值可在存储过程内部被改变，并可返回
不接受外部传入的数据，仅返回计算之后的值。
只能当做转出参数
INOUT 输入输出参数
调用时指定，并且可被改变和返回
需要数据传入存储过程经过调用计算后，再传出返回值

可当做传入转出参数

type：
any volid mysql data type

[characteristic ..] routine_body p179

调用语法：

CALL sp_name([parameter][,...]);

2.2删除存储过程或者函数

一次只能删除一个存储过程和函数，删除存储过程或者函数需要有该过程或者函数 ALTER ROUTINE 权限

DROP {PROCEDURE|FUNCTION}[IF EXISTS]sp_name

2.3查看存储过程或者函数

2.3.1查看存储过程或者函数的状态

SHOW {PROCEDURE|FUNCTION} STATUS [like 'patten']

2.3.2查看存储过程或者函数的定义

查看存储过程或者函数的代码

SHOW CREATE {PROCEDURE|FUNCTION} sp_name

2.3.3通过查看系统表了解储存过程和函数信息

通过information_schema.Routines了解存储过程和函数的名称、类型、语法、创建人等信息

select * from routines where ROUTINE_NAME = ‘patten’;

2.4变量的使用

在5.1版本中，变量是不区分大小写的

2.4.1变量的定义

DECLARE：定义一个局部变量，只作用于BEGIN..END中，变量定义必须写在复合语句的前面

DECLARE var_name[,...] type[DEFAULT value]

var_name：变量名，如果需要一次声明多个相同类型的变量
type：变量类型
[DEFAULT value]：可以使用DEFAULT赋默认值

2.4.2变量的赋值

SET var_name=expr[,....]
#直接赋值使用SET，可以赋常量或者表达式

SELECT col_name[,...] INTO var_name[,...] table_expr
#通过查询将结果赋值给变量，这要求查询返回的结果必须只有一行

2.5定义条件和处理

条件定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤

2.5.1条件的定义

定义错误

DECLARE condition_name CONDITION FOR condition_value

condition_value:
SQLSTATE[VALUE]sqlstate_value | mysql_error_code

2.5.2条件的处理

错误的处理

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type：
CONTINUE：继续执行下面的语句
EXIT：执行终止
UNDO：现在还不支持！！
condition_value：
SQLSTATE的值
mysql-error-code的值
SQLWARNING
NOT FOUND
SQLLEXCEPTION

如出现在condition_value的错误类型，则该语句进行捕获，在进行处理

2.6光标的使用

使用光标对结果集进行循环的处理。光标的使用包括光标的声明、open、fetch、close。

DECLARE cursor_name CURSOR FOR select_statement
#声明光标

OPEN cursor_name
#open光标

FETCH cursor_name INTO var_name[,var_name]...
#fetch 光标

CLOSE cursor_name
#close 光标

ps：变量和条件必须在最前面声明，然后才能是光标的声明，最后才可以是处理程序的声明。

2.7流程控制

2.7.1 IF语句

IF 条件 THEN 语句
    ELSEIF 条件 THEN 语句....
  ELSE 语句
 END IF

2.7.2 CASE语句

CASE 条件名
    WHEN 值 THEN 语句...
  ELSE 语句
END IF

CASE 
    WHEN 条件名=值 THEN 语句...
  ELSE 语句
END IF

2.7.3 LOOP语句

[begin_lable:]loop
    statement_list
 END LOOP[end_lable]

LEAVE [begin_lable]：用来从标注的流程构造中推出
ITERATE [begin_lable]：跳过当前循环的剩下的语句

2.7.4 REPEAT语句

[begin_lable:] REPEAT
    statement_list
UNTIL search_condition
end REPEAT[end_lable]

2.7.5 WHILE语句

[begin_lable:] WHILE search_condition DO
    statement_list
END WHILE [end_lable]

WHILE是满足条件才执行循环，至少循环0次
REPEAT是满足条件退出，至少循环一次

存储过程和函数