PostgreSQL的函数和存储过程--MemFireDB

艾木酱
• 阅读 1501

简介

PostgreSQL是最流行的对象关系型数据库系统。它是一个强大的、高性能的数据库系统。在这篇文章中，我们将讨论如何使用函数和存储过程来执行操作，如插入、删除、更新和查询。 感兴趣的同学可以通过 memfiredb.com 提供的免费云数据库一边操作一边阅读。

函数

一般来说，函数是一组进行任何操作的SQL语句，如选择、插入、删除和更新。在PostgreSQL中有两种类型的函数 "系统定义的函数 "和 "用户定义的函数"。在这篇文章中，我们讨论用户定义的函数。

函数语法
CREATE OR REPLACE FUNCTION function_name (parameters-list)
RETURNS return_type
LANGUAGE plpgsql
AS
$$
    DECLARE
    --- variables
    BEGIN
        --- SQL statements (logic)
    END
$$
优势

你可以在一个函数中拥有多个SQL语句，你可以返回任何类型的结果，如一个表或一个单一的值（整数，varchar，日期，时间戳，等等）。

限制

你不能在函数中使用事务。

存储过程

为了克服函数的限制，PostgreSQL提供了存储过程来支持事务。在存储过程中，我们可以启动、提交、回滚事务。然而，存储过程不能像表一样返回一个结果集。它只能返回INOUT参数。

语法

CREATE OR REPLACE PROCEDURE procedure_name (parameters-list)
LANGUAGE plpgsql
AS
$$
DECLARE
--- Variables
BEGIN

    --- SQL statements (logic)

END
$$

创建数据库

我们这里使用 memfiredb.com 提供的免费云数据库，登录之后点击创建数据库，输入相应的信息就可以完成数据库的创建了，非常简单方便：

PostgreSQL的函数和存储过程--MemFireDB

创建完数据库后，下载一个推荐的客户端，参照教程连接数据库。 https://community.memfiredb.com/topic/47/%E5%A6%82%E4%BD%95%E4%B8%8B%E8%BD%BDwindows%E5%AE%A2%E6%88%B7%E7%AB%AF-dbeaver

建表

让我们使用以下SQL脚本在testdb数据库中创建Employees表：

CREATE TABLE Employees
(
    Id serial,
    Name VARCHAR(100),
    DateOfBirth Date,
    City VARCHAR(100),
    Designation VARCHAR(100),
    JoiningDate Date
)

存储过程创建及使用

参数类型

在创建存储过程和函数之前，让我们先看一下参数的类型，有三种类型的参数，我们可以在函数和存储过程中使用。

  • IN
  • OUT
  • INOUT
IN

IN代表一个输入参数。它用于在函数或存储过程中传递数值，如果我们不在参数名称后面使用IN关键字，默认情况下所有的参数都是输入类型。

OUT

OUT代表输出类型的参数。它返回值；你可以把它作为空值传递，也可以不初始化，因为这些类型的参数只用于从函数和存储过程中设置和返回值。

INOUT

INOUT代表输入和输出类型的参数，这些类型的参数可以用来传递值，也可以从一个函数或存储过程中返回值。

创建存储过程

使用下面的脚本来创建一个名为 "AddEmployee "的存储过程。这将在Employees表中插入信息。

CREATE OR REPLACE PROCEDURE AddEmployee
(
    EmpId INOUT INT,
    EmpName VARCHAR(100),
    EmpDob DATE,
    EmpCity VARCHAR(100),
    EmpDesignation VARCHAR(100),
    EmpJoiningDate DATE
)
LANGUAGE plpgsql AS
$$
BEGIN
   INSERT INTO Employees (Name,DateOfBirth,City,Designation,JoiningDate) VALUES
    (EmpName,
     EmpDob,
     EmpCity,
     EmpDesignation,
     EmpJoiningDate
    ) RETURNING Id INTO EmpId;
END
$$;

然后执行一下该存储过程，将信息插入到Employees表中：

CALL AddEmployee(null,'Peter Parker','1997-10-01','New York' ,'Web Developer','2020-11-01');

然后我们执行命令，检查一下是否有数据插入到表中：

SELECT * FROM Employees;

接下来我们将创建一个存储过程来更新Employees记录：

CREATE OR REPLACE PROCEDURE UpdateEmployee
(
    EmpId INT,
    EmpName VARCHAR(100),
    EmpDob DATE,
    EmpCity VARCHAR(100),
    EmpDesignation VARCHAR(100),
    EmpJoiningDate DATE
)
LANGUAGE plpgsql AS
$$
BEGIN
   UPDATE Employees SET
   Name = EmpName,
   DateOfBirth = EmpDob,
   City = EmpCity,
   Designation = EmpDesignation,
   JoiningDate = EmpJoiningDate
   Where Id = EmpId;
