Mysql 之存储过程篇

(附件中含此次内容的ppt，转载请注明作者出处！)

 

为什么要使用存储过程    why？！

优点：
1、预编译，已优化，效率较高。避免了SQL语句在网络传输然后再解释的低效率,缓解网络带宽。
2、存储过程可以重复使用，减少开发人员的工作量。
3、业务逻辑封装性好，修改方便。
4、安全。可设定只有某此用户才具有对指定存储过程的使用权；也不会有SQL语句注入问题存在。

什么时候用存储过程  when？！

存储过程不仅仅适用于大型项目，对于中小型项目，使用存储过程也是非常有必要的。
当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；
当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；
还有就是比较复杂的统计和汇总也要考虑。


怎么来使用存储过程  how？！

一.创建存储过程
1.基本语法：
CREATE PROCEDURE pd_name(参数[in 参数 数据类型],[out 参数 数据类型]，[inout 参数 数据类型])BEGIN.........
………End

例1.1:无参数的存储过程

create procedure pd_1()
begin
    declare i int;
    set i=0;
    while i<5 do
        insert into t_pro(name)values(i);
    set i = i+1;
    end while;
end

call pd_1();


例1.2：带有参数的存储过程

create procedure proc_name (in parameter  int)
begin
declare variable varchar(20);
if parameter=1 then
set variable='MySQL';
else
set variable='JAVA';
end if;
insert into t_pro (name) values (variable);
end;


例1.3：带有参数的存储过程

create procedure pd_3(in a int,in b int,out c int)
begin
   select a;
   select b;
   select c;
   set c = a+b;
end

set @c = 0;
call pd_3(1,1,@c)
select @c;

在mysql的存储过程中不能使用return返回，但是函数可以。


例1.4：在存储过程中调用另一个存储过程

create procedure pro_a(in a int,in b int,out c int)
begin
set c =  a+b;
end

create procedure pro_b()
begin
declare t int default 0;
call pro_a(1,2,t);
select t+4 as result;
end

注：存储过程中可以调用另一个存储过程或函数，但是不能在一个存储过程中删除另一个存储过程。


例1.5：在存储过程中调用另外一个函数

create function dd(a int,b int) returns int
begin
return a+b;
end

select dd(2,4);

create procedure pp()
begin
declare a int default 0;
--set a =  dd(2,3);
select dd(2,4) into a;
select a;
end


存储过程和函数的区别：

1、函数只能通过return语句返回单个值或者表对象,而存储过程不允许执行return，但是通过out参数返回多个值

2、函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。

3、存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强

4、函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等等．而存储过程的限制相对就比较少

存储过程中的游标使用：

create procedure pro_cursor()
begin
  declare a,b,c int;
  declare cur cursor for select id from t_pro;--声明一个游标
  declare continue handler for not found set b = 1;--声明游标事件
  open cur;--打开游标
  set b = 0;
  set c = 0;
  while b = 0 do
  fetch cur into a;--将当前游标值赋给变量
  if b = 0 then
set c = c + a;
  end if;
  end while;
  close cur;--关闭游标
  select c;
  end