MySql PREPARE stmt FROM [function argument] 和 CURSORs

Question

我有一个 MySql 函数，它接受 3 个参数。最后一个参数是一个查询本身，我可以使用 PREPARE(?) 执行它，至少在它没有多个结果的情况下是这样。我想遍历从 PREPARED 语句返回的所有结果。

我该怎么做？我在考虑 CURSOR，但我能发现的是不可能在 PREPARED 变量语句上使用 CURSOR。

我想要实现的是:1. 我在我的申请中看到了结果。结果被分页。2. 我想搜索特定行，找到它所属的页面，并将其放入 View 中。3. 结果 View 可以通过多种方式进行过滤和排序，因此 MySql 函数的第三个参数是结果 View 中填充的查询。

希望我说清楚了，否则让我知道。

到目前为止，我有以下内容: DELIMITER $$ # 否则不能使用分号结束一行

/*
The bar graph (clsBarGraph) shows cows in pages.
If you wish to search for a page with a certain Cow ID you need to do alot of things.
Therefore this function is created, to do the heavy lifting all in the database engine.accessible
@param LongCowID The long cow ID of the cow you wish to get the page for
@param ItemsPerPage To determine on what page a cow comes, it is necessary to known how many cows will fit into a page
@param SelectQuery The query that was used to view the data in the BarGraph. This determines ordering, which cows to have in the resultset to limit on, etc.
        This should be without the limit
@return The page number to set the view to, or -1 if the cow does not exist.
*/
CREATE FUNCTION `GetPageForCowID`(LongCowID INT, ItemsPerPage INT, SelectQuery VARCHAR(255))
RETURNS INT
BEGIN
DECLARE `page` INT;

/* Prepares queries to execute */
PREPARE stmt_CheckIfCowExists FROM 'SELECT COUNT(`Long_Cow_ID`) as `rows` FROM `cow_data` INTO @NumberOfRows';
EXECUTE stmt_CheckIfCowExists;
IF @NumberOfRows = 1 THEN 
    /* The cow does nto exist */
    SET `page` = -1;
ELSE
    /* The cow does exist */
    /* Get all the cows used in the view of the data, without a limit and put it into a variable */
    SET @SelectQuery = CONCAT(@SelectQuery, ' INTO @CowsForDataView');
    PREPARE stmt_SelectDataForView FROM @SelectQuery;
    EXECUTE stmt_SelectDataForView;
    SELECT COUNT(*) FROM stmt_SelectDataForView;

    DEALLOCATE PREPARE stmt_SelectDataForView;
END if;

/* Clean Up */
deallocate PREPARE stmt_CheckIfCowExists;
return `page`;
END

提前致谢。

Answer 1

将结果放入临时表:

SET @sql := CONCAT('CREATE TEMPORARY TABLE tmp_GetPageForCowID ', SelectQuery);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

然后可以在临时表的内容上迭代游标。