存储过程

        存储过程是事先经过编译并存储在数据库中的SQL语句的集合。其可以减少数据在数据库和服务器之间的传输，提高效率。就是对mysql语句的封装。特点：（1）封装、复用；（2）可以接收参数，返回数据；（3）减少网络交互，效率提升。

 一、基本语法

        创建存储过程：

                create procedure 存储过程名称({参数列表})

        begin

                sql语句

        end;

        调用：

                call 存储过程；

        查看：

                show create procedure 存储过程名称；

        删除：

                drop procedure if exists 存储过程；

二、变量

2.1 系统变量

        由mysql服务器提供，分为全局（global),会话(session)。全局变量在任何地方有效，会话变量只在会话中生效。

        查看系统变量：

查看系统变量

show global variables	查看所有系统变量
show global variables like	使用模糊查询搜索系统变量
select @@global 系统变量名	查看具体的变量

        设置系统变量：

                set global 系统变量名=值；

// 例如设置会话级别的自动提交关闭:set session antocommit=0;

Notations:重启服务器后所有的变量都会恢复原值，要永久改变需要修改mysql的配置文件。

2.2 用户自定义变量

        用户变量不需要提前声明，加上@符号就可以。起作用域是当前会话。

        赋值：

                set @变量名=....   或者 set @变量名:= ....

                select @变量名:=........  

        指定查询结果为变量赋值：

                select 查询字段 into @变量名 from 表名；

        查看： select @变量名1，@变量名2，.......

// 查看的变量即使没定义结果也是NULL

2.3 局部变量

       根据需要定义在局部生效的变量。访问前，需要DECLAR声明。其范围是在procedure其内声明的BEGIN...END块。声明：DECLAR 变量名 变量类型；赋值可以使用类似自定义变量的方法，只是局部变量前不用加@。

三、判断语句

3.1 if 语句

        语法： 

                if 条件1 then .....

                elseif 语句2 then....

                else ....

                end if ;

        举例如下:

create procedure p1()
begin
	declare score int default 58;
	declare res varchar(20);
	
	if score>60 then 
		set res:='及格';
	else
		set res:='不及格';
	end if;
		select res;
end;
call p1;

3.2 case语句

        语法1：

        case value1

                when value2 then....

                when value3 then...

                else ...

        end case;

        语法2：

         case

                when 条件1 then ....

                when 条件2 then.....

                else.....

          end case;

四、参数

存储参数类型

In	输入参数
out	输出参数
inout	输入输出参数

        使用参数修改3.1中的例子:   

create procedure p2(in score int ,out res varchar(10))
begin
	if score>60 then 
		set res:='及格';
	else
		set res:='不及格';
	end if;
		select res;
end;
call p2(68,@res);
select @res;

        需要一个用户变量来接收OUT的参数。使用inout参数的例子,将两百分制折算为百分制。

create procedure p3(inout score int)
begin
	set score:=score/2;
end;
set @score=120;
call p3(@score);
select @score;

五、循环语句       

5.1 while循环

        语法：

        while 条件 do

                .....

        end while;

        案例： 传入n，计算从一累加到n的值

create procedure p4(in n INT)
begin
	declare total int default 0;
	
	while n>0 do
		set total:=total+n;
		set n:=n-1;
	end while;
	select total;
end;
call p4(100);

5.2 repeat语句

        语法：

        repeat 

                语句

        until 条件

        end repeat;

        repeat和while的执行顺序有区别，一个先条件后语句，一个先语句后条件。

        上一节中的例子可以使用：

    repeat 
		set total:=total+n;
		set n:=n-1;
	until n<=0
	end repeat;

5.3 loop语句

        loop配合两个关键词使用：（1）leave:中断循环强制跳出；（2）iterate:提前结束循环进入下一次循环。

        语法：

        标识符：loop

                语句；

         end loop 标识符;

        定义存储过程，计算从1到n间的偶数的和

create procedure p6(in n INT)
begin
	declare total int default 0;
	
	sum:loop
		if n<=0 then
			leave sum;
		end if;
		
		if n%2=1 then
			set n:=n-1;
			iterate sum;
		end if;
        --在奇数跳到下一次循环时，同样要记得对n-1.
		
		set total:=total+n;
		set n:=n-1;
	end loop sum;
	
	select total;
end;
call p6(100);

六、其它语句

6.1 游标cursor

        当使用变量接收查询结果时，只能存储单个的数据，而不能接收表。游标就是用来存储查询结果集的数据类型，在存储过程或函数中可以使用游标对结果集进行循环处理。使用如下：

（1）声明：declare 游标名cursor for 查询语句

（2）打开游标：open cursor

(3) 获取游标：fetch 游标名into 变量；

（4） close 游标

6.2 条件处理程序handler

        handler主要用于解决流程控制过程中遇到相应问题的具体解决步骤。

        语法：declare handler_action handler for condition_value 要执行的操作

handler_action

CONTINUE	继续当前程序
EXIT	结束当前程

condition_value

SQLSTATE	状态码
SQLWARNING	所有01开头的SQLSTATE的简写
NOT FOUND	所有02开头的SQLSTATE的简写
SQLEXCEPTION	除SQLWARNING和NOT DOUND外SQLSTATE的简写

        使用cursor和handler从已有表中筛选数据获得一张新表：

create procedure p2 (in s int)
begin
	declare s_sid varchar(10);
	declare s_score int;
	declare s_cursor cursor for select sid,score from sc where cid='01' and score >=s;
	declare exit handler for SQLSTATE '02000' close s_cursor; 
    --声明游标和条件处理程序。SQLSTATE ‘02000’ 也可以写成NOT FOUND。当出现SQLSTATE ‘02000’时退出程序并关闭游标
	
	create table qualified (
	id int primary key auto_increment,
	sid varchar(10),
	score int);
    --创建新表
	
	open s_cursor;
	while true do
		fetch s_cursor into s_sid,s_score;
		insert into qualified (sid,score) values(s_sid,s_score);
    --将游标存入常量，再将常量插入到新表中
	end while;
	close s_cursor;
    --关闭游标
	
end;

call p2(60);
	
	

Notations:局部变量必须在游标之前声明

七、存储函数

        存储函数是有返回值的存储过程，且参数列表只能有IN。语法：

        create function 函数名(参数）

        returns type  函数特征

        --返回值类型

        begin

                语句

                return .....;

        end;

        函数特征有：（1）deterministic:输入相同，返回结果必相同；（2）NO SQL：没有SQL语句；（3）READS SQL DATA：包含读数据的语句，不含写数据的

Notations:参数不写IN也会默认是IN；