Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
Currently prepared statements can only be executed and only a single row may be fetched. This is a severe limitation, in particular when it comes to stored procedures. This limits the support for Dynamic SQL we have and makes migration from, say, Oracle, real difficult. Oracle has support for cursors on Dynamic SQL.
This is a suggested syntax:
CREATE OR REPLACE PROCEDURE do_select1(tab VARCHAR(64), id INTEGER) |
BEGIN
|
DECLARE no_data BOOL DEFAULT FALSE; |
DECLARE v_c1 VARCHAR(100); |
DECLARE c1 CURSOR; |
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_data = TRUE; |
|
|
PREPARE s1 FROM CONCAT('SELECT c1 FROM ', tab, ' WHERE id = ?'); |
OPEN c1 FOR s1 USING id; |
LOOP
|
FETCH c1 INTO v_c1; |
INSERT INTO othertable VALUES(v_c1); |
END LOOP; |
CLOSE c1; |
END; |
Cursor FOR-loops should also be supported:
CREATE OR REPLACE PROCEDURE do_select2(tab VARCHAR(64), id INTEGER) |
BEGIN
|
DECLARE v_c1 VARCHAR(100); |
DECLARE c1 CURSOR; |
|
|
PREPARE s1 FROM CONCAT('SELECT c1 FROM ', tab, ' WHERE id = ?'); |
|
|
FOR v_c1 IN c1(id) FOR s1 |
LOOP
|
INSERT INTO othertable VALUES(v_c1); |
END LOOP; |
END; |
Attachments
Issue Links
- relates to
-
MDEV-10152 Add support for REF CURSOR
-
- Open
-
-
MDEV-20034
sql_mode="oracle" does not support stored code returning REFCURSOR or SYS_REFCURSOR
-
- Open
-