ROW
This article is currently incomplete.
Note: This page describes features in the source repository for MariaDB 10.3. There are currently no official packages or binaries available for download which contain the features. If you want to try out any of the new features described here you will need to get and compile the code yourself.
MariaDB starting with 10.3.0
The ROW
data type will be introduced in MariaDB 10.3.0.
Syntax
ROW (<field name> <data type> [{, <field name> <data type>}... ])
Contents
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 inrec
. 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 incur
.
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
andcursor%ROWTYPE
variables can be used asSELECT...INTO
targets.
- Using multiple
ROW
variables in theSELECT..INTO
list will report an error.
- Using
ROW
variables with a different column count than in theSELECT..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
:
DROP TABLE IF EXISTS t1; 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
:
DROP TABLE IF EXISTS t1; 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 TABLE IF EXISTS t1; 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 TABLE IF EXISTS t1; 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 TABLE IF EXISTS t1; 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 TABLE IF EXISTS t1; 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 | +--------+--------+