7.存储过程，存储函数，游标

在mysql中，存储过程是一个可编程的函数，它在数据库中创建并保存。可以有SQL语句和一些特殊的控制结构组成。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。存储过程可以由程序、触发器或者另一个存储过程来调用，从而激活它，实现代码段中的SQL语句。

优点：存储过程能实现较快的执行速度；允许标准组件式编程即模块化程序设计；可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算；可以被作为一种安全机制来充分利用；能减少网络流量。

存储过程的创建

create procedure sp_name([proc_parameter[],....])[characteristic...]routime_body

sp_name存储过程的名称，默认在当前数据库中创建，proc_parameter存储过程的参数，characteristic参数制定存储过程的特性，routime_body参数是SQL代码的内容，可以用begin....and来标志开始和结束。proc_parameter中的每个参数由三部分组成，分别是输出类型，参数名和参数类型，格式为：[in|out|inout]param_name type

delimiter命令将mysql语句的结束标志修改为其他的符号；delimiter ##

简单的存储过程例子

delimiter $$
create procedure delete_stu(in bh int)
begin
   delete from sc where 编号=bh
end $$
delimiter;

存储过程体

定义变量
declare var_name[,...]type[default value]
declare生命变量 var_name为变量名称 type为变量类型，default value子句给变量一个默认值为value，如果不指定为null
例：declare my_sql int default=10;
        declare str1,str2 varchar(6);
为变量赋值
set var_name=expr[,var_name=expr]...
set为变量赋值，var_name变量名称，expr是赋值表达式，一个set语句可以为多个变量赋值，各个变量赋值用逗号隔开。
例：set my_sql=1,str1='hello'
select 姓名,系部 into name,project
from student
where sno=0268
#将student表中学号为0268的学生姓名，系部分别赋予name，project

常见的流程控制语句
if语句、case语句、loop语句、while语句、iterate语句、leave语句
if语句
if search_condition then statement_list
[elseif search_condition then statement_list]...
[else statement_list]
end if
search_condition判断条件，statement_list包含一个或多个SQL语句，表示不同的执行语句。当search_condition条件为真时，就执行相应的SQL语句。
#创建test存储过程，判断两个数的大小
delimiter $$
create procedure test.compar(in num1 int,in num2 int,out num3,char(6))
begin
    if num1>num2 then
        set num3='大于'
    elseif num1=num2 then
        set num3='等于'
    else 
        set num3='小于'
    end if;
    end $$
    delimiter;
case语句用来进行条件判断，它可以实现比if条件更复杂的条件判断
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，如果前面都不匹配，则执行[else statement_list]里的statement_list语句）
或者
case 
    when search_condition then statement_list
    [when search_condition then statement_list]...
    [else statement_list]
end case;
（search_condition为比较表达式，为真则执行then后的statement_list）
一个case语句经常可以充当一个if-then-else语句
#创建一个存储过程，针对不同的参数，返回不同的结果
delimiter $$
create procedure result(in str varchar(4),out sex varchar(4))
begin
    case str
    when 'M' then set sex='男';
    when 'F' then set sex='女';
    else set sex='无';
    end case;
end$$
delimiter
#第二种方式创建
delimiter $$
create procedure result(in str varchar(4),out sex varchar(4))
begin
    case 
        when str='M' then set sex='男';
        when str='F' then set sex='女';
    else set sex='无';
    end case;
end$$
delimiter

循环语句：while、repeat、loop语句
while语句是有条件控制的循环语句，当满足某条件时才执行循环体内的语句
[begin_label:]while search_condition do
    statement_list
end while[end_label]
search_condition判断条件，为真执行statement_list语句，不为真则结束循环
#判断值是否大于0，大于0执行-1操作，否则结束循环
delimiter$$
create procedure dowhile()
begin 
    declare v1 int default5;
    while v1>0 do
        set v1=v1-1;
    end while;
end $$
delimiter;
repeat语句是有条件的循环语句，当满足条件会跳出循环
[begin_label:]repeat
    statement_list
until search_condition
end repeat[end_label]
先执行statement_list循环语句，然后执行判断条件search_condition，当判断条件为真时，停止循环。
delimiter$$
create procedure repeat()
begin 
    declare v1 int default5;
    v1=v1-1;
    until v1<1;
    end repeat;
end $$
delimiter;
loop语句可以使某些语句重复执行，本身没有循环终止的条件，必须遇到leave语句才能停止循环。
[begin_label:]loop
    statement_list
end loop[end_label]
statement_list循环执行的语句，loop允许某些特定语句或语句群重复执行。
delimiter$$
create procedure doloop()
begin
    set @a=10;
    label:loop
        set @a=@a-1;
        if @a<0 then
            leave label;
            end if;
        end loop label;
end $$
delimiter;
变量a复制10，进入loop循环a=a-1操作，判断a是否小于0，是则使用leave语句跳出循环。
iterate语句是用来跳出循环的语句，是跳出当前循环，进入下次循环，
iterate label;  label参数是循环的标志
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 student;
end loop add_num;
该例循环执行count+1的操作，count值为100的结束循环，如果能被3整除，则跳出本次循环，不再执行下面的select语句。

游标
光标又称为游标，光表的使用包括声明光标，打开光标，使用光标和关闭光标。光标必须声明于处理程序之前，并且声明在变量和条件之后。
声明光标
declare cursor_name cursor from select_statement
cursor_name游标名称，游标名称使用与表名的使用规则相同。
select_statement是一个select语句，返回一行或多行数据。
注意：select子句中不能有into子句
declare student_cur cursor from 
    select * from student
    where sdept='计算机'
注意：游标只能在存储过程或存储函数中使用
打开游标 open cursor_name
读取游标 fetch cursor_name into var_name[,var_name]...
关闭游标 close cursor_name

调用存储过程
call sp_name([paramter[,...]]);sp_name为存储过程名，paramter为参数
创建存储过程
use student;
create procedure do_query()
select count(*) from student by sno;
调用 call do_query();
删除存储过程 drop procedure [if exists] sp_name
修改存储过程 alter procedure sp_name[characteristic...]

例创建名stu_count的存储函数，返回学生具体信息
delimiter $$
create function stu_count(emp_sno int) returns varchar(20)
begin
return(select * from student where sno=emp_sno);
end $$
delimiter;
调用stu_count
select stu_count(0268)
修改stu_count将读写权限修改为reads sql data并加上注释find name
alter function stu_count
    reads sql data
    comment 'find name'
删除stu_count
drop function stu_count