PG_函数与存储过程的区别

发表于 本文字数： 6.2k 阅读时长 ≈ 6 分钟

PostgreSQL | Function&Procedure | 函数与存储过程的区别

1. 简述

1.1 书面说法

函数（Function）和存储过程（Stored Procedure）都是数据库中的可重用代码块，用于执行特定的任务。

  1. 两者都提供了代码封装的机制，可以将一系列操作组织成单个单元。

  2. 都可以接受参数，并且可以返回结果。

  3. 存储过程和函数都可以在多个地方重复使用，减少了代码的冗余。

  4. 存储过程更倾向于执行一系列的操作，而函数更专注于返回一个值。

1.2 大白话讲

  1. Function通过Returns进行结果值返回，Procedure没有Returns返回值功能。（区别只是有没有Returns并不是说谁不能返回）

  2. Function通过Returns返回一个/组值，也可以returns record返回INOUT, OUT类参数值，这时候就不可以添加sql_body中的return语句。

  3. Procedure虽然不能使用Returns返回信息，但是可以通过INOUT类型参数返回多值，但不能使用Out。

  4. Functions可以被其它查询函数夹带调用并返回结果值（select XXX），Procedures不行，它只能使用CALL调用。

  5. 但是，我们说但是，在PG里Function和Procedure可以广义的理解差不多就是一个东西，结论看最后的问题示例。

2. 函数（Function）

2.1 定义

函数是一段预编译的代码，接受输入参数并返回一个值。函数可以是内置的系统函数，也可以是用户自定义的函数。

2.2 用途

函数主要用于计算和返回一个值，而不是执行一系列的SQL语句。它可以在SELECT语句、WHERE子句、ORDER BY子句等中使用。

2.3 执行

函数通常在SQL语句中嵌套使用，可以用于计算列值、过滤数据等。

2.4 事务处理

一般情况下，函数不包含事务控制语句，因为它们的目标是计算而不是执行修改数据库结构的操作。

2.5 说点例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | { IMMUTABLE | STABLE | VOLATILE }
    | [ NOT ] LEAKPROOF
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } ...


-- 简单点，常用结构
CREATE OR REPLACE FUNCTION FUNCTION_NAME(IN PARA1 INT,INOUT PARA2 INT, OUT PARA3) 
RETURNS [TYPE|RECORD]
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
[RETURN TYPE]
END;
$$;

1. 当参数都是IN类时

argmode：IN、INOUT、OUT（其中IN类参数类型可以将IN省略不写）

RETURNS TYPE + return xxx 按标准方式处理即可

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION f01(IN a INT,IN b INT) RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
  return a+b;
END;
$$;

select f01(1,2);
image-20231226182406647

2. 参数中出现OUT、INOUT参数时

FUNCTION中的return就不能写，否则报错

  1. 若OUT、INOUT参数出现数量为1个，RETURNS Type 的TYPE需要与INOUT参数一致，且存储过程内不需要写return;

    • 正确写法

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT) RETURNS INT
      LANGUAGE plpgsql
      AS $$
      DECLARE
      BEGIN
        b := a+b;
        -- return b;
      END;
      $$;

      select f01(2,3);
      image-20231226184048084

    • 有INOUT、OUT，function内有return会报错

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      --ERROR:  RETURN cannot have a parameter in function with OUT parameters
      CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT) RETURNS INT
      LANGUAGE plpgsql
      AS $$
      DECLARE
      BEGIN
        b := a+b;
        return b;
      END;
      $$;
      image-20231226184855326

      • 有INOUT、OUT，RETURNS TYPE不匹配会报错

        1
        2
        3
        4
        5
        6
        7
        8
        9
        --ERROR:  function result type must be integer because of OUT parameters
        CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT) RETURNS VARCHAR
        LANGUAGE plpgsql
        AS $$
        DECLARE
        BEGIN
          b := a+b;
        END;
        $$;
        image-20231226185432085

      • 有OUT参数时，调用时OUT参数位不需要输入信息的，否则报错

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        -- HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
        CREATE OR REPLACE FUNCTION f01(IN a INT,OUT b INT) RETURNS INT
        LANGUAGE plpgsql
        AS $$
        DECLARE
        BEGIN
          b := a+b;
        END;
        $$;

        select f01(2,3);
        image-20231226185753797

      • 有INOUT参数时，INOUT参数位必须输入信息，否则报错

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        -- HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
        CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT) RETURNS INT
        LANGUAGE plpgsql
        AS $$
        DECLARE
        BEGIN
          b := a+b;
        END;
        $$;

        select f01(2);
        image-20231226190041318

  2. 若OUT、INOUT参数出现数量为多个，需要改写为RETURNS record

    • 正确写法

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT,OUT c INT) RETURNS record
      LANGUAGE plpgsql
      AS $$
      DECLARE
      BEGIN
        b := a+b;
        c := b*b;
      END;
      $$;

      select f01(1,2);
      image-20231226184218073

      • 未调整为RETURNS record，将会报错

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        --ERROR:  function result type must be record because of OUT parameters
        CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT,OUT c INT) RETURNS INT
        LANGUAGE plpgsql
        AS $$
        DECLARE
        BEGIN
          b := a+b;
          c := b*b;
        END;
        $$;
        image-20231226190832948

