database/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-08-07 00:04:31 +03:00
Code Activity
Files
main
mariadb/mysql-test/suite/compat/oracle/t/sp-sys_refcursor.test
Alexander Barkov f11504af51 MDEV-20034 Add support for the pre-defined weak SYS_REFCURSOR 
This patch adds support for SYS_REFCURSOR (a weakly typed cursor)
for both sql_mode=ORACLE and sql_mode=DEFAULT.

Works as a regular stored routine variable, parameter and return value:

- can be passed as an IN parameter to stored functions and procedures
- can be passed as an INOUT and OUT parameter to stored procedures
- can be returned from a stored function

Note, strongly typed REF CURSOR will be added separately.

Note, to maintain dependencies easier, some parts of sql_class.h
and item.h were moved to new header files:

- select_results.h:
  class select_result_sink
  class select_result
  class select_result_interceptor

- sp_cursor.h:
  class sp_cursor_statistics
  class sp_cursor

- sp_rcontext_handler.h
  class Sp_rcontext_handler and its descendants

The implementation consists of the following parts:
- A new class sp_cursor_array deriving from Dynamic_array

- A new class Statement_rcontext which contains data shared
  between sub-statements of a compound statement.
  It has a member m_statement_cursors of the sp_cursor_array data type,
  as well as open cursor counter. THD inherits from Statement_rcontext.

- A new data type handler Type_handler_sys_refcursor in plugins/type_cursor/
  It is designed to store uint16 references -
  positions of the cursor in THD::m_statement_cursors.

- Type_handler_sys_refcursor suppresses some derived numeric features.
  When a SYS_REFCURSOR variable is used as an integer an error is raised.

- A new abstract class sp_instr_fetch_cursor. It's needed to share
  the common code between "OPEN cur" (for static cursors) and
  "OPER cur FOR stmt" (for SYS_REFCURSORs).

- New sp_instr classes:
  * sp_instr_copen_by_ref      - OPEN sys_ref_curor FOR stmt;
  * sp_instr_cfetch_by_ref     - FETCH sys_ref_cursor INTO targets;
  * sp_instr_cclose_by_ref     - CLOSE sys_ref_cursor;
  * sp_instr_destruct_variable - to destruct SYS_REFCURSOR variables when
                                 the execution goes out of the BEGIN..END block
                                 where SYS_REFCURSOR variables are declared.
- New methods in LEX:
  * sp_open_cursor_for_stmt   - handles "OPEN sys_ref_cursor FOR stmt".
  * sp_add_instr_fetch_cursor - "FETCH cur INTO targets" for both
                                static cursors and SYS_REFCURSORs.
  * sp_close - handles "CLOSE cur" both for static cursors and SYS_REFCURSORs.

- Changes in cursor functions to handle both static cursors and SYS_REFCURSORs:
  * Item_func_cursor_isopen
  * Item_func_cursor_found
  * Item_func_cursor_notfound
  * Item_func_cursor_rowcount

- A new system variable @@max_open_cursors - to limit the number
  of cursors (static and SYS_REFCURSORs) opened at the same time.
  Its allowed range is [0-65536], with 50 by default.

- A new virtual method Type_handler::can_return_bool() telling
  if calling item->val_bool() is allowed for Items of this data type,
  or if otherwise the "Illegal parameter for operation" error should be raised
  at fix_fields() time.

- New methods in Sp_rcontext_handler:
  * get_cursor()
  * get_cursor_by_ref()

- A new class Sp_rcontext_handler_statement to handle top level statement
  wide cursors which are shared by all substatements.

- A new virtual method expr_event_handler() in classes Item and Field.
  It's needed to close (and make available for a new OPEN)
  unused THD::m_statement_cursors elements which do not have any references
  any more. It can happen in various moments in time, e.g.
  * after evaluation parameters of an SQL routine
  * after assigning a cursor expression into a SYS_REFCURSOR variable
  * when leaving a BEGIN..END block with SYS_REFCURSOR variables
  * after setting OUT/INOUT routine actual parameters from formal
    parameters.
2025-04-19 10:59:58 +04:00

