存储过程和函数

1、什么是存储过程和函数

事先经过编译并存储在数据库中的一段SQL语句的集合。

函数必须有返回值 ，而存储过程没有。

函数的参数只能是in类型的，存储过程的参数可以使用in、out、input类型

注意：若有函数从其他类型的数据库迁移到MySQL，那么久可能因此需要将函数改造成存储过程。

2、存储过程和函数的相关操作

（1）确认是否有相应的权限。

创建存储过程或函数：需要create routine权限

修改或者删除存储过程或函数：需要alter routine权限

执行存储过程或者函数：需要execute权限

（2）创建、修改存储过程或函数的语法：

调用过程的语法如下：

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

（3）MySQL的存储过程和函数中运行包含DDL语句，也允许在存储过程中执行提交Commit即确认之前的修改或者回滚Rollback即放弃之前的修改。

不允许执行load data infile语句。

可以调用其他的过程或者函数。

DELIMITER $$：将结束符改成$$

（4）不支持使用create or replace对存储过程和函数进行修改。要用alter

（5）

（6）删除存储过程或者函数

一次只能删除一个，且有需要有alter routine权限。

drop {procedure |function}[if exists] sp_name

（7）查看存储过程或者函数

查看存储过程或者函数的状态：show {procedure|function} status [like 'pattern']

查看存储过程或者函数的定义：show create {procedure|function} sp_name

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

（8）变量的使用（5.1中变量不区分大小写）

变量的定义：declare可定义局部变量，作用范围只能在begin..end块中，可以用在嵌套的块中。变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。

declare var_name[,...]   type   [default  value] 

变量的赋值：直接赋值set 、通过查询赋值

 set var_name=expr[,var_name=expr]

select col_name[,...]  into  var_name[,...]  table_expr

（9）定义条件和处理

条件的定义：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] sqlstate_value

|condition_name

|SQLWARNING：对所有以01开头的SQLSTATE代码的速记

|NOT FOUND：对所有以02

|SQLEXCEPTION：对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记

|mysql_errpr_code

（10）光标的使用

在存储过程和函数中，可以使用光标对结果进行循环的处理。

声明光标:declare  cursor_name  cursor for  select_statement

OPEN光标：open cursor_name

fetch光标：fetch  cursor_name into var_name[,var_name]...

close光标：close  cursor_name

（11）流程控制

if、case、loop、leave、iterate、repeat、while语句

leave:从标注的流程构造中退出，通常和begin..end或者循环一起使用

interate:必须用在循环中，跳过当前循环的剩下的语句，直接进入下一轮循环。

repeat:有条件的循环控制语句，当满足条件的时候退出循环。

（12）事件调度器

将数据库按自定义的时间周期出发某种操作，可以理解为时间触发器。

查看调度去状态：show events  \G;

查看时间调度器状态：show   variables  like  '%scheduler%'

打开调度器：set  global  event_scheduler = 1;

查看进程：show  processlist

禁用（disable event ）或者删除（drop  event）调度器