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.

gaussdb=# create schema best_practices_for_procedure;
CREATE SCHEMA
-- An error is reported when pkg1 that depends on pkg2 is created.
gaussdb=# create or replace package best_practices_for_procedure.pkg1 as
procedure p1();
end pkg1;
/
CREATE PACKAGE
gaussdb=# 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.
gaussdb=# create or replace package best_practices_for_procedure.pkg2 as
a int;
procedure p1();
end pkg2;
/
CREATE PACKAGE
gaussdb=# create or replace package body best_practices_for_procedure.pkg2 as
procedure p1() as
begin
null;
end;
end pkg2;
/
CREATE PACKAGE BODY
gaussdb=# create or replace package best_practices_for_procedure.pkg1 as
procedure p1();
end pkg1;
/
CREATE PACKAGE
gaussdb=# 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
gaussdb=# drop schema best_practices_for_procedure cascade;
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to package 16836
drop cascades to function best_practices_for_procedure.p1()
drop cascades to package 16834
drop cascades to function best_practices_for_procedure.p1()
DROP SCHEMA