更新时间:2025-08-19 GMT+08:00
分享

复杂依赖

如果存储过程或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

相关文档