mirror of
https://github.com/MariaDB/server.git
synced 2025-08-07 00:04:31 +03:00
f11504af51
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.
193 lines
4.8 KiB
Plaintext
193 lines
4.8 KiB
Plaintext
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 # Aliasing
|
|
--echo #
|
|
|
|
DELIMITER /;
|
|
CREATE PROCEDURE p1(task VARCHAR) AS
|
|
c0 SYS_REFCURSOR;
|
|
c1 SYS_REFCURSOR;
|
|
v0 INT;
|
|
v1 INT;
|
|
BEGIN
|
|
OPEN c0 FOR SELECT 100 FROM DUAL UNION SELECT 101 FROM DUAL;
|
|
c1:= c0;
|
|
FETCH c0 INTO v0; -- fetch 100
|
|
FETCH c1 INTO v1; -- fetch 101
|
|
DBMS_OUTPUT.PUT_LINE('actual=' || v0 || ' ' || v1 || ' ' || 'expected=100 101');
|
|
IF task LIKE '%close_c0%' THEN
|
|
CLOSE c0;
|
|
END IF;
|
|
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' ||
|
|
'c1%ISOPEN=' || bool_to_char(c1%ISOPEN));
|
|
OPEN c1 FOR SELECT 200 FROM DUAL UNION SELECT 201 FROM DUAL;
|
|
FETCH c0 INTO v0; -- fetch 200 from the new OPEN c1
|
|
FETCH c1 INTO v1; -- fetch 201 from the new OPEN c1
|
|
DBMS_OUTPUT.PUT_LINE('actual=' || v0 || ' ' || v1 || ' ' || 'expected=200 201');
|
|
IF task LIKE '%close_c1%' THEN
|
|
CLOSE c1;
|
|
END IF;
|
|
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' ||
|
|
'c1%ISOPEN=' || bool_to_char(c1%ISOPEN));
|
|
END;
|
|
/
|
|
DELIMITER ;/
|
|
CALL p1('');
|
|
CALL p1('close_c0');
|
|
CALL p1('close_c1');
|
|
CALL p1('close_c0 close_c1');
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
DELIMITER /;
|
|
CREATE PROCEDURE p1(task VARCHAR) AS
|
|
c0 SYS_REFCURSOR;
|
|
c1 SYS_REFCURSOR;
|
|
v INT;
|
|
BEGIN
|
|
OPEN c0 FOR SELECT 1 FROM DUAL;
|
|
CLOSE c0;
|
|
CASE task
|
|
WHEN 'c0:=c1' THEN c0:=c1; -- Expect: Cursor is not open
|
|
WHEN 'c1:=c0' THEN c1:=c0; -- Expect: v is set to 2
|
|
ELSE NULL; -- Expect: Cursor is not open
|
|
END CASE;
|
|
OPEN c1 FOR SELECT 2 FROM DUAL;
|
|
FETCH c0 INTO v;
|
|
CLOSE c1;
|
|
DBMS_OUTPUT.PUT_LINE('v=' || v);
|
|
END;
|
|
/
|
|
DELIMITER ;/
|
|
--error ER_SP_CURSOR_NOT_OPEN
|
|
CALL p1('');
|
|
--error ER_SP_CURSOR_NOT_OPEN
|
|
CALL p1('c0:=c1');
|
|
CALL p1('c1:=c0');
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Aliasing: different variable scope
|
|
--echo #
|
|
|
|
DELIMITER /;
|
|
CREATE PROCEDURE p1 AS
|
|
c0 SYS_REFCURSOR;
|
|
v INT;
|
|
BEGIN
|
|
DECLARE
|
|
c1 SYS_REFCURSOR;
|
|
BEGIN
|
|
OPEN c1 FOR SELECT 1 AS c FROM DUAL;
|
|
c0:= c1;
|
|
END;
|
|
-- Although c1 who opened the cursor goes out of the scope here,
|
|
-- the alias still works:
|
|
FETCH c0 INTO v;
|
|
DBMS_OUTPUT.PUT_LINE('v='||v);
|
|
END;
|
|
/
|
|
DELIMITER ;/
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # A comprex script with many OPEN, FETCH, CLOSE statements
|
|
--echo #
|
|
|
|
DELIMITER /;
|
|
|
|
CREATE OR REPLACE PROCEDURE p1 AS
|
|
c0 SYS_REFCURSOR;
|
|
c1 SYS_REFCURSOR;
|
|
v0 VARCHAR(32);
|
|
v1 VARCHAR(32);
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('test1');
|
|
OPEN c0 FOR SELECT '10' FROM DUAL UNION SELECT '11' FROM DUAL;
|
|
c1:= c0;
|
|
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' || 'c1%ISOPEN=' || bool_to_char(c1%ISOPEN));
|
|
|
|
DBMS_OUTPUT.PUT_LINE('test1a');
|
|
CLOSE c1;
|
|
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' || 'c1%ISOPEN=' || bool_to_char(c1%ISOPEN));
|
|
BEGIN
|
|
FETCH c0 INTO v0;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN v0:='<FETCH c0 FAILED>';
|
|
END;
|
|
BEGIN
|
|
FETCH c1 INTO v1;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN v1:='<FETCH c1 FAILED>';
|
|
END;
|
|
DBMS_OUTPUT.PUT_LINE('v0=' || v0 || ' v1=' || v1);
|
|
|
|
DBMS_OUTPUT.PUT_LINE('test2:');
|
|
OPEN c1 FOR SELECT '20' FROM DUAL UNION SELECT '21' FROM DUAL UNION SELECT '22' FROM DUAL;
|
|
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' || 'c1%ISOPEN=' || bool_to_char(c1%ISOPEN));
|
|
BEGIN
|
|
FETCH c0 INTO v0;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN v0:='<FETCH c0 FAILED>';
|
|
END;
|
|
BEGIN
|
|
FETCH c1 INTO v1;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN v1:='<FETCH c1 FAILED>';
|
|
END;
|
|
DBMS_OUTPUT.PUT_LINE('v0=' || v0 || ' v1=' || v1);
|
|
|
|
DBMS_OUTPUT.PUT_LINE('test2a');
|
|
CLOSE c1;
|
|
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' || 'c1%ISOPEN=' ||bool_to_char(c1%ISOPEN));
|
|
BEGIN
|
|
FETCH c0 INTO v0;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN v0:='<FETCH c0 FAILED>';
|
|
END;
|
|
BEGIN
|
|
FETCH c1 INTO v1;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN v1:='<FETCH c1 FAILED>';
|
|
END;
|
|
DBMS_OUTPUT.PUT_LINE('v0=' || v0 || ' v1=' || v1);
|
|
|
|
DBMS_OUTPUT.PUT_LINE('test3');
|
|
OPEN c0 FOR SELECT '30' FROM DUAL UNION SELECT '31' FROM DUAL;
|
|
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' || 'c1%ISOPEN=' ||bool_to_char(c1%ISOPEN));
|
|
FETCH c0 INTO v0;
|
|
FETCH c1 INTO v1;
|
|
DBMS_OUTPUT.PUT_LINE('v0=' || v0 || ' v1=' || v1);
|
|
|
|
DBMS_OUTPUT.PUT_LINE('test4');
|
|
OPEN c0 FOR SELECT 'c0-40' FROM DUAL UNION SELECT 'c0-41' FROM DUAL;
|
|
OPEN c1 FOR SELECT 'c1-40' FROM DUAL UNION SELECT 'c1-41' FROM DUAL;
|
|
FETCH c0 INTO v0;
|
|
FETCH c1 INTO v1;
|
|
DBMS_OUTPUT.PUT_LINE('v0=' || v0 || ' v1=' || v1);
|
|
|
|
END;
|
|
/
|
|
DELIMITER ;/
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
DROP PACKAGE DBMS_OUTPUT;
|
|
DROP FUNCTION bool_to_char;
|