PostgreSQL | FunctionProcedure | 函数与存储过程的区别

文章目录

  • 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）都是数据库中的可重用代码块，用于执行特定的任务。

  1. 两者都提供了代码封装的机制，可以将一系列操作组织成单个单元。
  2. 都可以接受参数，并且可以返回结果。
  3. 存储过程和函数都可以在多个地方重复使用，减少了代码的冗余。
  4. 存储过程更倾向于执行一系列的操作，而函数更专注于返回一个值。

大白话讲

  1. Function通过Returns进行结果值返回，Procedure没有Returns返回值功能。（区别只是有没有Returns并不是说谁不能返回）
  2. Function通过Returns返回一个/组值，也可以returns record返回INOUT, OUT类参数值，这时候就不可以添加sql_body中的return语句。
  3. Procedure虽然不能使用Returns返回信息，但是可以通过INOUT类型参数返回多值，但不能使用Out。
  4. Functions可以被其它查询函数夹带调用并返回结果值（select XXX），Procedures不行，它只能使用CALL调用。
  5. 但是，我们说但是，在PG里Function和Procedure可以广义的理解差不多就是一个东西，结论看最后的问题示例。

2. 函数（Function）

2.1 定义

函数是一段预编译的代码，接受输入参数并返回一个值。函数可以是内置的系统函数，也可以是用户自定义的函数。

2.2 用途

函数主要用于计算和返回一个值，而不是执行一系列的SQL语句。它可以在SELECT语句、WHERE子句、ORDER BY子句等中使用。

2.3 执行

函数通常在SQL语句中嵌套使用，可以用于计算列值、过滤数据等。

2.4 事务处理

一般情况下，函数不包含事务控制语句，因为它们的目标是计算而不是执行修改数据库结构的操作。

2.5 说点例子

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ 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
BEGIN
	return a+b;
END;
$$;

select f01(1,2);

在这里插入图片描述

2. 参数中出现OUT、INOUT参数时

FUNCTION的sql_body中不能出现return行，否则报错；

  1. 若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
BEGIN
  b := a+b;
  return b;
END;
$$;

    在这里插入图片描述

  • 有INOUT、OUT，RETURNS TYPE不匹配会报错

    --ERROR:  function result type must be integer because of OUT parameters
 CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT) RETURNS VARCHAR
 LANGUAGE plpgsql
 AS $$
 DECLARE
 BEGIN
   b := 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
BEGIN
  b := 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);

  1. 若OUT、INOUT参数出现数量为多个，需要改写为RETURNS record

  • 正确写法

    CREATE OR REPLACE FUNCTION f01(IN a INT,INOUT b INT,OUT c INT) RETURNS record
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
  b := 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
