mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-08-17 07:51:36 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/sp-cursor-package-body.result
Alexander Barkov 385957a77c MDEV-36053 CURSOR declarations in PACKAGE BODY 
This change adds CURSOR declarations inside PACKAGE BODY.

PL/SQL mode:

  SET sql_mode=ORACLE;
  CREATE PACKAGE BODY pkg AS
    CURSOR mc0 IS SELECT c0, c1 FROM t1;
    PROCEDURE p1 AS
      rec mc0%ROWTYPE;
    BEGIN
      OPEN mc0;
      FETCH mc0 INTO rec;
      CLOSE mc0
    END;
  END;
  /

SQL/PSM mode:

  SET sql_mode=DEFAULT;
  CREATE PACKAGE BODY pkg
    mc0 CURSOR FOR SELECT c0, c1 FROM t1;
    PROCEDURE p1()
    BEGIN
      DECLARE rec ROW TYPE OF mc0;
      OPEN mc0;
      FETCH mc0 INTO rec;
      CLOSE mc0
    END;
  END;
  /

PACKAGE BODY cursors like local cursors
(declared inside a FUNCTION or a PROCEDURE) support:
  - OPEN/FETCH/CLOSE
  - FOR rec IN cur - an explicit cursor loop
  - Using a cursor row as an anchored variable data type:
    * DECLARE var cur%ROWTYPE;     -- sql_mode=ORACLE
    * DECLARE var ROW TYPE OF cur; -- sql_mode=DEFAULT

The patch details:

- Changing various class members and function/method parameters which
  store a CURSOR run-time address from "uint" to sp_rcontext_addr.
  A few classes now derive from sp_rcontext_addr instead of having
  a "uint m_cursor;" member.

  This change uses the same idea with what we did for SP variables,
  when we implemented PACKAGE BODY variables a few years ago.

- Fixing the grammar in sql_yacc.yy to allow CURSOR declarations
  inside PACKAGE BODY.

- Moving the C++ code handing the "CLOSE_SYM ident" grammar
  and creating an sp_instr_cclose instance from sql_yacc.yy
  into a new method LEX::sp_close().
  This is to have the grammar file smaller.
  Also, this code has significantly changed anyway.

- Adding a new class sp_pcontext_top. It's used for the top level parse
  context (of an sp_head). Note, its children contexts still use the old
  class sp_pcontext.

  sp_pcontext_top context additionally to sp_pcontext has:

    const sp_head *m_sp; -- The pointer to the sp_head owning this context
    Dynamic_array<sp_pcursor> m_member_cursors; -- PACKAGE BODY wide cursors

  m_sp->m_parent->get_parse_context() is used to find the sp_pcontext
  belonging to the parent PACKAGE BODY from a sp_pcontext_top instance
  belonging to a PROCEDURE/FUNCTION sp_pcontext_top.

- Adding a new member in sp_rcontext:
    Dynamic_array<sp_cursor*> m_member_cursors;
  It's used to store run-time data of PACKAGE BODY wide cursors.

- Adding a new class sp_instr_copen2. It's used to open PACKAGE BODY cursors.
  Unlike the usual cursors, PACKAGE BODY cursors:
  * do not use the cursor stack (sp_rcontext::m_cstack)
  * do not need a preceeding sp_instr_cpush
  * do not need a following sp_instr_cpop

  All cursor information such as "sp_lex_cursor" resides inside
  sp_instr_copen2 itself (rather than inside sp_instr_cpush which is
  used to store "sp_lex_cursor" in case of sp_instr_copen).

  Note, the other cursor related instructions:
    sp_instr_cfetch
    sp_instr_cclose
    sp_instr_cursor_copy_struct
  do not need sp_instr_xxx2 counter-parts.
  Thy just use sp_rcontext_addr to address cursors.

