文章目录
- PostgreSQL | Function&Procedure | 函数与存储过程的区别
- 1. 简述
- 书面说法
- 大白话讲
- 2. 函数（Function）
- 2.1 定义
- 2.2 用途
- 2.3 执行
- 2.4 事务处理
- 2.5 说点例子
- 1. 当参数都是IN类时
- 2. 参数中出现OUT、INOUT参数时
- 3. 存储过程（Procedure）
- 3.1 定义
- 3.2 用途
- 3.3 执行
- 3.4 事务处理
- 3.5 说点例子
- 问题
- 参阅
PostgreSQL | Function&Procedure | 函数与存储过程的区别
你可能想了解：PostgreSQL的Function和Procedure是一个东西吗？有什么区别？分别用于什么场景？如何更好的理解与记忆？有没有什么注意事项？
1. 简述
书面说法
函数（Function）和存储过程（Stored Procedure）都是数据库中的可重用代码块，用于执行特定的任务。
- 两者都提供了代码封装的机制，可以将一系列操作组织成单个单元。
- 都可以接受参数，并且可以返回结果。
- 存储过程和函数都可以在多个地方重复使用，减少了代码的冗余。
- 存储过程更倾向于执行一系列的操作，而函数更专注于返回一个值。
大白话讲
- Function通过Returns进行结果值返回，Procedure没有Returns返回值功能。（区别只是有没有Returns并不是说谁不能返回）
- Function通过Returns返回一个/组值，也可以returns record返回INOUT, OUT类参数值，这时候就不可以添加sql_body中的return语句。
- Procedure虽然不能使用Returns返回信息，但是可以通过INOUT类型参数返回多值，但不能使用Out。
- Functions可以被其它查询函数夹带调用并返回结果值（select XXX），Procedures不行，它只能使用CALL调用。
- 但是，我们说但是，在PG里Function和Procedure可以广义的理解差不多就是一个东西，结论看最后的问题示例。
2. 函数（Function）
2.1 定义
函数是一段预编译的代码，接受输入参数并返回一个值。函数可以是内置的系统函数，也可以是用户自定义的函数。
2.2 用途
函数主要用于计算和返回一个值，而不是执行一系列的SQL语句。它可以在SELECT语句、WHERE子句、ORDER BY子句等中使用。
2.3 执行
函数通常在SQL语句中嵌套使用，可以用于计算列值、过滤数据等。
2.4 事务处理
一般情况下，函数不包含事务控制语句，因为它们的目标是计算而不是执行修改数据库结构的操作。
2.5 说点例子
CREATE [ OR REPLACE ] FUNCTIONname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )[ RETURNS rettype| RETURNS TABLE ( column_name column_type [, ...] ) ]{ LANGUAGE lang_name| TRANSFORM { FOR TYPE type_name } [, ... ]| WINDOW| { IMMUTABLE | STABLE | VOLATILE }| [ NOT ] LEAKPROOF| { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }| { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }| PARALLEL { UNSAFE | RESTRICTED | SAFE }| COST execution_cost| ROWS result_rows| SUPPORT support_function| SET configuration_parameter { TO value | = value | FROM CURRENT }| AS 'definition'| AS 'obj_file', 'link_symbol'| sql_body} ...-- 简单点，常用结构
CREATE OR REPLACE FUNCTION FUNCTION_NAME(IN PARA1 INT,INOUT PARA2 INT, OUT PARA3)
RETURNS [TYPE|RECORD] LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
[RETURN TYPE]
END;
$$;
1. 当参数都是IN类时
argmode：IN、INOUT、OUT（其中IN类参数类型可以将IN省略不写）
RETURNS TYPE + return xxx 按标准方式处理即可
CREATE OR REPLACE FUNCTION f01(IN a INT,IN b INT) RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
BEGINreturn a+b;
END;
$$;select f01(1,2);
2. 参数中出现OUT、INOUT参数时
FUNCTION的sql_body中不能出现return行，否则报错；
- 若OUT、INOUT参数出现数量为1个，RETURNS Type 的TYPE需要与INOUT参数一致，且存储过程内不需要写return;
-
正确写法
CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT) RETURNS INT LANGUAGE plpgsql AS $$ DECLARE BEGIN b := a+b; -- return b; END; $$;select f01(2,3);
-
有INOUT、OUT，function内有return会报错
--ERROR: RETURN cannot have a parameter in function with OUT parameters CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT) RETURNS INT LANGUAGE plpgsql AS $$ DECLARE BEGINb := a+b;return b; END; $$;
-
有INOUT、OUT，RETURNS TYPE不匹配会报错
--ERROR: function result type must be integer because of OUT parametersCREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT) RETURNS VARCHARLANGUAGE plpgsqlAS $$DECLAREBEGINb := a+b;END;$$;
-
有OUT参数时，调用时OUT参数位不需要输入信息的，否则报错-
-- HINT: No function matches the given name and argument types. You might need to add explicit type casts. CREATE OR REPLACE FUNCTION f01(IN a INT,OUT b INT) RETURNS INT LANGUAGE plpgsql AS $$ DECLARE BEGINb := a+b; END; $$;select f01(2,3);
-
有INOUT参数时，INOUT参数位必须输入信息，否则报错
-- HINT: No function matches the given name and argument types. You might need to add explicit type casts. CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT) RETURNS INT LANGUAGE plpgsql AS $$ DECLARE BEGIN b := a+b; END; $$;select f01(2);
- 若OUT、INOUT参数出现数量为多个，需要改写为RETURNS record
-
正确写法
CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT,OUT c INT) RETURNS record LANGUAGE plpgsql AS $$ DECLARE BEGINb := a+b;c := b*b; END; $$;select f01(1,2);
-
未调整为RETURNS record，将会报错
--ERROR: function result type must be record because of OUT parameters CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT,OUT c INT) RETURNS INT LANGUAGE plpgsql AS $$ DECLARE BEGINb := a+b;c := b*b; END; $$;
-
多INOUT、OUT参数其它错误与1个INOUT、OUT基本一致，不再举例。
3. 存储过程（Procedure）
3.1 定义
存储过程是一组预编译的SQL语句集，被保存在数据库中，可以在需要时调用执行。存储过程通常由数据库管理员或有特殊权限的用户创建。
3.2 用途
存储过程主要用于封装和执行一系列的SQL语句，以完成特定的任务。它可以接受参数，并可以包含条件逻辑、循环等程序控制结构。
3.3 执行
存储过程可以被应用程序或其他存储过程调用。一旦创建，它们可以在数据库中被重复使用，提高了代码的可维护性和可重用性。
3.4 事务处理
存储过程可以包含事务控制语句，允许进行复杂的事务处理。
3.5 说点例子
CREATE [ OR REPLACE ] PROCEDUREname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ){ LANGUAGE lang_name| TRANSFORM { FOR TYPE type_name } [, ... ]| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER| SET configuration_parameter { TO value | = value | FROM CURRENT }| AS 'definition'| AS 'obj_file', 'link_symbol'| sql_body} ...-- 简单点，常用结构
CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(IN/INOUT PARA1,...)
AS $$
Declare
BEGIN
END;
$$ Language plpgsql;
-
基本用法与Function一致，只是Procedure参数中不能使用OUT类型，且不能使用Returns返回数据。
CREATE OR REPLACE PROCEDURE p01(IN a INT, INOUT b INT, INOUT c INT) Language plpgsql AS $$ DECLARE BEGINb := a+b;c := b*b; END; $$;call p01(2,3,4);
-
如果使用OUT参数将报错
--ERROR: procedures cannot have OUT arguments --HINT: INOUT arguments are permitted. CREATE OR REPLACE PROCEDURE p01(IN a INT, INOUT b INT, OUT c INT) Language plpgsql AS $$ DECLARE BEGINb := a+b;c := b*b; END; $$;
问题
-
函数和存储过程是否都可以执行DDL？
答案是：在PG中
都可以执行，不过函数一般用来计算结果，最好不要夹带DDL语句；CREATE OR REPLACE FUNCTION f01() RETURNS VOID Language plpgsql AS $$ DECLARE BEGINCREATE TABLE IF NOT EXISTS t1(id int);DROP TABLE t1; END; $$; select f01();CREATE OR REPLACE PROCEDURE p01() Language plpgsql AS $$ DECLARE BEGINCREATE TABLE IF NOT EXISTS t1(id int);DROP TABLE t1; END; $$; call p01();
-
为什么说PG中 函数 与 存储过程 广义上可以理解为一个东西？
我们来看一个报错，通过报错提示我们发现，函数与存储过程在报错中都被叫做
function。CREATE OR REPLACE FUNCTION f01() RETURNS VOID Language plpgsql AS $$ DECLARE BEGIN-- 不存在这张表，会报错；DROP TABLE t123; END; $$; select f01();CREATE OR REPLACE PROCEDURE p01() Language plpgsql AS $$ DECLARE BEGIN-- 不存在这张表，会报错；DROP TABLE t456; END; $$; call p01();
参阅
SQL - CREATE FUNCTION
SQL - CREATE PROCEDURE
Procedures Vs. Functions in PostgreSQL
🎉如果对你有所帮助，可以点赞、关注、收藏起来，不然下次就找不到了🎉
【点赞】⭐️⭐️⭐️⭐️⭐️
【关注】⭐️⭐️⭐️⭐️⭐️
【收藏】⭐️⭐️⭐️⭐️⭐️
Thanks for watching.
–Kenny
