我正在尝试对某些表运行相同的查询并从中检索数据,但在运行查询后,它说 0 行受到影响,结果表为空。 我正在使用光标。 我无法理解问题是什么
delimiter //
drop procedure if exists hunt //
create procedure hunt()
begin
DECLARE done int default false;
DECLARE table_name CHAR(255);
declare sqll longtext;
DECLARE cur1 cursor for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA = "xyz_database" and table_name LIKE "%vendor%" ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur1;
myloop: loop
fetch next from cur1 into table_name;
if done then
leave myloop;
end if;
set sqll ='INSERT INTO Task1_AverageCapacities SELECT AVG(capacity) as AverageCapacity
FROM (SELECT cycle,MAX(mAh_transferred_during_discharging) as capacity
FROM'+table_name+' where cycle<101
GROUP BY cycle) AS avg_capacity;';
PREPARE stmt FROM @sqll;
EXECUTE stmt;
DEALLOCATE PREPARE stmt ;
end loop;
close cur1;
end //
delimiter ;
call hunt();
您需要使用CONCAT()
函数来执行字符串连接,而不是+
运算符。
而且您需要在FROM
和表名之间留一个空格。
set sqll = CONCAT('INSERT INTO Task1_AverageCapacities SELECT AVG(capacity) as AverageCapacity
FROM (SELECT cycle,MAX(mAh_transferred_during_discharging) as capacity
FROM ', table_name, ' where cycle<101
GROUP BY cycle) AS avg_capacity;');
变量的名称是sqll
,而不是@sqll
,所以你用:
PREPARE stmt FROM sqll;