- Adding Sp_rcontext_handler_member
  It's used to handle PACKAGE BODY members:
  cursors and variables declared in the PACKAGE BODY,
  when they are accessed from its executable initialization section:

    CREATE PACKAGE BODY pkg AS
      CURSOR mc0 IS SELECT c0, c1 FROM t1; -- A member (PACKAGE BODY cursor)
      mv0 mc0%ROWTYPE;                     -- A member (PACKAGE BODY variable)
      PROCEDURE p1 AS
      BEGIN
        -- Accessing members from here use sp_rcontext_handler_package_body
        -- (members of the parent PACKAGE BODY)
        OPEN mc0;
        FETCH mc0 INTO mv0;
        CLOSE mc0;
      END;
    BEGIN
      -- NEW:
      -- Accessing members from here use sp_rcontext_handler_member
      -- (PACKAGE BODY own members)
      OPEN mc0;
      FETCH mc0 INTO mv0;
      CLOSE mc0;
    END;
    /

  Member variables and cursor are now marked with the "MEMBER." prefix
  in the "SHOW PACKAGE BODY code" output.
  Some old MTR tests have been re-recorded accordingly.

- Adding new virtual methods into Sp_rcontext_handler:

  virtual const sp_variable *get_pvariable(const sp_pcontext *pctx,
                                           uint offset) const;

  virtual const sp_pcursor *get_pcursor(const sp_pcontext *pctx,
                                        uint offset) const;

  They're used from sp_instr::print() virtual implementations.
  They internally calculate a proper sp_pcontext using as a parameter
  the sp_pcontext pointed by sp_instr::m_ctx.

  For example, Sp_handler_package_body::get_pvariable()/get_pcursor()
  accesses to this sp_pcontext:
      m_ctx->top_context()->m_sp->m_parent->get_parse_context(),
  i.e. the parse context of the PACKAGE BODY which is the parent for
   the current package PROCEDURE of FUNCTION an sp_instr belongs to.

- Adding a new method LEX::find_cursor(). It searches for a cursor in
  this order:
  * Local cursors in the nearst upper BEGIN/END block.
  * A member cursor of the current PACKAGE BODY
    (used from the PACKAGE BODY initialization section)
  * A member cursor of the parrent PACKAGE BODY
    (used from a package PROCEDURE or a package FUNCTION)

  Adding a new method LEX::find_cursor_with_error().
  In case when a cursor is not found, it automatically
  raises the ER_SP_CURSOR_MISMATCH SQL condition into
  the diagnostics area.

- Adding a new method sp_head::add_instr_copenX().
  It creates sp_instr_copen for local cursors,
  or sp_instr_copen2 for non-local cursors.

- Adding a new abstract class sp_lex_cursor_instr.
  It's used a common parent class for a few sp_instr_xxx classes,
  including the new sp_instr_copen2.
  This change is needed to avoid code duplication.

- Adding a new protected method sp_instr::print_cmd_and_var(), to print
  an instruction using this format: "command name@offset".
  It's used from a few implementations of sp_instr_xxx::print(),
  including sp_instr_copen2::print().
  This change is also needed to avoid code duplication.

- Moving the definition of "class Sp_rcontext_handler" from item.h
  into a new file sp_rcontext_handler.h
  This is to maitain header dependencies easier, as well as to move
  declarations not directly related to "class Item" outside of item.h

- Adding a new method sp_pcontext::frame_for_members(), to distinguish
  easier between local cursors/variables and PACKAGE BODY cursors/variables.

- Fixing "struct Lex_for_loop_st" to addionally store
  a const pointer to Sp_rcontext_handler, to distinguish between:
    * FOR rec IN local_cursor
    * FOR rec IN package_body_cursor
2025-06-27 11:17:19 +04:00

388 lines
7 KiB
Text
Raw Permalink Blame History

