深入解析PostgreSQL中的PL/pgSQL语法

在数据库管理系统中，PostgreSQL因其强大的功能和稳定性而受到广泛欢迎。其中，PL/pgSQL作为PostgreSQL的过程化语言，为用户提供了更为灵活和强大的编程能力。本文将深入解析PL/pgSQL的语法，帮助读者更好地掌握这门语言，从而在实际开发中更加得心应手。

一、PL/pgSQL简介

PL/pgSQL的定义和特点

PL/pgSQL是PostgreSQL数据库的专用过程化语言，它允许开发者在数据库内部编写复杂的控制结构和过程。PL/pgSQL的特点包括：

• 集成性：与PostgreSQL紧密集成，可以直接访问数据库的所有功能。

• 易读性：语法类似常见的程序设计语言，易于学习和使用。

• 高效性：代码在数据库服务器内部执行，减少了网络传输的开销。

• 扩展性：支持自定义函数、过程和触发器，增强了数据库的编程能力。

PL/pgSQL与其他数据库过程化语言的比较

与其他数据库的过程化语言相比，如Oracle的PL/SQL，PL/pgSQL在语法和功能上有很多相似之处，但也存在一些差异。PL/pgSQL更加开放和灵活，它支持更多的数据类型和操作，同时与PostgreSQL的开源特性相结合，为用户提供了更广泛的定制空间。
当然，PostgreSQL不止支持PL/pgSQL,还支持其他的语法。查看pgsql默认支持的语法：

postgres=# select * from pg_language;
  oid  | lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
-------+----------+----------+---------+--------------+---------------+-----------+--------------+--------
    12 | internal |       10 | f       | f            |             0 |         0 |         2246 |
    13 | c        |       10 | f       | f            |             0 |         0 |         2247 |
    14 | sql      |       10 | f       | t            |             0 |         0 |         2248 |
 13762 | plpgsql  |       10 | t       | t            |         13759 |     13760 |        13761 |
(4 rows)

PL/pgSQL的使用场景

PL/pgSQL的使用场景非常广泛，包括但不限于：

• 数据验证和转换：在数据写入数据库之前进行复杂的数据验证和转换操作。

• 业务逻辑封装：将复杂的业务逻辑封装在数据库内部，提高应用的性能和安全性。

• 触发器编写：利用PL/pgSQL编写触发器，实现数据的自动更新、日志记录等功能。

二、PL/pgSQL基础语法

PL/pgSQL结构

[<<label>>]
[DECLARE
    -- 变量声明部分
]
BEGIN
    -- 执行部分
    -- SQL 和 PL/pgSQL 语句
EXCEPTION
    -- 异常处理部分
END [label];

1, <<label>>:这是一个可选的标签，你可以用它来给代码块命名。如果你提供了这个标签，那么在代码块的结束部分你也需要使用相同的标签来标识代码块的结束。这个标签在嵌套代码块或者需要明确指定某个代码块时特别有用。
2，[DECLARE ... ]:DECLARE部分是可选的，用于声明变量、常量、游标或者自定义数据类型等。这些声明的元素将在BEGIN和END之间的代码块中使用。
例如：DECLARE my_var integer; 会声明一个名为my_var的整数类型变量。

3,BEGIN ... EXCEPTION ... END:BEGIN标记了代码块的实际开始。在这里，你可以放置SQL语句、控制结构（如IF、LOOP等）以及其他PL/pgSQL语句来执行你的逻辑。
如果在BEGIN和END之间的代码执行过程中发生了异常（如除以零、违反约束等），那么控制将传递到EXCEPTION部分（如果存在的话）。
4,EXCEPTION部分用于处理在BEGIN部分中发生的任何异常。你可以在这里编写特定的异常处理逻辑，比如记录错误信息、执行清理操作或者尝试恢复等。
5,END标记了代码块的结束。如果你在开始部分使用了标签，那么在这里你也需要提供相同的标签来明确标识这个代码块的结束。

下面是一个简单的例子，展示了如何使用这个结构来创建一个PL/pgSQL函数，该函数尝试除以一个数并处理可能的除以零异常：

CREATE OR REPLACE FUNCTION divide_numbers(numerator int, denominator int) RETURNS float AS $$
DECLARE
    result float;
