阿里云 个人网站,上海比较好的设计院,网页设计图片大小,做款app多少钱文章目录 创建简单的 PL/pgSQL 存储过程CREATE OR REPLACE FUNCTIONadd_two_numbers(a integer, b integer)RETURNS integerAS$$ ... $$函数体LANGUAGE plpgsql 创建带有 IN 和 OUT 参数的存储过程创建修改数据的存储过程创建带有异常处理的复杂存储过程 在 PostgreSQL 中… 文章目录 创建简单的 PL/pgSQL 存储过程CREATE OR REPLACE FUNCTIONadd_two_numbers(a integer, b integer)RETURNS integerAS$$ ... $$函数体LANGUAGE plpgsql 创建带有 IN 和 OUT 参数的存储过程创建修改数据的存储过程创建带有异常处理的复杂存储过程 在 PostgreSQL 中创建存储过程可以通过使用 CREATE FUNCTION 语句来实现。PostgreSQL 支持多种语言编写存储过程包括 SQL、PL/pgSQLProcedural 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 来输出调试信息。性能考虑尽量避免在存储过程中进行大量的行级操作因为这可能会导致性能问题。
