- 定义游标
DECLARE column_value varchar(60);
DECLARE done int default 0;
DECLARE liuma CURSOR FOR SELECT DISTINCT t.column_value from ABD.dynamic_column_table t;
DECLARE CONTINUE HANDLER for not found set done = 1;
- 打开游标
OPEN liuma;
- 取游标中的数据
FETCH liuma INTO column_value;
- 关闭游标
CLOSE liuma;
- 释放游标
deallocate liuma;
完整的例子
DROP PROCEDURE IF EXISTS test_p1;
DELIMITER $$
$$ CREATE PROCEDURE test_p1()
label_return:BEGIN
DROP TEMPORARY TABLE IF EXISTS ABD.dynamic_column_table;
CREATE TEMPORARY TABLE ABD.dynamic_column_table(
column_value VARCHAR(64) NULL
);
INSERT INTO ABD.dynamic_column_table(column_value)
VALUES("test1"),("test2");
BEGIN
DECLARE column_value varchar(60);
DECLARE done int default 0;
DECLARE liuma CURSOR FOR SELECT DISTINCT t.column_value from ABD.dynamic_column_table t;-- 定义游标
DECLARE CONTINUE HANDLER for not found set done = 1;
SET @dynamic_sql='';
OPEN liuma;
readLoop:LOOP
FETCH liuma INTO column_value;
-- 循环取出后具体的业务逻辑在这里
SET @dynamic_sql = CONCAT(@dynamic_sql,'max(IF(column_value=\'',column_value,'\'',',value,null)) AS ',column_value,',');
IF done = 1 THEN
LEAVE readLoop;
END IF;
END LOOP readLoop;
CLOSE liuma;
END;
SELECT @dynamic_sql;
END $$
DELIMITER ;
-- 调用
CALL test_p1()
注意点
- DECLARE 字段必须放在BEGIN, END 里面,并且必须放在BEGIN的最前面,不然会报错,所以在循环临时表的时候我们又写了个BEGIN, END. 或者把定义的语句移动到最开头BEGIN的后面。
- 下面的sql必须给表取别名,不然游标取不到数据,不知道为什么, 那个t不能不要。
DECLARE liuma CURSOR FOR SELECT DISTINCT t.column_value from ABD.dynamic_column_table t;-- 定义游标