mysql - 如何使用execute语句更新表？

Question

我有两个表(此处已大大简化):

QUADRI

ID SBR_750 b10C  TGI
---------------------
Q1    0      1    0
Q2    2      1    0
Q3    1      0    1

赛勒

CELLANAME NEEDED READY
----------------------
SBR_750    NULL   12
b10C       NULL   10
TGI        NULL    5

我想要 CELLE 中的结果:

CELLANAME NEEDED READY
------------------------
SBR_750     3     12
b10C        2     10
TGI         1      5

我尝试编写一个存储过程，但它不起作用:错误 1210。EXECUTE 的参数不正确。

这是代码:

CREATE DEFINER=`root`@`%.zamberlan.local` PROCEDURE `AggiornaCelle`(IN nomecella varchar(15))
BEGIN
set @s='update celle set needed=(select sum(?) from quadri) where cellaname=?';
set @NC=nomecella;
prepare stmt from @s;
execute stmt using @NC;
deallocate prepare stmt;
END

更新:它不起作用，所以我改变策略:

CREATE DEFINER=`root`@`%.zamberlan.local` PROCEDURE `AggiornaCelle`()
BEGIN
declare i int;
declare num_rows int;
declare col_name varchar(20);
DECLARE col_names CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = quadri
  ORDER BY ordinal_position;


select FOUND_ROWS() into num_rows;

SET i = 1;
the_loop: LOOP

   IF i > num_rows THEN
        CLOSE col_names;
        LEAVE the_loop;
    END IF;


    FETCH col_names 
    INTO col_name;     

     update celle set needed=sum(col_name) where cellaname=col_name;

    SET i = i + 1;  
END LOOP the_loop;
END

灵感来自mysql, iterate through column names .

但是我收到错误“光标未打开...”

Answer 1

您确实需要使用动态 sql 来执行此操作。 Celle 从 Quadri 知道它需要的所有列，因此您可以根据这一事实插入动态语句的创建。使用光标是一种很好的方法。

drop table if exists quadri;
create table quadri(ID varchar(2),SBR_750 int, b10C int, TGI int);
insert into quadri values
('Q1' ,   0 ,     1 ,   0),
('Q2' ,   2 ,     1 ,   0),
('Q3' ,   1 ,     0 ,   1);

drop table if exists celle;
create table CELLE (CELLANAME varchar(20) ,NEEDED int,READY int);
insert into celle values
('SBR_750'  ,  NULL ,  12),
('b10C'     ,  NULL ,  10),
('TGI'      ,  NULL ,   5);

drop procedure if exists `AggiornaCelle`;
delimiter $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `AggiornaCelle`()
begin
DECLARE done INT DEFAULT FALSE;
declare col_name varchar(20);
declare cur1 CURSOR FOR 
 SELECT cellaname FROM celle ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

open cur1;
read_loop: loop
        fetch cur1 into col_name;
        if done then leave read_loop; end if;
        set @sqlstr = concat('update celle set needed = (select sum(',col_name,') from quadri) where cellaname = ', char(39),col_name,char(39),';'); 
        insert into debug_table (msg) values(@sqlstr);
        prepare stmt from @sqlstr;
        execute stmt ;
        deallocate prepare stmt;
end loop;
close cur1;
end $$
delimiter ;

truncate table debug_table;
call `AggiornaCelle`();
select * from debug_table;
select * from celle;

MariaDB [sandbox]> select * from debug_table;
+----+------------------------------------------------------------------------------------------+------+
| id | msg                                                                                      | MSG2 |
+----+------------------------------------------------------------------------------------------+------+
|  1 | update celle set needed = (select sum(SBR_750) from quadri) where cellaname = 'SBR_750'; | NULL |
|  2 | update celle set needed = (select sum(b10C) from quadri) where cellaname = 'b10C';       | NULL |
|  3 | update celle set needed = (select sum(TGI) from quadri) where cellaname = 'TGI';         | NULL |
+----+------------------------------------------------------------------------------------------+------+
3 rows in set (0.00 sec)

MariaDB [sandbox]> select * from celle;
+-----------+--------+-------+
| CELLANAME | NEEDED | READY |
+-----------+--------+-------+
| SBR_750   |      3 |    12 |
| b10C      |      2 |    10 |
| TGI       |      1 |     5 |
+-----------+--------+-------+
3 rows in set (0.00 sec)

debug_table 的存在只是为了让我可以检查更新语句。