BEGIN
    IF denominator = 0 THEN
        RAISE EXCEPTION 'Cannot divide by zero';
    ELSE
        result := numerator / denominator;
    END IF;
    RETURN result;
EXCEPTION WHEN division_by_zero THEN
    RAISE EXCEPTION 'Caught division by zero';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

在这个例子中，我们声明了一个result变量来存储计算结果。在BEGIN部分，我们检查分母是否为零，如果是，则抛出一个异常。否则，我们执行除法并返回结果。在EXCEPTION部分，我们处理division_by_zero异常（尽管在这个特定的例子中，我们通过IF语句已经提前检查了这种情况）。

注意事项

• 执行部分不能省略：在PL/pgSQL代码块中，BEGIN和END之间的执行部分是必需的。即使这个部分没有任何语句，也不能省略。这是因为PL/pgSQL期望在这个部分中执行一些操作。
• 分号终止：在代码块中，每个声明（declaration）和每条语句（statement）都必须以分号（;）终止。这是PL/pgSQL语法的一部分，用于明确标识各个语句的结束。
• 块支持嵌套：PL/pgSQL允许在一个代码块内部嵌套另一个代码块。嵌套时，子块的END后面必须跟一个分号，而最外层的块END后面可以不跟分号。这是为了区分不同层次的代码块结束。
• 标签一致性：如果代码块使用了标签，那么END后面跟的标签名必须与块开始时的标签名一致。这是为了确保代码块的正确匹配和结束。
• 变量作用域：在代码块中声明的变量在当前块及其所有子块中都是有效的。如果子块中声明了与外部块同名的变量，它将覆盖外部块的变量（仅在子块内有效）。这是变量作用域的基本规则。
• 通过外部块标签访问变量：当子块中的变量覆盖了外部块的同名变量时，可以通过在变量名前加上外部块的标签来访问外部块的变量。这是一种解决变量名冲突的方法，允许在子块中同时访问和操作内部和外部的变量。

示例

<<outer_block>>
DECLARE
    outer_var integer := 10;
BEGIN
    RAISE NOTICE 'Outer variable: %', outer_var; -- 输出外部变量
    
    <<inner_block>>
    DECLARE
        inner_var integer := 20;
        outer_var integer := 30; -- 覆盖外部变量
    BEGIN
        RAISE NOTICE 'Inner variable: %', inner_var; -- 输出内部变量
        RAISE NOTICE 'Covered outer variable: %', outer_var; -- 输出被覆盖的外部变量（在子块中的值）
        RAISE NOTICE 'Original outer variable: %', outer_block.outer_var; -- 通过标签访问外部块的原始变量
    END inner_block; -- 子块结束，注意分号
    
    RAISE NOTICE 'Outer variable after inner block: %', outer_var; -- 输出外部变量，确认其值未被子块改变
END outer_block; -- 最外层块结束，可不加分号

匿名块

PL/pgSQL 的匿名块（Anonymous Blocks）是 PostgreSQL 数据库中的一种特性，允许你执行一次性的、不存储在数据库中的 PL/pgSQL 代码。这些块不像存储过程或函数那样具有持久的名称，它们只是临时执行，通常用于测试、调试或执行一些即时的数据库操作。

匿名块的结构与 PL/pgSQL 函数或过程相似，但不需要为其指定名称。它们包含 DECLARE、BEGIN、EXCEPTION（可选）和 END 部分，你可以在其中声明变量、执行语句以及处理异常。

以下是一个 PL/pgSQL 匿名块的基本结构示例：

DO $$ 
DECLARE 
    -- 声明变量
    my_variable integer := 10;
BEGIN
    -- 执行语句
    RAISE NOTICE 'The value of my_variable is %', my_variable;
    
    -- 其他逻辑操作
    -- ...
    
EXCEPTION WHEN OTHERS THEN
    -- 异常处理
    RAISE EXCEPTION 'An error occurred: %', SQLERRM;
END $$;

在这个示例中：

• DO 关键字用于开始一个匿名块。

• $$ 是用于界定匿名块主体的定界符，你也可以选择其他字符作为定界符，如 <<>>。

• DECLARE 部分用于声明在块内使用的变量。在这个例子中，我们声明了一个名为 my_variable 的整数变量并初始化为 10。