#
# MDEV-36053 CURSOR declarations in PACKAGE BODY
#
#
# FUNCTION:
# FETCH package_body_cursor INTO package_body_variable
#
CREATE PACKAGE pkg
FUNCTION f1() RETURNS INT;
END;
/
CREATE PACKAGE BODY pkg
DECLARE vc INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT 1 AS c FROM DUAL;
FUNCTION f1() RETURNS INT
BEGIN
OPEN cur;
FETCH cur INTO vc;
CLOSE cur;
RETURN vc;
END;
END;
/
SELECT pkg.f1() FROM DUAL;
pkg.f1()
1
DROP PACKAGE pkg;
#
# FUNCTION:
# FETCH package_body_cursor INTO local_variable
#
CREATE PACKAGE pkg
FUNCTION f1() RETURNS INT;
END;
/
CREATE PACKAGE BODY pkg
DECLARE cur CURSOR FOR SELECT 1 AS c FROM DUAL;
FUNCTION f1() RETURNS INT
BEGIN
DECLARE vc INT DEFAULT 0;
OPEN cur;
FETCH cur INTO vc;
CLOSE cur;
RETURN vc;
END;
END;
/
SELECT pkg.f1() FROM DUAL;
pkg.f1()
1
DROP PACKAGE pkg;
#
# PACKAGE BODY initialization section:
# FETCH package_body_cursor INTO package_body_variable
#
CREATE PACKAGE pkg
FUNCTION f1() RETURNS INT;
END;
/
CREATE PACKAGE BODY pkg
DECLARE cur CURSOR FOR SELECT 1 AS c FROM DUAL;
DECLARE vc INT;
FUNCTION f1() RETURNS INT
BEGIN
RETURN vc;
END;
-- initialization
OPEN cur;
FETCH cur INTO vc;
CLOSE cur;
END;
/
SELECT pkg.f1() FROM DUAL;
pkg.f1()
1
DROP PACKAGE pkg;
#
# PACKAGE BODY initialization:
# FETCH package_body_cursor INTO package_body_variable
# FUNCTION:
# FETCH package_body_cursor INTO package_body_variable
#
CREATE PACKAGE pkg
FUNCTION f1() RETURNS INT;
END;
/
CREATE PACKAGE BODY pkg
DECLARE cur CURSOR FOR SELECT 1 AS c FROM DUAL;
DECLARE vc0 INT;
DECLARE vc1 INT;
FUNCTION f1() RETURNS INT
BEGIN
OPEN cur;
FETCH cur INTO vc1;
RETURN vc0 + vc1;
END;
-- initialization
OPEN cur;
FETCH cur INTO vc0;
CLOSE cur;
END;
/
SELECT pkg.f1() FROM DUAL;
pkg.f1()
2
SELECT pkg.f1() FROM DUAL;
ERROR 24000: Cursor is already open
DROP PACKAGE pkg;
#
# PACKAGE BODY initialization nested BEGIN..END block:
# FETCH package_body_cursor INTO local_variable
# FUNCTION:
# FETCH package_body_cursor INTO package_body_variable
#
CREATE PACKAGE pkg
FUNCTION f1() RETURNS INT;
END;
/
CREATE PACKAGE BODY pkg
DECLARE cur CURSOR FOR SELECT 1 AS c FROM DUAL;
DECLARE vc0 INT;
DECLARE vc1 INT;
FUNCTION f1() RETURNS INT
BEGIN
OPEN cur;
FETCH cur INTO vc1;
RETURN vc0 + vc1;
END;
-- initialization
BEGIN
DECLARE vc2 INT;
OPEN cur;
FETCH cur INTO vc2;
CLOSE cur;
SET vc0= vc2;
END;
END;
/
SELECT pkg.f1() FROM DUAL;
pkg.f1()
2
SELECT pkg.f1() FROM DUAL;
ERROR 24000: Cursor is already open
DROP PACKAGE pkg;
#
# PACKAGE BODY initialization:
# OPEN package_body_cursor
# FUNCTION:
# FETCH package_body_cursor INTO package_body_variable
# PROCEDURE:
# CLOSE package_body_cursor
#
CREATE PACKAGE pkg
FUNCTION f1() RETURNS INT;
PROCEDURE p1close();
END;
/
CREATE PACKAGE BODY pkg
DECLARE cur CURSOR FOR SELECT 1 AS c FROM DUAL;
DECLARE vc0 INT;
FUNCTION f1() RETURNS INT
BEGIN
FETCH cur INTO vc0;
RETURN vc0;
END;
PROCEDURE p1close()
BEGIN
CLOSE cur;
END;
-- initialization
OPEN cur;
END;
/
SELECT pkg.f1() FROM DUAL;
pkg.f1()
1
CALL pkg.p1close();
SELECT pkg.f1() FROM DUAL;
ERROR 24000: Cursor is not open
DROP PACKAGE pkg;
#
# PACKAGE BODY executable section:
# ROW TYPE OF package_body_cursor
# PROCEDURE:
# ROW TYPE OF package_body_cursor
#
CREATE PACKAGE pkg
PROCEDURE p1();
END;
/
CREATE PACKAGE BODY pkg
DECLARE mc0 CURSOR FOR SELECT 0 AS c0, 'mc0' AS c1 FROM DUAL;
DECLARE mc1 CURSOR FOR SELECT 1 AS c0, TIME'10:20:30' AS c1 FROM DUAL;
DECLARE mr00 ROW TYPE OF mc0;
DECLARE mr01 ROW TYPE OF mc1;
DECLARE mr11 ROW TYPE OF mc1;
DECLARE mr10 ROW TYPE OF mc0;
PROCEDURE p1()
BEGIN
DECLARE r00 ROW TYPE OF mc0;
DECLARE r01 ROW TYPE OF mc1;
DECLARE r10 ROW TYPE OF mc1;
DECLARE r11 ROW TYPE OF mc0;
DECLARE r20 TYPE OF r00;
CREATE TABLE t00 AS SELECT r10.c0, r10.c1;
SHOW CREATE TABLE t00;
DROP TABLE t00;
CREATE TABLE t11 AS SELECT r11.c0, r11.c1;
SHOW CREATE TABLE t11;
DROP TABLE t11;
CREATE TABLE t20 AS SELECT r20.c0, r20.c1;
SHOW CREATE TABLE t20;
DROP TABLE t20;
END;
BEGIN
DECLARE e00 ROW TYPE OF mc0;
DECLARE e01 ROW TYPE OF mc1;
DECLARE e10 ROW TYPE OF mc1;
DECLARE e11 ROW TYPE OF mc0;
END;
END;
/
CALL pkg.p1();
Table Create Table
t00 CREATE TABLE `t00` (
`r10.c0` int(11) DEFAULT NULL,
`r10.c1` time DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
Table Create Table
t11 CREATE TABLE `t11` (
`r11.c0` int(11) DEFAULT NULL,
`r11.c1` varchar(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
Table Create Table
t20 CREATE TABLE `t20` (
`r20.c0` int(11) DEFAULT NULL,
`r20.c1` varchar(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP PACKAGE pkg;
#
# PROCEDURE:
# FOR rec IN package_body_cursor
#
CREATE PACKAGE pkg
PROCEDURE p1();
END;
/
CREATE PACKAGE BODY pkg
DECLARE c0 CURSOR FOR SELECT 10 AS c0, 'c10' AS c1 FROM DUAL UNION
SELECT 11 AS c0, 'c11' AS c1 FROM DUAL;
PROCEDURE p1()
BEGIN
FOR r0 IN c0
DO
SELECT r0.c0, r0.c1;
END FOR;
END;
END;
/
CALL pkg.p1();
r0.c0 r0.c1
10 c10
r0.c0 r0.c1
11 c11
DROP PACKAGE pkg;
#
# PROCEDURE:
# FOR rec IN package_body_cursor_with_parameters
#
CREATE PACKAGE pkg
PROCEDURE p1();
END;
/
CREATE PACKAGE BODY pkg
DECLARE c0 CURSOR(c0 INT, c1 VARCHAR(10)) FOR SELECT c0, c1 FROM DUAL;
PROCEDURE p1()
BEGIN
FOR i IN 1..2
DO
FOR r0 IN c0(1,'c1')
DO
SELECT r0.c0, r0.c1;
END FOR;
END FOR;
END;
END;
/
CALL pkg.p1();
r0.c0 r0.c1
1 c1
r0.c0 r0.c1
1 c1
DROP PACKAGE pkg;
#
# PROCEDURE
# FOR rec IN package_body_cursor_with_package_body_variables_in_select_list
#
CREATE PACKAGE pkg
PROCEDURE p1();
END;
/
CREATE PACKAGE BODY pkg
DECLARE v0 INT DEFAULT 1;
DECLARE v1 VARCHAR(10) DEFAULT 'v1';
DECLARE c0 CURSOR FOR SELECT v0, v1 FROM DUAL;
PROCEDURE p1()
BEGIN
FOR i IN 1..2
DO
FOR r0 IN c0
DO
SELECT r0.v0, r0.v1;
END FOR;
END FOR;
END;
END;
/
CALL pkg.p1();
r0.v0 r0.v1
1 v1
r0.v0 r0.v1
1 v1
DROP PACKAGE pkg;
#
# PACKAGE BODY initialization section:
# OPEN,FETCH,CLOSE local_cursor0, INTO local_variable0
# OPEN,FETCH,CLOSE local_cursor0, INTO local_variable1
# FUNCTION:
# OPEN,FETCH,CLOSE package_body_cursor0, INTO package_body_variable0
# OPEN,FETCH,CLOSE package_body_cursor0, INTO package_body_variable1
#
CREATE PACKAGE pkg
FUNCTION f1() RETURNS INT;
END;
/
CREATE PACKAGE BODY pkg
DECLARE mv0 TEXT;
DECLARE mv1 TEXT;
DECLARE mv2 TEXT;
DECLARE mc0 CURSOR FOR SELECT 'mc0' AS c FROM DUAL;
DECLARE mc1 CURSOR FOR SELECT 'mc1' AS c FROM DUAL;
FUNCTION f1() RETURNS TEXT
BEGIN
OPEN mc0;
FETCH mc0 INTO mv0;
CLOSE mc0;
OPEN mc1;
FETCH mc1 INTO mv1;
CLOSE mc1;
RETURN CONCAT(mv0,' ',mv1,' ',mv2);
END;
BEGIN -- package body initialization
DECLARE lv0 TEXT;
DECLARE lv1 TEXT;
DECLARE lc0 CURSOR FOR SELECT 'lc0' AS c FROM DUAL;
DECLARE lc1 CURSOR FOR SELECT 'lc1' AS c FROM DUAL;
OPEN lc0;
FETCH lc0 INTO lv0;
CLOSE lc0;
OPEN lc1;
FETCH lc1 INTO lv1;
CLOSE lc1;
SET mv2= CONCAT(lv0,' ',lv1);
END;
END;
/
SELECT pkg.f1();
pkg.f1()
mc0 mc1 lc0 lc1
DROP PACKAGE pkg;
#
# Duplicate cursor in PACKAGE BODY
#
CREATE PACKAGE pkg
FUNCTION f1() RETURNS INT;
END;
/
CREATE PACKAGE BODY pkg
DECLARE c CURSOR FOR SELECT 'c' AS c FROM DUAL;
DECLARE c CURSOR FOR SELECT 'c' AS c FROM DUAL;
FUNCTION f1() RETURNS TEXT
BEGIN
RETURN NULL;
END;
END;
/
ERROR 42000: Duplicate cursor: c
DROP PACKAGE pkg;
Reference in a new issue View git blame Copy permalink