跳到主要内容

PostgreSQL PL/pgSQL

什么是 PL/pgSQL？​

PL/pgSQL 是 PostgreSQL 数据库中的一种过程化编程语言，专门用于编写存储过程、触发器和函数。它结合了 SQL 的强大功能和过程化编程的灵活性，允许开发者在数据库层面实现复杂的业务逻辑。

PL/pgSQL 类似于 Oracle 的 PL/SQL，但专为 PostgreSQL 设计。它支持变量声明、条件判断、循环控制、异常处理等编程特性，使得开发者可以在数据库内部完成数据处理和逻辑控制。

为什么使用 PL/pgSQL？​

使用 PL/pgSQL 有以下几个优势：

  1. 性能优化：将逻辑放在数据库层面可以减少应用程序与数据库之间的通信开销，提升性能。
  2. 代码复用：通过存储过程和函数，可以在多个应用程序中复用相同的逻辑。
  3. 数据一致性：触发器可以确保数据在插入、更新或删除时自动执行特定的逻辑，保证数据一致性。
  4. 安全性：通过存储过程，可以限制用户对底层数据的直接访问，增强数据安全性。

PL/pgSQL 基础语法​

1. 声明变量​

在 PL/pgSQL 中，变量使用 DECLARE 关键字声明。变量可以是 SQL 数据类型，也可以是自定义类型。

sql
DO $$
DECLARE
    user_name TEXT := 'John Doe';
    user_age INT := 30;
BEGIN
    RAISE NOTICE 'User: %, Age: %', user_name, user_age;
END $$;

输出：

NOTICE:  User: John Doe, Age: 30

2. 条件判断​

PL/pgSQL 支持 IF、ELSIF 和 ELSE 语句，用于条件判断。

sql
DO $$
DECLARE
    score INT := 85;
BEGIN
    IF score >= 90 THEN
        RAISE NOTICE 'Grade: A';
    ELSIF score >= 80 THEN
        RAISE NOTICE 'Grade: B';
    ELSE
        RAISE NOTICE 'Grade: C';
    END IF;
END $$;

输出：

NOTICE:  Grade: B

3. 循环控制​

PL/pgSQL 支持 LOOP、FOR 和 WHILE 循环。

sql
DO $$
DECLARE
    counter INT := 1;
BEGIN
    LOOP
        RAISE NOTICE 'Counter: %', counter;
        counter := counter + 1;
        EXIT WHEN counter > 5;
    END LOOP;
END $$;

输出：

NOTICE:  Counter: 1
NOTICE:  Counter: 2
NOTICE:  Counter: 3
NOTICE:  Counter: 4
NOTICE:  Counter: 5

4. 异常处理​

PL/pgSQL 支持 EXCEPTION 块，用于捕获和处理异常。

sql
DO $$
BEGIN
    -- 尝试除以零
    PERFORM 1 / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero error';
END $$;

输出：

NOTICE:  Division by zero error

实际案例：用户注册触发器​

假设我们有一个用户表 users，每当有新用户注册时，我们希望自动记录注册时间。可以通过触发器实现这一功能。

1. 创建用户表​

sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    registered_at TIMESTAMP
);

2. 创建触发器函数​

sql
CREATE OR REPLACE FUNCTION set_registered_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.registered_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 创建触发器​

sql
CREATE TRIGGER trigger_set_registered_at
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION set_registered_at();

4. 测试触发器​

sql
INSERT INTO users (username) VALUES ('Alice');
SELECT * FROM users;

输出：

 id | username |       registered_at       
----+----------+---------------------------
  1 | Alice    | 2023-10-01 12:34:56.789
提示

触发器可以用于自动执行各种数据库操作，如数据验证、日志记录、数据同步等。

总结​

PL/pgSQL 是 PostgreSQL 中强大的过程化编程语言，适用于编写存储过程、触发器和函数。通过 PL/pgSQL，开发者可以在数据库层面实现复杂的业务逻辑，提升性能和数据一致性。

附加资源​

练习​

  1. 编写一个 PL/pgSQL 函数，计算两个数的最大公约数（GCD）。
  2. 创建一个触发器，在 users 表中插入数据时，自动将用户名转换为大写。
备注

完成练习后，可以在 PostgreSQL 中测试你的代码，确保其正确性。