Package Variables

A package variable is a global variable defined in a package. Its lifecycle covers the entire database session. Improper use may cause the following problems:

• If a variable is completely transparent to users who have the package access permission, the variable may be shared among multiple stored procedures and modified unexpectedly.
• The lifecycle of a package variable is at the session level. Improper operations may cause residual data and affect other stored procedures.
• Caching a large number of package variables in a session may consume a substantial amount of memory.

Therefore, you are advised to use package variables with caution and ensure that their access and lifecycle are properly managed.

gaussdb=# create schema best_practices_for_procedure;
CREATE SCHEMA
gaussdb=# create or replace package best_practices_for_procedure.pkg1 as
id int;
name varchar2(20);
arg int;
procedure p1();
end pkg1;
/
CREATE PACKAGE
gaussdb=# create or replace package body best_practices_for_procedure.pkg1 as
procedure p1() as
begin
 id := 1;
 name := 'huawei';
 arg := 20;
end;
end pkg1;
/
CREATE PACKAGE BODY
-- Create a stored procedure and modify package variables.
gaussdb=# create or replace procedure best_practices_for_procedure.pro1 () as
begin
best_practices_for_procedure.pkg1.id := 2;
best_practices_for_procedure.pkg1.name := 'gaussdb';
best_practices_for_procedure.pkg1.arg := 18;
end;
/
CREATE PROCEDURE
-- Change the value of a package variable.
gaussdb=# call best_practices_for_procedure.pro1();
 pro1
------

(1 row)

-- In practice, it is found that the parameters have been modified.
gaussdb=# declare
begin
dbe_output.print_line('id = ' || best_practices_for_procedure.pkg1.id || ' name = ' || best_practices_for_procedure.pkg1.name || ' arg = ' || best_practices_for_procedure.pkg1.arg);
best_practices_for_procedure.pkg1.p1();
dbe_output.print_line('id = ' || best_practices_for_procedure.pkg1.id || ' name = ' || best_practices_for_procedure.pkg1.name || ' arg = ' || best_practices_for_procedure.pkg1.arg);
end;
/
id = 2 name = gaussdb arg = 18
id = 1 name = huawei arg = 20
ANONYMOUS BLOCK EXECUTE
gaussdb=# drop schema best_practices_for_procedure cascade;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to package 16782
drop cascades to function best_practices_for_procedure.p1()
drop cascades to function best_practices_for_procedure.pro1()
DROP SCHEMA