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

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/274065.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【Java】工业园区高精准UWB定位系统源码

【Java】工业园区高精准UWB定位系统源码

UWB (ULTRA WIDE BAND, UWB) 技术是一种无线载波通讯技术，它不采用正弦载波，而是利用纳秒级的非正弦波窄脉冲传输数据，因此其所占的频谱范围很宽。UWB定位系统依托在移动通信，雷达，微波电路，云计算与大数据…
阅读更多...
02之Python运算符与if结构

02之Python运算符与if结构

Day02之Python运算符与if结构 一、昨日回顾 1、回顾昨天的课程内容 略 2、回顾昨天的作业 定义变量，c1 ‘可乐’，c2 ‘牛奶’，通过Python代码把c1内容调整为牛奶，c2调整为可乐。 # 1、定义两个变量 c1 可乐 c2 牛奶# 2、…
阅读更多...
以源码为驱动：Java版工程项目管理系统平台助力工程企业迈向数字化管理的巅峰

以源码为驱动：Java版工程项目管理系统平台助力工程企业迈向数字化管理的巅峰

随着企业规模的不断扩大和业务的快速发展，传统的工程项目管理方式已经无法满足现代企业的需求。为了提高工程管理效率、减轻劳动强度、提高信息处理速度和准确性，企业需要借助先进的数字化技术进行转型。本文将介绍一款采用Spring CloudSpring BootMybat…
阅读更多...
[Linux] MySQL数据库的备份与恢复

[Linux] MySQL数据库的备份与恢复

一、数据库备份的分类和备份策略 1.1 数据库备份的分类 1）物理备份 物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。 物理备份方法： 冷备份(脱机备份) ：是在关闭数据库的时候进…
阅读更多...
Text-to-SQL小白入门（十）RLHF在Text2SQL领域的探索实践

Text-to-SQL小白入门（十）RLHF在Text2SQL领域的探索实践

本文内容主要基于以下开源项目探索实践， Awesome-Text2SQL:GitHub - eosphoros-ai/Awesome-Text2SQL: Curated tutorials and resources for Large Language Models, Text2SQL, Text2DSL、Text2API、Text2Vis and more.DB-GPT-Hub：GitHub - eosphoros-ai…
阅读更多...
Java 对象内存布局

Java 对象内存布局

在虚拟机中，Java对象在内存中的布局可以分为三块： 对象头（Header） ：包含 markword 标记字段和类型指针，32 位上大小是 8 个字节，64 位 16 个字节，实例数据（Instance Dat…
阅读更多...
特殊求和（C++）

特殊求和（C++）

