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

游标

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

游标的使用方式

1. 定义游标：Declare 游标名称 CURSOR for table;(table也可以是select出来的结果集)
2. 打开游标：Open 游标名称;
3. 从结果集获取数据到变量：fetch 游标名称 into field1,field2;
4. 执行语句：执行需要处理数据的语句
5. 关闭游标：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;

3. 临时表使用完以后需要主动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语句，有点生疏了啊。还是赶紧把坑给记下来，加深下印象吧。