[#MDEV-12291] Allow ROW variables as SELECT INTO targets

Under terms of this task we'll add support for ROW type variables as SELECT..INTO targets.

Example:

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();

+--------+--------+

| rec1.a | rec1.b |

+--------+--------+

|     10 | b10    |

+--------+--------+

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();

+--------+--------+

| rec1.a | rec1.b |

+--------+--------+

|     10 | b10    |

+--------+--------+

sql_mode=ORACLE will additionally support 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();

+--------+--------+

| rec1.a | rec1.b |

+--------+--------+

|     10 | b10    |

+--------+--------+

sql_mode=ORACLE will additionally support 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();

+--------+--------+

| rec1.a | rec1.b |

+--------+--------+

|     10 | b10    |

+--------+--------+

An attempt to use multiple ROW variables in the SELECT..INTO list will report an error.

An attempt to use ROW variables with a different column count than in the SELECT..INTO list will report an error.

PL/SQL parser (MDEV-10142) [MDEV-12291] Allow ROW variables as SELECT INTO targets Created: 2017-03-17 Updated: 2020-08-27 Resolved: 2017-03-18
Status:	Closed
Project:	MariaDB Server
Component/s:	Parser
Affects Version/s:	10.3
Fix Version/s:	10.3.0