Mysql必读MySQL存储过程的优化实例

我们注意到“insert into tb_testnum values(p_boxnumber,p_usertype);”语句中,tb_testnum表之后没有列出具体的字段名,这个也是不规范的.如果在以后的软件版本中,tb_testnum表中新增了字段,那么这条insert语句极有可能会报错.因此,规范的写法是无论tb_testnum表中有多少字段,在执行insert操作时,都要列出具体的字段名.修改之后的存储过程如下：MYSQL入门

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';

优化五
MYSQL入门

在执行insert语句之后,要用MySQL中自带的@error_count参数来判断插入数据是否成功,方便开发人员跟踪执行结果.如果该参数的值不为0,表示插入失败,那么我们就用一个返回参数值来表示操作失败.修改之后的存储过程如下：MYSQL入门

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,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';

总结
MYSQL入门

从上面可以看出,一个短短的存储过程,就有这么多需要优化的地方,看来存储过程的编写也不是一件很简单的事情.确实,我们在编写代码(不仅仅是存储过程)的时候,一定要从代码的功能、可读性、性能等多方面来考虑,这样才能够写出优美的、具备较长生命周期的代码,进而开发出高质量的软件产品.希望本文能对大家学习MySQL存储过程有所帮助,也谢谢大家对编程之家PHP的支持.MYSQL入门

（编辑：厦门网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!