END
$$;

我们试着执行一下：

CALL UpdateEmployee(1,'Peter S Parker','1999-10-01','New York' ,'Web Developer','2020-11-01');

我们已经创建了能执行插入和更新的存储过程，现在我们将创建一个存储过程，使我们能够删除Employees中的记录。

CREATE OR REPLACE PROCEDURE DeleteEmployee
(
    EmpId INT
)
LANGUAGE plpgsql AS
$$
BEGIN
    DELETE FROM Employees WHERE Id = EmpId;
END
$$;

执行该存储过程：

CALL DeleteEmployee(2);

会删除id是2的记录。 上面我们演示了如何使用存储过程，接下来我们将演示如何创建和使用函数。

函数创建及使用

先看一个简单的例子：

CREATE OR REPLACE FUNCTION GetAllEmployees()
RETURNS Employees
LANGUAGE SQL
AS
$$
    SELECT * FROM Employees;
$$;

然后我们试着执行下面的语句：

SELECT * FROM GetAllEmployees();

上面的select语句会从GetAllEmployees函数的返回结果中进行查询。 下面是一个可以接受输入参数的例子：

CREATE OR REPLACE FUNCTION GetEmployeeById(EmpId INT)
RETURNS Employees
LANGUAGE SQL
AS
$$
    SELECT * FROM Employees WHERE Id = EmpId;
$$;

执行：

SELECT * FROM GetEmployeeById(3);

再看一个例子，我们正Employees表中有一个出生日期字段，所以让我们创建一个函数来根据出生日期返回年龄。这里用到了一个系统内置的age函数来计算年龄，它接受两个参数：当前日期和出生日期。它通过计算差值来返回年龄。

CREATE OR REPLACE FUNCTION GetEmployeeAge(EmpId INT, Age OUT VARCHAR(100))
LANGUAGE plpgsql
AS
$$
BEGIN
    SELECT AGE(NOW()::Date,DateOfBirth) into Age FROM Employees WHERE Id = EmpId;
END;
$$

上面定义的GetEmployeeAge函数包含两个参数，其中EmpId是int类型的输入参数，Age则是varchar类型的输出参数，所以，该函数的调用方法如下：

SELECT * FROM GetEmployeeAge(1);

总结 当我们需要返回单值结果或表格式结果时，函数是一个很好的选择。如果你想启动一个事务，提交或回滚多个SQL语句，那么存储过程是最佳选择。 PostgreSQL是最流行的对象关系型数据库系统。它是一个强大的、高性能的数据库系统。在这篇文章中，我们将讨论如何使用函数和存储过程来执行操作，如插入、删除、更新和查询。 感兴趣的同学可以通过 memfiredb.com 提供的免费云数据库一边操作一边阅读。

函数

一般来说，函数是一组进行任何操作的SQL语句，如选择、插入、删除和更新。在PostgreSQL中有两种类型的函数 "系统定义的函数 "和 "用户定义的函数"。在这篇文章中，我们讨论用户定义的函数。

函数语法
CREATE OR REPLACE FUNCTION function_name (parameters-list)
RETURNS return_type
LANGUAGE plpgsql
AS
$$
    DECLARE
    --- variables
    BEGIN
        --- SQL statements (logic)
    END
$$
优势

你可以在一个函数中拥有多个SQL语句，你可以返回任何类型的结果，如一个表或一个单一的值（整数，varchar，日期，时间戳，等等）。

限制

你不能在函数中使用事务。

存储过程

为了克服函数的限制，PostgreSQL提供了存储过程来支持事务。在存储过程中，我们可以启动、提交、回滚事务。然而，存储过程不能像表一样返回一个结果集。它只能返回INOUT参数。

语法

CREATE OR REPLACE PROCEDURE procedure_name (parameters-list)
LANGUAGE plpgsql
AS
$$
DECLARE
--- Variables
BEGIN

    --- SQL statements (logic)

END
$$

创建数据库

我们这里使用 memfiredb.com 提供的免费云数据库，登录之后点击创建数据库，输入相应的信息就可以完成数据库的创建了，非常简单方便：

PostgreSQL的函数和存储过程--MemFireDB

创建完数据库后，下载一个推荐的客户端，参照教程连接数据库。 https://community.memfiredb.com/topic/47/%E5%A6%82%E4%BD%95%E4%B8%8B%E8%BD%BDwindows%E5%AE%A2%E6%88%B7%E7%AB%AF-dbeaver

建表