BEGIN
 b := 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 ] PROCEDURE
    name ( [ [ 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;

  1. 基本用法与Function一致，只是Procedure参数中不能使用OUT类型，且不能使用Returns返回数据。

    CREATE OR REPLACE PROCEDURE p01(IN a INT, INOUT b INT, INOUT c INT) Language plpgsql
AS $$
DECLARE
BEGIN
  b := a+b;
  c := b*b;
END;
$$;

call p01(2,3,4);

  2. 如果使用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
BEGIN
  b := a+b;
  c := b*b;
END;
$$;

问题

  1. 函数和存储过程是否都可以执行DDL？

    答案是：在PG中都可以执行，不过函数一般用来计算结果，最好不要夹带DDL语句；

    CREATE OR REPLACE FUNCTION f01() RETURNS VOID Language plpgsql
AS $$
DECLARE
BEGIN
  CREATE TABLE IF NOT EXISTS t1(id int);
  DROP TABLE t1;
END;
$$;
select f01();

CREATE OR REPLACE PROCEDURE p01() Language plpgsql
AS $$
DECLARE
BEGIN
	CREATE TABLE IF NOT EXISTS t1(id int);
  DROP TABLE t1;
END;
$$;
call p01();

  2. 为什么说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


http://www.niftyadmin.cn/n/5288089.html

相关文章

鸿蒙小车之软件定时器实验

鸿蒙小车之软件定时器实验

说到鸿蒙我们都会想到华为mate60：遥遥领先！我们一直领先！ 我们这个小车也是采用的是鸿蒙操作系统，学习鸿蒙小车，让你遥遥领先于你的同学。 文章目录 前言一、什么是软件定时器？二、软件定时器的特性三、软件…
阅读更多...
【数据结构】六、树和二叉树

【数据结构】六、树和二叉树

目录 一、树的基本概念 二、二叉树 2.1二叉树的性质 2.2二叉树的存储结构 2.3遍历二叉树 先序遍历 中序遍历 后序遍历 层次遍历 2.4二叉树的应用 计算叶子数 前序遍历建树 根据序列恢复二叉树 计算树的深度 判断完全二叉树 三、线索二叉树 3.1线索化 四、树和森林…
阅读更多...
算符优先语法分析设计原理与实现

算符优先语法分析设计原理与实现

前言： 作者的词法分析程序以及算符优先语法分析设计程序仓库链接 1、目标任务 **[实验项目] **以专题 1 词法分析程序的输出为语法分析的输入，实现算符优先分析算法，完成以下描述算术表达式的算符优先文法的算符优先分析过程。 G[E]:E→E…
阅读更多...
蓝桥杯备赛 day 1 —— 递归 、递归、枚举算法（C/C++，零基础，配图）

蓝桥杯备赛 day 1 —— 递归 、递归、枚举算法（C/C++，零基础，配图）

目录 🌈前言 📁 枚举的概念 📁递归的概念 例题： 1. 递归实现指数型枚举 2. 递归实现排列型枚举 3. 递归实现组合型枚举 📁 递推的概念 例题： 斐波那契数列 📁习题 1. 带分数 2. 反硬币 3. 费解的…
阅读更多...
Elasticsearch之常用DSL语句

Elasticsearch之常用DSL语句

目录 1. Elasticsearch之常用DSL语句 1.1 操作索引 1.2 文档操作 1.3 DSL查询 1.4 搜索结果处理 1.5 数据聚合 1. Elasticsearch之常用DSL语句 1.1 操作索引 mapping是对索引库中文档的约束，常见的mapping属性包括： - type：字段数据类…
阅读更多...
Floyd（弗洛伊德）算法总结

Floyd（弗洛伊德）算法总结

知识概览 Floyd算法适合解决多源汇最短路问题，其中源点是起点，汇点是终点。时间复杂度是。 例题展示 题目链接 活动 - AcWing 系统讲解常用算法与数据结构，给出相应代码模板，并会布置、讲解相应的基础算法题目。https://www.acw…
阅读更多...
AcWing算法进阶课-1.17.1费用流

AcWing算法进阶课-1.17.1费用流

算法进阶课整理 CSDN个人主页：更好的阅读体验 原题链接 题目描述 给定一个包含 n n n 个点 m m m 条边的有向图，并给定每条边的容量和费用，边的容量非负。 图中可能存在重边和自环，保证费用不会存在负环。 求从 S S S 到 …
阅读更多...
时序预测 | Matlab实现SSA-CNN-LSTM麻雀算法优化卷积长短期记忆神经网络时间序列预测

时序预测 | Matlab实现SSA-CNN-LSTM麻雀算法优化卷积长短期记忆神经网络时间序列预测

时序预测 | Matlab实现SSA-CNN-LSTM麻雀算法优化卷积长短期记忆神经网络时间序列预测 目录 时序预测 | Matlab实现SSA-CNN-LSTM麻雀算法优化卷积长短期记忆神经网络时间序列预测预测效果基本介绍程序设计参考资料 预测效果 基本介绍 MATLAB实现SSA-CNN-LSTM麻雀算法优化卷积长短…
阅读更多...
最新文章

Copyright @ 2022~2023