Mysql 存储过程中使用游标循环读取临时表

游标

游标(Cursor)是用于查看或者处理结果集中的数据的一种方法。游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。

游标的使用方式

定义游标：Declare 游标名称 CURSOR for table;(table也可以是select出来的结果集)
打开游标：Open 游标名称;
从结果集获取数据到变量：fetch 游标名称 into field1,field2;
执行语句：执行需要处理数据的语句
关闭游标：Close 游标名称;

BEGIN
  # 声明自定义变量
  declare c_stgId int;
  declare c_stgName varchar(50);
  # 声明游标结束变量
  declare done INT DEFAULT 0;

  # 声明游标 cr 以及游标读取到结果集最后的处理方式
  declare cr cursor for select Name,StgId from StgSummary limit 3;
  declare continue handler for not found set done = 1;

  # 打开游标
  open cr;

  # 循环
  readLoop:LOOP
    # 获取游标中值并赋值给变量
    fetch cr into c_stgName,c_stgId;
    # 判断游标是否到底，若到底则退出游标
    # 需要注意这个判断
    IF done = 1 THEN
      LEAVE readLoop;
    END IF; 

      SELECT c_stgName,c_stgId;

  END LOOP readLoop;
  -- 关闭游标
  close cr;
END

声明变量Declare语句注意点：

  • Declare语句通常用来声明本地变量、游标、条件或者handler
  • Declare语句只允许出现在BEGIN...END语句中而且必须出现在第一行
  • Declare的顺序也有要求，通常是先声明本地变量，再是游标，然后是条件和handler

自定义变量命名注意点：

自定义变量的名称不要和游标的结果集字段名一样。若相同会出现游标给变量赋值无效的情况。

临时表

临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表，并且操作属于本连接的临时表。
与普通创建语句的区别就是使用 TEMPORARY 关键字

CREATE TEMPORARY TABLE StgSummary(
 Name VARCHAR(50) NOT NULL,
 StgId INT NOT NULL DEFAULT 0
);

临时表使用限制

  1. 在同一个query语句中，只能查找一次临时表。同样在一个存储过程中也不能多次查询临时表。但是不同的临时表可以在一个query中使用。
  2. 不能用RENAME来重命名一个临时表，但是可以用ALTER TABLE代替
ALTER TABLE orig_name RENAME new_name;
  • 临时表使用完以后需要主动Drop掉
DROP TEMPORARY TABLE IF EXISTS StgTempTable;

存储过程中使用游标循环读取临时表数据

BEGIN
## 创建临时表
CREATE TEMPORARY TABLE if not exists StgSummary(
 Name VARCHAR(50) NOT NULL,
 StgId INT NOT NULL DEFAULT 0
);
TRUNCATE TABLE StgSummary;

## 新增临时表数据
INSERT INTO StgSummary(Name,StgId)
select '临时数据',1

BEGIN

# 自定义变量
declare c_stgId int;
declare c_stgName varchar(50);
declare done INT DEFAULT 0;

declare cr cursor for select Name,StgId from StgSummary ORDER BY StgId desc LIMIT 3;
declare continue handler for not found set done = 1;

-- 打开游标
open cr;
testLoop:LOOP
	-- 获取结果
	fetch cr into c_stgName,c_stgId;
	IF done = 1 THEN
		LEAVE testLoop;
	END IF; 

  SELECT c_stgName,c_stgId;

END LOOP testLoop;
-- 关闭游标
close cr;

End;
DROP TEMPORARY TABLE IF EXISTS StgSummary;
End;

最开始的时候，先创建临时表，再定义游标。但是存储过程无论如何都保存不了。直接报错You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE ...根本原因就是上面提到的注意点（Declare语句只允许出现在BEGIN...END语句中而且必须出现在第一行）。所以最后只能多个加一对BEGIN...END进行隔开。

总结

以前写SQL Server的存储过程，没有仔细注意过这个问题，定义变量一般都在程序中部，MySQL就想当然的随便写，最后终于踩坑了。这两个语法上差别不大，但是真遇到差别还是挺突然的。不过也好久没有写SQL语句，有点生疏了啊。还是赶紧把坑给记下来，加深下印象吧。

