ROW
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 | +--------+--------+