将表作为变量传递给mysql存储过程中的循环

I want to store the result of my stored procedure based on the table passed as a parameter and then make a loop from it so that I can update the selected rows.

我想基于作为参数传递的表存储我的存储过程的结果,然后从它做一个循环,以便我可以更新选定的行。

CREATE DEFINER=`root`@`localhost` PROCEDURE `close_transaction_procedure`(IN `tablename` VARCHAR(100), IN `businessdate_column` VARCHAR(40), IN `primary_number` VARCHAR(30), IN `lead_time` INT)
BEGIN

SET @strprd = CONCAT('SELECT ',primary_number, ', status_code FROM ',tablename,' WHERE ',businessdate_column ,' < DATE_SUB(NOW(), INTERVAL ', lead_time ,' DAY)');

PREPARE stmt1 FROM @strprd;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

-- loop based on results of execute stmt1

END

1 个解决方案

#1

You could try to update without a loop, using a temporary table to store the results of the select:

您可以尝试在没有循环的情况下进行更新,使用临时表来存储select的结果:

CREATE DEFINER=`root`@`localhost` PROCEDURE `close_transaction_procedure`(IN `tablename` VARCHAR(100), IN `businessdate_column` VARCHAR(40), IN `primary_number` VARCHAR(30), IN `lead_time` INT)
BEGIN

/* create a temporary table where you'll store your select's result */
DROP TEMPORARY TABLE IF EXISTS temp_records;
CREATE TEMPORARY TABLE IF NOT EXISTS temp_records
(
  primary_number VARCHAR(100),
  status_code VARCHAR(100)
);

/* store the result of the select into temp_records with the INSERT...SELECT construct */
SET @strprd = CONCAT('INSERT INTO temp_records(primary_number, status_code) SELECT ',primary_number, ', status_code FROM ',tablename,' WHERE ',businessdate_column ,' < DATE_SUB(NOW(), INTERVAL ', lead_time ,' DAY)');

PREPARE stmt1 FROM @strprd;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

/* now that you have your result in temp_records table, you can update without a loop, using temp_records table as reference */

END