PL/SQL parser - Phase 2 (MDEV-10764)

[MDEV-13139] Package-wide variables in CREATE PACKAGE Created: 2017-06-21  Updated: 2023-09-25

Status: Stalled
Project: MariaDB Server
Component/s: Parser, Stored routines
Affects Version/s: 10.3, 10.4, 10.5
Fix Version/s: None

Type: Technical task Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 1
Labels: Compatibility, Oracle

Issue Links:
Blocks
is blocked by MDEV-13292 Move the code from sp_head::init() to... Closed

 Description   

As of version 10.4, MariaDB supports package variables in CREATE PACKAGE BODY only.

Under terms of this task we'll also allow package wide-variables in the CREATE PACKAGE statement.

Example:

DROP PACKAGE pkg1;
 
DELIMITER $$
 
CREATE PACKAGE pkg1
 
AS
 
  m_count INT:= 0;
 
  FUNCTION get_count RETURN INT;
 
  PROCEDURE set_count(new_count INT);
 
END;
 
$$
 
CREATE PACKAGE BODY pkg1
 
AS
 
  FUNCTION get_count RETURN INT
 
  AS
 
  BEGIN
 
    m_count:= m_count + 1;
 
    RETURN m_count;
 
  END;
 
  PROCEDURE set_count(new_count INT)
 
  AS
 
  BEGIN
 
    m_count:= new_count;
 
  END;
 
END;
 
$$
 
DELIMITER ;
 
SELECT pkg1.get_count() FROM dual; -- this returns 1
 
SELECT pkg1.get_count() FROM dual; -- this returns 2
 
CALL pkg1.set_count(30);
 
SELECT pkg1.get_count() FROM dual;  -- this returns 31

Note, unlike stored routines parameters, package-wide VARCHAR variables must be declared with length.

Package variables are initialized on the first package invocation in the current session.

Variable declarations may not refer to functions declared in the same package. So this script will return an error:

DROP PACKAGE pkg1;
 
DELIMITER $$
 
CREATE PACKAGE pkg1
 
AS
 
  m_count INT:= init_count();
 
  FUNCTION init_count RETURN INT;
 
END;
 
$$



 Comments   
Comment by Alexander Barkov [ 2019-04-24 ]

Was closed in a mistake

Generated at Thu Feb 08 08:03:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.