Support for cursors on prepared statements

  DECLARE cursor_name FOR prepared_statement_name;    -- <dynamic declare cursor>

  PREPARE prepared_statement_name FROM 'SELECT 1,2';  -- Placeholders are also possible

  OPEN cursor_name;                                   -- USING clause is also possible

CREATE OR REPLACE TABLE t1 (id INT, c1 VARCHAR(10));

INSERT INTO t1 VALUES (10,'c10'),(11,'c11'),(12,'c12');

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1(tab VARCHAR(64), id INTEGER)

BEGIN

  DECLARE no_data BOOL DEFAULT FALSE;

  DECLARE v_c1 VARCHAR(100);

  DECLARE v_id INT;

  DECLARE c1 CURSOR FOR s1;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_data = TRUE;

  PREPARE s1 FROM CONCAT('SELECT id, c1 FROM ', tab, ' WHERE id >= ?');

  OPEN c1 USING id;

  fetch_loop: LOOP

    FETCH c1 INTO v_id, v_c1;

    IF no_data THEN

      LEAVE fetch_loop;

    END IF;

    SELECT v_id, v_c1;

  END LOOP;

  CLOSE c1;

END;

$$

DELIMITER ;

CALL p1('t1',11);

CREATE OR REPLACE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1),(2),(3);

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1()

BEGIN

  DECLARE v TEXT;

  DECLARE c CURSOR FOR stmt;

  BEGIN

    DECLARE r ROW TYPE OF c; -- This is wrong

  END;

END;

$$

DELIMITER ;

CALL p1;

CREATE OR REPLACE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1),(2),(3);

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1()

BEGIN

  DECLARE c1 CURSOR FOR stmt;

  PREPARE stmt FROM 'SELECT a FROM t1';

  FOR r IN c1

  DO

    INSERT INTO t2 VALUES(r.a);

  END FOR;

END;

$$

DELIMITER ;

CALL p1;

CREATE OR REPLACE PROCEDURE p1(dynamic_sql VARCHAR) AS

  v1 INT;

  v2 VARCHAR(256);

  c SYS_REFCURSOR;

BEGIN

  OPEN c FOR dynamic_sql;

  FETCH c INTO v1, v2;

  DBMS_OUTPUT.PUT_LINE(v1 || ' ' || v2);

  CLOSE c;

END;

/

CALL p1('SELECT 123, ''v123'' FROM DUAL');

123 v123