mysql存储过程

Question

我已经创建了以下存储过程，用于在表panel_user中插入行：

CREATE PROCEDURE panel_users()
BEGIN
DECLARE i INTEGER;
DECLARE user_id BIGINT(20);
DECLARE panel_id BIGINT(20);
DECLARE wt DECIMAL(10,6);
DECLARE level DECIMAL(6,2);
DECLARE total INTEGER;
SET i=1;
SELECT COUNT(*) INTO total FROM smartmeter_usage.users_reporting;
WHILE i<=total DO
  SELECT u.userId INTO user_id, p.panelId INTO panel_id, ur.usage_percent INTO level, ipr.wt_base INTO wt
  FROM mondrainusage.user_d u 
  INNER JOIN smartmeter_usage.imei_phone_reporting ipr ON u.imeiNumber = ipr.IMEI 
  INNER JOIN smartmeter_usage.users_reporting ur ON ur.mobile_number = ipr.mobile_number
  INNER JOIN smartmeter_usage.panel pn ON pn.panel_id = ur.panel_id
  INNER JOIN mondrainusage.panel p ON p.panelName = pn.panel_name
  WHERE u.userId = i;
  INSERT INTO mondrainusage.panel_user (panelId, userId, userWeight, usageLevel)  VALUES(panel_id, user_id, wt, level);
  SET i = i+1;
END WHILE
END

我收到以下错误：

Undeclared variable: p

我不明白我的问题出了什么问题。有出路吗？

编辑：

K，我得到了上述问题的解。以下是解决办法：

CREATE PROCEDURE panel_users()
BEGIN
DECLARE i INTEGER;
DECLARE user_id BIGINT(20);
DECLARE panel_id BIGINT(20);
DECLARE wt DECIMAL(10,6);
DECLARE level DECIMAL(6,2);
DECLARE total INTEGER;
SET i=1;
SELECT COUNT(*) INTO total FROM smartmeter_usage.users_reporting;
WHILE i<=total DO
  SELECT u.userId, p.panelId, ur.usage_percent, ipr.wt_base INTO user_id,  panel_id,  level, wt
  FROM mondrainusage.user_d u 
  INNER JOIN smartmeter_usage.imei_phone_reporting ipr ON u.imeiNumber = ipr.IMEI 
  INNER JOIN smartmeter_usage.users_reporting ur ON ur.mobile_number = ipr.mobile_number
  INNER JOIN smartmeter_usage.panel pn ON pn.panel_id = ur.panel_id
  INNER JOIN mondrainusage.panel p ON p.panelName = pn.panel_name
  WHERE u.userId = i;
  INSERT INTO mondrainusage.panel_user (panelId, userId, userWeight, usageLevel) VALUES(panel_id, user_id, wt, level);
  SET i = i+1;
 END WHILE
END

Bit现在它给了我在第21行“END”附近的语法错误。

为何会这样呢？

编辑：

找到了解决办法：

END WHILE;

Answer 1

以下是一个行之有效的解决方案。

CREATE PROCEDURE panel_users()
BEGIN
DECLARE i INTEGER;
DECLARE user_id BIGINT(20);
DECLARE panel_id BIGINT(20);
DECLARE wt DECIMAL(10,6);
DECLARE level DECIMAL(6,2);
DECLARE total INTEGER;
SET i=1;
SELECT COUNT(*) INTO total FROM smartmeter_usage.users_reporting;
WHILE i<=total DO
  SELECT u.userId, p.panelId, ur.usage_percent, ipr.wt_base INTO user_id,  panel_id,  level, wt
  FROM mondrainusage.user_d u 
  INNER JOIN smartmeter_usage.imei_phone_reporting ipr ON u.imeiNumber = ipr.IMEI 
  INNER JOIN smartmeter_usage.users_reporting ur ON ur.mobile_number = ipr.mobile_number
  INNER JOIN smartmeter_usage.panel pn ON pn.panel_id = ur.panel_id
  INNER JOIN mondrainusage.panel p ON p.panelName = pn.panel_name
  WHERE u.userId = i;
  INSERT INTO mondrainusage.panel_user (panelId, userId, userWeight, usageLevel) VALUES(panel_id, user_id, wt, level);
  SET i = i+1;
 END WHILE;
END