以上就是Mysql 存储过程中使用游标循环读取临时表的详细内容，更多关于MySQL 游标循环读取临时表的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySQL游标概念与用法详解

    本文实例讲述了MySQL游标概念与用法.分享给大家供大家参考,具体如下: 1.游标的概念(Cursor) 一条sql,对应N条资源,取出资源的接口,就是游标,沿着游标,可以一次取出1行.如果开发过安卓的同学应该知道有一个Api是Cursor,也是读取SQLite数据库用的,和这个有点类似. 2.使用游标的步骤 (1)声明 使用declare进行声明 declare 游标名 cursor for select_statement (2)打开游标 使用open进行打开 open 游标名 (3)从游标

  • 详解Mysql 游标的用法及其作用

    [mysql游标的用法及作用] 例子: 当前有三张表A.B.C其中A和B是一对多关系,B和C是一对多关系,现在需要将B中A表的主键存到C中: 常规思路就是将B中查询出来然后通过一个update语句来更新C表就可以了,但是B表中有2000多条数据, 难道要执行2000多次?显然是不现实的:最终找到写一个存储过程然后通过循环来更新C表, 然而存储过程中的写法用的就是游标的形式. [简介] 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制. ​ 游标充当指针的作用. ​ 尽管游标能

  • mysql存储过程之游标（DECLARE）原理与用法详解

    mysql存储过程之游标（DECLARE）原理与用法详解

    本文实例讲述了mysql存储过程之游标(DECLARE)原理与用法.分享给大家供大家参考,具体如下: 我们在处理存储过程中的结果集时,可以使用游标,因为游标允许我们迭代查询返回的一组行,并相应地处理每行.mysql的游标为只读,不可滚动和敏感三种模式,我们来看下: 只读:无法通过光标更新基础表中的数据. 不可滚动:只能按照select语句确定的顺序获取行.不能以相反的顺序获取行. 此外,不能跳过行或跳转到结果集中的特定行. 敏感:有两种游标:敏感游标和不敏感游标.敏感游标指向实际数据,不敏感游标

  • mysql的存储过程、游标 、事务实例详解

    mysql的存储过程.游标 .事务实例详解 下面是自己曾经编写过的mysql数据库存储过程,留作存档,以后用到的时候拿来参考. 其中,涉及到了存储过程.游标(双层循环).事务. [说明]:代码中的注释只针对当时业务而言,无须理会. 代码如下: DELIMITER $$ DROP PROCEDURE IF EXISTS `transferEmailTempData`$$ CREATE PROCEDURE transferEmailTempData(IN jobId VARCHAR(24)) BEG

  • Mysql存储过程循环内嵌套使用游标示例代码

    BEGIN -- 声明变量 DECLARE v_addtime_begin varchar(13); DECLARE v_addtime_end varchar(13); DECLARE v_borrow_id int; DECLARE v_count int; DECLARE s1 int; /** 声明游标,并将查询结果存到游标中 **/ DECLARE c_borrow CURSOR FOR SELECT ID from rocky_borrow WHERE BORROWTYPE = 2

  • mysql游标的原理与用法实例分析

    本文实例讲述了mysql游标的原理与用法.分享给大家供大家参考,具体如下: 本文内容: 什么是游标 创建游标 使用游标 首发日期:2018-04-18 什么是游标: 如果你前面看过mysql函数,会发现无法使用返回多行结果的语句.但如果你又确实想要使用时,就需要使用到游标,游标可以帮你选择出某个结果(这样就可以做到返回单个结果). 另外,使用游标也可以轻易的取出在检索出来的行中前进或后退一行或多行的结果. 游标可以遍历返回的多行结果. 补充: Mysql中游标只适用于存储过程以及函数. 创建游标

  • mysql声明游标的方法

    mysql声明游标的方法: 1.声明变量和游标 declare result varchar(3000); declare flag1 varchar(100); DECLARE done INT DEFAULT FALSE; declare cur1 CURSOR FOR SELECT flag from bmlx; 2.变量赋值 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; set result = ''; 3.打开游标和循环

  • 带你彻底搞懂python操作mysql数据库(cursor游标讲解)

    带你彻底搞懂python操作mysql数据库(cursor游标讲解)

    1.什么是游标? 一张图讲述游标的功能: 图示说明: 2.使用游标的好处? 如果不使用游标功能,直接使用select查询,会一次性将结果集打印到屏幕上,你无法针对结果集做第二次编程.使用游标功能后,我们可以将得到的结果先保存起来,然后可以随意进行自己的编程,得到我们最终想要的结果集. 3.利用python连接数据库,经常会使用游标功能 1)以python连接mysql数据库为例 2)使用游标的操作步骤 首先,使用pymysql连接上mysql数据库,得到一个数据库对象. 然后,我们必须要开启数据

  • Mysql 存储过程中使用游标循环读取临时表

    游标 游标(Cursor)是用于查看或者处理结果集中的数据的一种方法.游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力. 游标的使用方式 定义游标:Declare 游标名称 CURSOR for table;(table也可以是select出来的结果集) 打开游标:Open 游标名称; 从结果集获取数据到变量:fetch 游标名称 into field1,field2; 执行语句:执行需要处理数据的语句 关闭游标:Close 游标名称; BEGIN # 声明自定义变量 declare

  • MySQL存储过程中使用WHILE循环语句的方法

    本文实例讲述了MySQL存储过程中使用WHILE循环语句的方法.分享给大家供大家参考.具体如下: mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc() -> BEGIN -> -> DECLARE i int; -> SET i=1; -> loop1: WHILE i<=10 DO -> IF MOD(i,2)<>0 THEN /*Even num

  • mysql存储过程中使用游标的实例

    复制代码 代码如下: DELIMITER $$ DROP PROCEDURE IF EXISTS getUserInfo $$ CREATE PROCEDURE getUserInfo(in date_day datetime)-- -- 实例-- MYSQL存储过程名为:getUserInfo-- 参数为:date_day日期格式:2008-03-08--    BEGINdeclare _userName varchar(12); -- 用户名declare _chinese int ; -

  • MySQL存储过程中实现执行动态SQL语句的方法

    本文实例讲述了MySQL存储过程中实现执行动态SQL语句的方法.分享给大家供大家参考.具体实现方法如下: mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE set_col_value -> (in_table VARCHAR(128), -> in_column VARCHAR(128), -> in_new_value VARCHAR(1000), -> in_where VARCHAR(4

  • 浅谈MySQL存储过程中declare和set定义变量的区别

    在存储过程中常看到declare定义的变量和@set定义的变量.简单的来说,declare定义的类似是局部变量,@set定义的类似全局变量. 1.declare定义的变量类似java类中的局部变量,仅在类中生效.即只在存储过程中的begin和end之间生效. 2.@set定义的变量,叫做会话变量,也叫用户定义变量,在整个会话中都起作用(比如某个应用的一个连接过程中),即这个变量可以在被调用的存储过程或者代码之间共享数据.如何理解呢?可以看下面这个简单例子,很好理解. (1)先执行下面脚本,创建一

  • MySQL存储过程中游标循环的跳出和继续操作示例

    最近遇到这样的问题,在MySQL的存储过程中,游标操作时,需要执行一个conitnue的操作.众所周知,MySQL中的游标循环操作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅. 1.REPEAT 复制代码 代码如下: REPEAT     Statements;   UNTIL expression END REPEAT demo DECLARE num INT; DECLARE my_string  VARCHAR(255);

  • mysql 存储过程中变量的定义与赋值操作

    一.变量的定义 mysql中变量定义用declare来定义一局部变量,该变量的使用范围只能在begin...end 块中使用,变量必须定义在复合语句的开头,并且是在其它语句之前,也可以同时申明多个变量,如果需要,可以使用default赋默认值. 定义一个变量语法如下: declare var_name[,...] type[default value]看一个变量定义实例 declare last date;二.mysql存储过程变量赋值 变量的赋值可直接赋值与查询赋值来操作,直接赋值可以用set

  • MySQL存储过程中的基本函数和触发器的相关学习教程

    MySQL存储过程的常用函数 一.字符串类 CHARSET(str) //返回字串字符集 CONCAT (string2 [,... ]) //连接字串 INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0 LCASE (string2 ) //转换成小写 LEFT (string2 ,length ) //从string2中的左边起取length个字符 LENGTH (string ) //string长度 LOAD_FI

  • mysql存储过程中的异常处理解析

    定义异常捕获类型及处理方法: DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement handler_action: CONTINUE | EXIT | UNDO condition_value: mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND |

  • MySQL存储过程中使用动态行转列

    MySQL存储过程中使用动态行转列

    本文介绍的实例成功的实现了动态行转列.下面我以一个简单的数据库为例子,说明一下. 数据表结构 这里我用一个比较简单的例子来说明,也是行转列的经典例子,就是学生的成绩 三张表:学生表.课程表.成绩表 学生表 就简单一点,学生学号.学生姓名两个字段 CREATE TABLE `student` ( `stuid` VARCHAR(16) NOT NULL COMMENT '学号', `stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名', PRIMARY KEY (`

随机推荐