存储过程中游标的使用一例

最新推荐文章于 2023-05-21 08:00:00 发布

兰青

最新推荐文章于 2023-05-21 08:00:00 发布

阅读量1.9k

点赞数

分类专栏： SQL随记文章标签：存储数据

本文链接：https://blog.csdn.net/f247316233/article/details/52177387

版权

SQL随记专栏收录该内容

1 篇文章 0 订阅

订阅专栏

在存储过程中使用游标实现根据A表的项目依次查询B表的功能，最后把数据都放到临时表（temporary ）中，返回一个结果集。如不放到临时表中返回结果集，每次循环都会返回一个结果集。具体存储过程的代码如下：

# Host: 192.168.*.*  (Version: 5.5.20)
# Generator: MySQL-Front 5.3  (Build 4.4)

CREATE PROCEDURE `GetWorkloadByWard`(IN cWard varchar(20),IN dStart datetime,IN dEnd datetime)
BEGIN
     -- 不存在则创建临时表 
      create temporary table if not exists tmpTable  
      (  
         Name varchar(50),  
         Count varchar(10)
      );
      -- 使用前先清空临时表。 
      truncate TABLE tmpTable;

      BEGIN
         -- 声明一个标志done，判断游标是否遍历完成
        DECLARE done INT DEFAULT 0;
        -- 声明变量存放从游标中提取的数据，名字不能与游标中使用的列名相同
        DECLARE iname varchar(50);
        DECLARE itype varchar(20);
        DECLARE src varchar(20);
        DECLARE cate varchar(20);
        DECLARE keyvalue varchar(50);
        -- 为了处理方便，关键字最多有三个
        DECLARE key1 varchar(20) DEFAULT '';
        DECLARE key2 varchar(20) DEFAULT '';
        DECLARE key3 varchar(20) DEFAULT '';
        -- 记录关键字分隔符的位置
        DECLARE location INT DEFAULT 0;

        -- 声明游标对应的 SQL 语句
        DECLARE cw CURSOR FOR
            select FS01, FE10, FE11, FS10, FS20 from w432 order by FI01;

        -- 游标循环到最后时会将 done 设置为 1，此时说明循环结束
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        -- 打开游标，开始循环
        open cw;
        -- 遍历每一行
        REPEAT
        -- 把每一行的数据放到对应的变量中
        FETCH cw INTO iname, itype, src, cate, keyvalue;
            if(not done) then
          -- 下面可以使用参数对应的数据做需要的处理
              -- 先给三个关键字初始值，给变量设置值的时候要使用 set 
              set key1 = '';
              set key2 = '';
              set key3 = '';
              -- 只处理任务的相关数据
              if(itype = 'task') then
                  -- 处理关键字，判断条件的写法和sql的where条件的写法类似
                  if(keyvalue is not null and keyvalue != '') then
                      -- keyvalue的值不为空时keyvalue是关键字，为空时iname的值是关键字
                      -- 第一个关键字 key1
                      -- 使用LOCATE()函数获取指定字符的位置
                      set location = LOCATE(',',keyvalue);
                      -- 大于0说明存在指定字符
                      if(location > 0) then
                      -- 截取指定字符左边的内容给关键字key1，然后截取指定字符左边的内容给自身，用于后续处理
                          set key1 = SUBSTRING(keyvalue,1,location-1);
                          set keyvalue = SUBSTRING(keyvalue,location+1);
                          -- 第二个关键字 key2 ，处理类似 key1
                          set location = LOCATE(',',keyvalue);
                          if(location > 0) then
                              set key2 = SUBSTRING(keyvalue,1,location-1);
                              set keyvalue = SUBSTRING(keyvalue,location+1);
                              -- 第三个关键字 key3 ，处理类似 key1
                              set location = LOCATE(',',keyvalue);
                              if(location > 0) then
                                  set key3 = SUBSTRING(keyvalue,1,location-1);
                                  #set keyvalue = SUBSTRING(keyvalue,location+1);
                              else
                                  if(keyvalue is not null and keyvalue != '') then
                                      set key3 = keyvalue;
                                  end if;
                              end if;
                          else
                          -- 如果不包含指定字符，则 keyvalue 就是关键字
                              if(keyvalue is not null and keyvalue != '') then
                                  set key2 = keyvalue;
                              end if;
                          end if;
                      else
                          set key1 = keyvalue;
                      end if;
                  end if;
                  -- 处理三个键值，方便后面查询语句的使用
                  if(key1 is not null and key1 != '') then
                      if(key2 is null or key2 = '') then
                          set key2 = key1;
                      end if;
                      if(key3 is null or key3 = '') then
                          set key3 = key1;
                      end if;
                  else
                  -- 如果三个键的值都是空，说明关键字是 iname
                      set key1 = iname;
                      set key2 = iname;
                      set key3 = iname;
                  end if;
                  -- 处理类别，如果类别存在，增加按类别查找的条件
                  if(cate is not null and cate != '') then
                      -- "INSERT INTO Table(ID,NAME) SELECT ID,NAME FROM OtherTable;"
                      -- 把从OtherTable中查出的数据插入Table表中
                      INSERT INTO tmpTable(Name,Count) 
                      SELECT iname,COUNT(*) FROM wg_tasks t LEFT JOIN wg_users u ON t.uid=u.id 
                      WHERE u.region=cWard AND t.status='T' AND t.exectime BETWEEN dStart AND dEnd AND t.cateName=cate 
                      AND (t.advText LIKE CONCAT('%',key1,'%') OR t.advText LIKE CONCAT('%',key2,'%') OR t.advText LIKE CONCAT('%',key3,'%'));
                  else
                      INSERT INTO tmpTable(Name,Count) 
                      SELECT iname,COUNT(*) FROM wg_tasks t LEFT JOIN wg_users u ON t.uid=u.id 
                      WHERE u.region=cWard AND t.status='T' AND t.exectime BETWEEN dStart AND dEnd 
                      AND (t.advText LIKE CONCAT('%',key1,'%') OR t.advText LIKE CONCAT('%',key2,'%') OR t.advText LIKE CONCAT('%',key3,'%'));     
                  end if;
              else
              -- 非任务的项目暂不处理，但是为了显示，依然取出
                  INSERT INTO tmpTable(Name,Count) SELECT iname,'';
              end if;
            end if;
          UNTIL done END REPEAT;
        CLOSE cw;
       -- 查找临时表，把数据返回
       SELECT * FROM tmpTable;
    END;
