Postgresql中PL/pgSQL的游标、自定义函数、存储过程的使用

逃离我推掉我的手 2024-02-05 17:03 60阅读 0赞

## 场景 ##

Postgresql中PL/pgSQL代码块的语法与使用-声明与赋值、IF语句、CASE语句、循环语句：

[Postgresql中PL/pgSQL代码块的语法与使用-声明与赋值、IF语句、CASE语句、循环语句-CSDN博客][Postgresql_PL_pgSQL_-_IF_CASE_-CSDN]

上面讲了基本语法，下面记录游标、自定义函数、存储过程的使用。

注：

博客：  
[霸道流氓气质\_C\#,架构之路,SpringBoot-CSDN博客][C_SpringBoot-CSDN]

## 实现 ##

### 1、PL/pgSQL游标 ###

PL/pgSQL 游标允许我们封装一个查询，然后每次处理结果集中的一条记录。游标可以将大结果集拆分成许多小的记录，

避免内存溢出；另外，我们可以定义一个返回游标引用的函数，然后调用程序可以基于这个引用处理返回的结果集。

使用游标的步骤：

1. 声明游标变量；

2. 打开游标；

3. 从游标中获取结果；

4. 判断是否存在更多结果。如果存在，执行第 3 步；否则，执行第 5 步；

5. 关闭游标。

示例代码：

DO $$
    DECLARE
     rec_user RECORD;
     cur_user CURSOR(user_name VARCHAR) FOR
     SELECT id, name
     FROM b_user
     WHERE name = user_name;
    BEGIN
     -- 打开游标
     OPEN cur_user('222');
     
     LOOP
     -- 获取游标中的记录
     FETCH cur_user INTO rec_user;
     -- 没有找到更多数据时退出循环
     EXIT WHEN NOT FOUND;
     RAISE NOTICE '%,% ' , rec_user.id, rec_user.name;
     END LOOP;
     
     -- Close the cursor
     CLOSE cur_user;
    END $$;

示例代码运行结果

![4fc8243dbeb5407a9e9f4c5bdadaa36e.png][]

首先，声明了一个游标 cur\_user，并且绑定了一个查询语句，通过一个参数user\_name 获取指定姓名的用户；

然后使用 OPEN 打开游标；接着在循环中使用 FETCH 语句获取游标中的记录，如果没有找到更多数据退出循环语句；

变量 rec\_user 用于存储游标中的记录；最后使用 CLOSE语句关闭游标，释放资源。

### 2、创建自定义PL/pgSQL函数 ###

要创建一个自定义的 PL/pgSQL 函数，可以使用 CREATE FUNCTION 语句。

CREATE 表示创建函数，OR REPLACE 表示替换函数定义；

name 是函数名；括号内是参数，多个参数使用逗号分隔；argmode 可以是 IN（输入）、OUT（输出）、INOUT（输入输出）

或者 VARIADIC（数量可变），默认为 IN；argname 是参数名称；argtype 是参数的类型；default\_expr是参数的默认值；

rettype 是返回数据的类型；AS 后面是函数的定义，和上文中的匿名块相同；最后，LANGUAGE 指定函数实现的语言。

创建一个示例函数，用于返回指定姓名的用户数量

CREATE
     OR REPLACE FUNCTION get_user_count (user_name VARCHAR ) RETURNS INTEGER AS $$ DECLARE
     ln_count INTEGER;
    BEGIN
     SELECT COUNT
      (*) INTO ln_count
     FROM
      b_user
     WHERE
      name = user_name;
     RETURN ln_count;
     
    END; $$ LANGUAGE plpgsql;

函数调用方式

SELECT name,get_user_count(name)
    FROM b_user ;

调用结果

### ![8fc400d3704a461287cac07981e70170.png][] ###

### 3、创建存储过程 ###

存储过程，使用 CREATE PROCEDURE 语句创建

存储过程的定义和函数主要的区别在于没有返回值，其他内容都类似。以下示例创建了一个存储过程用于修改用户的信息

CREATE
     OR REPLACE PROCEDURE update_user (user_id in integer,user_name IN VARCHAR) AS $$ BEGIN
      UPDATE b_user
      SET name = user_name
     WHERE
      id = user_id;
     
    END; $$ LANGUAGE plpgsql;

存储过程调用方法：

call update_user(1,'badao');

[Postgresql_PL_pgSQL_-_IF_CASE_-CSDN]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/135090263
[C_SpringBoot-CSDN]: https://blog.csdn.net/badao_liumang_qizhi
[4fc8243dbeb5407a9e9f4c5bdadaa36e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/a599355467314f64b751e00e2a41366c.png
[8fc400d3704a461287cac07981e70170.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/0002dcc89c4244c98da43be7c69f7fd8.png