MySQL游标获取NULL [英] MySQL cursor fetch NULL
本文介绍了MySQL游标获取NULL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
为什么我的两个变量都输出NULL
? SELECT
光标的一部分正常工作.
Why both my variables output NULL
? SELECT
part of the cursor is working properly.
CREATE PROCEDURE p2()
BEGIN
# Account table
DECLARE accountid INT;
DECLARE accountname VARCHAR(1000);
# 1. cursor finished/done variable comes first
DECLARE done INT DEFAULT 0;
# 2. the curser declaration and select
DECLARE c_account_id_name CURSOR FOR SELECT
accountid,
accountname
FROM temp.test;
# 3. the continue handler is defined last
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
OPEN c_account_id_name;
SET accountid = 0;
SET accountname = '';
read_loop: LOOP
FETCH c_account_id_name
INTO accountid, accountname;
IF done
THEN
LEAVE read_loop;
END IF;
SELECT accountname;
END LOOP;
END;
推荐答案
游标中的变量和select属性不能相同...这是一个MySQL错误. 这将起作用
Variable and select attribute in cursor can't be the same...it's a MySQL bug. This will work
DROP PROCEDURE IF EXISTS p2;
DELIMITER $$
CREATE PROCEDURE p2()
BEGIN
# Account table
DECLARE v_accountidsome INT; #pay attention
DECLARE v_accountnameelst VARCHAR(1000); #pay attention
# 1. cursor finished/done variable comes first
DECLARE v_done INT DEFAULT FALSE;
# 2. the cursor declaration and select
DECLARE c_account_id_name CURSOR FOR SELECT
accountid, #pay attention
accountname #pay attention
FROM temp.test;
# 3. the continue handler is defined last
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done = TRUE;
OPEN c_account_id_name;
read_loop: LOOP
FETCH c_account_id_name
INTO v_accountidsome, v_accountnameelst;
IF v_done
THEN
LEAVE read_loop;
END IF;
SELECT v_accountidsome;
SELECT v_accountnameelst;
END LOOP;
CLOSE c_account_id_name;
END $$
DELIMITER ;
CALL p2();
在此处
这篇关于MySQL游标获取NULL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文