PostgreSQL教程(二十)：PL/pgSQL过程语言

PL/pgSQL是PostgreSQL所支持的一种过程语言，它具有丰富的特性，可供开发者在数据库端编写存储过程和触发器等逻辑。以下是PL/pgSQL过程语言的完整攻略：

1. PL/pgSQL的基本语法结构

PL/pgSQL的语句格式如下：

DECLARE  -- 声明变量
BEGIN  -- ...
   -- statements
EXCEPTION  -- ...
   -- error handling statements
END;

其中各部分的作用如下：

• DECLARE: 声明存储过程或触发器体内部使用的本地变量
• BEGIN: 存储过程或触发器体内部的执行逻辑开始
• EXCEPTION: 定义在存储过程或触发器执行过程中可能发生的错误情况
• END: 存储过程或触发器体内部的执行逻辑结束

2. PL/pgSQL的变量声明

在PL/pgSQL中，可以声明各种不同类型的变量，例如：

DECLARE
    var_name var_type := var_value; -- 初始化变量值

其中，var_name是变量的名称，var_type是变量的数据类型，var_value是变量的初始值（可选）。

3. PL/pgSQL的流程控制

PL/pgSQL的流程控制包括：IF THEN、LOOP、FOR LOOP、WHILE、CASE WHEN等。

3.1 IF THEN

IF expression THEN  
   statements;
ELSE  
   statements;
END IF;

其中，expression是一个条件表达式，如果返回true，则执行IF块内的语句。否则，执行ELSE块内的语句。

3.2 LOOP

LOOP  
   statements;
   EXIT [WHEN expression];  -- 结束循环，可选的带条件
END LOOP;

其中，EXIT可用于结束循环，也可以带一个条件来确定何时结束循环。

3.3 FOR LOOP

FOR loop_var IN reversed boolean_expression LOOP  
   statements;
END LOOP;

其中，loop_var是迭代变量，boolean_expression是循环条件。

3.4 WHILE

WHILE expression LOOP  
   statements;
END LOOP;

其中，expression是一个条件表达式，只要它返回true，就会一直执行循环体中的语句。

3.5 CASE WHEN

CASE  
   WHEN condition1 THEN statements;
   WHEN condition2 THEN statements;
   ELSE statements;
END CASE;

其中，condition1、condition2等是表达式，与CASE后的值进行比较。如果匹配，执行相应的statements。如果没有匹配到任何值，则执行ELSE块内的语句。

4. PL/pgSQL的异常处理

在PL/pgSQL中，异常处理是通过EXCEPTION块来实现的。

DECLARE
   -- variables
BEGIN
   -- statements
EXCEPTION
   WHEN exception_name THEN statements;
   WHEN OTHERS THEN statements;
END;

其中，exception_name是一个异常名称，statements是要执行的语句。如果没有指定任何异常名称，所有未知异常都将被捕获并执行OTHERS块中的语句。

5. PL/pgSQL过程的示例

5.1 创建一个存储过程，计算指定两个数字的和。

CREATE FUNCTION add_two_numbers(x INT, y INT) RETURNS INT AS $$
DECLARE
   total INT;
BEGIN
   total := x + y;
   RETURN total;
END;
$$ LANGUAGE plpgsql;

使用示例：

SELECT add_two_numbers(10, 20); -- 返回30

5.2 创建一个触发器，自动将创建时间和最后修改时间写入product表中。

CREATE OR REPLACE FUNCTION update_product_timestamp() RETURNS TRIGGER AS $$
BEGIN
   NEW.create_datetime := now();
   NEW.update_datetime := now();
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_product_timestamp 
   BEFORE INSERT OR UPDATE ON product 
   FOR EACH ROW 
   EXECUTE PROCEDURE update_product_timestamp();

这个触发器将在product表中插入新记录或更新现有记录时自动更新create_datetime和update_datetime字段。