Complex Dependencies
If there are complex dependencies between stored procedures or packages, the dependent objects may not be created or initialized during creation. As a result, the stored procedure fails to be compiled. In addition, when an object is modified or rebuilt, other stored procedures and packages that directly or indirectly depend on the object become invalid and need to be recompiled, which affects system performance.
To improve system stability and performance, do not create complex dependencies between stored procedures and packages.
-- Create an ORA-compatible database. 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 -- An error is reported when pkg1 that depends on pkg2 is created. db_test=# create or replace package best_practices_for_procedure.pkg1 as procedure p1(); end pkg1; / CREATE PACKAGE db_test=# create or replace package body best_practices_for_procedure.pkg1 as procedure p1() as begin best_practices_for_procedure.pkg2.a := 100; end; end pkg1; / ERROR: "best_practices_for_procedure.pkg2.a" is not a known variable. LINE 3: best_practices_for_procedure.pkg2.a := 100; ^ QUERY: DECLARE begin best_practices_for_procedure.pkg2.a := 100; end -- You can create pkg1 only after pkg2 is created. db_test=# create or replace package best_practices_for_procedure.pkg2 as a int; procedure p1(); end pkg2; / CREATE PACKAGE db_test=# create or replace package body best_practices_for_procedure.pkg2 as procedure p1() as begin null; end; end pkg2; / CREATE PACKAGE BODY db_test=# create or replace package best_practices_for_procedure.pkg1 as procedure p1(); end pkg1; / CREATE PACKAGE db_test=# create or replace package body best_practices_for_procedure.pkg1 as procedure p1() as begin best_practices_for_procedure.pkg2.a := 100; end; end pkg1; / CREATE PACKAGE BODY db_test=# drop schema best_practices_for_procedure cascade; NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to package 16526 drop cascades to function best_practices_for_procedure.p1() drop cascades to package 16524 drop cascades to function best_practices_for_procedure.p1() DROP SCHEMA templatea=# \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