mysql存储过程、存储函数及流程控制

                    存储过程
一、基本语法
create procedure sp_name([proc_parameter[,...]])
[characteristic...]routine_body 
begin
end
sp_name 表示存储过程的名字
proc_parameter 存储过程参数例表[IN OUT INOUT]三个部分组成
其中IN 表示传进来的参数
其中OUT 表示传出去的参数
其中INOUT 表示传进来但最终传回的参数
routine_body 参数是SQL代码的内容(类似于触发器的for each row)
begin..end标志SQL代码的开始和结束



二、关于IN OUT INPOUT的举例说明
IN 参数例子
delimiter &&
create procedure alvin1(
in p_in int) #设置传入的参数类型和变量
begin
select p_in;  #查询第一次传入的参数值
set p_in=2;   #内部重新赋值给p_in变量
select p_in;  #赋值后在此查询
end &&
delimiter ;
set @p_in=1;  #开始传入参数1
call alvin1(@p_in); #调用存储过程，查看和对比输出的值

OUT 参数例子
delimiter &&
create procedure alvin2(
out p_out int)
begin
select p_out;
set p_out=2;
select p_out;
end &&
delimiter ;
set @p_out=1; 传入的参数1之后
call alvin2(@p_out)调用了之后。是否和IN一样都显示出来了？还是无效？

INOUT 参数例子
delimiter &&
create procedure alvin3(
inout p_inout int)
begin
select p_inout;
set p_inout=2;
select p_inout;
end &&
delimiter ;

三、举例说明
需求：创建一个存储过程，要求(返回mysql的版本，用户 所在的数据库、用户名称)
delimiter &&
create procedure zy1(
out getversion varchar(30),
out userversion varchar(30),
out userdatabase varchar(30),
out userconnection int)
reads sql data
begin
select version() into getversion;
select user() into userversion;
select database() into userdatabase;
select connection_id() into userconnection;
end &&
delimiter ;

需求二、统计vendors vend_id的数量总共有多少条?
out zycount int
select count(*) into zycount from vendors

#====================================================
                              存储函数
create function sp_name([func_parameter[,.....]]) 
return type
[characteristic...]routine_body
begin...end
其中sp_name 存储函数的名称
func_parameter 函数参数列表
return type 指定返回的参数类型
routine_body SQL代码内容
begin..end标志SQL代码的开始和结束
注意：与存储过程不同，
1、参数只有输入类型
2、向调用方返回结果值

举例:
delimiter &&
create function alvin11(
bb_id int)
returns varchar(20)
begin
return(select vend_name from vendors where vend_id=bb_id);
end &&
delimiter ;

练习需求:编写一个存储函数，要求出入cust_id的的时候返回order_date这个字段的值
表名为:orders

#====================================================
                     流程控制
(1）存储过程if语句的使用方法
delimiter &&
create procedure zyif(
in aa int,out bb int)
begin
if aa>20 then
set bb=30;
elseif aa=20
then
set bb=20;
else
set bb=15;
end if;
end &&
delimiter ;

(2)存储过程case用法
delimiter &&
create procedure zy_case(in aa int,inout bb int)
begin
case
when aa=20 then set bb=20;
when aa>20 and aa<=50 then set bb=30;
when aa>51 then set bb=60;
else set bb=15;
end case;
end &&
delimiter ;

(3)while 循环使用，插入1万数据
创建一个表
create table zybb
(user_id int,
name varchar(10));
Query OK, 0 rows affected (0.10 sec)

delimiter &&
create procedure zy_while()
begin
declare count int default 0;
while count < 100000 do
insert into zybb(user_id,name)values(count,'aaa1');
set count=count + 1;
end while;
end &&
delimiter ;
call zy_while() 调用存储过程

#===========================================================
(1)调用存储过程
call+存储过程名称+参数
如：call alvin_name(@p_inout)

(2)查询结果
select @p_inout

(3)查询存储过程
show procedure status\G;

(4)查询某个存储过程详细
show create procedure alvin1\G;

(5)查询存储函数
show function status\G;

(6)查询某个详细的存储函数
show create function alvin10\G;

(7)删除存储过程
drop procedure alvin1;

(8)删除存储函数
drop function alvin1;