The Ultimate Guide to High Availability with MariaDB

ROW

Syntax

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

Contents

  1. Syntax
  2. Description
  3. Features
    1. ROW fields as normal variables
    2. ROW type variables as FETCH targets
    3. ROW type variables as SELECT...INTO targets
  4. Features not implemented
  5. Examples
    1. Declaring a ROW in a stored procedure
    2. FETCH Examples
    3. SELECT...INTO Examples
  6. See Also

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;

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:

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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.