我想基于作为参数传递的表存储存储过程的结果,然后从中进行循环,以便更新所选的行。

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

最佳答案

您可以尝试使用临时表存储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

关于php - 将表作为变量传递以在mysql存储过程中循环,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/30047689/

10-09 20:23
查看更多