Home >Database >Mysql Tutorial >mysql存储过程的应用_MySQL
mysql存储过程的应用_MySQL
- WBOYOriginal
- 2016-06-01 13:13:311030browse
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 id FROM userRecord WHERE userId = p_userId LIMIT 1; SELECT FOUND_ROWS() INTO RCount; IF(RCount = 0 )THEN INSERT INTO `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; -- 结束 -- 插入一条用户记录详细信息 INSERT INTO `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 id FROM userRecord WHERE userId = p_userId LIMIT 1; SELECT FOUND_ROWS() INTO RCount; IF(RCount = 0 )THEN INSERT INTO `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);
如果需要精确到小时，则存储过程语句如下：
#记录每天的步行、睡眠、体重、消耗卡路里等信息#userRecordDetail 表中，如果存在当天数据，则修改，否则新增#userRecord 表中，如果存在，则累加，否则新增#类型：1步行2睡眠3卡路里消耗4体重#CALL userRecord_create(1001,45,100,1000,1000,500,500,2,1); DROP PROCEDURE IF EXISTS pro_userRecord_sleep; DELIMITER // CREATE PROCEDURE pro_userRecord_sleep(IN p_userId INT,IN p_sleepTimes INT ,IN p_lightSleepTimes INT ,IN p_heavySleepTimes INT ,IN p_wakeupNum INT) BEGIN DECLARE RCount INT; DECLARE resultId INT; -- 查看用户是否有详细记录 SELECT id INTO resultId FROM userRecordDetail WHERE userId = p_userId AND DATE_FORMAT(createTime,'%Y%m%d%H') = DATE_FORMAT(NOW(),'%Y%m%d%H') LIMIT 1; SELECT FOUND_ROWS() INTO RCount; IF (RCount=0) THEN -- 查看userRecord是否有用户总记录信息，不存在，则添加，否则修改 SELECT id FROM userRecord WHERE userId = p_userId LIMIT 1; SELECT FOUND_ROWS() INTO RCount; IF(RCount = 0 )THEN INSERT INTO `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; -- 结束 -- 插入一条用户记录详细信息 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,'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语句
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn