如何使用準備好的語句從 MySQL 儲存過程返回結果集？

DELIMITER $$

CREATE PROCEDURE List_IL()

BEGIN

 DECLARE Project_Number_val VARCHAR( 255 );
 DECLARE Temp_List_val VARCHAR(255);
 DECLARE Project_List_val VARCHAR(255);
 DECLARE FoundCount INT;
 DECLARE Project_Number INT;
 DECLARE db_Name VARCHAR(255);



 DECLARE no_more_rows BOOLEAN;
 DECLARE loop_cntr INT DEFAULT 0;
 DECLARE num_rows INT DEFAULT 0;


 DECLARE   projects_cur    CURSOR FOR
   SELECT  Project_Id
   FROM    Project_Details;


 DECLARE CONTINUE HANDLER FOR NOT FOUND
   SET no_more_rows = TRUE;


 OPEN projects_cur;
 select FOUND_ROWS() into num_rows;

 the_loop: LOOP

   FETCH  projects_cur
   INTO   Project_Number_val;


   IF no_more_rows THEN
       CLOSE projects_cur;
       LEAVE the_loop;
   END IF;


SET Project_List_val = CONCAT(Project_Number_val, '_List');
SET db_Name='panel';


SELECT COUNT(1) INTO  FoundCount  FROM information_schema.tables WHERE table_schema = `db_Name`  AND table_name = `Project_List_val`;

IF FoundCount = 1 THEN
SET @Project_Number=Project_Number_val;

SET @sql = CONCAT(' SELECT Panel_Id,', Project_Number_val,'
                   FROM ', @Project_List_val,' Where status=1');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;

   SET loop_cntr = loop_cntr + 1;
 END LOOP the_loop;


END $$

在上面的儲存過程中，如何獲取在執行準備好的語句期間選擇的所有行以及循環終止後我想返回整個結果集，無論哪個呼叫儲存過程。你能幫我怎麼做嗎？

我會建議你一個解決方案創建一個這樣的日誌表

CREATE TABLE log_records
(
Panel_Id INT,
Project_Number_val VARCHAR(255)
);

在您的程式碼中添加如下行，我會將這些記錄插入到您選擇的 log_table 中，請查看 /**Added by abdul */

   IF FoundCount = 1 THEN
       SET @Project_Number=Project_Number_val;

       SET @sql = CONCAT(' SELECT Panel_Id,', Project_Number_val,'    FROM ', @Project_List_val,' Where status=1');


   /*--Added by Abdul*/
       SET @insert_sql = CONCAT ('INSERT INTO log_records' ,'SELECT Panel_Id,', Project_Number_val,'    FROM ', @Project_List_val,' Where status=1');
       PREPARE stmt_insert FROM @insert_sql;
       EXECUTE stmt_insert;
       DEALLOCATE PREPARE stmt_insert; 
   /**--End**/     

       PREPARE stmt FROM @sql;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;
   END IF;

然後在哪裡循環結束添加行

SELECT * FROM log_records;

這將為您提供所需的結果。

還要在打開游標之前在程式碼中寫一行，否則每個過程呼叫都會在該表中附加數據。

TRUNCATE TABLE log_table;

引用自：https://dba.stackexchange.com/questions/36561