647 lines
11 KiB
Plaintext
Raw Permalink Blame History

SET sql_mode=ORACLE;
# Helper functions and packages
--disable_query_log
--source include/dbms_output.inc
--source include/bool_to_char.inc
--enable_query_log
--echo #
--echo # MDEV-20034 Add support for the pre-defined weak SYS_REFCURSOR
--echo #
--echo #
--echo # Error: Unknown cursor and wrong variable data type in OPEN, FETCH, CLOSE
--echo #
DELIMITER /;
--error ER_SP_UNDECLARED_VAR
BEGIN
OPEN c FOR SELECT 1 AS c FROM DUAL;
END;
/
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
DECLARE
c INT;
BEGIN
OPEN c FOR SELECT 1 AS c FROM DUAL;
END;
/
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
DECLARE
c INT;
BEGIN
CLOSE c;
END;
/
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
DECLARE
a INT;
c INT;
BEGIN
FETCH c INTO a;
END;
/
DELIMITER ;/
--echo #
--echo # Error: Closing a not open cursor
--echo #
DELIMITER /;
--error ER_SP_CURSOR_NOT_OPEN
DECLARE
c SYS_REFCURSOR;
BEGIN
CLOSE c;
END;
/
DELIMITER ;/
--echo #
--echo # Error: Fetching from a not open cursor
--echo #
DELIMITER /;
--error ER_SP_CURSOR_NOT_OPEN
DECLARE
a INT;
c SYS_REFCURSOR;
BEGIN
FETCH c INTO a;
END;
/
DELIMITER ;/
--echo #
--echo # Error: fetching beyond the available number of records
--echo # sql_mode=ORACLE preserves the variable value
--echo #
DELIMITER /;
DECLARE
a INT;
c SYS_REFCURSOR;
BEGIN
OPEN c FOR SELECT 1 FROM DUAL;
FETCH c INTO a;
DBMS_OUTPUT.PUT_LINE(a);
FETCH c INTO a;
DBMS_OUTPUT.PUT_LINE(a);
END;
/
DELIMITER ;/
--echo #
--echo # Two consequent OPEN (without a CLOSE in beetween) are allowed
--echo #
DELIMITER /;
DECLARE
a INT;
c SYS_REFCURSOR;
BEGIN
OPEN c FOR SELECT 1 FROM DUAL;
OPEN c FOR SELECT 2 FROM DUAL;
FETCH c INTO a;
DBMS_OUTPUT.PUT_LINE(a);
END;
/
DELIMITER ;/
--echo #
--echo # Many consequent OPEN (without a CLOSE in between) are allowed
--echo # and do not cause ER_TOO_MANY_OPEN_CURSORS.
--echo #
SET max_open_cursors=2;
DELIMITER /;
DECLARE
c SYS_REFCURSOR;
BEGIN
FOR i IN 1..3
LOOP
OPEN c FOR SELECT 1 AS c FROM DUAL;
END LOOP;
END;
/
DELIMITER ;/
SET max_open_cursors=DEFAULT;
--echo #
--echo # Simple use example (OPEN, FETCH, CLOSE)
--echo #
DELIMITER /;
DECLARE
c SYS_REFCURSOR;
a INT;
BEGIN
OPEN c FOR SELECT 1 FROM DUAL;
FETCH c INTO a;
CLOSE c;
DBMS_OUTPUT.PUT_LINE(a);
END;
/
DELIMITER ;/
--echo #
--echo # Fetching from two parallel cursors
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
DELIMITER /;
CREATE PROCEDURE p1() AS
a0 INT;
a1 INT;
c0 SYS_REFCURSOR;
c1 SYS_REFCURSOR;
BEGIN
OPEN c0 FOR SELECT a*10 FROM t1;
OPEN c1 FOR SELECT a*20 FROM t1;
FETCH c0 INTO a0;
FETCH c1 INTO a1;
DBMS_OUTPUT.PUT_LINE(a0 || ' ' || a1);
CLOSE c0;
CLOSE c1;
END;
/
DELIMITER ;/
CALL p1;
DROP PROCEDURE p1;
DROP TABLE t1;
--echo #
--echo # Returning an open cursor from a function
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20);
DELIMITER /;
CREATE FUNCTION f1 RETURN SYS_REFCURSOR AS
c SYS_REFCURSOR;
BEGIN
OPEN c FOR SELECT a FROM t1 ORDER BY a;
RETURN c;
END;
/
CREATE PROCEDURE p1 AS
a INT;
c SYS_REFCURSOR DEFAULT f1();
BEGIN
LOOP
FETCH c INTO a;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(a);
END LOOP;
CLOSE c;
END;
/
DELIMITER ;/
CALL p1;
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP TABLE t1;
--echo #
--echo # Returning SYS_REFCURSOR from a function: too many open cursors
--echo #
SET max_open_cursors=2;
DELIMITER /;
CREATE OR REPLACE FUNCTION f1 RETURN SYS_REFCURSOR IS
c SYS_REFCURSOR;
BEGIN
OPEN c FOR SELECT 1 AS a FROM DUAL;
RETURN c;
END;
/
--error ER_TOO_MANY_OPEN_CURSORS
DECLARE
c0 SYS_REFCURSOR;
c1 SYS_REFCURSOR;
c2 SYS_REFCURSOR;
a INT;
BEGIN
c0:= f1();
FETCH c0 INTO a;
c1:= f1();
FETCH c1 INTO a;
c2:= f1();
FETCH c2 INTO a;
END;
/
DELIMITER ;/
DROP FUNCTION f1;
SET max_open_cursors=DEFAULT;
--echo #
--echo # Returning an open cursor as an OUT param
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
DELIMITER /;
CREATE PROCEDURE p1(c OUT SYS_REFCURSOR) AS
BEGIN
OPEN c FOR SELECT a FROM t1 ORDER BY a;
END;
/
CREATE PROCEDURE p2 AS
a INT;
c SYS_REFCURSOR;
BEGIN
p1(c);
LOOP
FETCH c INTO a;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(a);
END LOOP;
CLOSE c;
END;
/
DELIMITER ;/
CALL p2;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
--echo #
--echo # Returning an open cursor as an OUT param: Too many open cursors
--echo #
SET @@max_open_cursors=2;
DELIMITER /;
CREATE PROCEDURE p1(c OUT SYS_REFCURSOR) AS
BEGIN
OPEN c FOR VALUES (10),(20);
END;
/
CREATE PROCEDURE p2 AS
a INT;
c0 SYS_REFCURSOR;
c1 SYS_REFCURSOR;
c2 SYS_REFCURSOR;
BEGIN
p1(c0);
LOOP
FETCH c0 INTO a;
EXIT WHEN c0%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(a);
END LOOP;
p1(c1);
LOOP
FETCH c1 INTO a;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(a);
END LOOP;
p1(c2);
END;
/
DELIMITER ;/
--error ER_TOO_MANY_OPEN_CURSORS
CALL p2;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
SET @@max_open_cursors=DEFAULT;
--echo #
--echo # Returning an open cursor as an OUT param: no "Too many open cursors"
--echo #
SET @@max_open_cursors=2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
DELIMITER /;
CREATE PROCEDURE p1(c OUT SYS_REFCURSOR) AS
BEGIN
OPEN c FOR SELECT a FROM t1 ORDER BY a;
END;
/
CREATE PROCEDURE p2 AS
a INT;
c SYS_REFCURSOR;
BEGIN
FOR i IN 1..5
LOOP
p1(c); -- This closes the cursor and reopens it in p1
LOOP
FETCH c INTO a;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(a);
END LOOP;
END LOOP;
CLOSE c;
END;
/
DELIMITER ;/
CALL p2;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
SET @@max_open_cursors=DEFAULT;
--echo #
--echo # Returning an open cursor as an INOUT param: no "Too many open cursors"
--echo #
SET @@max_open_cursors=2;
DELIMITER /;
CREATE PROCEDURE p1(c INOUT SYS_REFCURSOR) AS
BEGIN
OPEN c FOR VALUES (10), (20);
END;
/
CREATE PROCEDURE p2 AS
a INT;
c SYS_REFCURSOR;
BEGIN
FOR i IN 1..5
LOOP
p1(c); -- This closes the cursor and reopens it in p1
LOOP
FETCH c INTO a;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(a);
END LOOP;
END LOOP;
CLOSE c;
END;
/
DELIMITER ;/
CALL p2;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
SET @@max_open_cursors=DEFAULT;
--echo #
--echo # Function returning SYS_REFCURSOR and mysql.proc
--echo #
DELIMITER /;
CREATE FUNCTION f1() RETURN SYS_REFCURSOR AS
c0 SYS_REFCURSOR;
BEGIN
RETURN c0;
END;
/
DELIMITER ;/
SELECT returns FROM mysql.proc WHERE name='f1';
SHOW CREATE FUNCTION f1;
DROP FUNCTION f1;
--echo #
--echo # Procedure with a SYS_REFCURSOR parameter and mysql.proc
--echo #
DELIMITER /;
CREATE PROCEDURE p1(a0 OUT SYS_REFCURSOR) AS
c0 SYS_REFCURSOR;
BEGIN
a0:= c0;
END;
/
DELIMITER ;/
SELECT param_list FROM mysql.proc WHERE name='p1';
SHOW CREATE PROCEDURE p1;
DROP PROCEDURE p1;
--echo #
--echo # NULL predicate
--echo #
DELIMITER /;
CREATE PROCEDURE p1 AS
c0 SYS_REFCURSOR;
v INT;
BEGIN
DBMS_OUTPUT.PUT_LINE(bool_to_char(c0 IS NULL));
OPEN c0 FOR SELECT 1 FROM DUAL;
DBMS_OUTPUT.PUT_LINE(bool_to_char(c0 IS NULL));
FETCH c0 INTO v;
DBMS_OUTPUT.PUT_LINE(bool_to_char(c0 IS NULL));
CLOSE c0;
DBMS_OUTPUT.PUT_LINE(bool_to_char(c0 IS NULL));
END;
/
DELIMITER ;/
CALL p1;
DROP PROCEDURE p1;
--echo #
--echo # Cursor attributes on a not open SYS_REFCURSOR
--echo #
DELIMITER /;
DECLARE
c SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('c%ISOPEN=' || bool_to_char(c%ISOPEN));
END;
/
--error ER_SP_CURSOR_NOT_OPEN
DECLARE
c SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('c%FOUND=' || bool_to_char(c%FOUND));
END;
/
--error ER_SP_CURSOR_NOT_OPEN
DECLARE
c SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('c%NOTFOUND=' || bool_to_char(c%NOTFOUND));
END;
/
--error ER_SP_CURSOR_NOT_OPEN
DECLARE
c SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('c%ROWCOUNT=' || c%ROWCOUNT);
END;
/
DELIMITER ;/
--echo #
--echo # Cursor attributes on an open SYS_REFCURSOR
--echo #
DELIMITER /;
DECLARE
c SYS_REFCURSOR;
BEGIN
OPEN c FOR SELECT 1 FROM DUAL;
DBMS_OUTPUT.PUT_LINE('c%ISOPEN=' || bool_to_char(c%ISOPEN));
END;
/
DECLARE
c SYS_REFCURSOR;
a INT;
BEGIN
OPEN c FOR SELECT 1 FROM DUAL;
DBMS_OUTPUT.PUT_LINE('c%FOUND=' || bool_to_char(c%FOUND));
FETCH c INTO a;
DBMS_OUTPUT.PUT_LINE('c%FOUND=' || bool_to_char(c%FOUND));
FETCH c INTO a;
DBMS_OUTPUT.PUT_LINE('c%FOUND=' || bool_to_char(c%FOUND));
END;
/
DECLARE
c SYS_REFCURSOR;
a INT;
BEGIN
OPEN c FOR SELECT 1 FROM DUAL;
DBMS_OUTPUT.PUT_LINE('c%NOTFOUND=' || bool_to_char(c%NOTFOUND));
FETCH c INTO a;
DBMS_OUTPUT.PUT_LINE('c%NOTFOUND=' || bool_to_char(c%NOTFOUND));
FETCH c INTO a;
DBMS_OUTPUT.PUT_LINE('c%NOTFOUND=' || bool_to_char(c%NOTFOUND));
END;
/
DECLARE
c SYS_REFCURSOR;
a INT;
BEGIN
OPEN c FOR SELECT 1 FROM DUAL;
DBMS_OUTPUT.PUT_LINE('c%ROWCOUNT=' || c%ROWCOUNT);
FETCH c INTO a;
DBMS_OUTPUT.PUT_LINE('c%ROWCOUNT=' || c%ROWCOUNT);
FETCH c INTO a;
DBMS_OUTPUT.PUT_LINE('c%ROWCOUNT=' || c%ROWCOUNT);
END;
/
DELIMITER ;/
--echo #
--echo # - Returning a never opened cursor does not cause ER_TOO_MANY_OPEN_CURSORS
--echo # - Returning an opened+closed cursor does not cause ER_TOO_MANY_OPEN_CURSORS
--echo # - Only returning an opened cursor causes ER_TOO_MANY_OPEN_CURSORS
--echo #
SET @@max_open_cursors=2;
DELIMITER /;
CREATE FUNCTION f1(task VARCHAR) RETURN SYS_REFCURSOR AS
c SYS_REFCURSOR := NULL;
BEGIN
IF task LIKE '%open%' THEN
OPEN c FOR SELECT 1 FROM DUAL;
END IF;
IF task LIKE '%close%' THEN
CLOSE c;
END IF;
RETURN c;
END;
/
CREATE PROCEDURE p1(task VARCHAR) AS
c0 SYS_REFCURSOR;
c1 SYS_REFCURSOR;
c2 SYS_REFCURSOR;
BEGIN
c0:= f1(task);
DBMS_OUTPUT.PUT_LINE('0' || ' ' || CASE WHEN c0 IS NULL THEN '<NULL>' ELSE '<NOTNULL>' END ||
' ' || bool_to_char(c0%ISOPEN));
c1:= f1(task);
DBMS_OUTPUT.PUT_LINE('1' || ' ' || CASE WHEN c1 IS NULL THEN '<NULL>' ELSE '<NOTNULL>' END ||
' ' || bool_to_char(c1%ISOPEN));
c2:= f1(task);
DBMS_OUTPUT.PUT_LINE('2' || ' ' || CASE WHEN c2 IS NULL THEN '<NULL>' ELSE '<NOTNULL>' END ||
' ' || bool_to_char(c2%ISOPEN));
END;
/
DELIMITER ;/
CALL p1('none');
CALL p1('open_close');
--error ER_TOO_MANY_OPEN_CURSORS
CALL p1('open');
DROP PROCEDURE p1;
DROP FUNCTION f1;
SET @@max_open_cursors=DEFAULT;
--echo #
--echo # Cursor variables cannot be declared as part of a package
--echo #
DELIMITER /;
CREATE PACKAGE pkg AS
FUNCTION f1 RETURN INT;
END;
/
--error ER_NOT_ALLOWED_IN_THIS_CONTEXT
CREATE PACKAGE BODY pkg AS
cur SYS_REFCURSOR; -- This is wrong (the top PACKAGE BODY frame)
FUNCTION f1 RETURN INT AS
BEGIN
RETURN 1;
END;
END;
/
CREATE PACKAGE BODY pkg AS
vc INT := 0;
FUNCTION f1 RETURN INT AS
cur SYS_REFCURSOR;
BEGIN
RETURN vc;
END;
BEGIN
DECLARE
cur SYS_REFCURSOR; -- This is OK (executable section)
BEGIN
OPEN cur FOR SELECT 1 AS c FROM DUAL;
FETCH cur INTO vc;
CLOSE cur;
END;
END;
/
DELIMITER ;/
SELECT pkg.f1() FROM DUAL;
DROP PACKAGE pkg;
DROP FUNCTION bool_to_char;
DROP PACKAGE DBMS_OUTPUT;
View Git Blame Copy Permalink