第一个mysql存储过程

    从事运维平台的开发已经有4周了，数据库表结构的设计变动了几次，存储引擎也从mysiam变为支持事务的innodb，过去两天主要写出了一个存储过程，解决响应时间入库的山寨方式，现在只要调用这个存储过程即可。

    按照惯例，先说明一下这个存储过程解决的主要技术难点：

1、存储过程中，表名不能用变量的问题，用了prepare来解决；

2、－分隔的字符相加问题，用了substing_index()函数巧妙地解决了

3、调用方式如下：

CALL UpdateRequestTimeReg('表名','url','time','1-3-3-4-$',@a);

 

存储过程的源码如下，这是我的first存储过程，感慨较多啊，07年找工作时，在u88，人家问我会不会写存储过程，我说没问题，其实根本不会写，嘿嘿。

 

建表的语句，表结构等，日后统一整理：

CREATE TABLE `access_log_reg_201203` (
  `access_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `access_url` varchar(100) NOT NULL default '',
  `access_times` int(11) default NULL,
  `request_time` varchar(100) default NULL,
  `request_ip` varchar(256) default NULL,
  PRIMARY KEY  (`access_datetime`,`access_url`)
) ENGINE=innodb DEFAULT CHARSET=gbk;

 

--------------------------------

drop procedure if exists UpdateRequestTimeReg;

DELIMITER $$
-- Purl 是存储过程接收的access_url, Pdatetime是存储过程接收的access_datetime
create procedure UpdateRequestTimeReg(IN tablename varchar(80), IN Purl varchar(100), IN Pdatetime datetime ,IN AddValue varchar(80), OUT ReturnValue varchar(80) )
BEGIN

--表里取出的串存在@OldValue中，不在此声明，而是在select into时生成
--declare OldValue varchar(80);
--OldValue循环加上此存储过程接收的参数AddValue后，形成NewValue
declare NewValue varchar(80);
--保存OldValue中，以-分隔的各个数字
declare TmpStrOld varchar(20);
--保存AddValue中，以-分隔的各个数字
declare TmpStrAdd varchar(20);

declare Length int ;
declare i int ;

--初始化新字符串
set NewValue='';
set autocommit=0;

--这句话弄了半下午，主要是表名用变量和select into到变量这两个问题。
SET @SqlCmd = concat('select request_time into @OldValue from ', tablename ,' where access_url=\'', Purl, '\' and access_datetime=\'', Pdatetime, '\' for update');
PREPARE stmt FROM @SqlCmd;
EXECUTE stmt;

set Length = length(@OldValue);
set i = 1;
-- 以OldValue的长度为遍历次数，按-分隔，一个个地累加OldValue和AddValue，遇到'$'时退出循环，最后形成NewValue

 while_label: while i < Length
 do
 set TmpStrOld = substring_index( substring_index(@OldValue, '-', i), '-', -1 );
 set TmpStrAdd = substring_index( substring_index(AddValue, '-', i), '-', -1 );

 -- 判断结束，结束条件必须是传过来的串和数据库中的串的最后一位都是$，否则要返回错误，通知调用者
 if(TmpStrOld = '$') AND (TmpStrAdd= '$')
 then
  --返回值应该是SUCCESS,这里返回一半，待会update完毕后返回全部
  set ReturnValue = 'SUCC';
  --select ReturnValue;
  leave while_label;
 end if;

 if(TmpStrOld = '$') OR (TmpStrAdd = '$')
  then
  set ReturnValue = 'format error, exit with no update';
  --select ReturnValue;
  leave while_label;
 end if;

 -- 不是结束符的话，循环构造新值。
 --select i,TmpStrOld,TmpStrAdd;
 set NewValue =  concat(NewValue, TmpStrOld + TmpStrAdd, '-');

 set i=i+1;
        end while;

if(TmpStrOld != '$') OR (TmpStrAdd != '$')
 then
 set ReturnValue = 'format error, exit with no update';
end if;

set NewValue =  concat(NewValue, '$');

if(ReturnValue = 'SUCC')
then
 --update tablename set request_time=NewValue where access_url=Purl and access_datetime=Pdatetime;
 SET @SqlCmd = concat('update ', tablename ,' set request_time=\'', NewValue,'\' where access_url=\'', Purl, '\' and access_datetime=\'', Pdatetime, '\'');
 PREPARE stmt FROM @SqlCmd;
 EXECUTE stmt;

 -- 拼凑最后的返回值，应该是SUCCESS
 set ReturnValue = concat(ReturnValue, 'ESS');
end if;

commit;
set autocommit=1;
END $$

DELIMITER ;

 

    最后，一定要感谢一下网上的这几篇文章，帮我解决了很多疑问：

http://mxohy.blog.sohu.com/160758392.html  讲解autocommit

存储过程一例：

http://www.cnblogs.com/rooney/archive/2011/12/05/2276590.html

substring使用例子：

存储过程中select into 不能用变量的bug解决办法：