• BEGIN 和 END 之间的部分是执行主体，其中可以包含任意数量的 PL/pgSQL 语句。在这个例子中，我们使用 RAISE NOTICE 语句来输出变量的值。

• EXCEPTION 部分是可选的，用于处理在执行主体中可能发生的异常。在这个例子中，我们捕获所有类型的异常并抛出一个包含错误消息的新异常。

匿名块在执行完毕后不会留下任何持久化的对象或结构，这使得它们非常适合于临时的、一次性的任务。你可以通过任何支持 PL/pgSQL 的客户端（如 psql、数据库管理工具或应用程序代码）来执行这些块。

示例：

DO $$ 
DECLARE 
    my_variable integer;
BEGIN
    my_variable := 10;
    RAISE NOTICE 'The value of my_variable is %', my_variable;
END $$;

在这个例子中，我们使用 DO 关键字开始一个匿名块。在 BEGIN 和 END 之间，我们声明了一个名为 my_variable 的整数变量，并将其设置为 10。然后，我们使用 RAISE NOTICE 语句输出变量的值。

子块

子块（Subblock）在 PL/pgSQL 中是一个非常重要的概念，它允许你将代码进行逻辑上的拆分和组织，使得复杂的代码结构更加清晰和易于管理。以下是关于子块的详细介绍：

定义与结构：

子块是嵌套在其他代码块（如函数、过程或另一个子块）内部的代码块。
它具有与外层代码块相似的结构，包括可选的 DECLARE 部分、必需的 BEGIN 和 END 部分，以及可选的异常处理部分。

嵌套与层次：

PL/pgSQL 支持多层嵌套，意味着一个子块内部还可以包含另一个子块，形成层次化的代码结构。
每个子块都独立于其父块，但可以访问父块中声明的变量（除非被同名变量覆盖）。

变量作用域与可见性：

在子块中声明的变量具有局部作用域，它们只在子块内部可见和有效。
如果子块中声明了与外部块同名的变量，它将覆盖外部块的变量（在子块内部）。这是变量遮蔽（shadowing）的一个例子。
尽管子块内的变量可以覆盖外部块的变量，但外部块的变量并未被删除或修改，只是在子块内不可直接访问（除非使用块标签）。

块标签与变量访问：

当需要在子块中引用被覆盖的外部块变量时，可以使用块标签作为变量的限定符。例如，如果外部块标签为 outer_block，并且有一个被覆盖的变量 counter，则可以通过 outer_block.counter 来访问外部块的 counter 变量。

执行流程与控制：

子块的执行流程完全包含在其父块之内。当父块执行到子块时，会先执行子块的代码，然后再继续执行父块中子块之后的代码。
子块可以包含自己的控制结构（如 IF 语句、LOOP 循环等），这些控制结构仅影响子块内部的执行流程。

异常处理：

子块可以拥有自己的异常处理部分，用于捕获和处理在子块执行过程中发生的异常。
如果子块中没有处理某个异常，该异常会被传递到父块中进行处理（如果父块有相应的异常处理逻辑）。

用途与优势：

子块的主要用途是组织代码，将复杂的逻辑拆分成更小、更易于理解和维护的部分。
通过使用子块，可以提高代码的可读性、可维护性和可重用性，降低代码的复杂性。
综上所述，子块是 PL/pgSQL 中一个非常强大的特性，它允许开发者以更加结构化和模块化的方式编写复杂的数据库逻辑。

示例

DO $$ 
DECLARE 
    outer_var integer := 10;
BEGIN
    -- 在这里，我们有一个外部块，它声明并初始化了一个变量 outer_var
    
    <<inner_block>>
    DECLARE
        inner_var integer := 20;
    BEGIN
        -- 在这里，我们有一个子块，它声明并初始化了一个变量 inner_var
        
        -- 输出内部变量的值
        RAISE NOTICE 'Inner variable: %', inner_var;
        
        -- 输出外部变量的值，但由于外部变量被内部变量覆盖，这里将输出内部变量的值
        RAISE NOTICE 'Outer variable: %', inner_var;
        
        -- 使用外部块的标签访问外部块的变量
        RAISE NOTICE 'Original outer variable: %', outer_var;
        
        -- 修改内部变量的值
        inner_var := 30;
        
        -- 输出修改后的内部变量值
        RAISE NOTICE 'Inner variable after modification: %', inner_var;
    END inner_block;
    
    -- 输出外部变量的值，此时外部变量的值保持不变
    RAISE NOTICE 'Outer variable after inner block: %', outer_var;
