【MySQL】存储过程

概念

存储过程和函数可以理解为一段sql的集合，他们被事先编译好并且存储在数据库中。在pascal语言中，是有过程和函数的区别的，过程可以理解为没有返回值得函数。不过在C语言总，则没有过程这个概念，统一为函数

创建存储过程

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

begin

存储过程体

end

create procedure user_data()

begin

select * from table_a;

end;

调用存储过程

call user_data();

执行结果：

这就是一个简单的存储过程

参数类型

在上边创建存储过程中，我们知道在user_data()里边可以携带参数的

解释参数

1.in 表示用来输入

2.out 表示用来输出

3.inout 可以用来输入也可以用来输出

携带参数的存储过程

先使用命令删除我们之前创建的user_data的存储过程

drop procedure user_data;

创建在带参数的存储过程

create procedure user_data(in id int)

begin

select * from table_a where id = id;

end

存储过程中的常用数据类型

1.数值类型

int float double decimal(用于钱上)

2.日期类型

timestamp data year

3.字符串

char varchar text

游标的使用

游标也有的资料上称为光标。

我们可以在存储过程中使用游标来对结果集进行循环的处理。

游标的使用步骤基本分为：声明、打开、取值、关闭。

语法：

DECLARE test_cursor CURSOR FOR 结果集; //声明游标

OPEN test_cursor; //打开游标

CLOSE test_cursor; //关闭游标

DECLARE CONTINUE HANDLER FOR NOT FOUND //结果集查询不到数据自动跳出

总结：

游标的声明的语法： declare 游标名称 cursor for 查询语句;

打开光标的语法： open 游标名称;

获取游标数据： fetch 游标名称 into 变量名 1 [,变量名 2 …]

关闭游标的语法： close 游标名称;

游标的基本使用须知：对某个表按照循环的处理，判断循环结束 的条件是捕获 not found 的条件，当 fetch 光标找不到下一条记录的 时候，就会关闭光标然后退出过程。

可能有过 Pascal 编程经验的朋友们都会知道，声明的顺序也是很 重要的，在 SQL 中，我们使用 declare 定义的顺序是：变量、条件、 游标、应用程序

操作 查询出来的数据会放置于临时表中，然后再通过游标去读取数据。

案例

create procedure user_data(out count int)
begin
  declare user_id int;
  -- 游标标识
  declare blag int default 1;
  -- 游标
  declare user_cursor cursor for select data.user_id from mac_user as data;
  -- 处理not fount的异常
  declare continue handler for not found set blag = 0;
  set count = 0;
  -- 打开游标
  open user_cursor;
  -- 执行循环
  read_loop:loop
    -- 获取游标中的值
    fetch user_cursor into user_id;
    -- 检测循环是否
    if blag = 0 then
      -- 跳出循环
      leave read_loop;
    end if;
--    if user_id = 1 then
      set count = count + user_id;
--    end if;
  end loop read_loop;
  -- 关闭游标
  close user_cursor; 
  select count;
end;
call user_data(@count);  

命令查看存储过程

show procedure status;

show create procedure kaka;

存储过程的优点

1.第一点优势就是执行速度快，因为我们的每个sql语句执行都需要经过编译，然后在运行，但是存储过程都是直接编译好了之后直接运行即可

2.第二点优势就是减少网络流量，我们传输一个存储过程比我们传输大量的sql语句的开销要小的多

3.第三点就是提高系统安全性，因为存储过程可以使用权限控制，而且参数化的存储过程可以有效的放置slq注入攻击，保证了其安全性

4.第四点就是耦合性降低，当我们表结构发生变化之后，我们可以修改相应的存储过程，我们的应用程序在一定程度上需要改动的地方就比较少了

5.第五点就是重用性强。因为我们在写好一个存储过程之后，再次调用只需要一个名称即可，也就是一次编写，随处调用，而且使用存储过程也可以让程序的模块化加强

存储过程的缺点

1.移植性差。因为存储过程是和数据库绑定的，如果我们更换数据库之类的操作，可能很多地方需要改动。

2.修改不方便。因为对于存储过程而言，我们并不能特别有效的调试，他的一些bug可能发现的会晚，增加了应用的危险性。

3.优势不明显赘余功能。对于小型的web应用来说，如果我们使用语句缓存，发现编译sql的开销并不大，但是使用在存储过程缺需要检查权限一类的开销，这些赘余功能也会在一定程度上拖累性能