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

CREATE PACKAGE [BODY] for sql_mode=DEFAULT

    XMLWordPrintable

Details

    Description

      Let's allow the CREATE PACKAGE [BODY] statement in sql_mode=DEFAULT.

      It will work almost like the same statement for sql_mode=ORACLE, but will use SQL/PSM dialect instead of PL/SQL dialect in all parts of the statement, including:

      • routine declarations
      • variable declatations
      • initialization section
      • etc

      For example:

      SET sql_mode='';
      		 
       
      		 
      DELIMITER $$
      		 
      CREATE OR REPLACE PACKAGE pkg
      		 
        PROCEDURE p1();
      		 
        FUNCTION f1() RETURNS INT;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;


      DELIMITER $$
      		 
      CREATE OR REPLACE PACKAGE BODY pkg
      		 
        -- variable declarations
      		 
        DECLARE a INT DEFAULT 11;
      		 
        DECLARE b INT DEFAULT 10;
      		 
       
      		 
        -- routine declarations
      		 
        PROCEDURE p1()
      		 
        BEGIN
      		 
          SELECT CURRENT_USER;
      		 
        END;
      		 
        FUNCTION f1() RETURNS INT
      		 
        BEGIN
      		 
          RETURN a;
      		 
        END;
      		 
       
      		 
        -- package initialization section
      		 
        SET a=a-b;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;

      Invocation examples:

      MariaDB [test]> SELECT pkg.f1();
      		 
      +----------+
      		 
      | pkg.f1() |
      		 
      +----------+
      		 
      |        1 |
      		 
      +----------+


      MariaDB [test]> CALL pkg.p1();
      		 
      +---------------+
      		 
      | CURRENT_USER  |
      		 
      +---------------+
      		 
      | bar@localhost |
      		 
      +---------------+

      In the future we can add CREATE MODULE on top of this work, for sql_mode=DB2, for IBM DB2 compatibility.

      Attachments

        Issue Links

          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32219 Shift/reduce grammar conflict: GRANT .. ON FUNCTION

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

          Task - A task that needs to be done. MDEV-32220 sql_yacc.yy: unify the drop_routine rule

          • Major - Major loss of function.
          • Closed
          relates to

          Technical task - A technical task. MDEV-10591 Oracle-style packages

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33384 ALTER PACKAGE [ BODY ] statement

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33385 Support package routines in metadata views

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33386 Wrong error message on `GRANT .. ON PACKAGE no_such_package ..`

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33395 I_S views to list stored packages

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-33382 Documentation fixes for CREATE PACKAGE and CREATE PACKAGE BODY

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33399 Package variables return a wrong result when changed inside a function

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33402 Calling a private package routine by its 3-step name does not fail

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-33403 Document stored packages overview

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33428 Error messages ER_PACKAGE_ROUTINE_* are not good enough

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

          Web Link MySQL feature request: Please add CREATE MODULE syntax, or Oracle PACKAGE equivalent

          Web Link Packages in MariaDB default mode - Peter Gulutzan's blog

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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