vlambda博客
学习文章列表

测试为什么要学习存储过程，存储过程对于测试工作中的帮助是什么？当我们需要在数据库中添加、修改、删除、查询大量数据时（主要是添加数据），有两种实现方式。一个是通过接口的方式，也就是通过书写接口脚本或者使用接口测试工具实现对数据库操作，我之前一直采用这种方式，这种方式有一个很好的优点，那就是不考虑内部接口调用各种微服务接口的过程，或者说各种表连接方式的过程，比如：一对一，一对多，多对多，或者说是链式调用等，但是有一个缺点，那就是当插入大批量的数据时，速度非常慢，以及当接口性能不达标时，会出现超时等。

第二种方式是就是数据库存储过程，这种方式是通过预编译的SQL语句集合，通过数据库自带的存储过程的语法，在数据库中添加数据，因为是预编译的SQL，它的性能在没有复杂的事务处理以及复杂的运算的时候，添加数据会非常的快，添加几十万或者上百万的数据时候，使用存储过程是非常方便高效的，但是也有其缺点，需要考虑到各种表之间的关系，需要拿到数据库的设计结构。

--阿木木

 

一、什么是存储过程

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

简单来说就是一个为了完成特定功能的预编译后的SQL语句集合，创建该存储过程时候已经完成了编译，调用时候只需要传递参数即可，不需要再次编译，运行速度快。

 

二、为什么使用存储过程

1、存储过程只在创建时进行编译，一般的SQL每执行一次就需要编译一次，存储过程是一次编译，永久使用，可以提高数据库执行速度

2、对数据库进行复杂操作时候，对多个表进行增删改查时候，可以将复杂操作使用存储过程封装与数据库提供的事务处理结合一起使用，使用存储过程只需要连接一次数据库，使用普通的SQL需要多次连接数据库。

3、存储过程可以重复使用

4、安全性高，可设定只有某个用户才能使用存储过程，可以具体到表中的某个字段的修改等。

三、什么情况下不使用存储过程

1、存储过程每个数据库的写法不一样，可移植性差

2、对于简单的SQL没必要写存储过程

3、存储过程不一定会减少网络传输

4、存储过程安全对于单用户没有影响

5、存储过程的开发需要定义好标准

6、大并发量情况，不能写过多运算或者业务相关的存储过程，需要再前端简化业务逻辑。

四、存储过程编写

1、关键字说明：

DELIMITER //声明mysql中的结束符，mysql的默认结束符合是分号（;），在编写存储过程中，会因为分号的原因，导致存储过程的编写提前结束，DELIMITER后面所跟的符号可以自定义，默认大家都用//或者$$。

CREATE PROCEDURE demo_in_parameter(IN p_in int)  声明存储过程，PROCEDURE表示存储过程，demo_in_parameter表示存储过程名（自定义），IN p_in int表示参数类型，IN表示输入参数，p_in表示参数名，int表示参数类型，后面详细说明。

BEGIN .... END   表示存储过程开始和结束符号，中间的省略号表示我们需要输入的SQL语句集合，可以多次嵌套。

SET @p_in=1  表示变量赋值，使用set关键字设置p_in这个外部变量的值为1

DECLARE l_int int unsigned default 4000000 变量定义，使用DECLARE定义变量，后面跟参数名、参数类型、有无符号、默认值。

CALL test_proc(@num)CALL表示调用存储过程，test_proc表示存储过程的名字，@num，表示用户变量

SELECT @numSELECT表示查询变量值

 

2、存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT形式如：

CREATE  PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

第一种：IN 参数表示，读取外部变量值，且有效范围仅限存储过程，存储过程的SQL语句集执行完毕之后，不会对外部变量产生影响。除非在存储过程中该变量加了@符号，表示为用户变量，上面的例子是加了@符号的变量它的输出就会改变，下图详细说明：

 

 

第二种：OUT 输出参数：out类型的参数不读取外部变量值，在存储过程执行结束后，保留新值，下图说明：

 

第三种：INOUT参数：INOUT参数可以读取外部变量值，在存储过程执行完毕后保留新值，传出的值只能是变量。

 

还有一种特殊的不指定参数类型的例子，如果在创建存储过程时没有指定参数类型，则需在调用时候指定参数值。

 

3、变量说明

变量定义：局部变量声明一定要放在存储过程体的开始，mysql中使用DECLARE进行变量定义

DECLARE  variable_name [,variable_name...] datatype [DEFAULT value];

datatype 为 MySQL 的数据类型，如: int, float, date,varchar(length)

DECLARE l_int int unsigned default 4000000;

DECLARE l_numeric number(8,2) DEFAULT 9.95;

DECLARE l_date date DEFAULT '1999-12-31';

DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';

DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

变量赋值：SET 变量名 = 表达式值，变量赋值可以在不同的存储过程中继承

用户变量：在MySQL客户端使用用户变量，使用@标记

4、注释说明

--：该风格一般用于单行注释。

/**/：该风格一般用于多行注释。

5、MySQL存储过程的控制语句

变量的作用域：内部的变量在其作用域范围内享有更高的优先权，当执行到end，内部变量显示，此时已经在其作用域外，变量不可见。因为在存储过程外再也不能找到这个声明的变量，可以通过out参数，或者将其值指派给会话变量用户变量来保存其值。

（1）条件控制语句

if-then-end if 语句

 

if-then-else-end if语句

 

case-when-then-else-end case语句

 

（2）循环控制语句

while -do-end while:在执行前检查

 

repeat-util-end repeat:在执行后检查

 

loop-end loop

loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。LABLES标号可以用在 begin repeat while 或者 loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

 

LABLES 标号：

标号可以用在 begin repeat while 或者 loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

 

（3）ITERATE迭代控制语句

ITERATE 通过引用复合语句的标号,来从新开始复合语句,跳出本次循环，开始下一次循环。只能出现在循环LOOP、REPEAT和WHILE中。

 

五、使用场景小demo演示

1、批量添加数据

 

2、批量删除数据