前言:
如今同学们对“简述sql中存储过程和函数的区别”大约比较注意,你们都想要分析一些“简述sql中存储过程和函数的区别”的相关知识。那么小编同时在网上网罗了一些有关“简述sql中存储过程和函数的区别””的相关内容,希望朋友们能喜欢,姐妹们快快来学习一下吧!简介
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 登录之后点击创建数据库,输入相应的信息就可以完成数据库的创建了:
创建完数据库后,下载一个推荐的客户端,参照教程连接数据库。
建表
让我们使用以下SQL脚本在testdb数据库中创建Employees表:
CREATE TABLE Employees( Id serial, Name VARCHAR(100), DateOfBirth Date, City VARCHAR(100), Designation VARCHAR(100), JoiningDate Date)存储过程创建及使用参数类型
在创建存储过程和函数之前,让我们先看一下参数的类型,有三种类型的参数,我们可以在函数和存储过程中使用。
INOUTINOUTIN
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语句,那么存储过程是最佳选择。
标签: #简述sql中存储过程和函数的区别