PostgreSQL PL/pgSQL 基础与实践

01 PL/pgSQL 语法基础

PL/pgSQL 是 PostgreSQL 的扩展语言，允许开发者编写存储过程、自定义函数和其他逻辑功能模块。掌握其语法是数据库开发的基础。

语法结构

声明与赋值：通过 DECLARE 关键字定义变量，使用 AS alias 给变量命名，DEFAULT 设置默认值。
```
DECLARE var_name type [DEFAULT default_value];
```

条件判断：使用 IF 语句进行条件判断，支持多种逻辑运算符。

IF condition THEN  -- 代码块ELSE IF condition  -- 代码块ELSE  -- 代码块END IF;

CASE 语句：用于处理多种条件分支，支持简单 CASE 和分段 CASE。
```
CASE  WHEN condition THEN    -- 代码块  ELSE    -- 代码块END;
```

循环语句：使用 LOOP 进行循环操作，可以通过 FETCH 获取结果集，EXIT 退出循环。

DECLARE i integer := 1;BEGIN  LOOP    -- 代码块    FETCH NEXT FROM cursor INTO record;    EXIT WHEN NOT FOUND;  END LOOP;END;

02 游标（Cursor）操作

游标是一种可以逐行处理结果集的机制，适用于处理大量数据，避免内存溢出。

游标使用步骤

声明游标变量：

DECLARE rec_record RECORD; -- 声明一个记录变量DECLARE cur_cursor CURSOR FOR SELECT ...; -- 声明游标

打开游标：

OPEN cur_cursor('参数');

获取和处理结果：

FETCH cur_cursor INTO rec_record; -- 获取记录

关闭游标：

CLOSE cur_cursor;

示例代码

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;    RAISE NOTICE '% %', rec_user.id, rec_user.name;    EXIT WHEN NOT FOUND;  END LOOP;  CLOSE cur_user;END$$ LANGUAGE plpgsql;

03 自定义函数（Function）

自定义函数用于封装复杂逻辑，提高代码复用性。

创建函数语法

CREATE [OR REPLACE] FUNCTION function_name (参数列表) RETURNS 返回类型 AS $$BEGIN  -- 函数体END;$$ LANGUAGE plpgsql;

示例函数

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

调用函数

SELECT name, get_user_count(name) FROM b_user;

04 存储过程（Procedure）

存储过程执行特定任务，类似于函数，但没有返回值。

创建存储过程语法

CREATE [OR REPLACE] PROCEDURE procedure_name (参数列表) AS $$BEGIN  -- 代码块END;$$ LANGUAGE plpgsql;

示例存储过程

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