END $$;


--输出结果
NOTICE:  Inner variable: 20
NOTICE:  Outer variable: 20
NOTICE:  Original outer variable: 10
NOTICE:  Inner variable after modification: 30
NOTICE:  Outer variable after inner block: 10
DO

在这个例子中，进一步展示了子块和变量覆盖的概念。定义了一个外部块，它声明并初始化了一个变量 outer_var。然后，定义了一个子块，它声明并初始化了一个同名的变量 inner_var，这会导致外部变量被覆盖。这个例子还展示了如何使用外部块的标签来访问外部块中的变量，以及如何修改和输出内部变量的值。最后，例子中输出了外部变量的值，它仍然保持不变。

注释

在PL/PGSQL中，你可以使用两种类型的注释：

单行注释：使用两个连续的连字符（--）开始，直到行尾。
例如：

-- 这是一个单行注释
SELECT * FROM users;

多行注释：使用 /* 开始，使用 */ 结束。
例如：

/*
这是一个
多行注释

*/
SELECT * FROM users;

变量与数据类型

在PL/PGSQL中，变量用于存储临时数据，这些数据可以在代码执行过程中使用。为了使用变量，你需要先声明它，并指定其数据类型。

数据类型：

PL/PGSQL支持多种数据类型，包括整数、文本、日期、数值等。以下是一些常见的数据类型：

整数类型：

• integer：有符号的整数
• smallint：较小的有符号整数
• bigint：较大的有符号整数
• serial：自动增加的整数，通常用作主键

文本类型：

• text：可变长度的文本字符串
• varchar(n)：可变长度的文本字符串，其中n是最大长度
• char(n)：固定长度的文本字符串，其中n是长度

日期和时间类型：

• date：日期（年、月、日）
• time：时间（时、分、秒）
• timestamp：日期和时间
• interval：时间间隔

数值类型：

• numeric 或 decimal：精确的小数
• real 或 float：浮点数
• double precision：双精度浮点数

布尔类型：

• boolean：真或假

其他类型：

• bit(n)：固定长度的位串
• bit varying(n) 或 varbit(n)：可变长度的位串
• bytea：二进制数据
• uuid：通用唯一标识符
• … 以及其他

变量声明：

在PL/PGSQL中，你可以使用 DECLARE 关键字声明变量，并指定其数据类型。例如

DECLARE
    my_integer_variable integer;
    my_text_variable text;
    my_date_variable date;
    my_numeric_variable numeric(10,2);

在上面的示例中，声明了四个变量：一个整数变量、一个文本变量、一个日期变量和一个数值变量。

DO $$ 
DECLARE 
    my_integer_variable integer;
    my_text_variable text;
    my_date_variable date;
    my_numeric_variable numeric(10,2);
BEGIN
    -- 整数类型变量赋值
    my_integer_variable := 10;
    RAISE NOTICE 'Integer variable: %', my_integer_variable;

    -- 文本类型变量赋值
    my_text_variable := 'Hello, World!';
    RAISE NOTICE 'Text variable: %', my_text_variable;

    -- 日期类型变量赋值
    my_date_variable := '2024-10-23'::date;
    RAISE NOTICE 'Date variable: %', my_date_variable;

    -- 数值类型变量赋值
    my_numeric_variable := 123.45;
    RAISE NOTICE 'Numeric variable: %', my_numeric_variable;
END $$;

在这个例子中，声明了四种类型的变量：整数、文本、日期和数值。然后，分别给这些变量赋值，并使用 RAISE NOTICE 语句输出它们的值。

请注意，例子中使用了 ::date 来进行类型转换，将字符串 '2024-10-23' 转换为日期类型。

运行这段代码，你将在输出中看到每个变量的值，如：

NOTICE:  Integer variable: 10
NOTICE:  Text variable: Hello, World!
NOTICE:  Date variable: 2024-10-23
NOTICE:  Numeric variable: 123.45

注意事项：

