更新时间:2025-08-19 GMT+08:00
兼容性功能
由于数据库的兼容性差异,在不同兼容性设置或GUC参数下,存储过程的行为可能不一致。用户在使用这些兼容性功能时,应谨慎操作。例如:
- 由于不同兼容性的原因,带出参的FUNCTION在某些情况下可能忽略出参值。开启GUC参数 behavior_compat_options='proc_outparam_override' 后,部分场景可以确保出参值和返回值的正确返回。然而,由于此功能在不同兼容性设置下的行为存在差异,建议避免使用带出参的FUNCTION,改用带出参的PROCEDURE。
gaussdb=# CREATE SCHEMA best_practices_for_procedure; CREATE SCHEMA --创建带出参的function。 gaussdb=# CREATE OR REPLACE FUNCTION best_practices_for_procedure.func (a out int, b out int) RETURN int AS --仅做示例,不推荐使用。 c int; BEGIN a := 1; b := 2; c := 3; RETURN c; END; / CREATE FUNCTION --调用带出参的function,发现参数a,b并未赋值。 gaussdb=# DECLARE a int; b int; c int; BEGIN c := best_practices_for_procedure.func(a, b); dbe_output.print_line('a := ' || a || ' b := ' || b || ' c := ' || c); END; / a := b := c := 3 ANONYMOUS BLOCK EXECUTE --设置GUC参数。 gaussdb=# SET behavior_compat_options='proc_outparam_override'; SET --再次调用带出参的function,参数a,b,c均被赋值。 gaussdb=# DECLARE a int; b int; c int; BEGIN c := best_practices_for_procedure.func(a, b); dbe_output.print_line('a := ' || a || ' b := ' || b || ' c := ' || c); END; / a := 1 b := 2 c := 3 ANONYMOUS BLOCK EXECUTE --推荐使用带出参的存储过程来替代带出参的函数,可将上述函数改写为下面的存储过程。 gaussdb=# RESET behavior_compat_options; gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.proc (a OUT int, b OUT int, c OUT int) AS BEGIN a := 1; b := 2; c := 3; END; / CREATE PROCEDURE gaussdb=# DECLARE a int; b int; c int; BEGIN best_practices_for_procedure.proc(a, b, c); dbe_output.print_line('a := ' || a || ' b := ' || b || ' c := ' || c); END; / a := 1 b := 2 c := 3 ANONYMOUS BLOCK EXECUTE gaussdb=# DROP SCHEMA best_practices_for_procedure cascade; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to function best_practices_for_procedure.func() drop cascades to function best_practices_for_procedure.proc() DROP SCHEMA
- 在动态语句中,如果占位符名称重复,不同数据库的兼容性设置可能导致其绑定到不同的变量,从而影响预期行为。开启GUC参数 behavior_compat_options='dynamic_sql_compat' 后,可以使用同名占位符绑定不同变量。然而,由于此功能在不同兼容性设置下的行为存在差异,建议避免使用同名占位符。
gaussdb=# CREATE SCHEMA best_practices_for_procedure; CREATE SCHEMA gaussdb=# CREATE TABLE best_practices_for_procedure.tb1 (a int, b int); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE --创建使用动态语句的存储过程,使用相同占位符绑定相同变量。 gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.pro_dynexecute() AS a int := 1; b int := 2; BEGIN EXECUTE IMMEDIATE 'INSERT INTO best_practices_for_procedure.tb1 VALUES(:1, :1),(:2, :2);' USING IN a, IN b; END; / CREATE PROCEDURE gaussdb=# CALL best_practices_for_procedure.pro_dynexecute(); pro_dynexecute ---------------- (1 row) --查看表发现相同占位符绑定的是相同的变量。 gaussdb=# SELECT * FROM best_practices_for_procedure.tb1; a | b ---+--- 1 | 1 2 | 2 (2 rows) --设置GUC参数。 gaussdb=# SET behavior_compat_options='dynamic_sql_compat'; SET gaussdb=# TRUNCATE TABLE best_practices_for_procedure.tb1; TRUNCATE TABLE --创建使用动态语句的存储过程,使用相同占位符绑定不同变量。 gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.pro_dynexecute() AS a int := 1; b int := 2; c int := 3; d int := 4; BEGIN EXECUTE IMMEDIATE 'INSERT INTO best_practices_for_procedure.tb1 VALUES(:1, :1),(:2, :2);' USING IN a, IN b, IN c, IN d; END; / CREATE PROCEDURE gaussdb=# CALL best_practices_for_procedure.pro_dynexecute(); pro_dynexecute ---------------- (1 row) --设置GUC参数后调用函数,相同占位符可以绑定不同变量。 gaussdb=# SELECT * FROM best_practices_for_procedure.tb1; a | b ---+--- 1 | 2 3 | 4 (2 rows) gaussdb=# RESET behavior_compat_options; RESET gaussdb=# DROP SCHEMA best_practices_for_procedure CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table best_practices_for_procedure.tb1 drop cascades to function best_practices_for_procedure.pro_dynexecute() DROP SCHEMA
父主题: 语句功能