在MYSQL中使用动态SQL创建游标 [英] Creating cursor with Dynamic SQL in MYSQL

问题描述

我正在编写一个存储过程，该过程将游标打开到表，然后遍历所有记录.在迭代过程中，我基于第一个游标的结果创建一个动态查询.我需要在动态sql上打开游标，但MySQL不允许这样做，因为根据mysql 的官方文档，必须在声明处理程序之前声明游标.必须在声明处理程序和变量之前声明变量和条件声明游标或处理程序". .这是脚本

I am writing a stored procedure which opens a cursor to a table and then iterate through all records. In the iterating process I create a dynamic query based on the results of first cursor. I need to open the cursor on dynamic sql, but MySQL is not allowing to do me so, as accoriding to the official doc of mysql "Cursors must be declared before declaring handlers. Variables and conditions must be declared before declaring either cursors or handlers". Here is the script

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_test$$

CREATE PROCEDURE `sp_test`()
BEGIN
    -- Declarations

    DECLARE prepared_sql VARCHAR(1000);
    DECLARE index_count INT;

    -- Cursors
    DECLARE cursor1 CURSOR FOR SELECT * from table1;
    -- Continue Handler for Cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
    -- Open cursors
    OPEN cursor1;

    -- Business Logic
    all_alerts_loop: LOOP
        -- Fetch record from cursor1 and create a dynamic sql

        -- Check if cursor has reached to end than leave the loop
        IF no_more_rows THEN
            LEAVE all_alerts_loop;
        END IF;


        WHILE @some_other_variable <> 0
        DO
                              -- I want to open cursor 2 on this sql
            -- set @prepared_sql =  'create dynamic sql here';  
                    END WHILE;

                    -- This works fine
        PREPARE stmt FROM @prepared_sql;
        EXECUTE stmt;

                    -- But can't define cursor here? so what is the solution
                    -- Gives syntax error, I have tried with @prepared_sql also rather than stmt
        DECLARE cursor2 CURSOR FOR stmt;

    END LOOP;

    -- closing cursors
    CLOSE cursor1;
    END$$

DELIMITER ;

任何想法如何为动态查询创建游标?在MYSQL中

Any idea how to create cursor for dynamic query? in MYSQL

推荐答案

创建另一个过程，并在此新过程中编写游标的代码，然后从要声明游标的位置调用该过程...

Create an another Procedure and write the code of cursor in this new procedure and then call the procedure from where u want to declare a cursor...

这篇关于在MYSQL中使用动态SQL创建游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！