PostgreSQL11: 支持存储过程(SQL Stored Procedures)

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介:

PostgreSQL 11 版本一个重量级新特性是对存储过程的支持,同时支持存储过程嵌入事务,存储过程是很多 PostgreSQL 从业者期待已久的特性,尤其是很多从Oracle转到PostgreSQL朋友,尽管PostgreSQL提供函数可以实现大多数存储过程的功能,但在函数中无法执行事务实现部分提交,换句话说,函数中的SQL要么都执行成功,要不全部返回失败。

PostgreSQL 11 版本对存储过程的支持,从兼容层面考虑和Oracle的兼容性进一步增强,本文演示下PostgreSQL存储过程的创建和调用,并且演示存储过程支持嵌入事务样例。
发行说明
SQL stored procedures, with support for embedded transactions
存储过程创建语法
1
2
3
4
5
6
7
8
9
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'

} ...
存储过程调用语法
CALL name ( [ argument ] [, ...] )
存储过程调用比较简单,使用 CALL 命令即可,而函数的调用是使用 SELECT 命令。

存储过程嵌入事务测试
创建一个简单的存储过程演示下。

创建测试表
CREATE TABLE t1 (id int4);
CREATE TABLE t1_log (ctime timestamp(6) without time zone , operation text,ins_values int4);
创建存储过程 ins_t1
1
2
3
4
5
6
CREATE OR REPLACE PROCEDURE ins_t1(a integer) AS

$$ BEGIN INSERT INTO t1(id) VALUES(a); INSERT INTO t1_log(ctime,operation,ins_values) VALUES (clock_timestamp(),'INSERT',a); END $$

LANGUAGE 'plpgsql';

调用存储过程 ins_t1(),如下

1
2
francs=> CALL ins_t1(1);
CALL

查看表 t1 和 t1_log 记录。

1
2
3
4
5
6
7
8
9
10
11
francs=> SELECT * FROM t1;

id

1
(1 row)

francs=> SELECT * FROM t1_log;

       ctime            | operation | ins_values
2018-09-25 15:08:16.026122 INSERT 1

(1 row)

以上看出两条SQL都已提交。

创建存储过程 ins_t1_part
1
2
3
4
5
6
7
8
9
CREATE OR REPLACE PROCEDURE ins_t1_part(a integer) AS

$$ BEGIN INSERT INTO t1(id) VALUES(a); COMMIT; INSERT INTO t1_log(ctime,operation,ins_values) VALUES (clock_timestamp(),'INSERT',a); ROLLBACK; END $$

LANGUAGE 'plpgsql';

调用存储过程 ins_t1_part(),如下

1
2
francs=> CALL ins_t1_part(2);
CALL
查看表 t1 和 t1_log 记录。

1
2
3
4
5
6
7
8
9
10
11
12
francs=> SELECT * FROM t1;

id

1
2
(2 rows)

francs=> SELECT * FROM t1_log;

       ctime            | operation | ins_values
2018-09-25 15:08:16.026122 INSERT 1

(1 row)
发现 t1 表的数据已提交,而 t1_log 表的数据没有提交,验证了存储过程支持嵌入式事务。

参考
CREATE PROCEDURE
TECH PREVIEW: POSTGRESQL 11 – CREATE PROCEDURE
最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
2月前
|
存储 SQL 数据库
SQL Server存储过程的优缺点
【10月更文挑战第18天】SQL Server 存储过程具有提高性能、增强安全性、代码复用和易于维护等优点。它可以减少编译时间和网络传输开销,通过权限控制和参数验证提升安全性,支持代码共享和复用,并且便于维护和版本管理。然而,存储过程也存在可移植性差、开发和调试复杂、版本管理问题、性能调优困难和依赖数据库服务器等缺点。使用时需根据具体需求权衡利弊。
|
2月前
|
存储 SQL 缓存
SQL Server存储过程的优缺点
【10月更文挑战第22天】存储过程具有代码复用性高、性能优化、增强数据安全性、提高可维护性和减少网络流量等优点,但也存在调试困难、移植性差、增加数据库服务器负载和版本控制复杂等缺点。
|
2月前
|
存储 SQL 数据库
Sql Server 存储过程怎么找 存储过程内容
Sql Server 存储过程怎么找 存储过程内容
90 1
|
2月前
|
存储 SQL 数据库
SQL Server存储过程的优缺点
【10月更文挑战第17天】SQL Server 存储过程是预编译的 SQL 语句集,存于数据库中,可重复调用。它能提高性能、增强安全性和可维护性,但也有可移植性差、开发调试复杂及可能影响数据库性能等缺点。使用时需权衡利弊。
|
2月前
|
存储 SQL 数据库
SQL Server 临时存储过程及示例
SQL Server 临时存储过程及示例
59 3
|
2月前
|
存储 SQL 安全
|
2月前
|
存储 SQL 数据库
使用SQL创建视图和存储过程
使用SQL创建视图和存储过程
20 0
|
3月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数(三)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
4月前
|
存储 SQL 安全
【数据库高手的秘密武器:深度解析SQL视图与存储过程的魅力——封装复杂逻辑,实现代码高复用性的终极指南】
【8月更文挑战第31天】本文通过具体代码示例介绍 SQL 视图与存储过程的创建及应用优势。视图作为虚拟表,可简化复杂查询并提升代码可维护性;存储过程则预编译 SQL 语句,支持复杂逻辑与事务处理,增强代码复用性和安全性。通过创建视图 `high_earners` 和存储过程 `get_employee_details` 及 `update_salary` 的实例,展示了二者在实际项目中的强大功能。
44 1
|
4月前
|
SQL 存储 关系型数据库
PostgreSQL核心之SQL基础学习
PostgreSQL核心之SQL基础学习
50 3