mysql游标不加载变量

Question

我无法让 FETCH 语句在下面的代码中正常工作。它实际上并没有将任何数据放入变量 registerName 中。 registerName 的值与 FETCH 语句之前的值相同。谢谢!

-- Declare variables/cursors needed for building pivot query
DECLARE qry VARCHAR(8000);
DECLARE registerName VARCHAR(128) DEFAULT '';
DECLARE done BOOLEAN DEFAULT 0;

DECLARE registers CURSOR
FOR
SELECT RegisterName
FROM Register r
INNER JOIN EgaugeDevice ed ON ed.id = r.EgaugeDeviceId
INNER JOIN Site s ON s.id = ed.SiteId
INNER JOIN Facility f ON f.id = s.FacilityId
WHERE ShowInSite = 1 AND FacilityName = FACILITY;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

-- Use temporary table to get results from instantaneous view
CREATE TEMPORARY TABLE IF NOT EXISTS instData (
    id INT,
    RegisterId INT,
    InstantaneousValue BIGINT,
    Date_Time DATETIME,
    Time_Stamp BIGINT
);

TRUNCATE TABLE instData;

INSERT INTO instData(id, RegisterId, InstantaneousValue, Date_Time, Time_Stamp)
SELECT id, RegisterId, InstantaneousValue, Date_Time, Time_Stamp
FROM vRegisterDataInstantaneous
WHERE Date_Time >= now() - INTERVAL 1 DAY
ORDER BY Time_Stamp DESC;

-- build pivot query from registers listed in Register table
OPEN registers;

FETCH registers INTO registerName;

select registerName AS Test;

CLOSE registers;

Answer 1

列和变量 RegisterName 是否可以不同名？这可能会导致冲突，Local Variable Scope and Resolution