mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-20 22:52:12 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/sql/sp_cursor.h
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

289 lines
7.9 KiB
C++
Raw Permalink Blame History

/*
Copyright (c) 2009, 2025, MariaDB Corporation.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 of the License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */
#ifndef SP_CURSOR_INCLUDED
#define SP_CURSOR_INCLUDED
#include "sp_rcontext_handler.h"
class Server_side_cursor;
class sp_cursor_statistics
{
protected:
ulonglong m_fetch_count; // Number of FETCH commands since last OPEN
ulonglong m_row_count; // Number of successful FETCH since last OPEN
bool m_found; // If last FETCH fetched a row
public:
sp_cursor_statistics()
:m_fetch_count(0),
m_row_count(0),
m_found(false)
{ }
bool found() const
{ return m_found; }
ulonglong row_count() const
{ return m_row_count; }
ulonglong fetch_count() const
{ return m_fetch_count; }
void reset() { *this= sp_cursor_statistics(); }
};
class sp_instr_cpush;
/* A mediator between stored procedures and server side cursors */
class sp_lex_keeper;
class sp_cursor: public sp_cursor_statistics
{
private:
/// An interceptor of cursor result set used to implement
/// FETCH <cname> INTO <varlist>.
class Select_fetch_into_spvars: public select_result_interceptor
{
List<sp_fetch_target> *m_fetch_target_list;
uint field_count;
bool m_view_structure_only;
bool send_data_to_variable_list(List<sp_fetch_target> &vars,
List<Item> &items);
public:
Select_fetch_into_spvars(THD *thd_arg, bool view_structure_only)
:select_result_interceptor(thd_arg),
m_view_structure_only(view_structure_only)
{}
void reset(THD *thd_arg)
{
select_result_interceptor::reinit(thd_arg);
m_fetch_target_list= NULL;
field_count= 0;
}
uint get_field_count() { return field_count; }
void set_spvar_list(List<sp_fetch_target> *vars)
{
m_fetch_target_list= vars;
}
bool send_eof() override { return FALSE; }
int send_data(List<Item> &items) override;
int prepare(List<Item> &list, SELECT_LEX_UNIT *u) override;
bool view_structure_only() const override { return m_view_structure_only; }
};
public:
sp_cursor()
:result(NULL, false),
server_side_cursor(NULL),
m_persistent(false)
{ }
sp_cursor(THD *thd_arg, bool persistent, bool view_structure_only)
:result(thd_arg, view_structure_only),
server_side_cursor(NULL),
m_persistent(persistent)
{}
virtual ~sp_cursor()
{ destroy(); }
virtual sp_lex_keeper *get_lex_keeper() { return nullptr; }
int open(THD *thd, bool check_max_open_cursor_counter= true);
int close(THD *thd);
my_bool is_open() const
{ return MY_TEST(server_side_cursor); }
int fetch(THD *, List<sp_fetch_target> *vars, bool error_on_no_data);
bool export_structure(THD *thd, Row_definition_list *list);
void reset(THD *thd_arg)
{
sp_cursor_statistics::reset();
result.reinit(thd_arg);
server_side_cursor= NULL;
}
/*
Reset a cursor before reopening (two OPEN without CLOSE in between).
This method does not raise ER_SP_CURSOR_ALREADY_OPEN.
It's used to handle:
c SYS_REFCURSOR;
OPEN c FOR SELECT 1;
OPEN c FOR SELECT 2; -- This is allowed without closing the previous OPEN
*/
void reset_for_reopen(THD *thd_arg)
{
if (is_open())
close(thd_arg);
reset(thd_arg);
}
virtual sp_instr_cpush *get_push_instr() { return nullptr; }
private:
Select_fetch_into_spvars result;
Server_side_cursor *server_side_cursor;
bool m_persistent;
void destroy();
};
class sp_cursor_array_element: public sp_cursor
{
uint m_ref_count;
public:
sp_cursor_array_element()
:sp_cursor(),
m_ref_count(0)
{ }
uint ref_count() const { return m_ref_count; }
void ref_count_inc() { m_ref_count++; }
void ref_count_dec(THD *thd)
{
/*
For performance purposes, the SP instructions in sp_head::m_instr
do not guarantee that the number of ref_cursor_inc() calls matches the
number of ref_cursor_dec() calls:
We don't add sp_instr_destruct_variable instructions in these cases:
- before sp_instr_freturn and sp_instr_preturn
- after the very last instruction
(the one before the END of the most outer stored routine block)
So sp_head::execute() can leave with some SYS_REFCURORs variables
still attached to thd->m_statement_cursor elements.
Later they get detached by the sp_rcontext::sp_variable_detach_all()
calls in sp_head::execute_procedure() and sp_head::execute_function().
Executing a bunch of sp_instr_destruct_variable instructions would
be more expensive.
*/
if (m_ref_count > 0)
{
m_ref_count--;
if (!m_ref_count && is_open())
close(thd);
}
}
void reset(THD *thd, uint ref_count)
{
sp_cursor::reset(thd);
m_ref_count= ref_count;
}
};
class sp_cursor_array: public Dynamic_array<sp_cursor_array_element>
{
protected:
Type_ref_null find_unused()
{
for (size_t i= 0 ; i < size(); i++)
{
if (!at(i).is_open() && !at(i).ref_count())
return Type_ref_null((ulonglong) i);
}
return Type_ref_null();
}
Type_ref_null append(THD *thd);
public:
sp_cursor_array()
:Dynamic_array(PSI_INSTRUMENT_MEM, 0)
{}
~sp_cursor_array()
{
free(current_thd);
}
ULonglong_null ref_count(ulonglong offset) const
{
return offset < elements() ?
ULonglong_null((ulonglong) at((size_t) offset).ref_count()) :
ULonglong_null();
}
void ref_count_inc(ulonglong offset)
{
if (offset < elements())
at((size_t) offset).ref_count_inc();
}
void ref_count_dec(THD *thd, ulonglong offset)
{
if (offset < elements())
at((size_t) offset).ref_count_dec(thd);
}
void ref_count_update(THD *thd, const Type_ref_null &old_value,
const Type_ref_null &new_value)
{
if (old_value.is_null())
{
if (!new_value.is_null())
ref_count_inc(new_value.value());
}
else if (new_value.is_null())
{
ref_count_dec(thd, old_value.value());
}
else if (old_value.value() != new_value.value())
{
ref_count_dec(thd, old_value.value());
ref_count_inc(new_value.value());
}
}
/*
Find a cursor at the offset specified by "ref".
@param thd - current thd
@param ref - the field containing the cursor offset
@param for_open - tells if the cursor is needed for OPEN or
for FETCH/CLOSE and determines the behaviour
on dereference failure.
Dereference failure means either of these:
- ref->is_null() returned true.
This happens when the reference SYS_REFCURSOR variable
owning the Field "ref" is not assigned to any cursors yet.
- ref->val_int() returned an offset greater than elements()-1.
This can mean that something went wrong in the code.
If dereference failed, then:
- In case for_open is false the function returns nullptr.
- In case for_open is true, the function searches for an unused cursor.
If all cursors are used, it appends a new cursor to the end of the array.
*/
sp_cursor_array_element *get_cursor_by_ref(THD *thd, Field *ref,
bool for_open);
void close(THD *thd)
{
for (uint i= 0; i < (uint) size(); i++)
{
if (at(i).is_open())
at(i).close(thd);
}
}
void free(THD *thd)
{
close(thd);
free_memory();
}
};
#endif // SP_CURSOR_INCLUDED
Reference in a new issue View git blame Copy permalink