• 数据类型的兼容性：pl/pgsql中使用的数据类型与SQL中的数据类型兼容，如integer、varchar、char等。确保在赋值或声明变量时，使用正确的数据类型，以避免类型转换错误或数据损失。
• 默认值的设置：如果为变量设置了默认值，该变量在进入begin块时将被初始化为该默认值。如果未设置默认值，变量将被初始化为SQL的空值。默认值是在每次进入块时计算的，这意味着如果默认值依赖于某些动态值（如当前时间），那么每次进入块时，默认值都会重新计算。
• constant选项：使用constant选项可以确保变量在进入begin块后不会被重新赋值，保证该变量为常量。这与C语言中的常量概念相似。需要注意的是，constant选项与默认值并不矛盾。您可以为constant变量设置默认值，但该变量在之后的代码中不能被修改。
• not null约束：如果为变量声明了not null约束，那么试图将null值赋给该变量将导致运行时错误。为了满足not null约束，所有声明为not null的变量也必须在声明时定义一个非空的默认值。

参数别名

在PL/pgSQL（PostgreSQL的过程语言）中，参数别名是一种为函数或过程的输入参数设置别名的方式，使得在后续的代码中可以用这个别名代替参数名。这可以提高代码的可读性和可维护性。

在PL/pgSQL中，可以使用DECLARE部分来声明参数别名。下面是一个例子来说明参数别名的用法：

CREATE OR REPLACE FUNCTION test(p_subtotal int) 
RETURNS numeric AS $$
DECLARE
    -- 为参数设置别名
    subtotal alias for p_subtotal;
BEGIN
    -- 使用别名进行计算
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

--调用函数
postgres=# select test(1000);
 test
-------
 60.00
(1 row)

在这个例子中，定义了一个函数test，它接受一个名为p_subtotal的整数参数。在DECLARE部分，例子中为p_subtotal定义了一个别名subtotal。这样，在函数体内部，我们可以使用subtotal这个别名来代替p_subtotal。

在函数体中，我们使用subtotal进行计算，并返回结果。当调用这个函数并传递一个整数作为参数时，它将返回该整数的6%值。

也可以直接使用$1表示第一个入参，示例：

CREATE OR REPLACE FUNCTION test(p_subtotal int) 
RETURNS numeric AS $$
DECLARE
    -- 为参数设置别名
    subtotal alias for $1;
BEGIN
    -- 使用别名进行计算
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

输出参数

在PL/pgSQL（PostgreSQL的过程语言）中，函数可以定义参数为OUT或INOUT类型。这些参数类型在函数内部与外部之间传递信息时起着重要的作用。

OUT 参数

OUT参数用于从函数内部向外部传递数据。这意味着在函数被调用时，您不需要为OUT参数提供值，但在函数执行期间，您可以在函数内部为其分配一个值。当函数结束时，这个值会被返回给调用者。

CREATE OR REPLACE FUNCTION get_sum(a int, b int, sum OUT int) AS $$
BEGIN
    sum := a + b;
END;
$$ LANGUAGE plpgsql;

在这个例子中，get_sum函数有两个IN参数a和b，以及一个OUT参数sum。在函数内部，我们将a和b的和赋值给sum，并在函数结束时返回它。
接下来，我们创建一个匿名代码块来调用这个函数，并打印输出结果：

DO $$
DECLARE
    sum_value int;
BEGIN
    -- 直接调用函数并选择输出参数
    SELECT get_sum(10, 20) INTO sum_value;
    RAISE NOTICE 'The sum is: %', sum_value;
END $$;

运行这个匿名代码块，您会在输出中看到：

NOTICE:  The sum is: 30

INOUT 参数

INOUT参数是IN和OUT的组合。这意味着您可以在函数被调用时为其提供一个值，并在函数执行期间修改它。当函数结束时，这个修改后的值会被返回给调用者。

CREATE OR REPLACE FUNCTION add_and_multiply(a int, b int, result INOUT int) AS $$
BEGIN
    result := a + b;
    result := result * 2;
END;
$$ LANGUAGE plpgsql;

在这个例子中，add_and_multiply函数有一个INOUT参数result。在函数内部，我们首先计算a和b的和，并将其赋值给result，然后将其乘以2。当函数结束时，这个修改后的result值会被返回给调用者。
然后，我们创建一个匿名代码块来调用这个函数，并打印输出结果：

DO $$
DECLARE
    result int = 0;
