首頁 > 資料庫 > mysql教程 > mysql存储过程的应用_MySQL

mysql存储过程的应用_MySQL

WBOY
發布: 2016-06-01 13:13:31
原創
1034 人瀏覽過

MySQL存储过程的创建

(1). 格式

MySQL存储过程创建的格式:CREATE PROCEDURE 过程名 ([过程参数[,...]])
[特性 ...] 过程体

这里先举个例子:

  1. mysql> DELIMITER //
  2. mysql> CREATE PROCEDURE proc1(OUT s int)
  3. -> BEGIN
  4. -> SELECT COUNT(*) INTO s FROM user;
  5. -> END
  6. -> //
  7. 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语句

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板