mysql储存过程写法 mysql存储过程语法_生活百科

概述在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用存储过程来封装数据库操作。如果项目的存储过程较多，书写又没有一定的规范，将会影响以后的系统维护困难和大存储过程逻辑的难以理解，另外如果数据库的数据量大或者项目对存储过程的性能要求很，就会遇到优化的问题，否则速度有可能很慢。一个经过优化过的存储过程要比一个性能差的存储过程的效率甚至高几百倍。
未优化的存储过程：

文章插图

文章插图
优化二在向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语句之前，都应该有一个返回值，我们为此定义一个输出参数。修改之后的存储过程如下：
【mysql储存过程写法 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 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';