ROW

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.

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:

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

See Also

• STORED PROCEDURES
• DECLARE Variable