让我们使用以下SQL脚本在testdb数据库中创建Employees表：

CREATE TABLE Employees
(
    Id serial,
    Name VARCHAR(100),
    DateOfBirth Date,
    City VARCHAR(100),
    Designation VARCHAR(100),
    JoiningDate Date
)

存储过程创建及使用

参数类型

在创建存储过程和函数之前，让我们先看一下参数的类型，有三种类型的参数，我们可以在函数和存储过程中使用。

  • IN
  • OUT
  • INOUT
IN

IN代表一个输入参数。它用于在函数或存储过程中传递数值，如果我们不在参数名称后面使用IN关键字，默认情况下所有的参数都是输入类型。

OUT

OUT代表输出类型的参数。它返回值；你可以把它作为空值传递，也可以不初始化，因为这些类型的参数只用于从函数和存储过程中设置和返回值。

INOUT

INOUT代表输入和输出类型的参数，这些类型的参数可以用来传递值，也可以从一个函数或存储过程中返回值。

创建存储过程

使用下面的脚本来创建一个名为 "AddEmployee "的存储过程。这将在Employees表中插入信息。

CREATE OR REPLACE PROCEDURE AddEmployee
(
    EmpId INOUT INT,
    EmpName VARCHAR(100),
    EmpDob DATE,
    EmpCity VARCHAR(100),
    EmpDesignation VARCHAR(100),
    EmpJoiningDate DATE
)
LANGUAGE plpgsql AS
$$
BEGIN
   INSERT INTO Employees (Name,DateOfBirth,City,Designation,JoiningDate) VALUES
    (EmpName,
     EmpDob,
     EmpCity,
     EmpDesignation,
     EmpJoiningDate
    ) RETURNING Id INTO EmpId;
END
$$;

然后执行一下该存储过程，将信息插入到Employees表中：

CALL AddEmployee(null,'Peter Parker','1997-10-01','New York' ,'Web Developer','2020-11-01');

然后我们执行命令，检查一下是否有数据插入到表中：

SELECT * FROM Employees;

接下来我们将创建一个存储过程来更新Employees记录：

CREATE OR REPLACE PROCEDURE UpdateEmployee
(
    EmpId INT,
    EmpName VARCHAR(100),
    EmpDob DATE,
    EmpCity VARCHAR(100),
    EmpDesignation VARCHAR(100),
    EmpJoiningDate DATE
)
LANGUAGE plpgsql AS
$$
BEGIN
   UPDATE Employees SET
   Name = EmpName,
   DateOfBirth = EmpDob,
   City = EmpCity,
   Designation = EmpDesignation,
   JoiningDate = EmpJoiningDate
   Where Id = EmpId;
END
$$;

我们试着执行一下：

CALL UpdateEmployee(1,'Peter S Parker','1999-10-01','New York' ,'Web Developer','2020-11-01');

我们已经创建了能执行插入和更新的存储过程，现在我们将创建一个存储过程，使我们能够删除Employees中的记录。

CREATE OR REPLACE PROCEDURE DeleteEmployee
(
    EmpId INT
)
LANGUAGE plpgsql AS
$$
BEGIN
    DELETE FROM Employees WHERE Id = EmpId;
END
$$;

执行该存储过程：

CALL DeleteEmployee(2);

会删除id是2的记录。 上面我们演示了如何使用存储过程，接下来我们将演示如何创建和使用函数。

函数创建及使用

先看一个简单的例子：

CREATE OR REPLACE FUNCTION GetAllEmployees()
RETURNS Employees
LANGUAGE SQL
AS
$$
    SELECT * FROM Employees;
$$;

然后我们试着执行下面的语句：

SELECT * FROM GetAllEmployees();

上面的select语句会从GetAllEmployees函数的返回结果中进行查询。 下面是一个可以接受输入参数的例子：

CREATE OR REPLACE FUNCTION GetEmployeeById(EmpId INT)
RETURNS Employees
LANGUAGE SQL
AS
$$
    SELECT * FROM Employees WHERE Id = EmpId;
$$;

执行：

SELECT * FROM GetEmployeeById(3);

再看一个例子，我们正Employees表中有一个出生日期字段，所以让我们创建一个函数来根据出生日期返回年龄。这里用到了一个系统内置的age函数来计算年龄，它接受两个参数：当前日期和出生日期。它通过计算差值来返回年龄。

CREATE OR REPLACE FUNCTION GetEmployeeAge(EmpId INT, Age OUT VARCHAR(100))
LANGUAGE plpgsql
AS
$$
BEGIN
    SELECT AGE(NOW()::Date,DateOfBirth) into Age FROM Employees WHERE Id = EmpId;
