七的博客

PostgreSQL11快速入门(八)-函数和存储过程

PostgreSQL

PostgreSQL11快速入门(八)-函数和存储过程

在前面一个章节里，我们接触到了很多查询相关的语句。 每一个功能都可以对应着一条小的 SQL 语句，然后程序中进行调用即可。

在有些场景下，比如电力行业、银行、金融、政务等，经常是将一些业务逻辑写在数据库中的。 一些核心的计费、流程等业务，业务流程很复杂，如果是通过写代码来去实现的话，实现流程会很复杂。但是如果通过函数或者存储过程的话，代码将会简洁许多。

因为如果使用这种面向对象语言开发的话，可能需要定义很多实体类来接收数据，转换数据等。有没有办法将这些业务逻辑直接写在数据库中呢？ 答案是有，通过函数以及存储过程来实现。

1. 函数

1.1 函数是什么

函数是一段可重用的 SQL 代码 , 它接受输入参数 , 执行特定的逻辑,并返回结果。 你可以理解为它就是一条普通的 SQL 语句，但是这条语句中的一部分参数不是固定的，可以通过参数传递进来。

先来看个函数例子：

CREATE OR REPLACE FUNCTION get_meter_no(meter_id INTEGER)
RETURNS VARCHAR AS $$
DECLARE
    v_meter_no VARCHAR;  // 声明一个变量，这里用来放置返回值
BEGIN
    SELECT meter_no INTO v_meter_no   // 查询 meter_no 的值，放到 v_meter_no 变量中
    FROM meter
    WHERE id = meter_id;  // 普通的SQL语句， meter_id 是函数入参传进来的

    RETURN v_meter_no;  // 返回变量
END;
$$ LANGUAGE plpgsql;

1.2 怎么创建函数

在 PG 中,我们可以使用 CREATE FUNCTION 语句来创建函数。创建函数的基本语法如下:

CREATE FUNCTION function_name(param1 type1, param2 type2, ...)
RETURNS return_type AS $$
BEGIN
    -- 函数体
    -- 使用 RETURN 语句返回结果
END;
$$ LANGUAGE plpgsql;
  • function_name 是函数名称 。
  • param1, param2 是输入参数。
  • type1, type2 是参数的数据类型。
  • return_type 是返回值的数据类型。
  • 函数体使用 BEGIN 和 END 关键字包围 , 中间是 SQL 语句和 PL/pgSQL 控制结构。
  • LANGUAGE 子句指定函数使用的语言 , 这里使用 PL/pgSQL。

你仔细看一下的话，其实跟我们使用的一些编程语言是差不多的。 SQL 本质上也是一门计算机语言。 定义函数都是要制定函数名、形参类型、形参变量名、返回值类型等等。

1.3 怎么调用函数

创建完函数后 , 我们可以在 SQL 语句中直接调用它 , 优点像调用 PG 内置的函数一样:

SELECT get_meter_no(10001);

这个查询调用 get_meter_no() 函数 , 传入参数 10001 , 返回值就是电表对应的 meter_no。这里就可以不用关心 get_meter_no() 怎么去实现的，无论它是 10行还是100行，反正只需要调用并且传参即可。

切换到研发的视角，以 Java 语言 + MyBatis 来调用 SQL 函数写法：

public interface MeterMapper {
    String getMeterNo(@Param("meterId") int meterId);
}

