MySQL 存储过程基本语法

66 阅读 0 评论 44 点赞

我是靠谱客的博主甜美钥匙，最近开发中收集的这篇文章主要介绍MySQL 存储过程基本语法，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

DECLARE 用来定义变量： DECLARE + 变量名 + 数据类型（数据长度）

对于 DECLARE 定义的变量赋值有2种方式

set 变量名 = 值注意 set 赋值时，如果是sql 语句，sql语句必须用括号括起来

select ** into **

set 值可以对一个变量进行赋值

select ** into ** 可以对多个变量进行赋值

如果在sql需要对查询出来的多条数据进行循环，需要用游标来处理

DECLARE done BOOLEAN DEFAULT 0;  -- 定义一个变量，只有当游标到底时，才会改变

DECLARE dev_rs CURSOR FOR select device_id from device; -- 定义游标 并把值存在游标里

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  -- 把结束标志 绑定到游标上

定义事务

DECLARE t_error INTEGER DEFAULT 0; -- 定义一个变量 ，只有当执行出错时， 才会改变变量值

 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;

 IF t_error = 0 THEN  如果变量为0 就提交事务 否则进行回滚
      COMMIT;
   ELSE
      ROLLBACK;
   END IF;

跳出while 循环

dev_while: while done=0 DO --  dev_while: 加wehile 这样是为了 跳出while

  IF t_error = 1 THEN  --  出现异常
         leave dev_while;     -- 跳出当前while 循环

if 进行空判断时不能写 if 'a'!=null 或 if 'a'=null 必须 if 'a' is not null 或 if 'a' is null

'a' = null 得到的是空值

如果mysql 相对存储过程进行断点调试可以使用dbforge studio for mysql 进行断点调试

注意点：

select ** into ** 如果没有查询到值，那么游标会跳出循环必须加判断

 IF(EXISTS(select 1 from offline o where o.offline_id  = ( SELECT max(offline_id) offline_id from offline ol where ol.device_id = dev_id))) THEN

列子如下：

create PROCEDURE count_offline()

BEGIN
   DECLARE dev_id int;   -- DECLARE  用来定义变量 declare +变量名+数据类型，有的数据类型必须指定长度
   DECLARE is_online int; 
   DECLARE done BOOLEAN DEFAULT 0;  -- 定义一个变量，只有当游标到底时，才会改变
   DECLARE begintime VARCHAR(30); 
   DECLARE endtime VARCHAR(30); 
   DECLARE data_count BIGINT; -
   DECLARE offline_end int ;
   DECLARE last_offline_report_time BIGINT; 
   DECLARE newset_report_time datetime;
   DECLARE last_offline_id BIGINT;
   DECLARE t_error INTEGER DEFAULT 0; -- 定义一个变量 ，只有当执行出错时， 才会改变变量值
   DECLARE dev_rs CURSOR FOR select device_id from device;
   -- 将结束标志绑定到游标
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
   -- 把错误标志 绑定到 异常中
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
   set  begintime = (select date_format (now(),'%Y-%m-%d %H:00:00'));
   set  endtime =  (select date_format (now(),'%Y-%m-%d %H:59:59'));
   open dev_rs;
   FETCH   dev_rs into  dev_id;
   START TRANSACTION;  -- 开启事务
   dev_while: while done=0 DO --  dev_while: 加wehile 这样是为了 跳出while
     set is_online = (SELECT d.is_online  from device d where d.device_id = dev_id);
     
      IF(EXISTS(select 1 from offline o where o.offline_id  = ( SELECT max(offline_id) offline_id from offline ol where ol.device_id = dev_id))) THEN
          select o.offline_end ,o.last_report_time ,o.offline_id into offline_end,   last_offline_report_time,last_offline_id from  offline o where o.offline_id  = ( SELECT max(offline_id) offline_id from offline ol where ol.device_id = dev_id);
      ELSE
       
        set offline_end = 1;
        set last_offline_report_time = NULL;
      END IF; 
    
     set data_count = (select count(*)   from device_datas_hour ddh where ddh.device_id = dev_id and ddh.create_time BETWEEN begintime and endtime) ;  
    
     if(data_count > 0) THEN          
           IF(is_online = 0) THEN
               update device d set d.is_online =1 where d.device_id = dev_id;
           END IF;       
           IF( offline_end =0) THEN            
             set newset_report_time = (select MIN(ddh.create_time)  from device_datas_hour ddh where ddh.device_id = dev_id and ddh.create_time BETWEEN begintime and endtime);
             update offline ol set   ol.offline_end = 1 ,  ol.offine_end_time=newset_report_time  ,  ol.offline_time =  (UNIX_TIMESTAMP(newset_report_time) - last_offline_report_time)  where ol.offline_id = last_offline_id;
           END IF;
  
     ELSE
       
         set newset_report_time = (select MAX(ddh.create_time)  from device_datas_hour ddh where ddh.device_id = dev_id );
         IF(is_online = 1) THEN
               update device d set d.is_online = 0 where d.device_id = dev_id; 
         END IF;
         IF(newset_report_time IS NOT NULL) THEN
         
            IF(offline_end = 1)  THEN
                INSERT into offline (device_id,create_time,offline_time,offline_end,last_report_time) VALUES(dev_id,now(),(UNIX_TIMESTAMP(NOW()))-(UNIX_TIMESTAMP(newset_report_time)),0,(UNIX_TIMESTAMP(newset_report_time)));
      
             ELSEIF(offline_end = 0) THEN
                 update offline ol set   ol.offline_time =  (UNIX_TIMESTAMP(NOW()))-last_offline_report_time where ol.offline_id = last_offline_id;   
             END IF;
         END IF;   
     END IF;
     IF t_error = 1 THEN  --  出现异常
         leave dev_while;     -- 跳出当前while 循环
     END IF;
     FETCH dev_rs INTO dev_id;    
   END WHILE;   -- 结束while
   CLOSE dev_rs; -- 关闭游标
   -- 提交事务
   IF t_error = 0 THEN 
      COMMIT;
   ELSE
      ROLLBACK;
   END IF;
  
 END