mirror of
https://github.com/MariaDB/server.git
synced 2025-10-05 07:19:14 +02:00
856 lines
17 KiB
Text
856 lines
17 KiB
Text
#
|
|
# MDEV-33830 Support for cursors on prepared statements
|
|
#
|
|
# OPEN with a never prepared stmt
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE c CURSOR FOR stmt;
|
|
OPEN c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
ERROR HY000: Unknown prepared statement handler (stmt) given to OPEN
|
|
DROP PROCEDURE p1;
|
|
# OPEN with a deallocated stmt
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT 1';
|
|
DEALLOCATE PREPARE stmt;
|
|
OPEN c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
ERROR HY000: Unknown prepared statement handler (stmt) given to OPEN
|
|
DROP PROCEDURE p1;
|
|
# OPEN with a non-SELECT statement
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'DROP TABLE t1';
|
|
OPEN c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
ERROR 42000: Cursor statement must be a SELECT
|
|
DROP PROCEDURE p1;
|
|
# Bad queries in OPEN
|
|
CREATE PROCEDURE p1(query TEXT)
|
|
BEGIN
|
|
DECLARE v TEXT;
|
|
DECLARE c SYS_REFCURSOR;
|
|
OPEN c FOR query;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1('SELECT syntax error error error');
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'error error' at line 1
|
|
CALL p1('BEGIN; SELECT 1; END');
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1; END' at line 1
|
|
CALL p1('SELECT Unknown');
|
|
ERROR 42S22: Unknown column 'Unknown' in 'SELECT'
|
|
CALL p1('SELECT 1 INTO unknown');
|
|
ERROR 42000: Undeclared variable: unknown
|
|
CALL p1('SELECT 1 INTO v');
|
|
ERROR 42000: Undeclared variable: v
|
|
CALL p1('SELECT 1 INTO @v');
|
|
ERROR 42000: Cursor SELECT must not have INTO
|
|
DROP PROCEDURE p1;
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE c SYS_REFCURSOR;
|
|
OPEN c FOR SELECT 1 INTO @v;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
ERROR 42000: Cursor SELECT must not have INTO
|
|
# SET STATEMENT .. FOR OPEN is not supported
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v0 TEXT;
|
|
DECLARE c CURSOR FOR ps;
|
|
SET STATEMENT max_sort_length=1000 FOR OPEN c;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OPEN c;
|
|
CLOSE c;
|
|
END' at line 5
|
|
# SQL Standard cursor: Dynamic OPEN requires CLOSE before a new OPEN
|
|
CREATE PROCEDURE p1(cl BOOL)
|
|
BEGIN
|
|
DECLARE v TEXT;
|
|
DECLARE c CURSOR FOR ps;
|
|
PREPARE ps FROM 'SELECT ?';
|
|
OPEN c USING 1;
|
|
IF (cl) THEN
|
|
CLOSE c;
|
|
END IF;
|
|
OPEN c USING 2;
|
|
FETCH c INTO v;
|
|
SELECT v;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1(FALSE);
|
|
ERROR 24000: Cursor is already open
|
|
CALL p1(TRUE);
|
|
v
|
|
2
|
|
DROP PROCEDURE p1;
|
|
# SQL Standard cursor + max_open_cursors
|
|
SET max_open_cursors=1;
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v0 TEXT;
|
|
DECLARE v1 TEXT;
|
|
DECLARE c0 CURSOR FOR ps0;
|
|
DECLARE c1 CURSOR FOR ps1;
|
|
PREPARE ps0 FROM 'SELECT ?,?';
|
|
PREPARE ps1 FROM 'SELECT ?,?';
|
|
OPEN c0 USING 10,11;
|
|
OPEN c1 USING 20,21;
|
|
FETCH c0 INTO v0, v1;
|
|
SELECT v0, v1;
|
|
FETCH c1 INTO v0, v1;
|
|
SELECT v0, v1;
|
|
CLOSE c0;
|
|
CLOSE c1;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
ERROR HY000: Too many open cursors; max 1 cursors allowed
|
|
DROP PROCEDURE p1;
|
|
SET max_open_cursors=DEFAULT;
|
|
# Oracle style cursors + max_open_cursors
|
|
SET max_open_cursors=1;
|
|
CREATE PROCEDURE p1(open_c1 BOOL)
|
|
BEGIN
|
|
DECLARE v TEXT;
|
|
DECLARE c0 SYS_REFCURSOR;
|
|
DECLARE c1 SYS_REFCURSOR;
|
|
OPEN c0 FOR 'SELECT ?' USING 1;
|
|
OPEN c0 FOR 'SELECT ?' USING 2; -- Ok to reopen
|
|
OPEN c0 FOR 'SELECT ?' USING 3; -- Ok to reopen
|
|
FETCH c0 INTO v;
|
|
SELECT v;
|
|
IF (open_c1) THEN
|
|
OPEN c1 FOR 'SELECT ?' USING 10;
|
|
END IF;
|
|
END;
|
|
$$
|
|
CALL p1(TRUE);
|
|
v
|
|
3
|
|
ERROR HY000: Too many open cursors; max 1 cursors allowed
|
|
CALL p1(FALSE);
|
|
v
|
|
3
|
|
DROP PROCEDURE p1;
|
|
SET max_open_cursors=DEFAULT;
|
|
# SQL Standard dynamic cursor: FOR loop is not supported
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
CREATE 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;
|
|
$$
|
|
ERROR HY000: Incorrect usage of FOR..IN and <dynamic cursor name>
|
|
DROP TABLE t1;
|
|
# SQL Standard dynamic cursor: ROW TYPE OF is not supported
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v TEXT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
BEGIN
|
|
DECLARE r ROW TYPE OF c;
|
|
END;
|
|
END;
|
|
$$
|
|
ERROR HY000: Incorrect usage of ROW TYPE OF and <dynamic cursor name>
|
|
# Opening two SQL Stanard dynamic cursors from the same PS
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v0 TEXT;
|
|
DECLARE v1 TEXT;
|
|
DECLARE c0 CURSOR FOR ps;
|
|
DECLARE c1 CURSOR FOR ps;
|
|
PREPARE ps FROM 'SELECT ?,?';
|
|
OPEN c0 USING 10,11;
|
|
OPEN c1 USING 20,21;
|
|
FETCH c0 INTO v0, v1;
|
|
SELECT v0, v1;
|
|
FETCH c1 INTO v0, v1;
|
|
SELECT v0, v1;
|
|
CLOSE c0;
|
|
CLOSE c1;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v0 v1
|
|
10 11
|
|
v0 v1
|
|
20 21
|
|
DROP PROCEDURE p1;
|
|
# A single FETCH from a simple SELECT without tables
|
|
CREATE PROCEDURE p1(dynamic_sql TEXT)
|
|
BEGIN
|
|
DECLARE v1 INT;
|
|
DECLARE v2 TEXT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM dynamic_sql;
|
|
OPEN c;
|
|
FETCH c INTO v1, v2;
|
|
SELECT v1, v2;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1('SELECT 123, ''v123''');
|
|
v1 v2
|
|
123 v123
|
|
CALL p1('VALUES (123, ''v123'')');
|
|
v1 v2
|
|
123 v123
|
|
DROP PROCEDURE p1;
|
|
# A single FETCH + SELECT without tables + OPEN in an inner block
|
|
CREATE PROCEDURE p1(dynamic_sql TEXT)
|
|
BEGIN
|
|
PREPARE stmt FROM dynamic_sql;
|
|
BEGIN
|
|
DECLARE v1 INT;
|
|
DECLARE v2 TEXT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
OPEN c;
|
|
FETCH c INTO v1, v2;
|
|
SELECT v1, v2;
|
|
CLOSE c;
|
|
END;
|
|
END;
|
|
$$
|
|
CALL p1('SELECT 123, ''v123''');
|
|
v1 v2
|
|
123 v123
|
|
CALL p1('VALUES (123, ''v123'')');
|
|
v1 v2
|
|
123 v123
|
|
DROP PROCEDURE p1;
|
|
# A loop for FETCH with an I_S query
|
|
CREATE PROCEDURE p1 ()
|
|
BEGIN
|
|
DECLARE dynamic_sql TEXT;
|
|
DECLARE v1 INT;
|
|
DECLARE v2 VARCHAR(64);
|
|
DECLARE end_of_data INT DEFAULT 0;
|
|
DECLARE c0 CURSOR FOR stmt1;
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_data = 1;
|
|
SET dynamic_sql='SELECT id, collation_name'
|
|
' FROM INFORMATION_SCHEMA.COLLATIONS'
|
|
' WHERE id IN(33,83,192)'
|
|
' ORDER BY id';
|
|
PREPARE stmt1 FROM dynamic_sql;
|
|
OPEN c0;
|
|
loop1: LOOP
|
|
FETCH FROM c0 INTO v1, v2;
|
|
IF end_of_data THEN LEAVE loop1; END IF;
|
|
SELECT v1, v2;
|
|
END LOOP;
|
|
CLOSE c0;
|
|
END;
|
|
$$
|
|
CALL p1();
|
|
v1 v2
|
|
33 utf8mb3_general_ci
|
|
v1 v2
|
|
83 utf8mb3_bin
|
|
v1 v2
|
|
192 utf8mb3_unicode_ci
|
|
CALL p1();
|
|
v1 v2
|
|
33 utf8mb3_general_ci
|
|
v1 v2
|
|
83 utf8mb3_bin
|
|
v1 v2
|
|
192 utf8mb3_unicode_ci
|
|
DROP PROCEDURE p1;
|
|
# DECLARE..FOR stmt; OPEN..USING;
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v0 TEXT;
|
|
DECLARE v1 TEXT;
|
|
DECLARE v2 TEXT;
|
|
DECLARE v3 TEXT;
|
|
DECLARE c0 CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT ?,?,? FROM DUAL';
|
|
OPEN c0 USING NULL,1,2;
|
|
FETCH c0 INTO v0, v1, v2;
|
|
CLOSE c0;
|
|
SELECT v0, v1, v2;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v0 v1 v2
|
|
NULL 1 2
|
|
DROP PROCEDURE p1;
|
|
# DECLARE..FOR stmt; -- with a LIMIT clause param
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
CREATE PROCEDURE p1(limit_as_text BOOL)
|
|
BEGIN
|
|
DECLARE v TEXT;
|
|
DECLARE c SYS_REFCURSOR;
|
|
DECLARE end_of_data BOOL DEFAULT FALSE;
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_data = TRUE;
|
|
IF limit_as_text THEN
|
|
OPEN c FOR 'SELECT a FROM t1 LIMIT ?' USING '2';
|
|
ELSE
|
|
OPEN c FOR 'SELECT a FROM t1 LIMIT ?' USING 2;
|
|
END IF;
|
|
loop1: LOOP
|
|
FETCH c INTO v;
|
|
IF end_of_data THEN LEAVE loop1; END IF;
|
|
SELECT v;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1(FALSE);
|
|
v
|
|
1
|
|
v
|
|
2
|
|
CALL p1(TRUE);
|
|
v
|
|
1
|
|
v
|
|
2
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
# A single FETCH from a table
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10);
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT a FROM t1';
|
|
OPEN c;
|
|
FETCH c INTO v;
|
|
SELECT v;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v
|
|
10
|
|
DROP TABLE t1;
|
|
DROP PROCEDURE p1;
|
|
# DEFAULT(column) as a USING expression
|
|
CREATE TABLE t1 (a INT DEFAULT 10);
|
|
INSERT INTO t1 VALUES (11);
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v TEXT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT ?';
|
|
OPEN c USING (SELECT DEFAULT(a) FROM t1);
|
|
FETCH c INTO v;
|
|
CLOSE c;
|
|
SELECT v;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v
|
|
10
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE a INT DEFAULT 10;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT ?';
|
|
OPEN c USING DEFAULT(10);
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '10);
|
|
CLOSE c;
|
|
END' at line 6
|
|
# DEFAULT as a USING expression
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT ?';
|
|
OPEN c USING DEFAULT;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ';
|
|
CLOSE c;
|
|
END' at line 5
|
|
# IGNORE as a USING expression
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT ?';
|
|
OPEN c USING IGNORE;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IGNORE;
|
|
CLOSE c;
|
|
END' at line 5
|
|
# A single FETCH from a stored function
|
|
CREATE FUNCTION f1() RETURNS INT RETURN 123;
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT f1()';
|
|
OPEN c;
|
|
FETCH c INTO v;
|
|
SELECT v;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v
|
|
123
|
|
DROP FUNCTION f1;
|
|
DROP PROCEDURE p1;
|
|
# Fetching from an arbitrary two row dynamic SQL passed as a parameter
|
|
CREATE TABLE t1 (a INT, b TEXT);
|
|
INSERT INTO t1 VALUES (1,'b1'), (2, 'b2');
|
|
CREATE FUNCTION f1(p INT) RETURNS INT RETURN p;
|
|
CREATE FUNCTION f2(p TEXT) RETURNS TEXT RETURN p;
|
|
CREATE PROCEDURE p1 (dynamic_sql TEXT)
|
|
BEGIN
|
|
DECLARE v1 INT;
|
|
DECLARE v2 VARCHAR(32);
|
|
DECLARE end_of_data INT DEFAULT 0;
|
|
DECLARE c0 CURSOR FOR stmt0;
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_data = 1;
|
|
PREPARE stmt0 FROM dynamic_sql;
|
|
OPEN c0;
|
|
loop1: LOOP
|
|
FETCH FROM c0 INTO v1, v2;
|
|
IF end_of_data THEN LEAVE loop1; END IF;
|
|
SELECT v1, v2;
|
|
END LOOP;
|
|
CLOSE c0;
|
|
END;
|
|
$$
|
|
CALL p1('SELECT a,b FROM t1');
|
|
v1 v2
|
|
1 b1
|
|
v1 v2
|
|
2 b2
|
|
CALL p1('SELECT f1(a),f2(b) FROM t1');
|
|
v1 v2
|
|
1 b1
|
|
v1 v2
|
|
2 b2
|
|
CALL p1('SELECT a,b FROM t1 UNION ALL SELECT f1(a),f2(b) FROM t1');
|
|
v1 v2
|
|
1 b1
|
|
v1 v2
|
|
2 b2
|
|
v1 v2
|
|
1 b1
|
|
v1 v2
|
|
2 b2
|
|
CALL p1('SELECT a,b FROM t1 UNION DISTINCT SELECT f1(a),f2(b) FROM t1');
|
|
v1 v2
|
|
1 b1
|
|
v1 v2
|
|
2 b2
|
|
CALL p1('(SELECT 1,2)');
|
|
v1 v2
|
|
1 2
|
|
CALL p1('(SELECT 1,2) UNION (SELECT 11,12)');
|
|
v1 v2
|
|
1 2
|
|
v1 v2
|
|
11 12
|
|
CALL p1('WITH t AS (SELECT a, b FROM t1) SELECT * FROM t');
|
|
v1 v2
|
|
1 b1
|
|
v1 v2
|
|
2 b2
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
DROP FUNCTION f1;
|
|
DROP FUNCTION f2;
|
|
# Metadata change
|
|
CREATE TABLE t1 (a INT, b TEXT);
|
|
INSERT INTO t1 VALUES (10,'b10');
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v0, v1 TEXT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT * FROM t1';
|
|
OPEN c;
|
|
FETCH c INTO v0, v1;
|
|
CLOSE c;
|
|
DEALLOCATE PREPARE stmt;
|
|
SELECT v0, v1;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v0 v1
|
|
10 b10
|
|
ALTER TABLE t1 MODIFY COLUMN b TEXT DEFAULT '' FIRST ;
|
|
CALL p1;
|
|
v0 v1
|
|
b10 10
|
|
ALTER TABLE t1 MODIFY COLUMN b TEXT DEFAULT '' AFTER a;
|
|
CALL p1;
|
|
v0 v1
|
|
10 b10
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
# Attempts to open a dynamic cursor with a named parameter fail
|
|
# OPEN c(10); <-- this expects a static cursor declared as e.g.:
|
|
# DECLARE c CURSOR(pa INT) FOR SELECT * FROM t1 WHERE a=pa;
|
|
# Dynamic cursors with placeholders should be opened with USING:
|
|
# DECLARE c CURSOR FOR prepared_stmt;
|
|
# PREPARE prepared_stmt FROM 'SELECT * FROM t1 WHERE a=?';
|
|
# OPEN c USING 10;
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT 1 FROM DUAL WHERE ?>0';
|
|
OPEN c(10);
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
ERROR 42000: Incorrect parameter count to cursor 'c'
|
|
# A cursor with placeholder parameters: too few USING parameters
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT 1 FROM DUAL WHERE ?=0';
|
|
OPEN c;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
ERROR HY000: Incorrect arguments to OPEN
|
|
DROP PROCEDURE p1;
|
|
# A cursor with placeholder parameters: too many USING parameters
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT 1 FROM DUAL';
|
|
OPEN c USING 1;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
ERROR HY000: Incorrect arguments to OPEN
|
|
DROP PROCEDURE p1;
|
|
# A cursor with one param - SP
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10), (11), (12), (13);
|
|
CREATE FUNCTION f1() RETURNS INT RETURN 11;
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE end_of_data INT DEFAULT 0;
|
|
DECLARE c CURSOR FOR stmt;
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_data = 1;
|
|
PREPARE stmt FROM 'SELECT a FROM t1 WHERE a>?';
|
|
OPEN c USING f1();
|
|
FETCH c INTO v;
|
|
SELECT v;
|
|
loop1: LOOP
|
|
FETCH FROM c INTO v;
|
|
IF end_of_data THEN LEAVE loop1; END IF;
|
|
SELECT v;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v
|
|
12
|
|
v
|
|
13
|
|
DROP PROCEDURE p1;
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
# A cursor with one param - subselect
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10), (11), (12), (13);
|
|
CREATE FUNCTION f1() RETURNS INT RETURN 11;
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE end_of_data INT DEFAULT 0;
|
|
DECLARE c CURSOR FOR stmt;
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_data = 1;
|
|
PREPARE stmt FROM 'SELECT a FROM t1 WHERE a>?';
|
|
OPEN c USING (SELECT a FROM t1 WHERE a=11);
|
|
FETCH c INTO v;
|
|
SELECT v;
|
|
loop1: LOOP
|
|
FETCH FROM c INTO v;
|
|
IF end_of_data THEN LEAVE loop1; END IF;
|
|
SELECT v;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v
|
|
12
|
|
v
|
|
13
|
|
DROP PROCEDURE p1;
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
# A cursor with multiple parameters - SP + subquery mixture
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10), (11), (12), (13);
|
|
CREATE FUNCTION f1() RETURNS INT RETURN 11;
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE end_of_data INT DEFAULT 0;
|
|
DECLARE c CURSOR FOR stmt;
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_data = 1;
|
|
PREPARE stmt FROM 'SELECT a FROM t1 WHERE a>? AND a>? AND a>?';
|
|
OPEN c USING f1(), (SELECT a FROM t1 WHERE a=12), f1();
|
|
FETCH c INTO v;
|
|
SELECT v;
|
|
loop1: LOOP
|
|
FETCH FROM c INTO v;
|
|
IF end_of_data THEN LEAVE loop1; END IF;
|
|
SELECT v;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v
|
|
13
|
|
DROP PROCEDURE p1;
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
# A cursor with multiple parameters in SELECT list
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10), (11), (12), (13);
|
|
CREATE FUNCTION f1() RETURNS TEXT RETURN 'f1';
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v0 TEXT;
|
|
DECLARE v1 TEXT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT ?,? FROM t1';
|
|
OPEN c USING (SELECT MAX(a) FROM t1), f1();
|
|
FETCH c INTO v0, v1;
|
|
SELECT v0, v1;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v0 v1
|
|
13 f1
|
|
DROP PROCEDURE p1;
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
# OPEN cursor_name FOR stmt_expr
|
|
CREATE PROCEDURE p1 (dynamic_sql VARCHAR(256))
|
|
BEGIN
|
|
DECLARE c SYS_REFCURSOR;
|
|
DECLARE v VARCHAR(256);
|
|
OPEN c FOR dynamic_sql;
|
|
FETCH c INTO v;
|
|
SELECT v;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1('SELECT 1 FROM DUAL');
|
|
v
|
|
1
|
|
DROP PROCEDURE p1;
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v0 TEXT;
|
|
DECLARE v1 TEXT;
|
|
DECLARE v2 TEXT;
|
|
DECLARE c0 SYS_REFCURSOR;
|
|
OPEN c0 FOR 'SELECT ?,?,? FROM DUAL' USING NULL,1,2;
|
|
FETCH c0 INTO v0, v1, v2;
|
|
CLOSE c0;
|
|
SELECT v0, v1, v2;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v0 v1 v2
|
|
NULL 1 2
|
|
DROP PROCEDURE p1;
|
|
# OPEN cursor_name FOR stmt_expr_subselect
|
|
CREATE TABLE t1 (a VARCHAR(256));
|
|
INSERT INTO t1 VALUES ('SELECT 1 FROM DUAL');
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE c SYS_REFCURSOR;
|
|
DECLARE v VARCHAR(256);
|
|
OPEN c FOR (SELECT a FROM t1);
|
|
FETCH c INTO v;
|
|
SELECT v;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
ERROR 42000: OPEN..FOR does not support subqueries or stored functions
|
|
DROP TABLE t1;
|
|
# OPEN cursor_name FOR stmt_expr_sf
|
|
CREATE FUNCTION f1() RETURNS TEXT RETURN 'SELECT 1 FROM DUAL';
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE c SYS_REFCURSOR;
|
|
DECLARE v VARCHAR(256);
|
|
OPEN c FOR f1();
|
|
FETCH c INTO v;
|
|
SELECT v;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
ERROR 42000: OPEN..FOR does not support subqueries or stored functions
|
|
DROP FUNCTION f1;
|
|
# OPEN cursor_name FOR stmt_expr_sf
|
|
CREATE FUNCTION f1() RETURNS TEXT RETURN 'SELECT 1 FROM DUAL';
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE c SYS_REFCURSOR;
|
|
DECLARE v VARCHAR(256);
|
|
OPEN c FOR f1() USING f1();
|
|
FETCH c INTO v;
|
|
SELECT v;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
ERROR 42000: OPEN..FOR does not support subqueries or stored functions
|
|
DROP FUNCTION f1;
|
|
# Dynamic SQL is not allowed in stored functions
|
|
CREATE FUNCTION f1() RETURNS INT
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
RETURN 10;
|
|
END;
|
|
$$
|
|
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
|
|
CREATE FUNCTION f1() RETURNS INT
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c SYS_REFCURSOR;
|
|
OPEN c FOR 'SELECT 10';
|
|
FETCH c INTO v;
|
|
CLOSE c;
|
|
RETURN v;
|
|
END;
|
|
$$
|
|
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
|
|
# Static OPEN is OK in SF, both for Standard cursors and SYS_REFCURSORs
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c CURSOR FOR SELECT f1();
|
|
OPEN c;
|
|
FETCH c INTO v;
|
|
CLOSE c;
|
|
SELECT v;
|
|
END;
|
|
$$
|
|
CREATE PROCEDURE p2()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT f1()';
|
|
OPEN c;
|
|
FETCH c INTO v;
|
|
CLOSE c;
|
|
SELECT v;
|
|
END;
|
|
$$
|
|
CREATE PROCEDURE p3()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c CURSOR FOR stmt;
|
|
PREPARE stmt FROM 'SELECT ?';
|
|
OPEN c USING f1();
|
|
FETCH c INTO v;
|
|
CLOSE c;
|
|
SELECT v;
|
|
END;
|
|
$$
|
|
CREATE PROCEDURE p4()
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c SYS_REFCURSOR;
|
|
OPEN c FOR 'SELECT ?' USING f1();
|
|
FETCH c INTO v;
|
|
CLOSE c;
|
|
SELECT v;
|
|
END;
|
|
$$
|
|
CREATE FUNCTION f1() RETURNS INT
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c CURSOR FOR SELECT 10;
|
|
OPEN c;
|
|
FETCH c INTO v;
|
|
CLOSE c;
|
|
RETURN v;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v
|
|
10
|
|
CALL p2;
|
|
v
|
|
10
|
|
CALL p3;
|
|
v
|
|
10
|
|
CALL p4;
|
|
v
|
|
10
|
|
DROP FUNCTION f1;
|
|
CREATE FUNCTION f1() RETURNS INT
|
|
BEGIN
|
|
DECLARE v INT;
|
|
DECLARE c SYS_REFCURSOR;
|
|
OPEN c FOR SELECT 10;
|
|
FETCH c INTO v;
|
|
CLOSE c;
|
|
RETURN v;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
v
|
|
10
|
|
CALL p2;
|
|
v
|
|
10
|
|
CALL p3;
|
|
v
|
|
10
|
|
CALL p4;
|
|
v
|
|
10
|
|
DROP FUNCTION f1;
|
|
DROP PROCEDURE p1;
|
|
DROP PROCEDURE p2;
|
|
DROP PROCEDURE p3;
|
|
DROP PROCEDURE p4;
|