Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13139

CREATE PACKAGE: package-wide declarations

Details

    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 declarations in the CREATE PACKAGE statement:

      • TYPE type_name IS REF CURSOR – weak and strong cursor types
      • TYPE type_name IS RECORD (..) – record types
      • TYPE .. TABLE OF .. INDEX BY – collection types
      • Variables (all kinds of, including RECORD, TABLE OF, REF CURSOS, SYS_REFCURSOR)
      • Exceptions

      Example (a variable declaration):

      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

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34323 Oracle compatibility project 3

          • Major - Major loss of function.
          • Open
          is blocked by

          Technical task - A technical task. MDEV-13292 Move the code from sp_head::init() to sp_head::sp_head()

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34317 DECLARE TYPE type_name IS RECORD (..) with scalar members in stored routines

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34319 Associative arrays: DECLARE TYPE .. TABLE OF .. INDEX BY in stored routines

          • Critical - Crashes, loss of data, severe memory leak.
          • In Review
          is duplicated by

          Task - A task that needs to be done. MDEV-27248 Support for global package variables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-35973 Oracle Compatibility Project 1 for 2025

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36053 Syntax error on a CURSOR..IS declaration in PACKAGE BODY

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment -

            Was closed in a mistake

            bar Alexander Barkov added a comment - Was closed in a mistake
            iqbal Iqbal Hassan added a comment -

            According to https://docs.oracle.com/database/121/LNPLS/cursor_variable.htm#LNPLS01312 - "You cannot declare a cursor variable in a package specification.", where cursor variables refers to REF CURSOR and SYS_REFCURSOR.

            iqbal Iqbal Hassan added a comment - According to https://docs.oracle.com/database/121/LNPLS/cursor_variable.htm#LNPLS01312 - "You cannot declare a cursor variable in a package specification.", where cursor variables refers to REF CURSOR and SYS_REFCURSOR.

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.