MySQL存储过程的认识

一、存储过程概述

1、什么是存储过程
存储过程个人理解可以概括为一下几点：

• 存储过程是一组SQL语句集合，可以主动调用并给予参数完成程序中的单独功能。
• 存储过程是一种可进行编译的函数，在某种程度上是类似于OOP面向对象中方法。
• 存储过程可以视为是为了优化SQL批量处理而创建的。

2、为什么要用存储过程
很多应用程序中都会用到存储过程，个人对存储过程的优点可以概括为以下几点：

• 具有可复用性，程序中可以多次调用，可以省去很多的麻烦。不必多写SQL语句，只需要修改存储过程，对程序源代码毫无影响。

• 具有比较快的执行速度，存储过程都是先编译后存在数据库中（也就是预编译），之后用户可以指定过程名给定参数进行call调用，所以相比较大量的SQL语句进行批量处理，每次都要编译后执行，相对速度就慢很多。

• 具有较强的灵活性：在存储过程中不光只是简单的增删改查，还可以进行流程控制、参数声明等，这样就可以完成较为复杂的SQL运算要求。

• 具有一定安全性，用户在调用存储过程的过程中，只是在网络中显示调用存储过程的，而不会过多显示底层SQL语句，这样就可以对权限进行控制，保护了数据。

二、创建存储过程

SQL语句的编写不同的数据库写法会有许多不同，这里就以MySQL为例。
1、存储过程的常用语法

CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

（1）创建存储过程：

    drop procedure if exists MyPorcedure ;      create procedure MyPorcedure ()     begin     select* from shops_info;     end;  

存储过程体格式为开始与结束标志使用begin与end，它们之间是SQL语句或流程控制语句。
（2）调用存储过程

call MyPorcedure();

调用存储过程用到关键字call
（3）存储过程的参数
存储过程中有三种类型的的参数，分别为：输入、输出、输入输出。即IN,OUT,INOUT。

• IN：参数的值在调用存储过程时被指定，在存储过程中修改后的值不能被返回。但是内部的修改对调用者来说是不可见的，相当于函数中的内部局部变量。
• OUT：在存储过程中可以被修改，可以被返回，无论调用者是否传值给存储过程,在存储过程中默认值为NULL。相当于函数中return parm，给调用者使用的
• INOUT：调用时候被指定，在存储过程中可以被改变，也可以返回。

IN类型：

调用存储过程时候被指定数值，内部被修改后无法返回被修改的值，即指定的值是几，返回 的就是几。

delimiter //create procedure pr_test(in id int)beginif (id is not null)then set id = id + 1;end if;select id as in_id;end;//delimiter ;/*-----调用时候指定值-----*/set @id = 10;/*-----存储过程中内部修改了id的值，其运行的结果in_id = 11-----*/call pr_test(@id);/*-----id的值并未返回给调用者，其运行的结果in_id = 10-----*/select @id as out_id;

OUT类型：

调用存储过程时候无论是被指定还是未指定数值，内部被修改后会返回被修改的值，即指定的值是几，返回 的不是指定的值，而是内部被修改后更新的值。

delimiter //create procedure pr_test(out id int)beginif(id is not null)then set id = id + 1;end if;select id as in_id;end;//delimiter ;*-----调用时候指定id = 10，但是OUT类型默认的都是NULL-----*/set @id = 10;/*-----运行的结果in_id = 0-----*/call pr_test(@id);/*-----运行的结果in_id = 0：NULL内部被改为0了-----*/select @id as out_id;

存储内部调用之后id值被修改，内部in_id与外部out_id是一样的

INOUT类型：

值在存储过程内部修改后，可以被返回给调用者

delimiter //create procedure pr_test(inout id int)beginif(id is not null)then set id = id + 1;else set id = 0;end if;select id as in_id;end;//delimiter ;set @id = 10;/*-----运行的结果in_id = 11-----*/call pr_test(@id);/*-----运行的结果in_id = 11-----*/select @id as out_id;

（4）分隔符DELIMITER
MySQL默以“；”分号为分割号，如果不对存储过程使用分割号的话，编译过程很可能会出错，会以为存储过程是SQL语句而进行处理，所以首先得声明分隔符号，一般用”//”或者“;;”

/*-----改默认的;号为//-----*/DELIMITER //  CREATE PROCEDURE Myproc(OUT s int)    BEGIN      ....    END    ///*-----改回默认的;分割号-----*/DELIMITER ;

（5）存储过程中的变量
存储过程中的变量分为系统变量与用户变量。

• 定义变量：用declare

DECLARE var_name[varname]...date_type[DEFAULT VALUE];

定义用户变量MYPARAM 类型为INT，默认值为100。

DECLARE MYPARAM INT DEFAULT 100;

• 为变量赋值：用set赋值可以修改默认值。

SET var_name=expr[var_name=expr]...

expr可以是具体的数值或者字符串，也可以是表达式。
声明两个变量，分别为var1、var2和var3。

DECLARE var1,var2,var3 INT;SET var1=10,var2=20;SET var3=var1+var2;

（6）存储过程中常用到的流程控制语法

• IF-THEN-ELSE语句:

语法格式：

if...then...else...end if;

示例：

DROP PROCEDURE IF EXISTS Mypro;DELIMITER //CREATE PROCEDURE Mypro(IN parameter int)  BEGIN    DECLARE var int;    SET var=parameter+1;    IF var=0 THEN      INSERT INTO t VALUES (17);    END IF ;    IF parameter=0 THEN      UPDATE t SET s1=s1+1;    ELSE      UPDATE t SET s1=s1+2;    END IF ;  END ;  //DELIMITER ;

• CASE-WHEN-THEN-ELSE语句

语法格式：

case param   when value1 then         ...   when value2 then         ...   when value3 then         ...   else             ...   end case;  

实例：

DELIMITER //  CREATE PROCEDURE Mypro (IN parameter INT)    BEGIN      DECLARE var INT;      SET var=parameter+1;      CASE var        WHEN 0 THEN          INSERT INTO t VALUES (17);        WHEN 1 THEN          INSERT INTO t VALUES (18);        ELSE          INSERT INTO t VALUES (19);      END CASE ;    END ;  //DELIMITER ;

• WHILE-DO…END-WHILE语句：
  语法格式：

while 条件表达式 do    .....end while;

实例：

DELIMITER //  CREATE PROCEDURE Myproc()    BEGIN      DECLARE var INT;      SET var=0;      WHILE var<6 DO        INSERT INTO t VALUES (var);        SET var=var+1;      END WHILE ;    END;  //DELIMITER ;

2、关于存储过程相关语法

（1）删除存储过程
一般在我们创建存储过程之前，都要判断是否存在存储过程，如果存在则删除存储过程。

drop procedure if exists Myprocedure;

（2）显示数据库中存储过程的信息
显示存储过程中所存储过程的基本信息：

show procedure status

显示某一个创建MySQL存储过程的详细信息：

show create procedure Myprocedure

三、存储过程总结

1、存储过程的缺点

• 移植扩展难度很大，不同的数据库、不同的版本差别很大，不太可能移植，需要重写编写。
• 不易管理，当把很多的业务SQL语句流程控制全部写在存储体中，后期维护管理难度很大，没有分层思想。
• 不适用数据库的切割，一旦切割后，存储过程完全不知道数据存储在哪个数据库中。
• 存储过程调试很吃力，无法像Java/C语言等那样。

2、存储过程的应用场景

• 适用于复杂的数据处理、如报表处理、多条件多表联合查询，并做分页处理等情况。
• 应用程序需要重复固定的对数据库操作时并且对性能有一定的要求时。