​ 

  - 多INOUT、OUT参数其它错误与1个INOUT、OUT基本一致，不再举例。

3. 存储过程（Procedure）

3.1 定义

存储过程是一组预编译的SQL语句集，被保存在数据库中，可以在需要时调用执行。存储过程通常由数据库管理员或有特殊权限的用户创建。

3.2 用途

存储过程主要用于封装和执行一系列的SQL语句，以完成特定的任务。它可以接受参数，并可以包含条件逻辑、循环等程序控制结构。

3.3 执行

存储过程可以被应用程序或其他存储过程调用。一旦创建，它们可以在数据库中被重复使用，提高了代码的可维护性和可重用性。

3.4 事务处理

存储过程可以包含事务控制语句，允许进行复杂的事务处理。

3.5 说点例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } ...


-- 简单点，常用结构
CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(IN/INOUT PARA1,...)
AS $$
Declare
BEGIN
END;
$$ Language plpgsql;

  1. 基本用法与Function一致，只是Procedure参数中不能使用OUT类型，且不能使用Returns返回数据。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE OR REPLACE PROCEDURE p01(IN a INT, INOUT b INT, INOUT c INT) Language plpgsql
    AS $$
    DECLARE
    BEGIN
      b := a+b;
      c := b*b;
    END;
    $$;

    call p01(2,3,4);

​ image-20231226193231874

  1. 如果使用OUT参数将报错

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    --ERROR:  procedures cannot have OUT arguments
    --HINT:  INOUT arguments are permitted.
    CREATE OR REPLACE PROCEDURE p01(IN a INT, INOUT b INT, OUT c INT) Language plpgsql
    AS $$
    DECLARE
    BEGIN
      b := a+b;
      c := b*b;
    END;
    $$;

​ image-20231226193449098

问题

  1. 函数和存储过程是否都可以执行DDL？

    答案是：在PG中都可以执行，不过函数一般用来计算结果，最好不要夹带DDL语句；

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    CREATE OR REPLACE FUNCTION f01() RETURNS VOID Language plpgsql
    AS $$
    DECLARE
    BEGIN
      CREATE TABLE IF NOT EXISTS t1(id int);
      DROP TABLE t1;
    END;
    $$;
    select f01();

    CREATE OR REPLACE PROCEDURE p01() Language plpgsql
    AS $$
    DECLARE
    BEGIN
      CREATE TABLE IF NOT EXISTS t1(id int);
      DROP TABLE t1;
    END;
    $$;
    call p01();

​ image-20231226195441537

  1. 为什么说PG中 函数 与 存储过程 广义上可以理解为一个东西？

    我们来看一个报错，通过报错提示我们发现，函数与存储过程在报错中都被叫做function。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    CREATE OR REPLACE FUNCTION f01() RETURNS VOID Language plpgsql
    AS $$
    DECLARE
    BEGIN
      -- 不存在这张表，会报错；
      DROP TABLE t123;
    END;
    $$;
    select f01();

    CREATE OR REPLACE PROCEDURE p01() Language plpgsql
    AS $$
    DECLARE
    BEGIN
      -- 不存在这张表，会报错；
      DROP TABLE t456;
    END;
    $$;
    call p01();
    image-20231226200015394

参阅：

https://www.postgresql.org/docs/16/sql-createfunction.html

https://www.postgresql.org/docs/16/sql-createprocedure.html

Procedures Vs. Functions in PostgreSQL