[MDEV-12011] sql_mode=ORACLE: cursor%ROWTYPE in variable declarations - Jira

XML

Word

Printable

Details

Type: Technical task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3
Fix Version/s: 10.3.0
Component/s: Parser, Stored routines
Labels:
- Compatibility

Sprint:
10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

Description

This task will implement Oracle-stype %ROWTYPE declaration for cursors, for sql_mode=ORACLE.

Example:

  CURSOR cur IS SELECT a,b FROM t1;

  rec cur%ROWTYPE;

The record rec can store the entire row of data fetched from the cursor cur. There is no a need to specify column names and data types. They're automatically copied from the result set of the cursor cur.

A complete working example:

SET sql_mode=ORACLE;

DROP TABLE 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');

DROP PROCEDURE p1;

DELIMITER $$

CREATE PROCEDURE p1 AS

  CURSOR c IS SELECT a,b FROM t1;

BEGIN

  DECLARE

    rec c%ROWTYPE;

  BEGIN

    OPEN c;

    LOOP

      FETCH c INTO rec;

      EXIT WHEN c%NOTFOUND;

      SELECT 'rec=(' || rec.a ||','|| rec.b||')' AS c FROM dual;

    END LOOP;

    CLOSE c;

  END;

END;

$$

DELIMITER ;

CALL p1();

Note, in Oracle it's possible to use %ROWTYPE variables before opening the referenced cursor, or even without opening it. Also, record variables declared with %ROWTYPE can be initialized by the assignment operator instead of FETCH.

SET sql_mode=ORACLE;

DROP PROCEDURE p1;

DELIMITER $$

CREATE PROCEDURE p1 AS

  CURSOR c IS SELECT 10 AS a,20 AS b FROM t1;

BEGIN

  DECLARE

    rec c%ROWTYPE;

  BEGIN

    rec.a:= 10;

    rec.b:= 20;

    SELECT 'rec=(' || rec.a ||','|| rec.b||')' AS c;

  END;

END;

$$

DELIMITER ;

CALL p1();

%ROWTYPE can be used with open and closed cursors

Also, in MariaDB it's now not possible to declare a variable after cursors (see ~~MDEV-10598~~). So this won't work:

DECLARE

  CURSOR cur IS SELECT a,b FROM t1;

  rec cur%ROWTYPE;

BEGIN

  -- statements

END;

One will have to use an additional nested block:

DECLARE

  CURSOR cur IS SELECT a,b FROM t1;

  DECLARE

    rec cur%ROWTYPE;

  BEGIN

    -- statements

  END;

END;

Attachments

Issue Links

blocks

MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP

Closed

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

Closed

is blocked by

MDEV-10914 ROW data type for stored routine variables

Closed

MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations

Closed

Activity

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2017-02-07 10:10

Updated:: 2020-08-27 07:12

Resolved:: 2017-04-04 06:38

Git Integration

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