Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-10914

ROW data type for stored routine variables

    XMLWordPrintable

Details

    • 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

    Description

      Add support for the ROW data type variables in stored routines according to this SQL Standard syntax:

      <row type> ::= ROW <row type body>
       
      <row type body> ::= <left paren> <field definition> [ { <comma> <field definition> }... ] <right paren>
       
      <field definition> ::= <field name> <data type>
       
      <data type> ::= <predefined type>
      

      and add support for a new expression type, a ROW field reference, as follows:

      <field reference> ::= <row variable> <period> <field name>
      

      where <row variable> is a stored routine variable declared using <row type>.

      Example:

      DELIMITER $$
      CREATE PROCEDURE p1()
      BEGIN
        DECLARE a ROW (c1 INT, c2 VARCHAR(10));
        SET a.c1= 10;
        SET a.c2= 'test';
        INSERT INTO t1 VALUES (a.c1, a.c2);
      END;
      $$
      DELIMITER ;
      CALL p1();
      

      This task is needed to create infrastructure for MDEV-10593

      This task is also needed as a prerequisite for MDEV-10581, where this statement:

      FOR rec IN cursor
      

      will automatically declare an index variable rec of the ROW data type, according to the cursor structure.

      Row data type features:

      • Declaration of a ROW type stored routine variable (both local variables and parameters)
      • Declaration of a ROW type stored procedure OUT parameter
      • Default values in a ROW type variable declaration (e.g. DEFAULT ROW(1,2))
      • Assignment of a ROW type variable from another ROW type variable (using the SET command and the := operator in sql_mode=ORACLE)
      • Assignment of a ROW type variable from a ROW() function result (using the SET command and the := operator in sql_mode=ORACLE)
      • Passing a ROW type variable and a ROW() function result to stored routines
      • Comparison of a ROW type variable to another ROW type variable
      • Comparison of a ROW type variable to ROW() function

      Row field features:

      ROW fields (members) will act as normal variables, and will be able to appear in all query parts where an SP variable is allowed:

      • assignment (using the SET command)

        SET a.x= 10, a.y=20, a.z= b.z;
        

      • assignment (sql_mode=ORACLE specific syntax)

        a.x:= 10;
        a.x:= b.x;
        

      • passing to functions and operators

        SELECT f1(rec.a), rec.a<10;
        

      • clauses: select list, WHERE, HAVING, LIMIT, etc

        SELECT var.a, t1.b FROM t1 WHERE t1.b=var.b LIMIT var.c;
        

      • INSERT values

        INSERT INTO t1 VALUES (rec.a, rec.b, rec.c);
        

      • SELECT .. INTO targets

        SELECT a,b INTO rec.a, rec.b FROM t1 WHERE t1.id=10;
        

      • Dynamic SQL out parameters (EXECUTE and EXECUTE IMMEDIATE)

        EXECUTE IMMEDIATE 'CALL proc_with_out_param(?)' USING rec.a;
        

      Features not supported:

      The following features are out of scope of this task and will be implemented separately:

      • Returning a ROW type expression from a stored function (see MDEV-12252). This will need some grammar change to support field names after parentheses:

        SELECT f1().x FROM DUAL;
        

      • Returning a ROW type expression from a built-in hybrid type function, such as CASE, IF, etc.
      • ROW of ROWs

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.