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.
-- Create an ORA-compatible database. gaussdb=# CREATE DATABASE db_test DBCOMPATIBILITY 'ORA'; -- Switch to the ORA-compatible database. 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 -- Create a stored procedure and modify package variables. 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 -- Change the value of a package variable. db_test=# CALL best_practices_for_procedure.pro1(); pro1 ------ (1 row) -- In practice, it is found that the parameters have been modified. 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;
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot