01

2023-04-02 00:53| 来源: 网络整理| 查看: 265

🏆 文章目标：帮助那些了解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 代码块的定义：

[ ] [ 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 $$ 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 代表了相同的对象。

常量

如果在定义变量时指定了 CONSTANT 关键字，意味着定义的是常量。常量的值需要在声明时初始化，并且不能修改。

以下示例通过定义常量 PI 计算圆的面积：

DO $$ DECLARE PI CONSTANT NUMERIC := 3.14159265; radius NUMERIC; BEGIN radius := 1.0; RAISE NOTICE 'The area is %', PI * radius * radius; END $$;

NOTICE: The area is 3.1415926500 常量可以用于避免魔数（magic number），提高代码的可读性；也可以减少代码的维护工作，所有使用常量的代码都会随着常量值的修改而同步，不需要修改多个硬编码的数据值。

控制结构 IF 语句

IF 语句可以基于条件选择性执行操作， PL/pgSQL 提供了三种形式的 IF 语句。

IF … THEN … END IF IF … THEN … ELSE … END IF IF … THEN … ELSIF … THEN … ELSE … END IF 首先，最简单的 IF 语句如下：

IF boolean-expression THEN statements END IF;

如果表达式 boolean-expression 的值为真，执行 THEN 之后的语句；否则，忽略这些语句。例如：

DO $$ BEGIN IF 2 > 3 THEN RAISE NOTICE '2 大于 3'; END IF; IF 2 3 THEN RAISE NOTICE '2 大于 3'; ELSE RAISE NOTICE '2 小于 3'; END IF; END $$; NOTICE: 2 小于 3

第三种 IF 语句支持多个条件分支：

IF boolean-expression THEN statements [ ELSIF boolean-expression THEN statements ] [ ELSIF boolean-expression THEN statements ] ... [ ELSE statements ] END IF;

依次判断条件中的表达式，如果某个条件为真，执行相应的语句；如果所有条件都为假，执行 ELSE 后面的语句；如果没有 ELSE 就什么都不执行。例如：

DO $$ DECLARE i integer := 3; j integer := 3; BEGIN IF i > j THEN RAISE NOTICE 'i 大于 j'; ELSIF i 4,5,6}

以上示例通过 FOREACH 语句遍历了数组的一维切片。

【本文地址】

01

01

今日新闻

推荐新闻