mysql - 将变量传递到 CURSOR 的 SELECT 查询 (MySQL)
问题描述
我正在尝试下面的存储过程。但是,当我在“DECLARE cur1”行中传递实际列名时,SP 返回正确的值,但是当我传递变量名即输入参数 (colName) 时,它返回 0。我在下面的代码中添加了注释。代码是否正确?
DROP PROCEDURE IF EXISTS test1.checkHardcodedField;
CREATE PROCEDURE test1.checkHardcodedField(IN textValue CHAR(10), colName CHAR(10), OUT counter VARCHAR(100))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE temp CHAR(16);
DECLARE i INT DEFAULT 0;
-- Issue with this statement, returns incorrect value.
DECLARE cur1 CURSOR FOR SELECT colName FROM data1;
-- This statement works. MSH3 is actual column name
-- DECLARE cur1 CURSOR FOR SELECT MSH3 FROM data1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO temp;
IF done THEN
LEAVE read_loop;
END IF;
IF temp = textValue THEN
SET i = i + 1;
END IF;
END LOOP;
SET counter = i;
CLOSE cur1;
END;
解决方案
CREATE PROCEDURE procedure_name (..., fieldname VARCHAR, ...)
BEGIN
CREATE TEMPORARY TABLE tmp (field VARCHAR(10));
SET @sql := CONCAT('INSERT INTO tmp SELECT ', fieldname, ' FROM tablename;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
BEGIN
DECLARE cur CURSOR FOR SELECT field FROM tmp;
...
END;
DROP TABLE tmp;
END
推荐阅读
- python-3.6 - 错误:db 类型为 dbm.gnu,但该模块在 windows 中不可用
- javascript - 如何知道我的 gps 朝哪个方向前进?
- c# - 如何关闭标签页?
- r - dplyr 计算某些文本
- python - Python将redis对象转换为原始数据类型
- python - 使用 python 字典
- android-studio - 模拟器:coreaudio:无法初始化播放
- selenium - Can we pass priority and enabled from xls sheet for TestNg test?
- python - 在 Pandas 中通过窗口子句实现 MSSQL 的分区
- excel - 如何正确设置POST参数以接收指定日期的持股信息?