存储程序

有时候，为了完成一个常用的功能需要执行多条语句，而每次都在客户端中一条一条的输入这么多语句是很烦的。而存储程序就可以解决这个烦恼，它可以封装很多语句，然后为用户提供一种简单 方式来调用这个程序。

存储程序分为存储历程，触发器，事件，而存储例程又分为存储函数和存储过程

用户自定义变量：

和写代码一样，在mysql中一样可以定义变量。

格式：set @变量名=赋值；

在使用set语句时，如果变量名前没有加@符号，系统会把这个变量当作系统变量来对待，而不是用户自定义变量。

如果想查看这个变量的值，使用select语句就可以：select @变量名；

同一个变量可以存储不同类型的值，除了把一个常量赋值给一个变量，还可以把一个变量赋值给另一个变量

给变量赋值也可以用into语句来解决，比如这样select n1 from t1 into @b；

当某个查询语句的结果集最多包含一条记录，但是结果集的记录包含多个列时，如果我们想将结果集记录中各列的值分别赋值给不同的变量，此时就不能用set语句了，而只能用into子句来完成。

存储函数：

创建存储函数：

语法：

create function 存储函数名称（【参数列表】）

returns 返回值类型

begin 

   函数体内容

end

mysql> create function avg_score(s varchar(100))
    -> returns double
    -> begin
    -> return (selecct avg(score) from student_score where subject=s);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'avg(score) from student_score where subject=s)' at line 4

我们可以看到我们在完成这个函数体的构造的时候出现了问题，当写到return语句后，写下；后本来想按回车键换到下一行写end，但此时MySQL自动识别把分号之前的所有内容发送到了服务器。

如何解决这个问题呢？其实只要使用delimiter语句临时修改一下MySQL客户端的语句分隔号就好。

像这样：

mysql> delimiter $

 -> create function avg_score(s varchar(100))
    -> returns double
    -> begin
    ->   return (select avg(score) from student_score where subject = s);
    -> end $

如果在创建函数的时候出现如下报错：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

可以先执行下面这条语句：

mysql> set global log_bin_trust_function_creators=true;

现在创建完毕后可以测试一下函数的调用了：

mysql> select avg_score('计算机是怎样运行的');
+---------------------------------+
| avg_score('计算机是怎样运行的') |
+---------------------------------+
|                            77.8 |
+---------------------------------+
1 row in set (0.01 sec)

查看和删除储存函数：

如果想查看现在已经定义了多少个存储函数以及各个存储函数的相关属性，可以使用下面这个语句：

show function status 【like 需要匹配的函数名】

如果想查看某个函数具体是怎么定义的，可以使用这个语句：
show create function 函数名；

删除某个函数：

drop function 函数名

函数体的定义：

在函数体中定义局部变量：

前文在介绍用户自定义变量时说过，可以直接使用set语句为自定义变量赋值而不事先声明它。如果我们想在存储函数的函数体中使用变量的话，必须提前使用declare语句声明变量。

declere 变量名1， 变量名2， 。。。。数据类型 [defult 默认值]；

需要特别留心的是，函数体中的局部变量名不允许加@前缀。在声明了这个局部变量后，才可以使用它。

在函数体中使用用户自定义变量：

除局部变量外，也可以在函数体中使用之前用过的用户自定义变量，

mysql> delimiter $
mysql> create function user_defined_var_demo()
    -> returns int
    -> begin
    -> set @abc=10;
    -> return @abc;
    -> end $
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> select @abc;
+------------+
| @abc       |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> select user_defined_var_demo();
+-------------------------+
| user_defined_var_demo() |
+-------------------------+
|                      10 |
+-------------------------+
1 row in set (0.01 sec)

mysql> select @abc;
+------+
| @abc |
+------+
|   10 |
+------+
1 row in set (0.00 sec)
 

在这个例子中我们可以看到在没有调用函数之前这个用户自定义变量是没有变化的，只有在调用函数之后才发生改变。

这也就意味着即使存储函数执行完毕，该存储函数修改过的用户自定义变量的值将继续生效。这一点与函数体中使用declare声明的局部变量有明显差别；

判断语句的编写：

if 表达式 then

语句列表

[elseif 表达式 then

语句列表 ]

...

[else 

语句列表]

end if;

循环语句的编写：

分为三种循环语句：

while 表达式 do

   语句列表

end while；

repeat 

  语句列表

until 表达式 end repeat；

loop

语句列表

end loop

这几种其实都差不多，无非是先判断还是先执行的区别。

第三种稍微特殊一点，在语句列表中放一个判断语句来实现循环停止。

存储过程：

存储函数和存储过程都属于存储例程，都是对某些语句的一个封装。存储函数会给调用它的用户返回一个结果，但是存储过程却没有返回值。

创建存储过程：

create produce 存储过程名称 ([参数列表])

begin

需要执行的语句

end

mysql> delimiter $
mysql> create procedure t1_operation(
    -> m1_value int,
    -> n1_value char(1)
    -> )
    -> begin
    ->  select * from t1;
    ->  insert into t1(m1, n1) values(m1_value, n1_value);
    ->  select * from t1;
    -> end $
Query OK, 0 rows affected (0.03 sec)

存储过程的调用：

存储过程并没有返回值，不能像存储函数那样进行函数调用。如果我们需要调用某个存储过程，需要显示的使用call 语句。

call 存储过程（[参数列表]）；

mysql> call t1_operation(4, 'd');
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.01 sec)

+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

查看删除存储过程：

用于查看目前服务器上已经创建了哪些存储过程的语句：

show procedure status [like 需要匹配的存储过程名称]

用于查看某个存储过程具体是怎么定义的语句：

show create procedure 存储过程名称

用于删除存储过程的语句：

drop procdure 存储过程名称

存储过程的参数前缀：

in ： 用于调用者像存储过程传递数据，如果in参数在存储过程中被修改，则调用者不可见

out：用于把存储过程运行过程中产生的数据赋值给out参数，存储过程执行结束后，调用者可以访问out参数；

inout：综合in和out的特点。

游标简介：

到现在为止，面对结果集有多个列，要分别赋值到多个变量中去时只能使用select   into 语句来实现，当列过多的时候。为了方便我们访问具有多条记录的结果集，MySQL中引入了游标的概念。

创建游标：

declare 游标名字 cursor for 查询语句

打开和关闭游标：

open 游标名字；

close 游标名字；

打开游标一位置执行查询语句，是我们之前声明的游标与查询语句的结果集相关联起来。关闭游标意味着释放与该游标相关的资源，所以一旦用完游标，就要把它关掉。

通过游标获取记录：

fetch 游标名 into 变量1， 变量2，.....变量n

这个语句的意思就是把当前游标对应记录的各列的值依次赋值给into后面的各个变量

遍历结束时的执行策略：
fetch语句在遇到获取不到记录的时候本来会自动停止存储函数或者存储过程的执行，并直接向客户端返回一个错误，但如果我们不想返回这个错误而是采用事先声明的错误处理方式去处理。我们就要事先声明一个语句：

declare continue handler for not found 处理语句；

触发器：

我们在对标中的记录进行增删改查操作的前和后，都可能需要让mysql服务器自动执行一些额外的语句，这就是所谓的触发器的应用场景。

创建触发器：

create trigger 触发器名

{before|after}

{insert|delete|update}

on 表名

for each row

begin