MySQL之存储过程和函数

1.存储过程和函数概述
存储过程和函数是 事先经过编译并存储在数据库中的一段SQL语句的集合。
优点：减少应用程序和数据库之间的交互次数（需要网络传输），提高操作效率
函数：是一个有返回值的过程
过程：是一个没有返回值的函数。
2.创建存储过程
create procedure procedure_name([proc_parameter[,]])
begin
—SQL语句
end;
示例：

$，声明SQL语句的分隔符，替换;的作用,告诉MySQL解释器，该段命令是否已经结束了
delimiter $

3.调用存储过程
call procedure_name();

4.查看存储过程
–查询db_name数据库中所有的存储过程
select name from mysql.proc where db=‘db_name’

–查询存储过程的状态信息
show procedure status
show procedure status\G(navicat不支持\G)

–查询存储过程的定义
show create procedure procedure_name\G;

5.删除存储过程
drop procedure procedure_name;

6.语法
存储过程是一组封装SQL语句的集合，是可以进行编程的，可以使用变量，表达式，控制结构来完成比较复杂的功能。
1）变量
-declare
定义一个局部变量，该变量的范围只能在begin…end块中
declare var_name[…] type [DEFAULT value]

default指定默认值
–SET赋值
SET var_name=expr[,var_name=expr]…

通过select…into方式进行赋值操作

2）if条件判断
语法结构：
if search_condition then statement_list
[else if search_condition then statement_list]…
[else statement_list]
end if;

3)传递参数

create procedure name([in/out/inout] 参数 参数类型)
in输入
out输出
inout既可以作输入，也可以作输出

输入：
示例：
create procedure test6(in h int)
begin
declare des varchar(50) default ‘’;
if h>=180 then set des=‘身材高挑’;
elseif h>=170 then set des=‘身材标准’;
else set des=‘一般身材’;
end if;
select concat(‘身高’,h,‘对应身材是’,des);
end$

out:
create procedure test7(in h int,out des varchar(50))
begin
if h>=180 then set des=‘身材高挑’;
elseif h>=170 then set des=‘身材标准’;
else set des=‘一般身材’;
end if;
end$
@代表用户会话变量，当前会话关闭之后，所有这个变量全部释放
@@代表系统变量
4）case结构
方式一：
case case_value
when when_value then statement_list
[when when_value then statement_list]…
[else statement_list]
end case;

 	方式二：
 	 case 
 	when sercharch_condition(条件表达式) then statement_list
 	[when sercharch_condition  then statement_list]...
 	[else statement_list]
 	end case;

–给定一个月份，计算出所在的季度
create procedure test8(mon int)
begin
declare result varchar(10);
case
when mon>=1 and mon<=2 then
set result=‘第一季度’;
when mon>=3 and mon<=6 then
set result=‘第二季度’;
else
set result=‘其他’;
end case;
select concat(‘输入’,mon,‘计算结果’,result);
end$
5)while循环
语法结构
while search_condition do
statement_list
end while;
示例：计算从1加到n的值
delimiter $
create procedure test9(n int)
begin
declare i int default 1;
declare sum int default 0;
while i<=n do
set sum=sum+i;
set i=i+1;
end while;
select sum;
end$

6）repeat语句
满足条件则退出循环
repeat
statement_list
until searcher_condition(这里不需要分号，有分号会报错)
end repeat;

create procedure test10(n int)
begin
declare i int default 1;
declare sum int default 0;
repeat
set sum=sum+i;
set i=i+1;
until i=n+1
end repeat;
select sum;
end$

7）loop语句
退出循环的条件需要使用其他的语句定义，和leave语句结合使用
[begin_label:] LOOP
stetement_list
END LOOP [end_label]

create procedure test11(n int)
begin
declare sum int default 0;
c:loop
set sum=sum+n;
set n=n-1;
if n<=0 then
leave c;
end if;
end loop c;
select sum;
end$

8）leave语句
退出语句
if n<=0 then
leave c;
end if;
9）游标/光标
游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理
光标的使用包括：光标的声明,open,fetch,close
光标的声明
declare cursor_name cursor for select_statement;
open光标
open cursor_name;
fetch光标(相当于指针)
fetch cursor_name into var_name [,var_name]…

close光标
close sursor_name;

create procedure test12()
begin
declare e_id int(11);
declare e_name varchar(20);
declare e_age int(11);
declare e_salary int;
declare emp_result cursor for select * from emp;
open emp_result;
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat(‘id=’,e_id,’ name=’,e_name,’ age=’,e_age,’ salary=’,e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat(‘id=’,e_id,’ name=’,e_name,’ age=’,e_age,’ salary=’,e_salary);
close emp_result;
end$
循环并解决容错问题（遍历完不停会报错）
控制边界变量退出

create procedure test13()
begin
declare e_id int(11);
declare e_name varchar(20);
declare e_age int(11);
declare e_salary int;
declare i int default 1;
declare emp_result cursor for select * from emp;
declare exit handler for not found set i=0;
open emp_result;
repeat
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat(‘id=’,e_id,’ name=’,e_name,’ age=’,e_age,’ salary=’,e_salary);
until i=0
end repeat;
close emp_result;
end$

7.存储函数
语法结构
create function function_name([param type…])
returns types
begin
…
end;

例：获取满足条件的总记录数

create function fun1(id int)
returns int
begin
declare cnum int;
select count(*) into cnum from book where B_num=id;
return cnum;
end$

调用：select fun1(1);