mysql存储过程的创建
(1). 格式
mysql存储过程创建的格式：create procedure 过程名 ([过程参数[,...]])
[特性 ...] 过程体
这里先举个例子：
mysql> delimiter //  mysql> create procedure proc1(out s int)      -> begin     -> select count(*) into s from user;      -> end     -> //  mysql> delimiter ; 注：
（1）这里需要注意的是delimiter //和delimiter ;两句，delimiter是分割符的意思，因为mysql默认以;为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成sql语句进行处理，则存储过程的编译过程会报错，所以要事先用delimiter关键字申明当前段分隔符，这样mysql才会将;当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用,分割开。
（3）过程体的开始与结束使用begin与end进行标识。
这样，我们的一个mysql存储过程就完成了，是不是很容易呢?看不懂也没关系，接下来，我们详细的讲解。
下面的例子主要用到了
ⅰ. if-then -else语句
ⅰ.  found_rows() 语句
#记录每天的步行、睡眠、体重、消耗卡路里等信息#userrecorddetail 表中，如果存在当天数据，则修改，否则新增#userrecord 表中，如果存在，则累加，否则新增#类型：1步行2睡眠3卡路里消耗4体重#call userrecord_create(1001,45,100,1000,1000,500,500,2,1);drop procedure if exists pro_userrecord_stepnum; delimiter //create procedure pro_userrecord_stepnum(in p_userid int,in p_stepnum int)begin declare rcount int; -- 查看用户是否有详细记录 select id from userrecorddetail where userid = p_userid and date(createtime) = curdate() limit 1; select found_rows() into rcount; if (rcount=0) then --查看userrecord是否有用户总记录信息，不存在，则添加，否则修改 select idfrom userrecord where userid = p_userid limit 1; select found_rows() into rcount; if(rcount = 0 )then insertinto `userrecord`(`userid`,`totalstep`,`updatetime`,`createtime`) values (p_userid,p_stepnum,now(),now()); else update userrecord set totalstep = totalstep+p_stepnum where userid = p_userid; end if;-- 结束 -- 插入一条用户记录详细信息 insertinto `userrecorddetail`(`weigh`,`calorie`,`stepnum`,`userid`, `sleeptimes`,`lightsleeptimes`,`heavysleeptimes`, `wakeupnum`,`updatetime`,`createtime`) values (0,0,p_stepnum, p_userid,0,0,0,0,now(),now()); else --查看是否有用户总记录信息，不存在，则添加，否则修改 select idfrom userrecord where userid = p_userid limit 1; select found_rows() into rcount; if(rcount = 0 )then insertinto `userrecord`(`userid`,`totalstep`,`updatetime`,`createtime`) values (p_userid,p_stepnum,now(),now()); else update userrecord set totalstep = totalstep + p_stepnum where userid = p_userid; end if; -- 修改userrecorddetail update userrecorddetail set stepnum = stepnum + p_stepnum where userid = p_userid; end if;end;//delimiter ; show warnings; show create procedure pro_userrecord_stepnum;call pro_userrecord_stepnum(1009,111);
ⅰ. 创建表的语句如下:
drop table if exists `userrecord`;create table `userrecord` (`id` int(11) not null auto_increment,`userid` int(11) not null comment 'fk',`totalstep` int(11) default '0' comment '总步数',`updatetime` datetime default null,`createtime` datetime not null,primary key (`id`)) engine=myisam auto_increment=8 default charset=utf8 collate=utf8_unicode_ci comment='用户记录总表';/*data for the table `userrecord` */lock tables `userrecord` write;insertinto `userrecord`(`id`,`userid`,`totalstep`,`updatetime`,`createtime`) values (1,1001,88000,'2014-05-16 14:16:50','2014-05-13 14:16:52'),(2,1002,35000,'2014-05-16 14:26:22','2014-05-12 14:26:24'),(3,1003,95000,'2014-05-16 14:28:00','2014-05-12 14:28:06'),(4,1007,150000,'2014-05-16 14:30:31','2014-04-28 14:30:33'),(5,1009,288,'2014-05-19 16:24:26','2014-05-19 16:24:26'),(6,1010,33,'2014-05-19 17:01:50','2014-05-19 17:01:50'),(7,1011,33,'2014-05-19 17:03:31','2014-05-19 17:03:31');unlock tables;/*table structure for table `userrecorddetail` */drop table if exists `userrecorddetail`;create table `userrecorddetail` (`id` int(11) not null auto_increment,`weigh` double default '0' comment '今日体重 kg',`calorie` int(11) default '0' comment '今日消耗卡路里',`stepnum` int(11) default '0' comment '今日步数',`userid` int(11) not null comment 'fk',`sleeptimes` int(11) default '0' comment '今日睡眠时间 单位：分钟',`lightsleeptimes` int(11) default '0' comment '今日轻度睡眠时间 单位：分钟',`heavysleeptimes` int(11) default '0' comment '今日重度睡眠时间 单位：分钟',`wakeupnum` int(11) default '0' comment '今日唤醒次数',`updatetime` datetime default null,`createtime` datetime not null,primary key (`id`)) engine=myisam auto_increment=26 default charset=utf8 collate=utf8_unicode_ci comment='用户记录详细信息表';/*data for the table `userrecorddetail` */lock tables `userrecorddetail` write;insertinto `userrecorddetail`(`id`,`weigh`,`calorie`,`stepnum`,`userid`,`sleeptimes`,`lightsleeptimes`,`heavysleeptimes`,`wakeupnum`,`updatetime`,`createtime`) values (1,0,0,10000,1001,0,0,0,0,null,'2014-05-16 14:17:53'),(2,0,0,10000,1001,0,0,0,0,null,'2014-05-15 14:22:58'),(3,0,0,15000,1001,0,0,0,0,null,'2014-05-14 14:23:56'),(4,0,0,13000,1001,0,0,0,0,null,'2014-05-13 14:24:10'),(5,0,0,20000,1001,0,0,0,0,null,'2014-05-12 14:24:32'),(6,0,0,8000,1001,0,0,0,0,null,'2014-05-11 14:24:51'),(7,0,0,12000,1001,0,0,0,0,null,'2014-05-09 14:25:02'),(8,0,0,10000,1002,0,0,0,0,null,'2014-05-16 14:26:50'),(9,0,0,5000,1002,0,0,0,0,null,'2014-05-15 14:26:58'),(10,0,0,20000,1002,0,0,0,0,null,'2014-05-14 14:27:14'),(11,0,0,20000,1003,0,0,0,0,null,'2014-05-16 14:28:46'),(12,0,0,30000,1003,0,0,0,0,null,'2014-05-15 14:28:54'),(13,0,0,25000,1003,0,0,0,0,null,'2014-05-13 14:29:01'),(14,0,0,15000,1003,0,0,0,0,null,'2014-05-12 14:29:07'),(15,0,0,5000,1003,0,0,0,0,null,'2014-05-08 14:29:39'),(16,0,0,20000,1007,0,0,0,0,null,'2014-05-16 14:30:45'),(17,0,0,30000,1007,0,0,0,0,null,'2014-05-15 14:30:54'),(18,0,0,25000,1007,0,0,0,0,null,'2014-05-14 14:31:02'),(19,0,0,15000,1007,0,0,0,0,null,'2014-05-13 14:31:10'),(20,0,0,35000,1007,0,0,0,0,null,'2014-05-12 14:31:18'),(21,0,0,25000,1007,0,0,0,0,null,'2014-05-11 14:31:26'),(22,0,0,20000,1007,0,0,0,0,null,'2014-04-30 14:32:02'),(23,45,111,288,1009,600,100,500,2,'2014-05-19 16:24:26','2014-05-19 16:24:26'),(24,0,66,33,1010,0,0,0,0,'2014-05-19 17:01:50','2014-05-19 17:01:50'),(25,45,33,33,1011,600,100,500,0,'2014-05-19 17:03:31','2014-05-19 17:03:31');unlock tables;
下面的例子主要用到了
ⅰ. if-then -else语句