<select id="getMeterNo" resultType="string">
    SELECT get_meter_no(#{meterId})
</select>

1.4 使用函数的优势

通过上面的这个简单的例子，我们可以发现使用函数有以下几个优势:

  • 代码复用 : 跟我们写代码其实是一样的，可以重用的代码就抽取出来公用。SQL 函数也是可以封装一些常用的数据处理逻辑 , 供其他 SQL 语句调用 , 避免代码重复。

  • 性能更好 : 因为函数在数据库服务器端执行 , 。可以减少数据在客户端和服务器端之间的传输成本 , 假设上面的 get_meter_no() 函数里面是多条 SQL 执行出来的结果，如果写在客户端代码中的话，客户端就要多次调用 SQL 才能拿到最终的结果。 但是使用函数的话， 数据处理在服务端，服务端查询的花费是不变的，但是客户端跟服务端的通信次数减少了，这时候性能也就可以提升。

2. 存储过程

2.1 什么是存储过程

存储过程与函数类似 , 也是一段可重用的 SQL 代码。但存储过程通常用于执行一系列的数据库操作 , 如插入、更新、删除等 , 而不是用于计算和返回结果。

简单来说就是可以写一大堆的业务逻辑在里面。再一个需要注意的是存储过程没有返回值 , 只能通过 OUT 参数将结果传递给调用者，这一点也是差异比较大的地方。

在 PG 后面的版本中，存储过程还支持事务控制 , 可以在存储过程中使用 COMMIT 和 ROLLBACK 语句。

2.2 怎么创建存储过程

创建存储过程的基本语法如下:

CREATE PROCEDURE procedure_name(param1 type1, param2 type2, ...)
AS $$
BEGIN
    -- 过程体
    -- 使用 OUT 参数返回结果
END;
$$ LANGUAGE plpgsql;
  • procedure_name 是存储过程名称
  • param1, param2 是输入参数
  • type1, type2 是参数的数据类型。
  • 过程体使用 BEGIN 和 END 关键字包围 , 中间是 SQL 语句和 PL/pgSQL 控制结构。

例如创建一个存储过程,根据电表 ID 查询电表的详细信息并返回:

CREATE PROCEDURE get_meter_info(meter_id int, OUT serial_number varchar, OUT model varchar, OUT installation_date date)
AS $$
BEGIN
    SELECT m.serial_number, m.model, m.installation_date
    INTO serial_number, model, installation_date
    FROM meters m
    WHERE m.id = meter_id;
END;
$$ LANGUAGE plpgsql;

这个存储过程接受一个整型参数 meter_id , 查询 meters 表,获取指定电表的序列号、型号和安装日期 , 并通过 OUT 参数将结果返回给调用者。

2.3 怎么调用调用存储过程

调用存储过程的语法如下:

CALL procedure_name(param1, param2, ...);

比如要调用 get_meter_info 这个存储过程:

CALL get_meter_info(1, '', '', '');

这个语句调用 get_meter_info 存储过程, 传入电表 ID 1, 并使用空字符串和空日期作为 OUT 参数的占位符。执行后 , OUT 参数将被赋值为实际的查询结果。

切换到研发的视角，以 Java 语言 + MyBatis 来调用 SQL 函数写法。首先定义 Mapper 接口：

public interface MeterMapper {
   
    void getMeterInfo(@Param("meterId") int meterId,
                      @Param("serialNumber") String serialNumber,
                      @Param("model") String model,
                      @Param("installationDate") Date installationDate);
}

再定义 XML 中存储过程调用:

<select id="getMeterInfo" statementType="CALLABLE"> -- statementType 属性设置为 CALLABLE,表示这是一个存储过程调用。

    CALL get_meter_info(
        #{meterId},
        #{serialNumber, mode=OUT, jdbcType=VARCHAR},  -- mode=OUT 表示这是一个输出参数。
        #{model, mode=OUT, jdbcType=VARCHAR},
        #{installationDate, mode=OUT, jdbcType=DATE}
    )
</select>

当调用MeterMapper.getMeterInfo() 后，入参中的几个参数就会变成存储过程的返回值。可以看出这里只能使用引用的方式去接受存储过程返回的值，就不是特别的方便。

存储过程比较常用的就是执行一大堆的业务逻辑，典型的就是执行查询之后，把查询出来的数据做判断，然后插入更新删除等等。

我们的业务场景中，存储过程一般只返回一个状态值，一般不会说返回一大批的数据。如果是需要返回一批数据的话，可能会优先选择函数。

2.3 存储过程的优势

使用存储过程有以下几个优势:

  • 代码复用 : 同上面函数的描述。
  • 性能更好 : 同上面函数的描述。
  • 事务支持： 存储过程是支持事务控制的 , 所以可以在存储过程中提交事务以及回滚事务，在复杂的业务流程中，这是一个必须要有的特性，同时也是一个实用的特性。数据的一致性和完整性是非常重要的事情。

3. 选择使用函数还是存储过程？

  • 如果是返回查询结果之类的，那就直接选用函数。
  • 如果是执行很多复杂的业务逻辑，包括增删改查等，就选用存储过程。
  • 如果是需要事务控制，那么就只能选用存储过程。

4. 参考链接

◀ 上一篇
PostgreSQL11快速入门(七)-常用的数据查询
下一篇 ▶
PostgreSQL11快速入门(九)-视图