END;

关于上面SQL代码用到的一些知识点：
1、临时表：
创建临时表后要跟 BEGIN…END，在此BEGIN…END中做存储过程的具体的处理，否则创建临时表时容易引起错误。

注意：临时表的空间占用受 tem_table_size 限制，到达上限后MySQL自动把它转化为基于磁盘的MyISAM表保存到磁盘，可以包含TEXT, BLOB等字段。与之相似的有一个内存表，内存表的空间占用受 max_heap_table_size 限制，这个变量支持动态改变，即set @max_heap_table_size=1024M ,但对于已经存在的内存表没有什么作用，除非表被重新创建(create table)或者修改(alter table)或者truncate table，到达上限后会提示数据满错误，不能包含TEXT,BLOB等字段。

2、用到的两个字符串处理函数：
LOCATE(substr, str); LOCATE(substr, str, pos);
返回子串 substr 在字符串 str 中第一次出现的位置，如果使用 pos 参数则从 pos 位置后开始查找，如果在 str 中未找到子串 substr 返回 0；如代码 LOCATE(‘a’, ‘abcde’) 返回 1，可见在某些情况下 mysql 的下标是从 1 开始的。速度上比 like 稍快，但是不能使用索引。
SUBSTRING(str, pos); SUBSTRING(str, pos, length);
返回从 pos 位置开始截取 str 得到的字符串，如果使用 length 参数则截取 <=length 长度的字符串（剩余字符不够length长度时截取到最后）；如果 pos 参数的值是负数（如 -5）则从 str 最后一位向前数5位开始截取字符串。
3、表复制：
格式1：INSERT INTO Table2(field1, field2…) SELECT va,vb… FROM Table1
注意点：表 Table2 必须存在，相应的各 field 字段也必须存在，不仅可以插入 Table1 的字段还可以插入常量。
格式2：SELECT v1,v2… INTO Table2 FROM Table1
注意点：要求表 Table2 不存在，在插入时会自动创建表 Table2，并将Table1中的指定字段的数据复制到Table2中。

结束语：
人生就是要和对的人，以大家都舒服的方式，一起度过。