PACKAGE变量

PACKAGE变量是在PACKAGE内定义的全局变量，其生命周期覆盖整个数据库会话（SESSION）。不当使用可能引发以下问题：

• 对具备PACKAGE访问权限的用户完全透明，可能导致变量在多个存储过程间共享并意外被修改。
• 由于PACKAGE变量的生命周期为SESSION级别，不当操作可能造成数据残留，影响其他存储过程。
• 大量PACKAGE变量在SESSION中缓存可能占用大量内存。

因此，建议谨慎使用PACKAGE变量，并确保其访问和生命周期得到合理管理。

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
--创建存过修改package变量。
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
--修改package变量值。
gaussdb=# call best_practices_for_procedure.pro1();
 pro1
------

(1 row)

--在实际使用时发现参数已经被修改过。
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