Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10764 PL/SQL parser - Phase 2
  3. MDEV-13139

Package-wide variables in CREATE PACKAGE

    XMLWordPrintable

    Details

    • Type: Technical task
    • Status: Stalled (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3, 10.4, 10.5
    • Fix Version/s: None
    • Component/s: Parser, Stored routines
    • Labels:
      None

      Description

      As of version 10.4, MariaDB supports package variables in CREATE PACKAGE BODY only.

      Under terms of this task we'll also allow package wide-variables in the CREATE PACKAGE statement.

      Example:

      DROP PACKAGE pkg1;
      DELIMITER $$
      CREATE PACKAGE pkg1
      AS
        m_count INT:= 0;
        FUNCTION get_count RETURN INT;
        PROCEDURE set_count(new_count INT);
      END;
      $$
      CREATE PACKAGE BODY pkg1
      AS
        FUNCTION get_count RETURN INT
        AS
        BEGIN
          m_count:= m_count + 1;
          RETURN m_count;
        END;
        PROCEDURE set_count(new_count INT)
        AS
        BEGIN
          m_count:= new_count;
        END;
      END;
      $$
      DELIMITER ;
      SELECT pkg1.get_count() FROM dual; -- this returns 1
      SELECT pkg1.get_count() FROM dual; -- this returns 2
      CALL pkg1.set_count(30);
      SELECT pkg1.get_count() FROM dual;  -- this returns 31
      

      Note, unlike stored routines parameters, package-wide VARCHAR variables must be declared with length.

      Package variables are initialized on the first package invocation in the current session.

      Variable declarations may not refer to functions declared in the same package. So this script will return an error:

      DROP PACKAGE pkg1;
      DELIMITER $$
      CREATE PACKAGE pkg1
      AS
        m_count INT:= init_count();
        FUNCTION init_count RETURN INT;
      END;
      $$
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              bar Alexander Barkov
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated: