mariadb/plugin
Alexander Barkov 75142ff496 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.

- 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:
  1.  Local cursors in the nearst surrounding BEGIN/END block.
  2a. A member cursor of the current PACKAGE BODY
      (used from the PACKAGE BODY initialization section)
  OR
  2b. 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.

- Adding a new method sp_pcontext::frame_for_members_candidate(),
  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-07-03 13:36:06 +04:00
..
audit_null MDEV-7389 Request: log warnings into SQL_ERROR_LOG 2023-05-24 13:21:55 +03:00
auth_dialog Merge 10.1 into 10.2 2019-05-13 17:54:04 +03:00
auth_ed25519 Merge branch '11.4' into 11.8 2025-06-18 07:43:24 +02:00
auth_examples Merge branch '10.11' into 11.4 2025-04-26 10:53:02 +02:00
auth_gssapi Fix typos in C comments in miscellaneous files 2025-03-24 13:36:28 +11:00
auth_pam Fix remaining typos 2025-04-29 11:18:00 +10:00
auth_parsec MDEV-35482 Raise the plugin PARSEC maturity 2024-12-06 20:28:46 +01:00
auth_pipe Merge 10.1 into 10.2 2019-05-13 17:54:04 +03:00
auth_socket Merge 11.4 into 11.7 2024-12-02 17:51:17 +02:00
aws_key_management cleanup: CREATE_TYPELIB_FOR() helper 2024-11-05 14:00:47 -08:00
cracklib_password_check Merge 10.6 into 10.11 2024-02-08 15:04:46 +02:00
daemon_example MDEV-34534 main.plugin_load(daemon_example) - AddressSanitizer: Joining already joined thread, aborting 2024-11-18 22:35:40 +11:00
debug_key_management Merge branch '5.5' into 10.1 2019-05-11 22:19:05 +03:00
disks Fix typos in C comments in miscellaneous files 2025-03-24 13:36:28 +11:00
example_key_management Merge branch '5.5' into 10.1 2019-05-11 22:19:05 +03:00
feedback Fix typos in C comments in miscellaneous files 2025-03-24 13:36:28 +11:00
file_key_management MDEV-34712 Add support to sha2 and pbkdf2 key derivation in file_key_management 2025-04-28 13:43:32 +10:00
fulltext MDEV-28671 post-merge fixes 2024-05-27 12:39:02 +02:00
func_test MDEV-27277 Add a warning when max_sort_length is reached 2024-10-22 21:42:53 +07:00
handler_socket Fix typos in C comments in miscellaneous files 2025-03-24 13:36:28 +11:00
hashicorp_key_management Fix remaining typos 2025-04-29 11:18:00 +10:00
locale_info MDEV-31340 Remove MY_COLLATION_HANDLER::strcasecmp() 2024-04-18 15:22:10 +04:00
metadata_lock_info Merge remote-tracking branch 'origin/11.2' into 11.4 2024-07-09 21:45:37 +04:00
password_reuse_check Fix typos in C comments in miscellaneous files 2025-03-24 13:36:28 +11:00
provider_bzip2 SUMMARY/DESCRIPTION for compression provider RPMs 2021-12-08 14:57:37 +01:00
provider_lz4 SUMMARY/DESCRIPTION for compression provider RPMs 2021-12-08 14:57:37 +01:00
provider_lzma SUMMARY/DESCRIPTION for compression provider RPMs 2021-12-08 14:57:37 +01:00
provider_lzo SUMMARY/DESCRIPTION for compression provider RPMs 2021-12-08 14:57:37 +01:00
provider_snappy SUMMARY/DESCRIPTION for compression provider RPMs 2021-12-08 14:57:37 +01:00
qc_info MDEV-33091 pcre2 headers aren't found on Solaris 2024-09-05 12:14:06 +10:00
query_response_time Fix typos in C comments in miscellaneous files 2025-03-24 13:36:28 +11:00
server_audit Merge branch '11.8' into 12.0 2025-05-22 09:22:55 +02:00
simple_password_check Merge branch '10.6' into 10.7 2022-08-08 17:12:32 +02:00
sql_errlog Tag the logger service with ATTRIBUTE_FORMAT 2025-02-12 10:17:44 +01:00
test_sql_service MDEV-28671 post-merge fixes 2024-05-27 12:39:02 +02:00
type_cursor MDEV-36053 CURSOR declarations in PACKAGE BODY 2025-07-03 13:36:06 +04:00
type_geom fix typo spatial_ref_sys 2025-04-22 16:26:36 -04:00
type_inet Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
type_mysql_json Merge 10.6 into 10.11 2024-06-27 10:26:09 +03:00
type_mysql_timestamp MDEV-19123 Change default charset from latin1 to utf8mb4 2024-07-11 10:21:07 +04:00
type_test MDEV-19123 Change default charset from latin1 to utf8mb4 2024-07-11 10:21:07 +04:00
type_uuid Fix typos in C comments in miscellaneous files 2025-03-24 13:36:28 +11:00
user_variables Merge 10.6 into 10.11 2024-01-18 19:22:23 +02:00
userstat Merge remote-tracking branch 'github/bb-11.4-release' into bb-11.8-serg 2025-04-27 19:40:00 +02:00
versioning Merge branch '11.8' into main 2025-04-18 17:11:01 +02:00
win_auth_client Vanilla cleanups and refactorings 2021-10-26 17:07:46 +02:00
wsrep_info test.cnf files should !include default_my.cnf 2024-02-03 11:22:20 +01:00