就像用C/C++/Java等语言编写的代码一样，用SQL语言编写的存储过程也需要进行全面测试。本文以实际的MySQL存储过程为例，介绍存储过程测试的全过程。本文中要测试的存储过程如下：dropprocedureifexistspr_dealtestnum;delimiter//createprocedurepr_dealtestnum(inp_boxnumbervarchar(30),outp_resultint,outp_outusertypeint)pr_dealtestnum_label:begindeclarep_boxnumcounttypeint;declarep_usertypeint;setp_boxnumsnumcount=0;setp_usertype=numwheretestboxfromcountp)intop(*setboxnumbercountp)=p_boxnumber;ifp_boxnumcount>0thenbeginselectusertypeintop_usertypefromtb_testnumwhereboxnumber=p_boxnumber;setp_outusertype=(p_usertype+1)/10*10;setp_result=0;end;elsebeginsetp_result=1;end;endif;leavepr_dealtestnum_label;end;//delimiter;select'createprocedurepr_dealtestnumok';Thein存储过程中使用的表tb_testnum如下：其中，usertype字段的值必须大于1。测试存储过程一般遵循以下步骤：第一步是根据存储过程的输入输出参数设置正确的调用方式。第二步，根据调用时的入参值，将测试数据插入到相关表中。第三步，执行第一步中的存储过程调用语句，检查执行结果是否正确，并根据结果修改存储过程。下面将对其进行详细描述。第一步针对这个存储过程pr_dealtestnum，根据程序逻辑，我们可以设置如下调用语句：callpr_dealtestnum('2344273520',@1,@2);select@1,@2;callpr_dealtestnum('2344273521',@1,@2);select@1,@2;callpr_dealtestnum('2344273522',@1,@2);select@1,@2;callpr_dealtestnum('2344273523',@1,@2);select@1,@2;callpr_dealtestnum('2344273524',@1,@2);select@1,@2;callpr_dealtestnum('2344273525',@1,@2);select@1,@2;callpr_dealtestnum('2344273526',@1,@2);select@1,@2;callpr_dealtestnum('2344273527',@1,@2);select@1,@2;callpr_dealtestnum('2344273528',@1,@2);select@1,@2;callpr_dealtestnum('2344273529',@1,@2);select@1,@2;callpr_dealtestnum('2344273530',@1,@2);select@1,@2;callpr_dealtestnum('2344273531',@1,@2);select@1,@2;callpr_dealtestnum('2344273532',@1,@2);select@1,@2;callpr_dealtestnum('2344273533',@1,@2);select@1,@2;callpr_dealtestnum('2344273534',@1,@2);select@1,@2;callpr_dealtestnum('15696192523',@1,@2);select@1,@2;还可以设置更多的Call语句，其目的是为了更全面地测试存储过程。第二步，根据第一步设置的调用语句，考虑到程序逻辑，我们可以执行如下语句向tb_testnum表中插入数据：callpr_dealtestnum('2344273520',@1,@2);select@1,@2;callpr_dealtestnum('2344273521',@1,@2);select@1,@2;callpr_dealtestnum('2344273522',@1,@2);select@1,@2;callpr_dealtestnum('2344273523',@1,@2);select@1,@2;callpr_dealtestnum('2344273524',@1,@2);select@1,@2;callpr_dealtestnum('2344273525',@1,@2);select@1,@2;callpr_dealtestnum('2344273526',@1,@2);select@1,@2;callpr_dealtestnum('2344273527',@1,@2);select@1,@2;callpr_dealtestnum('2344273528',@1,@2);select@1,@2;callpr_dealtestnum('2344273529',@1,@2);select@1,@2;callpr_dealtestnum('2344273530',@1,@2);select@1,@2;callpr_dealtestnum('2344273531',@1,@2);select@1,@2;callpr_dealtestnum('2344273532',@1,@2);select@1,@2;callpr_dealtestnum('2344273533',@1,@2);select@1,@2;callpr_dealtestnum('2344273534',@1,@2);select@1,@2;callpr_dealtestnum('15696192523',@1,@2);选择@1,@2;Step3在表tb_testnum中插入数据后，我们开始逐条执行第一步中的存储过程调用语句，验证存储过程代码逻辑的正确性。先执行“callpr_dealtestnum('2344273520',@1,@2);select@1,@2;”，结果如下：mysql>callpr_dealtestnum('2344273520',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+-----+-----+|@1|@2|+-----+-----+|0|2|+------+------+1rowinset(0.00sec)下面分析一下执行结果是不是我们想要的。将输入参数“2344273520”带入存储过程，首先“selectcount(*)intop_boxnumcountfromtb_testnumwhereboxnumber='2344273520';”，此时“boxnumcount”变量的值为1（因为语句“插入tb_testnum(boxnumber,usertype)values('2344273520',1);”);然后，程序进入“ifp_boxnumcount>0then”分支，执行语句“selectusertypeintop_usertypefromtb_testnumwhereboxnumber='2344273520';”，“p_usertype”变量的值为1；然后，执行“setp_outusertype=(1+1)/10*10;”语句，即“p_outusertype”变量的值为“2/10*10”，进一步计算为“0*10”，最终结果为0；***，执行“setp_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|+------------+1rowinset(0.00sec)mysql>select(1+1)/10;+-----------+|(1+1)/10|+---------+|0.2000|+------------+1rowinset(0.00sec)我们可以看到，“(1+1)/10”的结果不是我们预期的0，而是0.2。看来在MySQL中，两个整数相除，不仅取结果的整数部分，还要取小数点好几位。那么，如果我们只想要整数部分怎么办呢？此时，我们可以使用floor()函数，即“floor((1+1)/10)”为结果的整数部分，如下图：mysql>selectfloor((1+1)/10);+----------------+|floor((1+1)/10)|+----------------+|0|+----------------+1rowinset(0.00sec现在，我们对存储过程进行相应的修改，修改后如下所示：dropprocedureifexistspr_dealtestnum;delimiter//createprocedurepr_dealtestnum(inp_boxnumbervarchar(30),outp_resultint,outp_outusertypeint)pr_dealtestnum_label:begindeclarep_boxnumcountint;declarep_usertypeint;setp_boxnumcount=0;setp_usertype=0;setp_outusertype=0;selectcount(*)intop_boxnumcountfromtb_testnumwhereboxnumber=p_boxnumber;ifp_boxnumcount>0thenbeginselectusertypeintop_usertypefromtb_testnumwhereboxnumber=p_boxnumber;setp_outusertype=floor((p_usertype+1)/10)*10;setp_result=0;end;elsebeginsetp_result=1;end;endif;leavepr_dealtestnum_label;end;//delimiter;select'createprocedurepr_dealtestnumok';执行"callpr_dealtestnum('2344273520',@1,@2);选择@1,@2;",th结果如下：mysql>callpr_dealtestnum('2344273520',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+-----+-----+|@1|@2|+------+------+|0|0|+------+------+1rowinset(0.00sec)此时的结果就是我们想要的我们可以继续执行第一步设置的其他存储过程调用语句，结果如下：mysql>callpr_dealtestnum('2344273521',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+-----+-----+|@1|@2|+-----+-----+|0|0|+-----+------+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273522',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+------+------+|@1|@2|+------+-----+|0|0|+------+------+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273523',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+------+------+|@1|@2|+-----+-----+|0|0|+-----+-----+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273524',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+-----+-----+|@1|@2|+------+-----+|0|0|+-----+-----+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273525',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+-----+-----+|@1|@2|+-----+-----+|0|0|+-----+-----+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273526',@1,@2);select@1,@2;QueryOK,1行受影响（0.00秒）+-----+-----+|@1|@2|+-----+-----+|0|10|+-----+-----+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273527',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+-----+-----+|@1|@2|+------+-----+|0|10|+-----+-----+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273528',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+-----+-----+|@1|@2|+-----+-----+|0|10|+-----+-----+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273529',@1,@2);select@1,@2;QueryOK,1行受影响（0.00秒）+-----+-----+|@1|@2|+-----+-----+|0|10|+------+------+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273530',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+------+-----+|@1|@2|+-----+------+|0|10|+------+------+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273531',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+-----+------+|@1|@2|+-----+-----+|0|10|+-----+-----+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273532',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+-----+-----+|@1|@2|+-----+-----+|0|20|+------+------+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273533',@1,@2);select@1,@2;QueryOK,1rowaffected(0.00sec)+------+-----+|@1|@2|+-----+------+|0|50|+------+------+1rowinset(0.00sec)mysql>callpr_dealtestnum('2344273534',@1,@2);select@1,@2;QueryOK,1rowaffected(0.01sec)+-----+------+|@1|@2|+-----+-----+|0|80|+-----+-----+1rowinset(0.00sec)mysql>callpr_dealtestnum('15696192523',@1,@2);select@1,@2;QueryOK,1rowaffected(0.01sec)+-----+-----+|@1|@2|+-----+-----+|1|0|+-----+-----+1rowinset(0.00sec)可以看到修改后的存储过程的执行结果正确***一条调用语句“callpr_dealtestnum('15696192523',@1,@2);select@1,@2;”，因为入参中“15696192523”对应的数据在tb_testnum表中不存在，于是程序进入“else”分支，“p_result”变量的值为1，“p_outusertype”变量的值为0。小结存储过程的测试方法同测试用其他编程语言编写的程序的方法。我们需要在充分理解程序逻辑的基础上构建完整多样的测试用例，并在测试过程中根据测试结果修改程序以达到我们预期的效果，最终满足用户的需求。需要强调的是，在软件开发过程中，我们测试代码的时间可能比写代码的时间要多。大家一定要有耐心，忍受测试代码过程中的“寂寞”。【本文为专栏作家周兆雄原创文章，作者微信公众号：周氏逻辑（logiczhou）】
