我想基于作为参数传递的表存储存储过程的结果,然后从中进行循环,以便更新所选的行。
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/