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

访问对象

未指定SCHEMA的存储过程将依据SEARCH_PATH的顺序查找对象,可能导致访问到非预期对象。如果不同模式中存在同名表、存储过程以及其他数据库对象,未明确指定SCHEMA可能引发意外结果。因此,建议在存储过程访问数据对象时,始终明确指定SCHEMA。

示例:

--创建两个不同的schema。
gaussdb=# create schema best_practices_for_procedure1;
CREATE SCHEMA
gaussdb=# create schema best_practices_for_procedure2;
CREATE SCHEMA
--在两个不同的schema下创建相同的存储过程。
gaussdb=# create or replace procedure best_practices_for_procedure1.proc1() as
begin
dbe_output.print_line('in schema best_practices_for_procedure1');
end;
/
CREATE PROCEDURE
gaussdb=# create or replace procedure best_practices_for_procedure2.proc1() as
begin
dbe_output.print_line('in schema best_practices_for_procedure2');
end;
/
CREATE PROCEDURE
--在不同的search_path下调用相同的存储过程可能存在差异。
gaussdb=# set search_path to best_practices_for_procedure1, best_practices_for_procedure2;
SET
gaussdb=# call proc1();
in schema best_practices_for_procedure1
 proc1
-------

(1 row)

gaussdb=# reset search_path;
RESET
gaussdb=# set search_path to best_practices_for_procedure2, best_practices_for_procedure1;
SET
gaussdb=# call proc1();
in schema best_practices_for_procedure2
 proc1
-------

(1 row)

gaussdb=# reset search_path;
RESET
gaussdb=# drop schema best_practices_for_procedure1 cascade;
NOTICE:  drop cascades to function best_practices_for_procedure1.proc1()
DROP SCHEMA
gaussdb=# drop schema best_practices_for_procedure2 cascade;
NOTICE:  drop cascades to function best_practices_for_procedure2.proc1()
DROP SCHEMA

相关文档