MySql——存储过程

1、什么是存储过程？
大多时候，我们都是对一个表或者多个表的单条语句进行操作，但在实际生活中，我们有可能对多个表的多条语句进行操作，这个时候就需要用到存储过程。
SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

2、存储过程的优点有哪些？
1）增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

2）标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

3）较快的执行速度：存储过程比批处理的执行速度快很多，因为存储过程是预编译的。

4）减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。

5）作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

3、为什么要用存储过程？
1）sql语句的执行需要经过编译，而大量向数据库发送相同语句，会造成不断编译，造成数据库的性能损耗[存储过程是预编译的]
2）大量向数据库发送sql语句将造成数据库和客户端之间的带宽损耗
3）节省编写复杂sql的过程
4）将重复性很高的一些操作，封装到一个存储过程中，简化SQL的调用
5）批量处理：SQL+循环，减少流量，也就是“跑批”
6）统一接口，确保数据的安全
相对于oracle数据库来说，MySQL的存储过程相对功能较弱，使用较少。

4、创建存储过程

/********************* 创建表 *****************************/
DROP TABLE if exists test;

CREATE TABLE test(
    id int(11) NULL
) ;
 
/********************** 最简单的一个存储过程 **********************/
drop procedure if exists sp;
CREATE PROCEDURE sp() select 1 ;

call sp();

最简单的一个存储过程执行结果

/********************* 带输入参数的存储过程 *******************/
drop procedure if exists sp1 ;

create procedure sp1(in p int)
comment 'insert into a int value'
begin
/* 定义一个整形变量 */
declare v1 int;
/* 将输入参数的值赋给变量 */
set v1 = p;

/* 执行插入操作 */
insert into test(id) values(v1);
end ;
/* 调用这个存储过程 */
call sp1(2) ;
/* 去数据库查看调用之后的结果 */
select * from test;

带输入参数的存储过程执行结果

/****************** 带输出参数的存储过程 ************************/
drop procedure if exists sp2 ;
create procedure sp2(out p int)
/*这里的DETERMINISTIC子句表示输入和输出的值都是确定的,不会再改变.目前mysql并没有实现该功能,因此加不加都是NOT DETERMINISTIC的*/
DETERMINISTIC
begin
select max(id) into p from test;
end;

/* 调用该存储过程，注意：输出参数必须是一个带@符号的变量 */
call sp2(@pv);
/* 查询刚刚在存储过程中使用到的变量 */

select @pv;

带输出参数的存储过程执行结果

/******************** 带输入和输出参数的存储过程 ***********************/
drop procedure if exists sp3 ;
create procedure sp3(in p1 int , out p2 int)
begin

if p1 = 1 then
/* 用@符号加变量名的方式定义一个变量，与declare类似 */
set @v = 10;
else
set @v = 20;
end if;

/* 语句体内可以执行多条sql，但必须以分号分隔 */

insert into test(id) values(@v);
select max(id) into p2 from test;

end;

/* 调用该存储过程，注意：输入参数是一个值，而输出参数则必须是一个带@符号的变量 */
call sp3(1,@ret);

select @ret;

带输入和输出参数的存储过程执行结果

/***************** 既做输入又做输出参数的存储过程 ***************************************/
drop procedure if exists sp4 ;
create procedure sp4(inout p4 int)
begin
if p4 = 4 then
set @pg = 400;
else
set @pg = 500;
end if; 

select @pg;

end;

call sp4(@pp);

/* 这里需要先设置一个已赋值的变量，然后再作为参数传入 */
set @pp = 4;
call sp4(@pp);

既做输入又做输出参数的存储过程的执行结果