BEGIN
    -- 使用 SELECT 语句调用函数，并明确地处理 INOUT 参数的返回值
    SELECT add_and_multiply(10, 20, result) INTO result;
    RAISE NOTICE 'The result is: %', result;
END $$;

运行这个匿名代码块，您会在输出中看到：

NOTICE:  The result is: 60

在这个例子中，我们为result参数提供了一个初始值0，然后在函数内部将其修改为60，并返回这个值。

我们展示了如何使用OUT和INOUT参数。在调用get_sum函数时，我们不需要为sum参数提供值，但在函数执行期间，它会被赋值。在调用add_and_multiply函数时，我们为result参数提供了一个初始值，并在函数内部修改它。

拷贝类型和记录类型

拷贝类型（Copy Types）和记录类型（Record Types）是两种不同的数据类型，它们在存储和处理数据时有不同的用途和特点。下面是对这两种类型的简单介绍：

拷贝类型（Copy Types）

拷贝类型并不是 PostgreSQL 中的一个正式术语，但可以理解为通过复制现有数据结构或类型来创建新的数据类型。在 PostgreSQL 中，常见的拷贝类型操作包括：

表的复制：

使用 CREATE TABLE ... AS SELECT ... 语句从现有表中创建一个新的表，新表具有与原表相同的结构和数据。
示例：

CREATE TABLE new_table AS SELECT * FROM existing_table;

类型定义的复制：

使用 CREATE TYPE 语句定义一个新的复合类型，该类型可以基于现有的类型。
示例：

CREATE TYPE person AS (name text, age int);
CREATE TYPE employee AS (person1 person, department text);

记录类型（Record Types）

记录类型（Record Type）是PL/pgSQL中用于处理表行的一种方式。使用记录类型，我们可以处理表中的单个行，而不需要显式地声明每个字段。

在PL/pgSQL中，我们可以使用FOR循环和FETCH命令与记录类型一起工作。

例如，以下是一个使用记录类型的示例，它从一个名为source_table的表中复制所有行到另一个表：

--源表（source_table）和目标表（target_table）的结构需要相同，
--或者至少目标是表需要有与源表相同数量的字段，并且这些字段的数据类型也要兼容。
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM source_table LOOP
        -- 插入数据到目标表
        INSERT INTO target_table VALUES r.*;
    END LOOP;
END $$;

在这个例子中，r是一个记录类型，它代表source_table表中的一行。在循环中，我们可以使用r.*来访问行中的所有字段，并将它们插入到target_table表中。

总的来说，拷贝类型和记录类型在PL/pgSQL中提供了处理表数据的有效方法。拷贝类型适用于大量数据的快速复制，而记录类型则适用于处理单个行。

总结

拷贝类型：通过复制现有数据结构或类型来创建新的数据类型，主要用于表的复制和类型定义的扩展。
记录类型：用于表示一组字段的集合，常用于过程语言中，具有动态结构和匿名特性，适用于存储和操作多个字段的数据。

控制结构

PL/pgSQL提供了丰富的控制结构，用于实现复杂的执行逻辑。

条件语句（IF、CASE）：用于根据条件执行不同的代码分支。IF语句用于简单的条件判断，CASE语句用于多分支的条件选择。

IF语句

DO $$
DECLARE
    age int := 25;
BEGIN
    IF age < 18 THEN
        RAISE NOTICE 'You are a minor.';
    ELSIF age >= 18 AND age < 60 THEN
        RAISE NOTICE 'You are an adult.';
    ELSE
        RAISE NOTICE 'You are a senior.';
    END IF;
END $$;

CASE语句

DO $$
DECLARE
    score int := 85;
BEGIN
    CASE
        WHEN score >= 90 THEN RAISE NOTICE 'Excellent!';
        WHEN score >= 80 THEN RAISE NOTICE 'Good!';
        WHEN score >= 60 THEN RAISE NOTICE 'Pass';
        ELSE RAISE NOTICE 'Fail';
    END CASE;
END $$;

循环语句（FOR、WHILE、LOOP）：用于重复执行某段代码。FOR循环用于遍历集合或数组，WHILE循环用于在满足条件时重复执行代码，LOOP循环用于无限循环，通常与退出条件结合使用。

FOR循环

DO $$
DECLARE
    i int;
BEGIN
    FOR i IN 1..5 LOOP
        RAISE NOTICE '%', i;
    END LOOP;


