存储过程测试流程--以MySQL为例
来源:互联网 发布:立讯精密 知乎 编辑:程序博客网 时间:2024/06/05 22:51
同C/C++/Java等语言编写的代码一样,用SQL语言编写的存储过程也需要进行充分的测试。本文以实际的MySQL存储过程为例,介绍存储过程测试的整个流程。
在本文中,需要被测试的存储过程如下:
drop procedure if exists pr_dealtestnum;delimiter //create procedure pr_dealtestnum( in p_boxnumber varchar(30), out p_result int, out p_outusertype int)pr_dealtestnum_label:begin declare p_boxnumcount int; declare p_usertype int; set p_boxnumcount = 0; set p_usertype = 0; set p_outusertype = 0; select count(*) into p_boxnumcount from tb_testnum where boxnumber=p_boxnumber; if p_boxnumcount > 0 then begin select usertype into p_usertype from tb_testnum where boxnumber=p_boxnumber; set p_outusertype = (p_usertype+1)/10*10; set p_result = 0; end; else begin set p_result = 1; end; end if; leave pr_dealtestnum_label;end;//delimiter ;select 'create procedure pr_dealtestnum ok';
在存储过程中使用到的表tb_testnum如下:
drop table if exists tb_testnum;create table tb_testnum( boxnumber varchar(30) not null, usertype int not null );create unique index idx1_tb_testnum on tb_testnum(boxnumber);
其中,usertype字段的值必须要大于1。
对存储过程进行测试大致遵循以下步骤:
第一步,按照存储过程的输入和输出参数设置正确的调用样式。
第二步,根据调用时的输入参数值来向相关的表中插入测试数据。
第三步,执行第一步中的存储过程调用语句,查看执行结果是否正确,并根据该结果来修改存储过程。
下面具体进行说明。
第一步
就本存储过程pr_dealtestnum而言,根据程序逻辑,我们可设置如下调用语句:
call pr_dealtestnum('2344273520',@1,@2);select @1,@2;call pr_dealtestnum('2344273521',@1,@2);select @1,@2;call pr_dealtestnum('2344273522',@1,@2);select @1,@2;call pr_dealtestnum('2344273523',@1,@2);select @1,@2;call pr_dealtestnum('2344273524',@1,@2);select @1,@2;call pr_dealtestnum('2344273525',@1,@2);select @1,@2;call pr_dealtestnum('2344273526',@1,@2);select @1,@2;call pr_dealtestnum('2344273527',@1,@2);select @1,@2;call pr_dealtestnum('2344273528',@1,@2);select @1,@2;call pr_dealtestnum('2344273529',@1,@2);select @1,@2;call pr_dealtestnum('2344273530',@1,@2);select @1,@2;call pr_dealtestnum('2344273531',@1,@2);select @1,@2;call pr_dealtestnum('2344273532',@1,@2);select @1,@2;call pr_dealtestnum('2344273533',@1,@2);select @1,@2;call pr_dealtestnum('2344273534',@1,@2);select @1,@2;call pr_dealtestnum('15696192523',@1,@2);select @1,@2;
大家还可以设置更多的调用语句,其目的是为了对存储过程进行更加充分的测试。
第二步
根据第一步设置的调用语句,兼顾程序逻辑,我们可执行如下语句向表tb_testnum中插入数据:
insert into tb_testnum(boxnumber,usertype) values('2344273520',1);insert into tb_testnum(boxnumber,usertype) values('2344273521',3);insert into tb_testnum(boxnumber,usertype) values('2344273522',4);insert into tb_testnum(boxnumber,usertype) values('2344273523',5);insert into tb_testnum(boxnumber,usertype) values('2344273524',7);insert into tb_testnum(boxnumber,usertype) values('2344273525',8);insert into tb_testnum(boxnumber,usertype) values('2344273526',9);insert into tb_testnum(boxnumber,usertype) values('2344273527',10);insert into tb_testnum(boxnumber,usertype) values('2344273528',11);insert into tb_testnum(boxnumber,usertype) values('2344273529',13);insert into tb_testnum(boxnumber,usertype) values('2344273530',14);insert into tb_testnum(boxnumber,usertype) values('2344273531',15);insert into tb_testnum(boxnumber,usertype) values('2344273532',26);insert into tb_testnum(boxnumber,usertype) values('2344273533',52);insert into tb_testnum(boxnumber,usertype) values('2344273534',87);
第三步
在向表tb_testnum中插入数据之后,我们开始逐条执行第一步中的存储过程调用语句,以验证存储过程代码逻辑的正确性。
首先执行“call pr_dealtestnum(‘2344273520’,@1,@2);select @1,@2;”,结果如下:
mysql> call pr_dealtestnum('2344273520',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 2 |+------+------+1 row in set (0.00 sec)
我们来分析执行结果是否是我们想要的。将入参“2344273520”带入存储过程中,首先是“select count(*) into p_boxnumcount from tb_testnum where boxnumber=’2344273520’;”,此时“boxnumcount”变量的值为1(因为之前执行过语句“insert into tb_testnum(boxnumber,usertype) values(‘2344273520’,1);”);接着,程序进入“if p_boxnumcount > 0 then”分支,执行“select usertype into p_usertype from tb_testnum where boxnumber=’2344273520’;”语句,“p_usertype”变量的值为1;然后,执行“set p_outusertype = (1+1)/10*10;”语句,即“p_outusertype”变量的值为“2/10*10”,进一步计算为“0*10”,最终结果为0;最后,执行“set p_result = 0;”语句,“p_result”变量的值为0。因此,最终两个输出参数的值都应该为0。
但是,实际的结果是,两个输出参数的值分别为0和2,是哪里出了问题呢?我们重点分析“(1+1)/10*10”的结果,将之在MySQL中单独执行,结果如下:
mysql> select (1+1)/10*10;+-------------+| (1+1)/10*10 |+-------------+| 2.0000 |+-------------+1 row in set (0.00 sec)mysql> select (1+1)/10;+----------+| (1+1)/10 |+----------+| 0.2000 |+----------+1 row in set (0.00 sec)
我们可以看到,“(1+1)/10”的结果并不是我们预想的0,而是0.2,看来,在MySQL中,两个整数相除并不是只取结果的整数部分,而是取了小数点后面的若干位。
那么,如果我们只想要整数部分,应该怎么办呢?此时,可以用floor()函数,即“floor((1+1)/10)”就是取了结果的整数部分,如下所示:
mysql> select floor((1+1)/10);+-----------------+| floor((1+1)/10) |+-----------------+| 0 |+-----------------+1 row in set (0.00 sec
现在,我们据此修改存储过程,修改之后如下所示:
drop procedure if exists pr_dealtestnum;delimiter //create procedure pr_dealtestnum( in p_boxnumber varchar(30), out p_result int, out p_outusertype int)pr_dealtestnum_label:begin declare p_boxnumcount int; declare p_usertype int; set p_boxnumcount = 0; set p_usertype = 0; set p_outusertype = 0; select count(*) into p_boxnumcount from tb_testnum where boxnumber=p_boxnumber; if p_boxnumcount > 0 then begin select usertype into p_usertype from tb_testnum where boxnumber=p_boxnumber; set p_outusertype = floor((p_usertype+1)/10)*10; set p_result = 0; end; else begin set p_result = 1; end; end if; leave pr_dealtestnum_label;end;//delimiter ;select 'create procedure pr_dealtestnum ok';
再次执行“call pr_dealtestnum(‘2344273520’,@1,@2);select @1,@2;”,结果如下:
mysql> call pr_dealtestnum('2344273520',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 0 |+------+------+1 row in set (0.00 sec)
此时的结果就是我们想要的。
我们可以继续执行在第一步中设置的其它存储过程调用语句,结果如下:
mysql> call pr_dealtestnum('2344273521',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 0 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273522',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 0 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273523',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 0 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273524',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 0 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273525',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 0 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273526',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 10 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273527',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 10 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273528',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 10 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273529',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 10 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273530',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 10 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273531',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 10 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273532',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 20 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273533',@1,@2);select @1,@2;Query OK, 1 row affected (0.00 sec)+------+------+| @1 | @2 |+------+------+| 0 | 50 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('2344273534',@1,@2);select @1,@2;Query OK, 1 row affected (0.01 sec)+------+------+| @1 | @2 |+------+------+| 0 | 80 |+------+------+1 row in set (0.00 sec)mysql> call pr_dealtestnum('15696192523',@1,@2);select @1,@2;Query OK, 1 row affected (0.01 sec)+------+------+| @1 | @2 |+------+------+| 1 | 0 |+------+------+1 row in set (0.00 sec)
可以看到,修改之后的存储过程的执行结果就是正确的了。最后一条调用语句“call pr_dealtestnum(‘15696192523’,@1,@2);select @1,@2;”中,因为输入参数中的“15696192523”对应的数据在tb_testnum表中不存在,因此程序进入了“else”分支,“p_result”变量的值就为1,而“p_outusertype”变量的值为0。
总结
存储过程的测试方法和用其它编程语言编写的程序的测试方法是一样的,都需要我们在充分理解程序逻辑的基础上构造完整的、多样化的测试用例,并在测试的过程中根据测试的结果来修改程序,以达到我们预期的结果,并最终满足用户的需求。
需要强调的是,在软件开发过程中,我们测试代码的时间,很可能会多余我们编写代码的时间,大家一定要耐着性子忍受测试代码过程中的“孤独感”。
- 存储过程测试流程--以MySQL为例
- 存储过程测试流程--以MySQL为例
- MDaemon以MySQL为存储方式的配置过程
- SQL的存储过程的理解与应用(以mysql为例)
- 如何用 MySQL 进行存储过程编写,以用户登陆为例,结构清晰,涵盖内容很多
- 【mysql 存储过程】存储过程之流程控制语句
- mySQL 视图、存储过程、流程控制实例
- Sybase数据库存储过程转化为Mysql数据库存储过程
- SQL Server 存储过程的建立和使用---以发邮件为例
- 尽量不要以sp_为前缀命名存储过程
- 尽量不要以sp_为前缀命名存储过程
- mysql的存储过程,存储函数,游标,流程控制
- MySQL存储过程:批量为用户授权
- 以测试为核心的软件开发过程
- 以测试为核心控制软件开发过程
- MySql存储过程及MySql常用流程控制语法
- MySql存储过程及MySql常用流程控制语法
- spring中配置log4j,并将log信息存储在数据库中(以mysql为例)
- 一个简单的基于JSTL的分页
- Android从零开始之重新认识Activity--生命周期
- 搭建安卓盒子开发调试环境
- 简要分析Uboot是如何启动内核
- Android EditText 自带清除按钮 (ViewGroup实现)
- 存储过程测试流程--以MySQL为例
- 遗传算法(Genetic Algorithm)
- iOS layout机制相关方法
- HashMap源码
- Structural Patterns Part 7/7: Proxy Pattern
- Android - android.view.InflateException: Binary XML file line #: Error inflating class fragment
- HashSet源码
- JDBC结合MySql完成增删改查
- 在PPT中FLASH的使用