MariaDB starting with 10.3.0
----------------------------
The ROW data type was introduced in MariaDB 10.3.0.

Syntax
------

ROW (<field name> <data type> [{, <field name> <data type>}... ])

Description
-----------

ROW is a data type for stored procedure variables.

Features
--------

ROW fields as normal variables
------------------------------

ROW fields (members) act as normal variables, and are able to appear in all
query parts where a stored procedure variable is allowed:

* Assignment is using the := operator and the SET command:

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

* 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;

ROW type variables as FETCH targets
-----------------------------------

ROW type variables are allowed as FETCH targets:

FETCH cur INTO rec;

where cur is a CURSOR and rec is a ROW type stored procedure variable.

Note, currently an attempt to use FETCH for a ROW type variable returns this
error:

ERROR 1328 (HY000): Incorrect number of FETCH variables

FETCH from a cursor cur into a ROW variable rec works as follows:

* The number of fields in cur must match the number of fields in rec.
 Otherwise, an error is reported.

* Assignment is done from left to right. The first cursor field is assigned to
 the first variable field, the second cursor field is assigned to the second
 variable field, etc.

* Field names in rec are not important and can differ from field names
 in cur.

See FETCH Examples (below) for examples of using this with sql_mode=ORACLE and
sql_mode=DEFAULT.

ROW type variables as SELECT...INTO targets
-------------------------------------------

ROW type variables are allowed as SELECT..INTO targets with some differences
depending on which sql_mode is in use.

* When using sql_mode=ORACLE, table%ROWTYPE and cursor%ROWTYPE
 variables can be used as SELECT...INTO targets.

* Using multiple ROW variables in the SELECT..INTO list will report an
 error.

* Using ROW variables with a different column count than in
 the SELECT..INTO list will report an error.

See SELECT...INTO Examples (below) for examples of using this with
sql_mode=ORACLE and sql_mode=DEFAULT.

Features not implemented
------------------------

The following features are planned, but not implemented yet:

* 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

Examples
--------

Declaring a ROW in a stored procedure
-------------------------------------

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

FETCH Examples
--------------

A complete FETCH example for sql_mode=ORACLE:

CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=oracle;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1 AS
 rec ROW(a INT, b VARCHAR(32));
 CURSOR c IS SELECT a,b FROM t1;
BEGIN
 OPEN c;
 LOOP
  FETCH c INTO rec;
  EXIT WHEN c%NOTFOUND;
  SELECT ('rec=(' || rec.a ||','|| rec.b||')');
 END LOOP;
 CLOSE c;
END;
$$
DELIMITER ;
CALL p1();

A complete FETCH example for sql_mode=DEFAULT:

CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=DEFAULT;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
 DECLARE done INT DEFAULT FALSE;
 DECLARE rec ROW(a INT, b VARCHAR(32));
 DECLARE c CURSOR FOR SELECT a,b FROM t1;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN c;
read_loop:
 LOOP
  FETCH c INTO rec;
  IF done THEN
   LEAVE read_loop;
  END IF;
  SELECT CONCAT('rec=(',rec.a,',',rec.b,')');
 END LOOP;
 CLOSE c;
END;
$$
DELIMITER ;
CALL p1();

SELECT...INTO Examples
----------------------

A SELECT...INTO example for sql_mode=DEFAULT:

SET sql_mode=DEFAULT;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
 DECLARE rec1 ROW(a INT, b VARCHAR(32));
 SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();

The above example returns:

+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
|     10 | b10    |
+--------+--------+

A SELECT...INTO example for sql_mode=ORACLE:

SET sql_mode=ORACLE;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1 AS
 rec1 ROW(a INT, b VARCHAR(32));
BEGIN
 SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();

The above example returns:

+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
|     10 | b10    |
+--------+--------+

An example for sql_mode=ORACLE using table%ROWTYPE variables as SELECT..INTO
targets:

SET sql_mode=ORACLE;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1 AS
 rec1 t1%ROWTYPE;
BEGIN
 SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();

The above example returns:

+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
|     10 | b10    |
+--------+--------+

An example for sql_mode=ORACLE using cursor%ROWTYPE variables as SELECT..INTO
targets:

SET sql_mode=ORACLE;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1 AS
 CURSOR cur1 IS SELECT * FROM t1;
 rec1 cur1%ROWTYPE;
BEGIN
 SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();

The above example returns:

+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
|     10 | b10    |
+--------+--------+

URL: https://mariadb.com/kb/en/row/