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/r/sp-sys_refcursor.result
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

644 lines
11 KiB
Plaintext
Raw Permalink Blame History

SET sql_mode=ORACLE;
#
# MDEV-20034 Add support for the pre-defined weak SYS_REFCURSOR
#
#
# Error: Unknown cursor and wrong variable data type in OPEN, FETCH, CLOSE
#
BEGIN
OPEN c FOR SELECT 1 AS c FROM DUAL;
END;
/
ERROR 42000: Undeclared variable: c
DECLARE
c INT;
BEGIN
OPEN c FOR SELECT 1 AS c FROM DUAL;
END;
/
ERROR HY000: Illegal parameter data type int for operation 'OPEN'
DECLARE
c INT;
BEGIN
CLOSE c;
END;
/
ERROR HY000: Illegal parameter data type int for operation 'CLOSE'
DECLARE
a INT;
c INT;
BEGIN
FETCH c INTO a;
END;
/
ERROR HY000: Illegal parameter data type int for operation 'FETCH'
#
# Error: Closing a not open cursor
#
DECLARE
c SYS_REFCURSOR;
BEGIN
CLOSE c;
END;
/
ERROR 24000: Cursor is not open
#
# Error: Fetching from a not open cursor
#
DECLARE
a INT;
c SYS_REFCURSOR;
BEGIN
FETCH c INTO a;
END;
/
ERROR 24000: Cursor is not open
#
# Error: fetching beyond the available number of records
# sql_mode=ORACLE preserves the variable value
#
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;
/
1
1
#
# Two consequent OPEN (without a CLOSE in beetween) are allowed
#
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;
/
2
#
# Many consequent OPEN (without a CLOSE in between) are allowed
# and do not cause ER_TOO_MANY_OPEN_CURSORS.
#
SET max_open_cursors=2;
DECLARE
c SYS_REFCURSOR;
BEGIN
FOR i IN 1..3
LOOP
OPEN c FOR SELECT 1 AS c FROM DUAL;
END LOOP;
END;
/
SET max_open_cursors=DEFAULT;
#
# Simple use example (OPEN, FETCH, CLOSE)
#
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;
/
1
#
# Fetching from two parallel cursors
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
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;
/
CALL p1;
10 20
DROP PROCEDURE p1;
DROP TABLE t1;
#
# Returning an open cursor from a function
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20);
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;
/
CALL p1;
10
20
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP TABLE t1;
#
# Returning SYS_REFCURSOR from a function: too many open cursors
#
SET max_open_cursors=2;
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;
/
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;
/
ERROR HY000: Too many open cursors; max 2 cursors allowed
DROP FUNCTION f1;
SET max_open_cursors=DEFAULT;
#
# Returning an open cursor as an OUT param
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
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;
/
CALL p2;
10
20
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
#
# Returning an open cursor as an OUT param: Too many open cursors
#
SET @@max_open_cursors=2;
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;
/
CALL p2;
10
20
10
20
ERROR HY000: Too many open cursors; max 2 cursors allowed
DROP PROCEDURE p1;
DROP PROCEDURE p2;
SET @@max_open_cursors=DEFAULT;
#
# Returning an open cursor as an OUT param: no "Too many open cursors"
#
SET @@max_open_cursors=2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
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;
/
CALL p2;
10
20
10
20
10
20
10
20
10
20
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
SET @@max_open_cursors=DEFAULT;
#
# Returning an open cursor as an INOUT param: no "Too many open cursors"
#
SET @@max_open_cursors=2;
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;
/
CALL p2;
10
20
10
20
10
20
10
20
10
20
DROP PROCEDURE p1;
DROP PROCEDURE p2;
SET @@max_open_cursors=DEFAULT;
#
# Function returning SYS_REFCURSOR and mysql.proc
#
CREATE FUNCTION f1() RETURN SYS_REFCURSOR AS
c0 SYS_REFCURSOR;
BEGIN
RETURN c0;
END;
/
SELECT returns FROM mysql.proc WHERE name='f1';
returns
sys_refcursor
SHOW CREATE FUNCTION f1;
Function sql_mode Create Function character_set_client collation_connection Database Collation
f1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" FUNCTION "f1"() RETURN sys_refcursor
AS
c0 SYS_REFCURSOR;
BEGIN
RETURN c0;
END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
DROP FUNCTION f1;
#
# Procedure with a SYS_REFCURSOR parameter and mysql.proc
#
CREATE PROCEDURE p1(a0 OUT SYS_REFCURSOR) AS
c0 SYS_REFCURSOR;
BEGIN
a0:= c0;
END;
/
SELECT param_list FROM mysql.proc WHERE name='p1';
param_list
a0 OUT SYS_REFCURSOR
SHOW CREATE PROCEDURE p1;
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
p1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" PROCEDURE "p1"(a0 OUT SYS_REFCURSOR)
AS
c0 SYS_REFCURSOR;
BEGIN
a0:= c0;
END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
DROP PROCEDURE p1;
#
# NULL predicate
#
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;
/
CALL p1;
true
false
false
false
DROP PROCEDURE p1;
#
# Cursor attributes on a not open SYS_REFCURSOR
#
DECLARE
c SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('c%ISOPEN=' || bool_to_char(c%ISOPEN));
END;
/
c%ISOPEN=false
DECLARE
c SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('c%FOUND=' || bool_to_char(c%FOUND));
END;
/
ERROR 24000: Cursor is not open
DECLARE
c SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('c%NOTFOUND=' || bool_to_char(c%NOTFOUND));
END;
/
ERROR 24000: Cursor is not open
DECLARE
c SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('c%ROWCOUNT=' || c%ROWCOUNT);
END;
/
ERROR 24000: Cursor is not open
#
# Cursor attributes on an open SYS_REFCURSOR
#
DECLARE
c SYS_REFCURSOR;
BEGIN
OPEN c FOR SELECT 1 FROM DUAL;
DBMS_OUTPUT.PUT_LINE('c%ISOPEN=' || bool_to_char(c%ISOPEN));
END;
/
c%ISOPEN=true
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;
/
c%FOUND=NULL
c%FOUND=true
c%FOUND=false
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;
/
c%NOTFOUND=NULL
c%NOTFOUND=false
c%NOTFOUND=true
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;
/
c%ROWCOUNT=0
c%ROWCOUNT=1
c%ROWCOUNT=1
#
# - Returning a never opened cursor does not cause ER_TOO_MANY_OPEN_CURSORS
# - Returning an opened+closed cursor does not cause ER_TOO_MANY_OPEN_CURSORS
# - Only returning an opened cursor causes ER_TOO_MANY_OPEN_CURSORS
#
SET @@max_open_cursors=2;
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;
/
CALL p1('none');
0 <NULL> false
1 <NULL> false
2 <NULL> false
CALL p1('open_close');
0 <NOTNULL> false
1 <NOTNULL> false
2 <NOTNULL> false
CALL p1('open');
0 <NOTNULL> true
1 <NOTNULL> true
ERROR HY000: Too many open cursors; max 2 cursors allowed
DROP PROCEDURE p1;
DROP FUNCTION f1;
SET @@max_open_cursors=DEFAULT;
#
# Cursor variables cannot be declared as part of a package
#
CREATE PACKAGE pkg AS
FUNCTION f1 RETURN INT;
END;
/
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;
/
ERROR HY000: 'sys_refcursor' is not allowed in this context
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;
/
SELECT pkg.f1() FROM DUAL;
pkg.f1()
1
DROP PACKAGE pkg;
DROP FUNCTION bool_to_char;
DROP PACKAGE DBMS_OUTPUT;
View Git Blame Copy Permalink