mysql存储过程和存储函数
1. 存储过程简介
操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，
而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
存储过程优点：
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).存储过程能过减少网络流量。当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，大大增加了网络流量并降低了网络负载。
(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
2、
存储过程procedure
变量:
全局变量:以@开头,如@var1,设置方法为set @var1 = 1000;select @var1 := 'hello,test!';
mysql> set @a=100;
mysql> select @a;
msyql>select count(*) into @var1 from employees;
mysql>select @var1;
系统变量:
系统变量使用@@引用
局部变量:
局部变量用在begin...end语句中,声明的是局部变量
delare var1 int;
set var1=100;
例:存储过程示例
mysql> create procedure sp1()
-> begin
-> declare var1 int;
-> declare var2 int default 0;
-> declare var3 varchar(20) charset utf8 default '湖南工业大学';
-> set var1=10000;
-> set var2=111;
-> select var1,var2,var2;
-> end
-> //
msyql> delimiter ;
mysql> call sp1();
1、使用存储过程传递参数
in 传入参数
out 传出参数
inout 传入传出参数
例:使用存储过程统计指定表的记录数,并且记录数能在外面使用
mysql> create procedure sp2(out num int)
-> begin
-> select count(*) into num from employees;
-> end
-> //
mysql>call sp2(@num)
mysql>select @num;
例:使用存储过程创建users表,包含id,name和sex三个字段
mysql> create procedure sp3(id int,name varchar(20),sex enum('man','woman'))
-> begin
-> create table if not exists users
-> (
-> id int primary key,
-> name varchar(20),
-> sex enum('man','woman')
-> )engine=innodb charset=utf8;
-> insert into users values(id,name,sex);
-> select * from users;
-> end
-> //
mysql> call sp3(1,'松江','man');
传入传出参数:
mysql> create procedure sp4(inout va int)
-> begin
-> set va := va + 10;
-> set va=va+10;
-> select va+10 into va; va是形参
-> end
-> //
mysql> set @var1=100;
mysql> call sp4(@var1); var1是实参
mysql> select @var1;
存值方法:
select .... into var|@var
select @var := 100
select emp_no,first_name into var1,var2(必须使用declare进行声明)
select emp_no,first_name into @var1,@var2
select @var1 := emp_no,@var2 := first_name from employees
声明变量
declare num1,num2 int,name varchar(20)
============================
第11章存储过程
一、基本语法 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 INOUT的举例说明
（1）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=5; #开始传入参数1
call alvin1(@p_in); #调用存储过程，查看和对比输出的值
（2）
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=5; 传入的参数1之后
call alvin2(@p_out);
调用了之后。是否和IN一样都显示出来了？还是无效？ 不和IN一样，不能传参
（3）
INOUT 参数例子
delimiter &&
create procedure alvin3(inout p_inout int)
begin
select p_inout;
set p_inout=2;
select p_inout;
end &&
delimiter ;
set @p_inout=5;
call alvin3(@p_inout);
和IN的结果一样
三、举例说明
需求一：创建一个存储过程，要求(返回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 ;
>call zy1(@a,@b,@c,@d);
>select @a,@b,@c,@d;
需求二、统计vendors vend_id的数量总共有多少条?
out zycount int
select count(*) into zycount from vendors
delimiter &&
create procedure vend_idnum(out vend_num int)
begin
select count(*) into vend_num from vendors;
end &&
delimiter ;
call vend_idnum(@idnum);
select @idnum;
#============== 流程控制 ======================
(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 ;
call zyif(50,@bb);
select @bb;
(2)存储过程case用法
语法： case 操作数
when 条件 then 执行语句;
when 条件 then 执行语句;
when 条件 then 执行语句;
else 执行语句
end case
例：
mysql> delimiter &&
mysql> 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 ;
mysql> call zy_case(60,@bb);
mysql> select @bb;
例：使用存储过程添加学生，当学生的id除3，余0时将学生插入s01班，余1时插入c02班，余2时插入c03班，
每个班的字段为id，name,age,sex四个字段。
mysql> create table c01(id int primary key,name varchar(10) not null,age int,sex enum('woman','man'));
mysql> create table c02 select from c01 where 1=2;//复制表结构。
mysql> delimiter //
mysql> create procedure sp6(in id int,in name varchar(10),in age int,in sex enum('woman','man'))
-> begin
-> declare num int;
-> set num=mod(id,3);
-> case num
-> when 0 then insert into c01 values(id,name,age,sex);
-> when 1 then insert into c02 values(id,name,age,sex);
-> when 2 then insert into c03 values(id,name,age,sex);
-> else
-> insert into c01 values(id,name,age,sex);
-> end case;
-> end
-> //
mysql> delimiter ;
mysql> call sp6(2,'林黛玉','20'，'woman');
select case age
when 20 then 语句;
when 30 then 语句;
...
else
语句;
end case from 表名;
select case
when age >= 20 && age<= 25 then 语句;
when age >25 && age<=60 then 语句;
...
else
语句;
end case from 表名;
(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(); 调用存储过程
(3)while 循环
leave 跳出循环
iterate 跳过本次循环
语法： while [expression] do
statements
end while
例：使用存储过程计算指定数字从1开始的和值
mysql> delimiter //
mysql> create procedure sp3(in snum int)
-> begin
-> declare sum,i int;
-> set sum=0,i=0;
-> sxjy:while i <= snum do
-> set sum := sum + i;
-> set i := i + 1;
-> end while;
-> select sum;
-> end
-> //
mysql> delimiter ;
练习：在test数据库下新建test1表,包括
id (整型，主键)，
num1 (整型，从1到200的随机数),
dt (datetime类型，为当前系统日期时间)
然后使用while循环插入200条记录。
mysql> create table test1(id int primary key auto_increment,num1 int,dt datetime);
msyql> delimiter //
mysql> create procedure test_while(in num int)
-> begin
-> declare num int default 1;
-> sxjy:while num <= 200 do
-> insert into test1(num1,dt) values(rand()*200,now());
-> set num := num + 1;
-> end while;
-> select * from test1;
-> truncate test1;
-> end
-> //
mysql> delimiter ;
(4)loop 循环(无限循环)
label:loop
statements
if 退出条件
leave label;
end if;
end loop
例：使用存储过程计算指定数字从1开始的和值
msyql> delimiter //
mysql> create procedure sp4(in snum int)
-> begin
-> declare sum,i int;
-> set sum=0,i=0;
-> if i<= snum then
-> set sum := sum + i;
-> set i := i+ 1;
-> else
-> leave sxjy;
-> end loop;
-> select sum;
-> end;
-> //
mysql> delimiter ;
(5)repeat until 循环
语法： repeat
循环体
until 条件
end repeat;
mysql> create procedure sp6( in snum int)
-> begin
-> declare sum,i int;
-> set sum=0,i=0;
-> repeat
-> set sum := sum + 1;
-> set i := i + 1;
-> until i > snum
-> end repeat;
-> select sum;
-> end
-> //
#===========================================================
(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;
#========= 存储函数 ==================
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 ;
select alvin11(1002);
练习:编写一个存储函数，要求出入cust_id的的时候返回order_date这个字段的值 表名为:orders
delimiter &&
create function alvin33(aa_id int)
returns datetime
begin
return(select order_date from orders where cust_id=aa_id);
end &&
delimiter ;
select alvin33(10003);
==================== 本章练习题目 =============
1、编写一个存储过程要求全自动填充 gongda 表，用户输入0，那么就填充数据为10万条。gongda结构包含
user_id user_name local_time字段。数据类型分别int varchar time
2、编写一个存储过程将gongda表前5000条 的local_time 用now()函数批量填上时间。
3、编写一个存储过程，用户输入大于1000时，返回值为1001 ，用户输入小于1000时或者大于等于800时返回值为999 其它的返回值为1；
4、编写一个存储函数，根据gongda表 用户输入的user_id号返回user_name和local_time。
5、vendors表，有3个参数，
user_id 和type info
如果type的值为1，将vend_name传给输出参数info 根据存储过程user_id查询vendors表中的记录，
如果type的值为2 将地址给输出参数info，根据存储过程user_id查询vendors表中的记录，
6、orders表中，有两个参数，user_id和type
根据user_id 用来查询orders表中的信息，
type类型的值如果为1，则返回订单日期，如果为2则返回订单编号。
如果为其它则返回错误。
============== 答案分析 =======
1.
mysql> qdelimiter &&
mysql> create procedure gongda_while()
-> begin
-> declare count int default 0;
-> while count<10000
-> do
-> insert into gongda(user_id,user_name,local_time) values(count,'zhang',now());
-> set count=count + 1;
-> end while;
-> update gongda set local_time=null where user_id<=5000;
-> end &&
mysql> delimiter ;
mysql> call gongda_while();
mysql> select * from gongda;
3.
mysql> delimiter &&
mysql> create procedure zll_if(in num int,out bb int)
-> begin
-> if num>1000 then set bb=1001;
-> elseif num<1000 and num>=800 then set bb=999;
-> else set bb=1;
-> end if;
-> end &&
-> delimiter ;
mysql> call zll_if(60,@bb);
mysql> select @bb;
4.
mysql> delimiter &&
mysql> create function zll(
-> bb_id int,return1 varchar(20),return2 varchar(20))
-> returns varchar(100)
-> begin
-> select user_name into return1 from gongda where user_id=bb_id;
-> select local_time into return2 from gongda where user_id=bb_id;
-> return concat(return1,":",return2);
-> end &&
mysql> delimiter ;
5.
mysql> delimiter &&
mysql> create procedure vendors_if(in type int,in user_id int,out info varchar(30))
-> begin
-> if type=1 then select vend_name into info from vendors where vend_id=user_id;
-> elseif type=2 then select vend_address into info from vendors where vend_id=user_id;
-> end if;
-> end &&
-> delimiter ;
mysql> call vendors_if(1,1002,@info);
mysql> select @info;
6.
mysql> delimiter &&
mysql> create procedure orders_if(in user_id int,in type int,out info varchar(20))
-> begin
-> if type=1 then select order_date into info from orders where cust_id=user_id;
-> elseif type=2 then select order_num into info from orders where cust_id=user_id;
-> end if;
-> end &&
-> delimiter ;
mysql> call orders_if(10003,1,@info);
mysql> select @info;
