创建简单的 PL/pgSQL 存储过程

在 PostgreSQL 中，创建存储过程可以通过使用 CREATE FUNCTION 语句来实现。PostgreSQL 支持多种语言编写存储过程，包括 SQL、PL/pgSQL（Procedural Language/PostgreSQL）、PL/Python、PL/Perl 等。其中，PL/pgSQL 是最常用的内置过程语言。

创建简单的 PL/pgSQL 存储过程

假设我们想要创建一个存储过程，它接受两个整数作为输入参数，并返回它们的和。

-- 创建存储过程
CREATE OR REPLACE FUNCTION add_two_numbers(a integer, b integer)
RETURNS integer AS $$
BEGINRETURN a + b;
END;
$$ LANGUAGE plpgsql;-- 调用存储过程
SELECT add_two_numbers(3, 5);

CREATE OR REPLACE FUNCTION

• CREATE：这是用来创建新对象的关键字。
• OR REPLACE：如果已经存在同名的函数，OR REPLACE 会先删除旧的函数再创建新的。这在修改已有函数时非常有用，因为它避免了需要手动删除再创建的麻烦，并且可以在不中断服务的情况下更新函数逻辑。

add_two_numbers(a integer, b integer)

• add_two_numbers：这是你给函数起的名字。你可以根据函数的功能为它取任何合法的名字。
• (a integer, b integer)：这部分定义了函数的参数列表。在这个例子中，函数有两个参数，分别是 a 和 b，并且它们的数据类型都是 integer（整数）。

RETURNS integer

• RETURNS：指定函数将返回的数据类型。在这个例子中，函数返回的是一个 integer 类型的结果，即两个输入整数相加后的结果。

AS$$ ... $$

• AS $$ 和后面的 $$：这两个符号标志着函数体的开始和结束。使用双美元符号（$$）作为分隔符可以避免与 SQL 语句中的单引号冲突。当然，你也可以使用其他字符或字符串作为分隔符，例如 $$、$BODY$ 等。

函数体

BEGINRETURN a + b;
END;

• BEGIN ... END;：这一对关键字包围着 PL/pgSQL 代码块。PL/pgSQL 是 PostgreSQL 的过程化语言，允许你在数据库中编写更复杂的逻辑。
• RETURN a + b;：这是函数体内的一条语句，表示返回两个参数 a 和 b 相加的结果。

LANGUAGE plpgsql

LANGUAGE：指定函数使用的编程语言。在这个例子中，plpgsql 表示使用的是 PostgreSQL 的内置过程化语言 PL/pgSQL。如果你使用的是另一种语言（如 SQL、PL/Python、PL/Perl），则应相应地更改这里的值。

创建带有 IN 和 OUT 参数的存储过程

如果你需要一个存储过程能够输出多个值，可以使用 OUT 参数。

-- 创建存储过程
CREATE OR REPLACE FUNCTION get_min_max(in_nums integer[])
RETURNS TABLE(min_val integer, max_val integer) AS $$
BEGINRETURN QUERY SELECT MIN(val), MAX(val) FROM unnest(in_nums) AS val;
END;
$$ LANGUAGE plpgsql;-- 调用存储过程
SELECT * FROM get_min_max(ARRAY[1, 2, 3, 4, 5]);

创建修改数据的存储过程

这里展示如何创建一个存储过程，该过程插入一条记录到表中。

-- 假设有一个名为 users 的表
CREATE TABLE IF NOT EXISTS users (id serial PRIMARY KEY,name text NOT NULL,age integer NOT NULL
);-- 创建存储过程
CREATE OR REPLACE PROCEDURE insert_user(user_name text, user_age integer)
LANGUAGE plpgsql
AS $$
BEGININSERT INTO users (name, age) VALUES (user_name, user_age);
END;
$$;-- 调用存储过程
CALL insert_user('Alice', 30);

请注意，从 PostgreSQL 11 开始，正式支持了 CREATE PROCEDURE 语法，用于创建不返回结果集的过程。在此之前版本中，你只能使用 CREATE FUNCTION 来创建过程，即使这些函数实际上并不返回任何有意义的值。

创建带有异常处理的复杂存储过程

对于更复杂的逻辑，你可以添加异常处理来增强程序的健壮性。

-- 创建存储过程
CREATE OR REPLACE PROCEDURE update_user(id integer, new_name text, new_age integer)
LANGUAGE plpgsql
AS $$
BEGINUPDATE usersSET name = new_name, age = new_ageWHERE users.id = id;IF NOT FOUND THENRAISE EXCEPTION 'User with ID % not found.', id;END IF;EXCEPTIONWHEN others THENRAISE NOTICE 'An error occurred: %', SQLERRM;-- 可以选择在此处记录日志或采取其他措施
END;
$$;-- 调用存储过程
CALL update_user(1, 'Bob', 28);

注意事项

• 权限：确保创建存储过程的用户有足够的权限。
• 事务管理：存储过程中的所有操作都在同一个事务中执行。如果过程中有任何错误发生，整个事务将被回滚。
• 调试：可以在存储过程中使用 RAISE NOTICE 或 RAISE WARNING 来输出调试信息。
• 性能考虑：尽量避免在存储过程中进行大量的行级操作，因为这可能会导致性能问题。