复杂依赖
如果存储过程或PACKAGE之间存在复杂的依赖关系,可能会在创建时遇到依赖对象尚未创建或初始化的情况,从而导致存储过程编译失败。此外,当某个对象被修改或重建时,直接或间接依赖该对象的其他存储过程和PACKAGE也会失效,需要重新编译,这会影响系统性能。
建议避免在存储过程和PACKAGE之间建立复杂的依赖关系,以提高系统的稳定性和性能。
gaussdb=# create schema best_practices_for_procedure; CREATE SCHEMA --创建依赖pkg2的pkg1,会报错。 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 --只能先创建被依赖的pkg2,再创建pkg1。 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