mariadb/mysql-test/main/sp-cursor-dynamic-debug.test

--source include/have_debug.inc

--echo #
--echo # MDEV-33830 Support for cursors on prepared statements
--echo #

--echo #
--echo # Without USING clause
--echo #

--echo # SQL Standard style: DECLARE c FOR stmt; OPEN c;

DELIMITER $$;
CREATE PROCEDURE p1()
BEGIN
  DECLARE c0 CURSOR FOR stmt0;
  PREPARE stmt0 FROM 'SELECT 1,2,3';
  OPEN c0;
  CLOSE c0;
END;
$$
DELIMITER ;$$
SHOW PROCEDURE CODE p1;
CALL p1;
DROP PROCEDURE p1;


--echo # Oracle style #1: OPEN c FOR dynamic_string

DELIMITER $$;
CREATE PROCEDURE p1()
BEGIN
  DECLARE c0 SYS_REFCURSOR;
  OPEN c0 FOR 'SELECT 1,2,3';
  CLOSE c0;
END;
$$
DELIMITER ;$$
SHOW PROCEDURE CODE p1;
CALL p1;
DROP PROCEDURE p1;


--echo # Oracle style #2: OPEN c FOR select_statement

DELIMITER $$;
CREATE PROCEDURE p1()
BEGIN
  DECLARE c0 SYS_REFCURSOR;
  OPEN c0 FOR SELECT 1,2,3;
  CLOSE c0;
END;
$$
DELIMITER ;$$
SHOW PROCEDURE CODE p1;
CALL p1;
DROP PROCEDURE p1;


--echo #
--echo # With USING clause
--echo #

--echo # SQL Standard style: DECLARE c FOR stmt; OPEN c USING..;

DELIMITER $$;
CREATE PROCEDURE p1()
BEGIN
  DECLARE c0 CURSOR FOR stmt0;
  PREPARE stmt0 FROM 'SELECT 1,2,3 FROM DUAL WHERE ?+?+?>0';
  OPEN c0 USING 1,2,3;
  CLOSE c0;
  DEALLOCATE PREPARE stmt0;
END;
$$
DELIMITER ;$$
SHOW PROCEDURE CODE p1;
CALL p1;
DROP PROCEDURE p1;


--echo # Oracle style #1: OPEN c FOR dynamic_string USING..;

DELIMITER $$;
CREATE PROCEDURE p1()
BEGIN
  DECLARE c0 SYS_REFCURSOR;
  OPEN c0 FOR 'SELECT 1,2,3 FROM DUAL WHERE ?+?+?>0' USING 1,2,3;
  CLOSE c0;
END;
$$
DELIMITER ;$$
SHOW PROCEDURE CODE p1;
CALL p1;
DROP PROCEDURE p1;