探索PostgreSQL的函数和存储过程

发表时间: 2021-09-26 17:24

简介

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

函数

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

函数语法

CREATE OR REPLACE FUNCTION function_name (parameters-list)RETURNS return_typeLANGUAGE plpgsqlAS$$    DECLARE    --- variables    BEGIN        --- SQL statements (logic)    END$$

优势

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

限制

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

存储过程

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

语法

CREATE OR REPLACE PROCEDURE procedure_name (parameters-list)LANGUAGE plpgsqlAS$$DECLARE--- VariablesBEGIN    --- SQL statements (logic)END$$

创建数据库

我们这里使用 memfiredb.com 登录之后点击创建数据库，输入相应的信息就可以完成数据库的创建了：

创建完数据库后，下载一个推荐的客户端，参照教程连接数据库。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 EmployeesLANGUAGE SQLAS$$    SELECT * FROM Employees;$$;

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

SELECT * FROM GetAllEmployees();

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

CREATE OR REPLACE FUNCTION GetEmployeeById(EmpId INT)RETURNS EmployeesLANGUAGE SQLAS$$    SELECT * FROM Employees WHERE Id = EmpId;$$;

执行：

SELECT * FROM GetEmployeeById(3);

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

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

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

SELECT * FROM GetEmployeeAge(1);

总结

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