PACKAGE变量

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

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

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

--创建ORA兼容数据库。
gaussdb=# CREATE DATABASE db_test DBCOMPATIBILITY 'ORA';

--切换至ORA兼容数据库。
gaussdb=# \c db_test 
db_test=# CREATE SCHEMA best_practices_for_procedure;
CREATE SCHEMA

db_test=# CREATE OR REPLACE PACKAGE best_practices_for_procedure.pkg1 AS
    id int;
    name varchar2(20);
    arg int;
    procedure p1();
END pkg1;
/
CREATE PACKAGE

db_test=# 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变量。
db_test=# 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变量值。
db_test=# CALL best_practices_for_procedure.pro1();
 pro1
------

(1 row)

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

db_test=# DROP SCHEMA best_practices_for_procedure CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to package 16443
drop cascades to function best_practices_for_procedure.p1()
drop cascades to function best_practices_for_procedure.pro1()
DROP SCHEMA

db_test=#  \c postgres
gaussdb=# DROP DATABASE db_test;