01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)(上)

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介: 01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)

🏆 文章目标:帮助那些了解Oracle PL/SQL,但是不熟悉PL/pgSQL 的人,用于快速整改或者上手。

🍀 01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)

✅ 创作者:Jay…

🎉 个人主页:Jay的个人主页

🍁 展望:若本篇点赞人数较多,将讲解Oracle PL/SQL 的使用方式,常见问题解答,以及一些编写规范。

概述

在 PostgreSQL 中,除了标准 SQL 语句之外,通过创建复杂的过程和函数来满足程序需要,我们称为存储过程(Stored Procedure)和自定义函数(User-Defined Function)。它有助于您执行通常在数据库中的单个函数中进行多次查询和往返操作的操作。

PL/pgSQL 简单易学,无论是否具有编程基础都能够很快学会。PL/pgSQL 存储过程,它和 Oracle PL/SQL 非常类似,是 PostgreSQL默认支持的存储过程,下面针对优缺点给大家做了简要分析:

优点

  • 减少应用和数据库之间的网络传输。所有的 SQL 语句都存储在数据库服务器中,应用程序只需要发送函数调用并获取除了结果,避免了发送多个 SQL 语句并等待结果。
  • 提高应用的性能。因为自定义函数和存储过程进行了预编译并存储在数据库服务器中。
  • 可重用性。存储过程和函数的功能可以被多个应用同时使用。
  • 作为脚本使用,如产品的liquibase 中, 清理或修复数据将非常好用。

缺点

  • 导致软件开发缓慢。因为存储过程需要单独学习,而且很多开发人员并不具备这种技能。
  • 不易进行版本管理和代码调试。
  • 不同数据库管理系统之间无法移植,语法存在较大的差异。

PL/pgSQL 代码块结构

结构

PL/pgSQL 是一种块状语言,因此存储过程和函数以代码块的形式进行组织。以下是一个 PL/pgSQL 代码块的定义:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements;
    ...
END [ label ];

说明

  • label 是一个可选的代码块标签,可以用于 EXIT 语句退出指定的代码块,或者限定变量的名称;
  • DECLARE 是一个可选的声明部分,用于定义变量;
  • BEGIN 和 END 之间是代码主体,也就是主要的功能代码;所有的语句都使用分号(;)结束,END 之后的分号表示代码块结束。

案例

DO $$ 
DECLARE
  name text;
BEGIN 
  name := 'PL/pgSQL';
  RAISE NOTICE 'Hello %!', name;
END $$;

以上是一个匿名块,与此相对的是命名块(也就是存储过程和函数)。其中,DO 语句用于执行匿名块;我们定义了一个字符串变量 name,然后给它赋值并输出一个信息;RAISE NOTICE 用于输出通知消息。