END $$;

WHILE循环

DO $$
DECLARE
    i int := 0;
BEGIN
    WHILE i < 5 LOOP
        RAISE NOTICE '%', i;
        i := i + 1;
    END WHILE;
END $$;

LOOP循环

DO $$
DECLARE
    i int := 0;
BEGIN
    LOOP
        EXIT WHEN i >= 5;
        RAISE NOTICE '%', i;
        i := i + 1;
    END LOOP;
END $$;

异常处理与错误捕获：PL/pgSQL提供了异常处理机制，允许开发者捕获并处理执行过程中发生的错误。使用BEGIN … EXCEPTION … END结构可以捕获指定类型的异常，并执行相应的错误处理代码。
示例

DO $$
DECLARE
BEGIN
    -- 尝试除以0
    RAISE NOTICE 'Dividing by 0...';
    10 / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero detected!';
END $$;

在这个例子中，当执行10 / 0时，会触发division_by_zero异常，然后执行异常处理代码块，输出"Division by zero detected!"。

三、PL/pgSQL高级特性

函数与过程

函数的创建与调用

在PL/pgSQL中，你可以创建自定义函数来执行特定的逻辑。函数可以接收参数，执行一系列操作，并返回一个值。创建函数后，你可以在其他SQL语句或PL/pgSQL代码块中调用它。

CREATE FUNCTION add_one(integer) RETURNS integer AS $$
BEGIN
    RETURN $1 + 1;
END;
$$ LANGUAGE plpgsql;


-- 调用函数
SELECT add_one(5);

过程的定义与执行

与函数类似，过程（在PostgreSQL中通常称为“存储过程”或“函数”，不返回值的函数可视为过程）用于封装一系列SQL语句。过程可以不返回值，主要用于执行操作，如数据插入、更新或删除。

CREATE FUNCTION update_salary(emp_id integer, new_salary numeric) RETURNS void AS $$
BEGIN
    UPDATE employees SET salary = new_salary WHERE id = emp_id;
END;
$$ LANGUAGE plpgsql;


-- 调用过程
SELECT update_salary(1, 50000);

函数与过程的参数传递

函数和过程可以接受参数，并根据这些参数执行操作。参数可以是IN（输入）、OUT（输出）或INOUT（输入输出）类型。

CREATE FUNCTION calculate(IN a integer, IN b integer, OUT sum integer, OUT product integer) AS $$
BEGIN
    sum := a + b;
    product := a * b;
END;
$$ LANGUAGE plpgsql;


-- 调用函数并获取输出参数
SELECT * FROM calculate(10, 20);

触发器

触发器的概念与作用

触发器是一种特殊的存储过程，它会在指定的数据库事件（如INSERT、UPDATE或DELETE）发生时自动执行。触发器可以帮助自动检查或修改数据，保持数据完整性，或执行自动化任务。

触发器的创建与绑定
你可以为特定的表创建触发器，并指定在何种事件上触发。触发器函数定义了当触发器被激活时要执行的操作。

--创建表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    department VARCHAR(50)
);

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    operation VARCHAR(10),      -- 操作类型，如 'INSERT', 'UPDATE', 'DELETE'
    table_name VARCHAR(100),    -- 被操作的表名
    record_id INT,              -- 被操作的记录ID
    changed_data TEXT,          -- 变更的数据，存储为JSON字符串
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 记录日志的时间
);

--编写触发器
CREATE OR REPLACE FUNCTION log_update() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log(operation, table_name, record_id, changed_data)
    VALUES ('UPDATE', TG_TABLE_NAME, NEW.id, ROW_TO_JSON(NEW) || ' - ' || ROW_TO_JSON(OLD));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

--绑定触发器
CREATE TRIGGER employees_update_trigger
AFTER UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION log_update();

--插入数据
INSERT INTO employees (name, salary, department) VALUES
('Alice', 50000, 'HR'),
('Bob', 60000, 'Engineering'),
('Charlie', 70000, 'Finance');

-- 更新数据触发
UPDATE employees
SET salary = 55000
WHERE id = 1;

--查看是否执行了触发器
SELECT * FROM audit_log;

--成功执行
postgres=# SELECT * FROM audit_log;
 id | operation | table_name | record_id |                                                       changed_data                                                        |          log_time
