[MDEV-36053] CURSOR declarations in PACKAGE BODY - Jira

XML

Word

Printable

Under the scope of this task we'll add support for CURSOR declarations in CREATE PACKAGE BODY.

Currently CURSORs are not supported in CREATE PACKAGE BODY. This script returns a syntax error near 'CURSOR cur IS...':

SET sql_mode=ORACLE;

DROP PACKAGE pkg;

DELIMITER /

CREATE PACKAGE pkg AS

  FUNCTION f1 RETURN INT;

END;

CREATE PACKAGE BODY pkg AS

  vc INT := 0;

  CURSOR cur IS SELECT 1 AS c FROM DUAL; -- Error is raised incorectly

  FUNCTION f1 RETURN INT AS

  BEGIN

    OPEN cur;

    FETCH cur INTO vc;

    CLOSE cur;

    RETURN vc;

  END;

END;

DELIMITER ;

SELECT pkg.f1() FROM DUAL;

ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';

A similar script in Oracle works fine and returns "1":

DROP PACKAGE pkg;

CREATE PACKAGE pkg AS

  FUNCTION f1 RETURN INT;

END;

CREATE PACKAGE BODY pkg AS

  vc INT := 0;

  CURSOR cur IS SELECT 1 AS c FROM DUAL;

  FUNCTION f1 RETURN INT AS

  BEGIN

    OPEN cur;

    FETCH cur INTO vc;

    CLOSE cur;

    RETURN vc;

  END;

END;

SELECT pkg.f1() FROM DUAL;

PKG.F1()

A package body CURSOR is a shared cursor instance

The state of a package body cursor is shared between package routines.

This script demonstrates the following scenario:

A package procedure open_cur_if_needed() opens the cursor (if it has not been opened earlier)
A package function f1() reads from the cursor (expecting that it has been opened earlier by other package routines)
A package procedure open_cur_if_needed() closes the cursor (if it has been opened earlier by other package routines)

DROP PACKAGE pkg;

CREATE PACKAGE pkg AS

  FUNCTION f1 RETURN INT;

  PROCEDURE open_cur_if_needed;

  PROCEDURE close_cur_if_needed;

END;

CREATE PACKAGE BODY pkg AS

  vc INT := 0;

  CURSOR cur IS SELECT 1 AS c FROM DUAL;

  PROCEDURE open_cur_if_needed AS

  BEGIN

    IF NOT cur%ISOPEN

    THEN

      OPEN cur;

    END IF;

  END;

  PROCEDURE close_cur_if_needed AS

  BEGIN

    IF cur%ISOPEN

    THEN

      CLOSE cur;

    END IF;

  END;

FUNCTION f1 RETURN INT AS

  BEGIN

    FETCH cur INTO vc;

    RETURN vc;

  END;

END;

CALL pkg.open_cur_if_needed;

SELECT pkg.f1() FROM DUAL;

CALL pkg.close_cur_if_needed;

The above script return "1".

blocks

MDEV-13139 CREATE PACKAGE: package-wide declarations

is duplicated by

MDEV-36081 CURSOR is not allowed in the top level block in PACKAGE BODY

relates to

MDEV-10152 Add support for TYPE .. IS REF CURSOR

MDEV-20034 Add support for the pre-defined weak SYS_REFCURSOR

MDEV-36047 Package body variables are not allowed as FETCH targets

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