更新时间: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

相关文档