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

DECLARE TYPE type_name IS RECORD (..) with scalar members in stored routines

Details

    Description

      Let's add support for TYPE type_name IS RECORD (..) inside the DECLARE section stored routines and anonymous blocks, with these limitations:

      • The record members can be of scalar data types only
      • The NOT NULL clause inside members won't be supported
      • The default values for members won't be supported

      Example:

      DECLARE
        TYPE DeptRecTyp IS RECORD (
          dept_id    NUMBER(4),
          dept_name  VARCHAR2(30),
          mgr_id     NUMBER(6),
          loc_id     NUMBER(4)
        );
        dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700);
        str VARCHAR(1024);
      BEGIN
        str:= 
         'dept_id:   ' || dept_rec.dept_id   || '; ' ||
         'dept_name: ' || dept_rec.dept_name || '; ' ||
         'mgr_id:    ' || dept_rec.mgr_id    || '; ' ||
         'loc_id:    ' || dept_rec.loc_id;
        DBMS_OUTPUT.PUT_LINE(str);
      END;
      

      dept_id: 11; dept_name: a; mgr_id: 201; loc_id: 1700
      

      Record members should support the anchored %TYPE attribute:

      CREATE TABLE t1
      (
          dept_id    NUMBER(4),
          dept_name  VARCHAR2(30),
          mgr_id     NUMBER(6),
          loc_id     NUMBER(4)
      );
       
      DECLARE
        TYPE DeptRecTyp IS RECORD (
          dept_id    t1.dept_id%TYPE,
          dept_name  t1.dept_name%TYPE,
          mgr_id     t1.mgr_id%TYPE,
          loc_id     t1.loc_id%TYPE
        );
        dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700);
        str VARCHAR(1024);
      BEGIN
        str:= 
         'dept_id:   ' || dept_rec.dept_id   || '; ' ||
         'dept_name: ' || dept_rec.dept_name || '; ' ||
         'mgr_id:    ' || dept_rec.mgr_id    || '; ' ||
         'loc_id:    ' || dept_rec.loc_id;
        DBMS_OUTPUT.PUT_LINE(str);
      END;
      

      Attachments

        Issue Links

          Activity

            Features outside of the scope of this task

            Declaring records using SUBTYPE will be implemented in a separate task when needed:

            CREATE TABLE t1
            (
                dept_id    NUMBER(4),
                dept_name  VARCHAR2(30),
                mgr_id     NUMBER(6),
                loc_id     NUMBER(4)
            );
             
            DECLARE
              SUBTYPE DeptRecTyp IS t1%ROWTYPE;
              dept_rec DeptRecTyp;
              str VARCHAR(1024);
            BEGIN
              dept_rec.dept_id  := 11;
              dept_rec.dept_name:= 'a';
              dept_rec.mgr_id   := 201;
              dept_rec.loc_id   := 1700;
              str:= 
               'dept_id:   ' || dept_rec.dept_id   || '; ' ||
               'dept_name: ' || dept_rec.dept_name || '; ' ||
               'mgr_id:    ' || dept_rec.mgr_id    || '; ' ||
               'loc_id:    ' || dept_rec.loc_id;
              DBMS_OUTPUT.PUT_LINE(str);
            END;
            

            NOT NULL and default clauses will be implemented in a separate task when needed:

            DECLARE
              TYPE DeptRecTyp IS RECORD (
                dept_id    NUMBER(4) NOT NULL := 10,
                dept_name  VARCHAR2(30) NOT NULL := 'Administration',
                mgr_id     NUMBER(6) := 200,
                loc_id     NUMBER(4) := 1700
              );
              dept_rec DeptRecTyp;
              str VARCHAR(1024);
            BEGIN
              str:= 
               'dept_id:   ' || dept_rec.dept_id   || '; ' ||
               'dept_name: ' || dept_rec.dept_name || chr(13) ||
               'mgr_id:    ' || dept_rec.mgr_id    || chr(13) ||
               'loc_id:    ' || dept_rec.loc_id;
              DBMS_OUTPUT.PUT_LINE(str);
            END;
            

            dept_id: 10; dept_name: Administration mgr_id: 200 loc_id: 1700 
            

            Records with records in members will be implemented in a separate task when needed:

            DECLARE
              TYPE name_rec IS RECORD (
                first  VARCHAR(64),
                last   VARCHAR(64)
              );
             
              TYPE contact IS RECORD (
                name  name_rec,     -- a nested record
                phone VARCHAR(32)
              );
              person contact;
            BEGIN
              person.name.first := 'John';
              person.name.last := 'Brown';
              person.phone := '1-654-222-1234';
              DBMS_OUTPUT.PUT_LINE (
                person.name.first  || ' ' ||
                person.name.last   || ', ' ||
                person.phone
              );
            END;
            

            John Brown, 1-654-222-1234 
            

            bar Alexander Barkov added a comment - Features outside of the scope of this task Declaring records using SUBTYPE will be implemented in a separate task when needed: CREATE TABLE t1 ( dept_id NUMBER(4), dept_name VARCHAR2(30), mgr_id NUMBER(6), loc_id NUMBER(4) );   DECLARE SUBTYPE DeptRecTyp IS t1%ROWTYPE; dept_rec DeptRecTyp; str VARCHAR (1024); BEGIN dept_rec.dept_id := 11; dept_rec.dept_name:= 'a' ; dept_rec.mgr_id := 201; dept_rec.loc_id := 1700; str:= 'dept_id: ' || dept_rec.dept_id || '; ' || 'dept_name: ' || dept_rec.dept_name || '; ' || 'mgr_id: ' || dept_rec.mgr_id || '; ' || 'loc_id: ' || dept_rec.loc_id; DBMS_OUTPUT.PUT_LINE(str); END ; NOT NULL and default clauses will be implemented in a separate task when needed: DECLARE TYPE DeptRecTyp IS RECORD ( dept_id NUMBER(4) NOT NULL := 10, dept_name VARCHAR2(30) NOT NULL := 'Administration' , mgr_id NUMBER(6) := 200, loc_id NUMBER(4) := 1700 ); dept_rec DeptRecTyp; str VARCHAR (1024); BEGIN str:= 'dept_id: ' || dept_rec.dept_id || '; ' || 'dept_name: ' || dept_rec.dept_name || chr(13) || 'mgr_id: ' || dept_rec.mgr_id || chr(13) || 'loc_id: ' || dept_rec.loc_id; DBMS_OUTPUT.PUT_LINE(str); END ; dept_id: 10; dept_name: Administration mgr_id: 200 loc_id: 1700 Records with records in members will be implemented in a separate task when needed: DECLARE TYPE name_rec IS RECORD ( first VARCHAR (64), last VARCHAR (64) ); TYPE contact IS RECORD ( name name_rec, -- a nested record phone VARCHAR (32) ); person contact; BEGIN person. name . first := 'John' ; person. name . last := 'Brown' ; person.phone := '1-654-222-1234' ; DBMS_OUTPUT.PUT_LINE ( person. name . first || ' ' || person. name . last || ', ' || person.phone ); END ; John Brown, 1-654-222-1234
            bar Alexander Barkov added a comment - The patch at https://github.com/MariaDB/server/pull/3515/commits/2eb26419c47aab6f64deae16b1ca8a2c5d47c478 looks ok to push.

            Hello elenst and QA team,

            Please find this branch for testing:

            https://github.com/MariaDB/server/tree/bb-11.7-bar-MDEV-34317-ora-record

            Thanks.

            bar Alexander Barkov added a comment - Hello elenst and QA team, Please find this branch for testing: https://github.com/MariaDB/server/tree/bb-11.7-bar-MDEV-34317-ora-record Thanks.

            ok to push

            ramesh Ramesh Sivaraman added a comment - ok to push

            People

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