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

XML

Word

Printable

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.

blocks

MDEV-12461 TYPE OF and ROW TYPE OF anchored data types for stored routine variables

relates to

MDEV-12333 Allow %ROWTYPE variable fields as FETCH INTO targets

MDEV-12334 Allow %ROWTYPE variable fields as SELECT INTO targets

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.