建站
咨詢
售后
建站咨詢
新聞中心
同C/C++/Java等語言編寫的代碼一樣，用SQL語言編寫的存儲(chǔ)過程也需要進(jìn)行充分的測試。本文以實(shí)際的MySQL存儲(chǔ)過程為例，介紹存儲(chǔ)過程測試的整個(gè)流程。
在本文中，需要被測試的存儲(chǔ)過程如下：
- 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';
在存儲(chǔ)過程中使用到的表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。
對(duì)存儲(chǔ)過程進(jìn)行測試大致遵循以下步驟：
***步，按照存儲(chǔ)過程的輸入和輸出參數(shù)設(shè)置正確的調(diào)用樣式。
第二步，根據(jù)調(diào)用時(shí)的輸入?yún)?shù)值來向相關(guān)的表中插入測試數(shù)據(jù)。
第三步，執(zhí)行***步中的存儲(chǔ)過程調(diào)用語句，查看執(zhí)行結(jié)果是否正確，并根據(jù)該結(jié)果來修改存儲(chǔ)過程。
下面具體進(jìn)行說明。
***步
就本存儲(chǔ)過程pr_dealtestnum而言，根據(jù)程序邏輯，我們可設(shè)置如下調(diào)用語句：
- 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;
大家還可以設(shè)置更多的調(diào)用語句，其目的是為了對(duì)存儲(chǔ)過程進(jìn)行更加充分的測試。
第二步
根據(jù)***步設(shè)置的調(diào)用語句，兼顧程序邏輯，我們可執(zhí)行如下語句向表tb_testnum中插入數(shù)據(jù)：
- 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中插入數(shù)據(jù)之后，我們開始逐條執(zhí)行***步中的存儲(chǔ)過程調(diào)用語句，以驗(yàn)證存儲(chǔ)過程代碼邏輯的正確性。
首先執(zhí)行“call pr_dealtestnum(‘2344273520’,@1,@2);select @1,@2;”，結(jié)果如下：
- 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)
我們來分析執(zhí)行結(jié)果是否是我們想要的。將入?yún)ⅰ?344273520”帶入存儲(chǔ)過程中，首先是“select count(*) into p_boxnumcount from tb_testnum where boxnumber=’2344273520’;”，此時(shí)“boxnumcount”變量的值為1(因?yàn)橹皥?zhí)行過語句“insert into tb_testnum(boxnumber,usertype) values(‘2344273520’,1);”);接著，程序進(jìn)入“if p_boxnumcount > 0 then”分支，執(zhí)行“select usertype into p_usertype from tb_testnum where boxnumber=’2344273520’;”語句，“p_usertype”變量的值為1;然后，執(zhí)行“set p_outusertype = (1+1)/10*10;”語句，即“p_outusertype”變量的值為“2/10*10”，進(jìn)一步計(jì)算為“0*10”，最終結(jié)果為0;***，執(zhí)行“set p_result = 0;”語句，“p_result”變量的值為0。因此，最終兩個(gè)輸出參數(shù)的值都應(yīng)該為0。
但是，實(shí)際的結(jié)果是，兩個(gè)輸出參數(shù)的值分別為0和2，是哪里出了問題呢?我們重點(diǎn)分析“(1+1)/10*10”的結(jié)果，將之在MySQL中單獨(dú)執(zhí)行，結(jié)果如下：
- 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”的結(jié)果并不是我們預(yù)想的0，而是0.2，看來，在MySQL中，兩個(gè)整數(shù)相除并不是只取結(jié)果的整數(shù)部分，而是取了小數(shù)點(diǎn)后面的若干位。
那么，如果我們只想要整數(shù)部分，應(yīng)該怎么辦呢?此時(shí)，可以用floor()函數(shù)，即“floor((1+1)/10)”就是取了結(jié)果的整數(shù)部分，如下所示：
- mysql> select floor((1+1)/10);
- +-----------------+
- | floor((1+1)/10) |
- +-----------------+
- | 0 |
- +-----------------+
- 1 row in set (0.00 sec
現(xiàn)在，我們據(jù)此修改存儲(chǔ)過程，修改之后如下所示：
- 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';
再次執(zhí)行“call pr_dealtestnum(‘2344273520’,@1,@2);select @1,@2;”，結(jié)果如下：
- 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)
此時(shí)的結(jié)果就是我們想要的。
我們可以繼續(xù)執(zhí)行在***步中設(shè)置的其它存儲(chǔ)過程調(diào)用語句，結(jié)果如下：
- 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)
可以看到，修改之后的存儲(chǔ)過程的執(zhí)行結(jié)果就是正確的了。***一條調(diào)用語句“call pr_dealtestnum(‘15696192523’,@1,@2);select @1,@2;”中，因?yàn)檩斎雲(yún)?shù)中的“15696192523”對(duì)應(yīng)的數(shù)據(jù)在tb_testnum表中不存在，因此程序進(jìn)入了“else”分支，“p_result”變量的值就為1，而“p_outusertype”變量的值為0。
總結(jié)
存儲(chǔ)過程的測試方法和用其它編程語言編寫的程序的測試方法是一樣的，都需要我們在充分理解程序邏輯的基礎(chǔ)上構(gòu)造完整的、多樣化的測試用例，并在測試的過程中根據(jù)測試的結(jié)果來修改程序，以達(dá)到我們預(yù)期的結(jié)果，并最終滿足用戶的需求。
需要強(qiáng)調(diào)的是，在軟件開發(fā)過程中，我們測試代碼的時(shí)間，很可能會(huì)多余我們編寫代碼的時(shí)間，大家一定要耐著性子忍受測試代碼過程中的“孤獨(dú)感”。
【本文是專欄作者周兆熊的原創(chuàng)文章，作者微信公眾號(hào)：周氏邏輯(logiczhou)】
文章題目：存儲(chǔ)過程測試流程--以MySQL為例
轉(zhuǎn)載來于：http://www.dlmjj.cn/article/cohicgo.html