$$ 用于替换单引号('),因为 PL/pgSQL 代码主体必须是字符串文本,意味着代码中所有的单引号都必须转义(重复写两次)。对于上面的示例,需要写成以下形式.

DO
'DECLARE
  name text;
BEGIN 
  name := ''PL/pgSQL'';
  RAISE NOTICE ''Hello %!'', name;
END ';

显然这种写法很不方便,因此 PL/pgSQL 提供了 $$ 避免单引号问题。我们经常还会遇到其他形式的符号,例如 $function$ 或者 $procedure$,作用也是一样。

运行结果如下:

postgres=# DO $$ 
postgres$# DECLARE
postgres$#   name text;
postgres$# BEGIN 
postgres$#   name := 'PL/pgSQL';
postgres$#   RAISE NOTICE 'Hello %!', name;
postgres$# END $$;
NOTICE:  Hello PL/pgSQL!

嵌套子块

PL/pgSQL 支持代码块的嵌套,也就是将一个代码块嵌入其他代码块的主体中。被嵌套的代码块被称为子块(subblock),包含子块的代码块被称为外部块(subblock )。子块可以将代码进行逻辑上的拆分,子块中可以定义与外部块重名的变量,而且在子块内拥有更高的优先级。例如:

DO $$ 
<<outer_block>>
DECLARE
  name text;
BEGIN 
  name := 'outer_block';
  RAISE NOTICE 'This is %', name;
  DECLARE 
     name text := 'sub_block';
  BEGIN 
     RAISE NOTICE 'This is %', name;
     RAISE NOTICE 'The name from the outer block is %', outer_block.name;
  END;
  RAISE NOTICE 'This is %', name;
END outer_block $$;

首先,外部块中定义了一个变量 name,值为“outer_block”,输出该变量的值;然后在子块中定义了同名的变量,值为“sub_block”,输出该变量的值,并且通过代码块标签输出了外部块的变量值;最后再次输出该变量的值。以上代码执行的输出结果如下:

NOTICE:  This is outer_block
NOTICE:  This is sub_block
NOTICE:  The name from the outer block is outer_block
NOTICE:  This is outer_block

声明与赋值

与其他编程语言类似,PL/pgSQL 支持定义变量和常量。

变量

变量是一个有意义的名字,代表了内存中的某个位置。变量总是属于某个数据类型,变量的值可以在运行时被修改。

在使用变量之前,需要在代码的声明部分进行声明:

variable_name data_type [ NOT NULL ] [ { DEFAULT | := | = } expression ];

其中,variable_name 是变量的名称,通常需要指定一个有意义的名称;data_type 是变量的类型,可以是任何 SQL 数据类型;如果指定了 NOT NULL,必须使用后面的表达式为变量指定初始值。

以下是一些变量声明的示例:

user_id integer;
quantity numeric(5) DEFAULT 0;
url varchar := 'http://mysite.com';

除了基本的 SQL 数据类型之外,PL/pgSQL 还支持基于表的字段或行或者其他变量定义变量:

myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
amount quantity%TYPE;

myrow 是一个行类型的变量,可以存储查询语句返回的数据行(数据行的结构要和 tablename 相同);myfield 的数据类型取决于 tablename.columnname 字段的定义;amount 和 quantity 的类型一致。

与行类型变量类似的还有记录类型变量,例如:

arow RECORD;

记录类型的变量没有预定义的结构,只有当变量被赋值时才确定,而且可以在运行时被改变。记录类型的变量可以用于任意查询语句或者 FOR 循环变量。

除此之外,PL/pgSQL 还可以使用 ALIAS 定义一个变量别名:

newname ALIAS FOR oldname;

此时,newname 和 oldname 代表了相同的对象。

关注公众号:熊猫Jay字节之旅,了解更多 AI 技巧 ~

01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)(下)+https://developer.aliyun.com/article/1492280

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
2月前
|
SQL 运维 关系型数据库
课程导航 | 学姐领航,共学PolarDB-X:从入门到精通实操课
本课程旨在教授核心技术和实际操作,学习安装部署、架构原理、SQL开发及最佳实践。本文将提供讲师介绍、学习群和扩展资料,适合各层次学习者,助你成为数据大师,入群方式和更多资料可在文章中获取。
课程导航 | 学姐领航,共学PolarDB-X:从入门到精通实操课
|
2月前
|
存储 SQL 人工智能
01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)(下)
01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)
|
2月前
|
Docker 容器 关系型数据库
【PolarDB-X从入门到精通】 第四讲:PolarDB分布式版安装部署(源码编译部署)
本期课程将于4月11日19:00开始直播,内容包括源码编译基础知识和实践操作,课程目标是使学员掌握源码编译部署技能,为未来发展奠定基础,期待大家在课程中取得丰富的学习成果!
【PolarDB-X从入门到精通】 第四讲:PolarDB分布式版安装部署(源码编译部署)
|
2月前
|
关系型数据库 MySQL 分布式数据库
【PolarDB-X从入门到精通】 第五讲:PolarDB集中式版安装部署(源码编译部署)
4月18日本周四晚19:00,一站式学习源码编译PolarDB-X标准版(集中式),各位敬请期待,我们不见不散!
【PolarDB-X从入门到精通】 第五讲:PolarDB集中式版安装部署(源码编译部署)
|
1月前
|
存储 关系型数据库 MySQL
Mysql存储过程查询结果赋值到变量
Mysql存储过程查询结果赋值到变量
37 0
|
2月前
|
关系型数据库 数据库 PostgreSQL
|
2月前
|
SQL 人工智能 Oracle
PostgreSQL 递归查询(含层级和结构)
PostgreSQL 递归查询(含层级和结构)
|
2月前
|
Cloud Native 分布式数据库 数据库
【PolarDB-X 从入门到精通】课程随堂互动获奖公告
【PolarDB-X 从入门到精通】课程随堂互动获奖名单出炉!快看看你是否在榜?
【PolarDB-X 从入门到精通】课程随堂互动获奖公告
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
328 0
|
存储 缓存 关系型数据库