----+-----------+------------+-----------+---------------------------------------------------------------------------------------------------------------------------+----------------------------
  1 | UPDATE    | employees  |         1 | {"id":1,"name":"Alice","salary":55000.00,"department":"HR"} - {"id":1,"name":"Alice","salary":50000.00,"department":"HR"} | 2024-11-20 13:08:07.739927
(1 row)

游标与事务处理

游标的声明与使用

游标（Cursor）是一个数据库对象，它允许用户或应用程序在查询结果集上执行迭代操作。游标允许你一次处理查询结果集的一行，这对于需要在结果集上进行操作（如更新、删除或检查数据）的复杂查询非常有用。

以下是一个使用游标的简单示例，假设我们有一个 employees 表，包含员工信息：

-- 创建一个示例表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- 插入一些示例数据
INSERT INTO employees (name, salary) VALUES
('Alice', 50000),
('Bob', 60000),
('Charlie', 70000);

-- 声明游标
BEGIN;
DECLARE emp_cursor CURSOR FOR
SELECT id, name, salary FROM employees;

-- 打开游标
OPEN emp_cursor;

-- 提取数据
FETCH NEXT FROM emp_cursor; -- 获取第一行
FETCH NEXT FROM emp_cursor; -- 获取第二行
FETCH NEXT FROM emp_cursor; -- 获取第三行

-- 关闭游标
CLOSE emp_cursor;

-- 结束事务
COMMIT;

PL/pgsql中使用游标

CREATE OR REPLACE FUNCTION update_salaries()
RETURNS VOID AS $$
DECLARE
    emp_record RECORD; -- 用于存储每行数据的变量
    emp_cursor CURSOR FOR
    SELECT id, name, salary FROM employees; -- 定义游标
BEGIN
    -- 打开游标
    OPEN emp_cursor;

    -- 循环提取数据
    LOOP
        FETCH emp_cursor INTO emp_record; -- 从游标中提取一行数据
        EXIT WHEN NOT FOUND; -- 如果没有更多数据，退出循环

        -- 更新薪水
        UPDATE employees
        SET salary = emp_record.salary * 1.10
        WHERE id = emp_record.id;
    END LOOP;

    -- 关闭游标
    CLOSE emp_cursor;
END;
$$ LANGUAGE plpgsql;

事务的开启、提交与回滚

PL/pgSQL支持事务处理，允许你在一系列操作中保持数据的一致性。你可以使用BEGIN、COMMIT和ROLLBACK语句来控制事务。

BEGIN; -- 开启事务

-- 执行一系列操作...
COMMIT; -- 提交事务

-- 或在出现错误时
ROLLBACK; -- 回滚事务

保存点与事务的部分回滚

保存点允许你在事务中设置一个标记，之后可以回滚到该点，而不是完全回滚整个事务。

BEGIN; -- 开启事务

-- 执行一些操作...
SAVEPOINT sp1; -- 设置保存点

-- 执行更多操作...
ROLLBACK TO SAVEPOINT sp1; -- 回滚到保存点
COMMIT; -- 提交事务的其余部分

四、PL/pgSQL性能优化与调试

性能优化策略

减少数据库交互次数：通过批量操作减少与数据库的往返次数，提高性能。

使用批量操作与预处理语句：利用COPY命令进行大量数据的快速导入，使用预处理语句减少SQL解析时间。

避免在循环中执行查询：尽量将循环内的查询移到循环外，或者使用集合操作来替代逐行处理。

调试技巧

RAISE语句的使用与级别选择：使用RAISE语句在PL/pgSQL代码中输出调试信息，帮助跟踪代码执行流程和变量值。

日志记录的开启与查看方法：配置PostgreSQL的日志记录级别，以捕获和查看PL/pgSQL代码执行过程中的详细信息。

使用外部工具进行调试：利用如pgAdmin等外部工具提供的调试功能，设置断点、查看变量值和执行流程。

PL/pgSQL为PostgreSQL数据库开发者提供了强大的工具，使得数据库操作变得更加灵活和可控。无论你是初学者还是经验丰富的开发者，PL/pgSQL都是值得学习和掌握的语言。通过掌握PL/pgSQL，可以更高效地处理数据库中的复杂逻辑，为数据库应用程序添加更多的功能和灵活性。