复杂依赖

如果存储过程或PACKAGE之间存在复杂的依赖关系，可能会在创建时遇到依赖对象尚未创建或初始化的情况，从而导致存储过程编译失败。此外，当某个对象被修改或重建时，直接或间接依赖该对象的其他存储过程和PACKAGE也会失效，需要重新编译，这会影响系统性能。

建议避免在存储过程和PACKAGE之间建立复杂的依赖关系，以提高系统的稳定性和性能。

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

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

--创建依赖pkg2的pkg1，会报错。
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

--只能先创建被依赖的pkg2，再创建pkg1。
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;