MySQL 存储过程、自定义函数 和 流程控制（ if 、case 、loop、leave、iterate 、repeat 和 while ）的语法、创建和使用
1）创建存储过程
drop procedure if exists [存储过程名] ;
delimiter [结束标记]
create procedure [存储过程名]([参数1], [参数2] ...)
begin
[存储过程体(一组合法的sql语句)]
end [结束标记]
delimiter ;
参数，其格式为 [in|out|inout] parameter_name type
delimiter [结束标记] 本身与存储过程的语法无关，用于表示存储过程的结束。
最后一个命令 delimiter ; 将 分隔符 改回 分号。
2）调用存储过程
call [存储过程名]([ proc_parameter [,proc_parameter ...]])
call [存储过程名]
说明：
当无参数时，可以省略括号，不写；
当有参数时，不可省略括号。
3）存储过程修改 ： 修改存储过程，就是删除重建。
4）删除存储过程： drop procedure [if exists] sp_name
drop table if exists `t_user`;
create table `t_user` (
`id` int not null auto_increment,
`name` varchar(20) not null,
primary key (`id`)
) engine=innodb auto_increment=1 default charset=utf8;
1.2.1、无参数的存储过程
drop procedure if exists myproc1;
delimiter $
create procedure myproc1()
begin
insert into t_user values(null, 'Jas'),(null, 'Joy');
end $
delimiter ;
调用，并查看结果：
drop procedure if exists myproc2;
delimiter $
create procedure myproc2(in userId int)
begin
select name from t_user where id = userId;
end $
delimiter ;
调用，并查看结果：
drop procedure if exists myproc3;
delimiter $
create procedure myproc3(in userId int, out username varchar(20))
begin
select name into username # 将查询到的用户名赋值给 username
from t_user where id = userId;
end $
delimiter ;
调用，并查看结果：
drop procedure if exists myproc4;
delimiter $
create procedure myproc4(inout a int)
begin
set a = a * 2;
end $
delimiter ;
调用存储过程，并查询结果：
delimiter $$
use `exam9` $$ ## exam9 是数据库
drop procedure if exists `updateStatus` $$ ## 如果存在，就删除
create procedure `updateStatus`()
begin
update exam set `status`="已结束" where `status` != "已结束" and (now() - endtime)>0 ;
update exam set `status`="正在答题" where `status` != "已结束" and ( now() - starttime)>=0
and (now() - endtime)0;
update haulinfo set bigstatus="进行中" where (curdate() - bigenddate)=0;
update exam set bigstatus=(select bigstatus from haulinfo where bigid=exam.bigid);
end $$
delimiter ;
1.3、区别：
1.3.1、存储过程的优点:
存储过程 就是把经常使用的 sql语句 或 业务逻辑封装起来，预编译保存在数据库中，当需要的时候从数据库中直接调用，省去了编译的过程；
提高了运行速度；
同时降低网络数据传输量（ 不用传一堆sql代码快，而是传一个存储过程名字和几个参数）。
1.3.2、存储过程 与 函数 的区别
一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
存储过程可以有返回值也可以没有返回值，而自定义函数必须要返回值，且返回值有且只有一个。
存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，因此它可以在查询语句中位于 from 关键字的后面。 SQL 语句中不可用存储过程，而可以使用函数。
自定义函数 就像是 abs() 、 concat() 内建函数一样去扩展 mysql 。
所以，udf 是对 mysql 功能的一个扩展。
2.1、自定义函数 udf1） 创建 udf
drop function if exists [函数名];
delimiter [结束标记]
create function [函数名]([参数1], [参数2] ...) returns [返回值类型]
begin
[方法体]
return [返回值];
end [结束标记]
delimiter ;
参数，其格式为 param_name type ，如 username varchar(20) 。
2） 删除 udf： drop function [函数名]
3）调用 udf ： select [函数名](param_value, ...)
t_user 表中的数据：
查询t_user 中的数据行数，并返回。
drop function if exists myfun1;
delimiter $
create function myfun1() returns int
begin
declare sum int default 0; # 定义局部变量 sum，默认值为 0
select count(*) into sum # 将查询的结果赋值给 sum
from t_user;
return sum;
end $
delimiter ;
调用结果：
drop function if exists myfun2;
delimiter $
create function myfun2(userId int) returns varchar(20)
begin
set @username=''; # 定义系统会话变量
select name into @username # 将用户名赋值给 username
from t_user
where id = userId;
return @username;
end $
delimiter ;
查看结果：
delimiter //
create function if exist deleteById(uid smallint unsigned) returns varchar(20)
begin
delete from t_order where id = uid;
return (select count (id) from son);
end //
delimiter ;
在函数体中，如果包含多条语句， 我们需要把多条语句放到 begin...end 语句块中。
begin...end 相当于 java 语言中的 { } 。
public int method(int param){
int result=0;
if(param==1){
......
result=......
}else if{param==2}{
....
result=......
}else{
.......
result=......
}
return result;
}
3.2、delimiter 修改默认的结束符
delimiter // 表示 将默认的结束符由 ; 改为 // ，以后的sql语句都要以 // 作为结尾 。
returns varchar(20) 声明 返回值 是 20位长度的字符串 。
returns int 声明返回值 int 。
reurn 语句 也包含在begin...end 中。
declare var_name[,varname]...date_type [default value];
简单来说就是：
declare 变量1[,变量2,... ]变量类型 [default 默认值]
这些变量的作用范围是在begin…end程序中,而且定义局部变量语句必须在begin…end的第一行定义
示例：
delimiter //
create function addNum(x smallint unsigned, y smallint unsigned) returns smallint
begin
declare a, b smallint unsigned default 10; ### 定义局部变量
set a = x, b = y;
return a+b;
end //
delimiter ;
上边的代码只是把两个数相加,当然,没有必要这么写,只是说明局部变量的用法,还是要说明下:这些局部变量的作用范围是在 begin...end 程序中
格式：
set parameter_name = value[,parameter_name = value...]
select ...... into parameter_name
示例：
...在某个udf中...
declare x int;
select count(id) from tdb_name into xxx ;
return x;
end//
3.7、全局变量定义
格式：
set @param_name = value
示例：
set @allparam = 100;
select @allparam;
上述定义并显示 @allparam 用户变量,其作用域只为当前用户的客户端有效。
存储过程 和 函数 中可以使用 流程控制语句 来 控制SQL 的执行。
mysql中可以使用 if 、case 、loop、leave、iterate 、repeat 和 while 语句 来进行流程控制。
每个流程中可能包含一个单独语句，或者是使用 begin...end 构造的复合语句，构造可以被嵌套。
根据是否满足条件，将执行不同的语句。
1） if 语法：
if search_condition then statement_list
[elseif search_condition then statement_list]
...
[else statement_list]
end if
参数说明：
search_condition 表示 条件判断语句；
statement_list 表示 不同条件的执行语句。
注意： mysql还有一个if()函数，不同于这里描述的 if 语句。
2） if 示例：
if age>20 then
set @count1=@count1+1;
elseif age=20 then
set @count2=@count2+1;
else
set @count3=@count3+1;
end if;
说明：
根据age与20的大小关系来执行不同的set语句。
如果age值大于20，那么将count 1的值加1；
如果age值等于20，那么将count 2的值加1；
其他情况将 count3 的值加 1 。
最后，if 语句都需要使用 end if 来结束。
case when 也用来进行条件判断，其可以实现 比 if 更复杂的条件判断。
1）case when 语法：
case case_value
when when_value then statement_list
[when when_value then statement_list] ...
[else statement_list]
end case
其中，
case_value 表示 条件判断的变量； when_value 表示 变量的取值； statement_list 表示 不同 when_value 值的执行语句。2）case when 语法2：
case
when search_condition then statement_list
[when search_condition then statement_list] ...
[else statement_list]
end case
参数说明：
search_condition 表示 条件判断语句； statement_list 表示 不同条件的执行语句。3）case when 示例
示列1：
case age
when 20 then set @count1=@count1+1;
else set @count2=@count2+1;
end case ;
示列2：
case
when age = 20 then
set @count1 = @count1+1;
else
set @count2 = @count2+1;
end case ;
如果 age 等于 20，count1 的值加 1；
否则 count2 的值加 1。
case when 都要使用 end case 结束。
注意：
这里的case 和 控制流程函数 中 sql case 表达式的 case语句有轻微不同。这里的 case when 不能有 else null 子句
并且用 end case 替代 end 来终止。
loop 可以使某些特定的语句重复执行，实现一个简单的循环。
但是 loop 本身没有停止循环的语句，必须使用 leave 等才能停止循环。
1） loop 语法：
[begin_label:] loop
statement_list
end loop [end_label]
参数说明：
begin_label 、end_label 分别表示 循环开始 和 结束的标志，这两个标志必须相同，而且都可以省略；
statement_list 表示需要循环执行的语句。
2） loop 示例
add_num: loop
set @count=@count+1;
end loop add_num ;
说明：
循环执行 count 加1的操作。
因为没有跳出循环的语句，这个循环成了一个死循环。
loop 循环都以 end loop 结束。
leave 用于跳出循环。
1） leave 语法：
leave label
参数说明：
label 表示 循环的标志。
2）leave 示例：
add_num: loop
set @count=@count+1;
if @count=100 then
leave add_num ;
end loop add_num ;
循环执行 count 加1的操作。
当 count 的值等于100时，则leave语句跳出循环。
5．iterate 跳出本次循环（continue）iterate 也是跳出循环。但是，iterate 语句是跳出本次循环，然后直接进入下一次循环。
iterate 只可以出现在loop、repeat、while 语句内。
1） iterate 语法：
iterate label
参数说明：
label 表示循环的标志。2）iterate 示例：
add_num: loop
set @count=@count+1;
if @count=100 then
leave add_num ;
else if mod(@count,3)=0 then
iterate add_num;
select from employee ;
end loop add_num ;
说明：
循环执行 count 加1的操作。
当 count 值为100时结束循环。
如果 count 的值能够整除3，则跳出本次循环，不再执行下面的select语句。
leave 和 iterate 的区别相同点：
leave 和 iterate 都用来跳出循环语句，但两者的功能是不一样的。
不同点：
leave 是跳出整个循环，然后执行循环后面的程序。
iterate 是跳出本次循环，然后进入下一次循环。
6．repeat （先）循环repeat 是有条件控制的循环。当满足特定条件时，就会跳出循环语句。
1）repeat 语法：
repeat
statement_list
until search_condition
end repeat
参数说明：
statement_list 表示 循环的执行语句；
search_condition 表示 结束循环的条件，满足该条件时循环结束。
2） repeat 示例：
repeat
set @count=@count+1;
until @count=100
end repeat ;
循环执行count 加1的操作。
当 count 值为10 0时 结束循环。
repeat循环都用end repeat结束。
7．while （先判断，再）循环while 也是有条件控制的循环语句。但while 和 repeat 是不一样的。
while 是当满足条件时，执行循环内的语句。
1） while 语法：
while search_condition do
statement_list
end while
参数说明：
search_condition 表示 循环执行的条件，满足该条件时循环执行；
statement_list 表示 循环的执行语句。
2） while 示例：
while @count<100 do
set @count=@count+1;
end while ;
循环执行count 加1的操作。
如果 count 值小于100时执行循环；
如果 count 值等于100了，则跳出循环。
while 循环需要使用end while 来结束。
作者：xiaojin21cen
mysql 存储过程
自定义
WHILE
IF
REPEAT
LEAVE
ITERATE
case
函数
存储
loop
存储过程
Mysql
