概述

在數據庫的開發過程中，經常會遇到復雜的業務邏輯和對數據庫的操作，這個時候就會用存儲過程來封裝數據庫操作。如果項目的存儲過程較多，書寫又沒有一定的規范，將會影響以后的系統維護困難和大存儲過程邏輯的難以理解，另外如果數據庫的數據量大或者項目對存儲過程的性能要求很，就會遇到優化的問題，否則速度有可能很慢。一個經過優化過的存儲過程要比一個性能差的存儲過程的效率甚至高幾百倍。

未優化的存儲過程：

在存儲過程中使用到的表tb_testnum結構如下：

在存儲過程中使用到的另外一張表tb_testnum_tmp結構如下：

從兩個表的結構可以看出，tb_testnum和tb_testnum_tmp所包含的字段完全相同，存儲過程pr_dealtestnum的作用是根據輸入參數將tb_testnum_tmp表的數據插入到tb_testnum表中。

優化一

存儲過程pr_dealtestnum的主體是一條insert語句，但這條insert語句里面又包含了select語句，這樣的編寫是不規范的。因此把這條insert語句拆分成兩條語句，即先把數據從tb_testnum_tmp表中查找出來，再插入到tb_testnum表中。修改之后的存儲過程如下：

優化二

在向tb_testnum表插入數據之前，要判斷該條數據在表中是否已經存在了，如果存在，則不再插入數據。同理，在從tb_testnum_tmp表中查詢數據之前，要先判斷該條數據在表中是否存在，如果存在，才能從表中查找數據。修改之后的存儲過程如下：

drop procedure if exists pr_dealtestnum;
delimiter //
 
create procedure pr_dealtestnum
(
 in p_boxnumber varchar(30)
)
pr_dealtestnum_label:begin
 declare p_usertype int;
 declare p_datacount int;
 
 select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
 if p_datacount > 0 then
 begin
 select usertype into p_usertype fromtb_testnum_tmp where boxnumber=p_boxnumber;
 end;
 else
 begin
 leave pr_dealtestnum_label;
 end;
 end if;
 
 select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
 if p_datacount = 0 then
 begin
 insert into tb_testnum values(p_boxnumber,p_usertype);
 leave pr_dealtestnum_label;
 end;
 else
 begin
 leave pr_dealtestnum_label;
 end;
 end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

優化三

不管向tb_testnum表插入數據的操作執行成功與否，都應該有一個標識值來表示執行的結果，這樣也方便開發人員對程序流程的追蹤和調試。也就是說，在每條leave語句之前，都應該有一個返回值，我們為此定義一個輸出參數。修改之后的存儲過程如下：

drop procedure if exists pr_dealtestnum;
delimiter //
 
create procedure pr_dealtestnum
(
 in p_boxnumber varchar(30),
 out p_result int -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
 declare p_usertype int;
 declare p_datacount int;
 
 select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
 if p_datacount > 0 then
 begin
 select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
 end;
 else
 begin
 set p_result = 1;
 leave pr_dealtestnum_label;
 end;
 end if;
 
 select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
 if p_datacount = 0 then
 begin
 insert into tb_testnum values(p_boxnumber,p_usertype);
 set p_result = 0;
 leave pr_dealtestnum_label;
 end;
 else
 begin
 set p_result = 2;
 leave pr_dealtestnum_label;
 end;
 end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

優化四

“insert into tb_testnum values(p_boxnumber,p_usertype);”語句中，tb_testnum表之后沒有列出具體的字段名，這個也是不規范的。如果在以后的軟件版本中，tb_testnum表中新增了字段，那么這條insert語句極有可能會報錯。因此，規范的寫法是無論tb_testnum表中有多少字段，在執行insert操作時，都要列出具體的字段名。修改之后的存儲過程如下：

drop procedure if exists pr_dealtestnum;
delimiter //
 
create procedure pr_dealtestnum
(
 in p_boxnumber varchar(30),
 out p_result int -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
 declare p_usertype int;
 declare p_datacount int;
 
 select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
 if p_datacount > 0 then
 begin
 select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
 end;
 else
 begin
 set p_result = 1;
 leave pr_dealtestnum_label;
 end;
 end if;
 
 select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
 if p_datacount = 0 then
 begin
 insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype);
 set p_result = 0;
 leave pr_dealtestnum_label;
 end;
 else
 begin
 set p_result = 2;
 leave pr_dealtestnum_label;
 end;
 end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

優化五

在執行insert語句之后，要用MySQL中自帶的@error_count參數來判斷插入數據是否成功，方便開發人員跟蹤執行結果。如果該參數的值不為0，表示插入失敗，那么我們就用一個返回參數值來表示操作失敗。修改之后的存儲過程如下：

drop procedure if exists pr_dealtestnum;
delimiter //
 
create procedure pr_dealtestnum
(
 in p_boxnumber varchar(30),
 out p_result int -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
 declare p_usertype int;
 declare p_datacount int;
 
 select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
 if p_datacount> 0 then
 begin
 select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
 end;
 else
 begin
 set p_result = 1;
 leave pr_dealtestnum_label;
 end;
 end if;
 
 select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
 if p_datacount = 0then
 begin
 insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype);
 if @error_count<>0 then
 begin
 set p_result= 3;
 end;
 else
 begin
 set p_result= 0;
 end;
 end if;
 end;
 else
 begin
 set p_result = 2;
 end;
 end if;
 
 leave pr_dealtestnum_label;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

總結

從上面可以看出，一個短短的存儲過程，就有這么多需要優化的地方，看來存儲過程的編寫也不是一件很簡單的事情。平時在編寫代碼(不僅僅是存儲過程)的時候，一定要從功能、可讀性、性能等多方面來考慮，這樣才能夠寫出優美的、具備較長生命周期的存儲過程。