存储过程测试流程--以MySQL为例

同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。

总结
存储过程的测试方法和用其它编程语言编写的程序的测试方法是一样的，都需要我们在充分理解程序逻辑的基础上构造完整的、多样化的测试用例，并在测试的过程中根据测试的结果来修改程序，以达到我们预期的结果，并最终满足用户的需求。

需要强调的是，在软件开发过程中，我们测试代码的时间，很可能会多余我们编写代码的时间，大家一定要耐着性子忍受测试代码过程中的“孤独感”。