mysql存储过程应用_mysql 存储过程的应用

MySQL存储过程的创建

(1). 格式

MySQL存储过程创建的格式：CREATE

PROCEDURE 过程名 ([过程參数[,...]])

[特性 ...] 过程体

这里先举个样例：

mysql> DELIMITER //

mysql>CREATEPROCEDUREproc1(OUTsint)

->BEGIN

->SELECTCOUNT(*)INTOsFROMuser;

->END

-> //

mysql> DELIMITER ;

注：

(1)这里须要注意的是DELIMITER

//和DELIMITER ;两句。DELIMITER是切割符的意思，由于MySQL默认以";"为分隔符，假设我们没有声明切割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITERkeyword申明当前段分隔符。这样MySQL才会将";"当做存储过程中的代码。不会运行这些代码，用完了之后要把分隔符还原。

(2)存储过程依据须要可能会有输入、输出、输入输出參数，这里有一个输出參数s，类型是int型。假设有多个參数用","切割开。

(3)过程体的開始与结束使用BEGIN与END进行标识。

这样，我们的一个MySQL存储过程就完毕了，是不是非常easy呢?看不懂也没关系，接下来，我们具体的解说。

以下的样例主要用到了

Ⅰ. 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语句