mysql存储过程的应用 - 数据库编程

`totalStep`,`updateTime`,`createTime`) VALUES (p_userId,p_stepNum,NOW(),NOW()); ELSE UPDATE userRecord SET totalStep = totalStep+p_stepNum WHERE userId = p_userId; END IF; -- 结束 -- 插入一条用户记录详细信息 INSERT INTO `userRecordDetail`(`weigh`,`calorie`,`stepNum`,`userId`, `sleepTimes`,`lightSleepTimes`,`heavySleepTimes`, `wakeupNum`,`updateTime`,`createTime`) VALUES (0,0,0, p_userId,p_sleepTimes,p_lightSleepTimes,p_heavySleepTimes,p_wakeupNum,NOW(),NOW()); ELSE -- 修改userRecordDetail UPDATE userRecordDetail SET sleepTimes = sleepTimes + p_sleepTimes, lightSleepTimes = lightSleepTimes + p_lightSleepTimes, heavySleepTimes = heavySleepTimes + p_heavySleepTimes, wakeupNum = wakeupNum + p_wakeupNum WHERE id = resultId; END IF; END;// DELIMITER ; SHOW WARNINGS; SHOW CREATE PROCEDURE pro_userRecord_sleep; CALL pro_userRecord_sleep(1009,600,100,500,2); ?

Ⅰ. 创建表的语句如下:

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;

insert  into `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;

insert  into `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,'20

mysql存储过程的应用(二)