END;
$$

上面定义的GetEmployeeAge函数包含两个参数，其中EmpId是int类型的输入参数，Age则是varchar类型的输出参数，所以，该函数的调用方法如下：

SELECT * FROM GetEmployeeAge(1);

总结 当我们需要返回单值结果或表格式结果时，函数是一个很好的选择。如果你想启动一个事务，提交或回滚多个SQL语句，那么存储过程是最佳选择。

Golang
点赞
收藏
评论区
推荐文章
Stella981 Stella981
•
3年前
PostgreSQL函数如何返回数据集
以下主要介绍PostgreSQL函数/存储过程返回数据集，或者也叫结果集的示例。背景：PostgreSQL里面没有存储过程，只有函数，其他数据库里的这两个对象在PG里都叫函数。函数由函数头，体和语言所组成，函数头主要是函数的定义，变量的定义等，函数体主要是函数的实现，函数的语言是指该函数实现的方式，目前内置的有c,plpgsql,sql和in
1193
0
0
Wesley13 Wesley13
•
3年前
MySQL管理与优化(9)：存储过程和函数
存储过程和函数存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。存储过程或函数的相关操作创建，修改存储过程或函数相关语法CREATEDEFINER{user|CURRENT_USER}PROCE
789
0
0
Wesley13 Wesley13
•
3年前
mysql——定义——存储过程和函数——概念
存储过程和函数是指将经常使用的一组SQL语句组合在一起，并将这些SQL语句当作一个整体存储在服务器中一、创建存储过程语法格式：createproceduresp_name(proc_parameter……)ch
608
0
0
Wesley13 Wesley13
•
3年前
mysql存储过程和自定义函数
存储过程简介SQL语句需要先编辑后执行，而存储过程是一组为了完成特定功能的SQL语句集，经编译后存储过程在数据库中，用户通过制定存储过程的名字并传给参数来调用它。存储过程是可编程的函数，在数据库中创建并保持，可以由SQL和控制结构组成。档想要在不同的应用平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的，数据库中的存储过程可以看错
661
0
0
Wesley13 Wesley13
•
3年前
Oracle存储过程创建及Java调用
在大型数据库系统中，有两个很重要作用的功能，那就是存储过程和触发器。在数据库系统中无论是存储过程还是触发器，都是通过SQL语句和控制流程语句的集合来完成的。相对来说，数据库系统中的触发器也是一种存储过程。存储过程在数据库中运算时自动生成各种执行方式，因此，大大提高了对其运行时的执行速度。在大型数据库系统如Oracle、SQLServer中都不仅提供了
618
0
0
Wesley13 Wesley13
•
3年前
Mysql存储过程
SQL语句需要先编译然后执行，而存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时
489
0
0
Wesley13 Wesley13
•
3年前
Oracle存储过程
1、定义所谓存储过程(StoredProcedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作。2、存储过程的创建Oracle存储过程包含三部分
542
0
0
凿壁偷光 凿壁偷光
•
1年前
SQLPro for SQLite for Mac(SQLite编辑器)v2023.35免激活版
SQLProforSQLite是一种流行的数据库管理工具，允许用户管理和操作SQLite数据库。它提供了一个用户友好的界面，可以轻松执行创建、修改和删除表等任务；插入、更新和删除数据；并执行SQL查询。
536
0
0
小万哥 小万哥
•
1年前
SQL（Structured Query Language）简介和常见 SQL 命令示例
简介SQL（StructuredQueryLanguage）是一种用于访问和操作关系型数据库的标准语言。它是一个功能强大的语言，用于执行各种数据库操作，包括检索数据、插入新记录、更新记录、删除记录、创建数据库、创建新表、设置权限以及执行存储过程和视图等。以
303
0
0
天翼云开发者社区 天翼云开发者社区
•
12个月前
如何通过云函数操作云数据库？
随着云计算的普及，云数据库作为一种高效、灵活和可靠的数据存储和管理服务，为企业提供了强大的支持。而云函数作为云计算的重要组成部分，为开发者提供了在云端执行代码的能力。本文将探讨如何通过云函数操作云数据库。
304
0
0
艾木酱
艾木酱
Lv1
千呼万唤始出来，犹抱琵琶半遮面。
文章
22
粉丝
6
获赞
26
热门文章
MemFireDB获华为云“鲲鹏云服务”和“Stack 8.0”兼容性认证
快速入门｜使用MemFire Cloud构建React Native应用程序
超轻量级的VSCode插件，数据库想用就用～
10.24程序员节！你盖楼，我送书～还能抽iPhone13！
以内存为中心的数据库设计考虑