系列文章目录 进阶的卡莎C++_睡觉觉觉得的博客-CSDN博客数1的个数_睡觉觉觉得的博客-CSDN博客双精度浮点数的输入输出_睡觉觉觉得的博客-CSDN博客足球联赛积分_睡觉觉觉得的博客-CSDN博客大减价(一级)_睡觉觉觉得的博客-CSDN博客小写字母的判断_睡觉觉觉得的博客-CSDN博客纸币(…
阅读更多...
LeetCode206反转链表(java实现)

LeetCode206反转链表(java实现)

今天带来的题目解析是leetcode206，反转链表，我们来看下题目描述 如何实现链表的反转呢？我在这里提供的思路是双指针的思路。 具体的思路如下： 假设我们的原链表如下 首先定义一个指针pre，用于指向head之前的位置&am…
阅读更多...
浮点数（float）与整型数（int）的转换

浮点数（float）与整型数（int）的转换

1.浮点数（float/double）转整型数（int）——向零舍入 假设定义float a1.3,b1.6,c2.0; int aa,bb,cc; 使用强制转换aa(int)a;bb(int)b;cc(int)c;结果aa1;bb1;cc2; 在处理时为了四舍五入，aa(int)(a0.5);bb(int)(b0.5);…
阅读更多...
Kafka、RocketMQ、RabbitMQ消息丢失可能存在的地方，以及解决方案

Kafka、RocketMQ、RabbitMQ消息丢失可能存在的地方，以及解决方案

这里主要对比：Kafka、RocketMQ、RabbitMQ 介绍一下消息生产、存储、消费三者的架构形式。 消息丢失可能存在的场景： 情况一： 生产者发送给MQ的过程消息丢失 在写消息的过程中因为网络的原因，还没到mq消息就丢失了；或…
阅读更多...
为什么要运营海外社媒？海外云手机能发挥什么作用？

为什么要运营海外社媒？海外云手机能发挥什么作用？

基于海外社媒在全球范围内拥有的大量流量，海外社媒运营成为了品牌推广、内容创作和用户互动的重要途径。本文将探讨海外社媒运营的重要性，并介绍海外云手机在这一过程中的卓越帮助。 海外社媒运营的重要性 首先，海外社媒运营有助于企业扩大品…
阅读更多...
搭建Vue前端项目的流程

搭建Vue前端项目的流程

1、安装nodejs 测试安装是否成功 $ npm -v 6.14.16 $ node -v v12.22.122、全局安装npm install -g vue/cli，后续会使用到vue命令 $ vue --version vue/cli 5.0.8使用vue create demo_project_fe命令创建项目，使用箭头键来选择，确认使用回车…
阅读更多...
Linux内核中断

Linux内核中断

Linux内核中断 ARM里当按下按键的时候，他首先会执行汇编文件start.s里面的异常向量表里面的irq,在irq里面进行一些操作。 再跳转到C的do_irq(); 进行操作：1）判断中断的序号；2）处理中断；3）清除中…
阅读更多...
macOS系统下载安装PyCharm社区版本的流程(详细）

macOS系统下载安装PyCharm社区版本的流程(详细）

第一步 进入PyCharm官网，链接：Get Your Educational Tool - JetBrains 第二步 选择下拉框，根据自己的电脑芯片选择下载版本（芯片查看位置：设置-通用-关于本机）然后点击Download按钮 ​​​​​​​ -- 第…
阅读更多...
XUbuntu22.04之删除多余虚拟网卡和虚拟网桥(二百零四)

XUbuntu22.04之删除多余虚拟网卡和虚拟网桥(二百零四)

简介： CSDN博客专家，专注Android/Linux系统，分享多mic语音方案、音视频、编解码等技术，与大家一起成长！ 优质专栏：Audio工程师进阶系列【原创干货持续更新中……】🚀 优质专栏：多媒…
阅读更多...
基础概念、思维习惯、知识图谱

基础概念、思维习惯、知识图谱

说明： 刚开始写本文，因为本文涉及的内容会很多，部分内容要写清楚不容易，所以提前声明，现在只是第一阶段，先收集零散的内容，第二阶段再大刀阔斧地重构目录。 一，基础概念 1&#xf…
阅读更多...
win桌面圣诞小插件分享

win桌面圣诞小插件分享

最终效果 文章目录 最终效果前言下载效果更多完结 前言 圣诞到了，最近有小伙伴问我有没有一些桌面圣诞小插件可以分享一下，所以我就特地去寻找了一些，感觉效果还不错，所以分享出来 下载 链接：https://pan.baidu.com…
阅读更多...
css 设置鼠标覆盖显示菜单

css 设置鼠标覆盖显示菜单

鼠标覆盖到“全部分类”效果如下 鼠标放到“精品推荐”效果如下 <!DOCTYPE html> <html lang"en"> <head><meta charset"UTF-8"><meta name"viewport" content"widthdevice-width, initial-scale1.0"&g…
阅读更多...
猫冻干哪个牌子好又便宜？性价比高的5款主食冻干猫粮推荐

猫冻干哪个牌子好又便宜？性价比高的5款主食冻干猫粮推荐

各位铲屎官都知道猫天性是食肉动物&#xff0c;无肉不欢的。而冻干猫粮对于猫咪来说是最好还原猫咪食肉天性的食物&#xff0c;不仅可以当成猫咪的主食&#xff0c;也可以用来给猫咪当成零食&#xff0c;帮助猫咪补充营养。冻干猫粮是经过真空冷冻干燥处理的鲜肉&#xff0c;能…
阅读更多...
DRF从入门到精通五（路由组件、认证组件、权限组件、频率组件及认证、权限源码分析）

DRF从入门到精通五（路由组件、认证组件、权限组件、频率组件及认证、权限源码分析）

文章目录 一、路由组件REST framework提供了两个routeraction装饰器 二、认证组件(Authentication)三、权限组件(Permissions)内置权限类 四、频率组件(Throttling)五、权限组件源码分析六、认证组件源码分析 一、路由组件 对于视图集ViewSetMixin&#xff0c;我们除了可以自己…
阅读更多...
最新文章

Copyright @ 2022~2023