本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

# 在 Amazon Aurora PostgreSQL 中模拟 Oracle PL/SQL 关联数组和适用于 PostgreSQL 的亚马逊 RDS
<a name="emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql"></a>

*Rajkumar Raghuwanshi、Bhanu Ganesh Gudivada 和 Sachin Khanna，Amazon Web Services*

## Summary
<a name="emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql-summary"></a>

[此模式描述了如何在 Amazon Aurora PostgreSQL 和 [Amazon RDS for PostgreSQL 环境中模拟索引位置为](https://aws.amazon.com/rds/aurora/)空的 Oracle PL/SQL 关联数组。](https://aws.amazon.com/rds/postgresql/)它还描述了 Oracle PL/SQL 关联数组和 PostgreSQL 数组之间在迁移期间如何处理空索引位置方面的一些区别。

就在迁移 Oracle 数据库时处理空索引位置方面，我们提供了 PostgreSQL 替代方案，而不是使用 `aws_oracle_ext` 函数。此模式使用附加列来存储索引位置，并且保留了 Oracle 对稀疏数组的处理方式，同时还整合了原生 PostgreSQL 功能。

**Oracle**

在 Oracle 中，集合可以初始化为空，并使用 `EXTEND` 集合方法进行填充，而该方法会将 `NULL` 元素附加到数组中。使用索引的 PL/SQL 关联数组时`PLS_INTEGER`，该`EXTEND`方法按顺序添加`NULL`元素，但也可以在非序列索引位置初始化元素。任何未显式初始化的索引位置均保留为空。

这种灵活性允许稀疏数组结构，其中元素可以填充到任意位置。使用具有 `FIRST` 和 `LAST` 的 `FOR LOOP` 迭代集合时，仅会处理已初始化的元素（无论是 `NULL` 还是具有定义的值），而空位置则会被跳过。

**PostgreSQL（Amazon Aurora 和 Amazon RDS）**

PostgreSQL 处理空值的方式与处理 `NULL` 值的方式有所不同。它将空值存储为不同实体，且仅会占用一个字节存储空间。当数组包含空值时，PostgreSQL 会像处理非空值一样分配顺序索引位置。但是顺序索引需要额外的处理，因为系统必须迭代所有索引位置，包括空位置。因此，相较于稀疏资料集，传统数组创建的效率更低。

**AWS Schema Conversion Tool**

[AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaConversionTool/) 通常使用`aws_oracle_ext`函数处理 Oracle-to-PostgreSQL迁移。在此模式中，我们提出了一种替代方法，该方法使用原生 PostgreSQL 功能，将 PostgreSQL 数组类型与用于存储索引位置的附加列相结合。然后，系统只需使用索引列即可迭代数组。

## 先决条件和限制
<a name="emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql-prereqs"></a>

**先决条件**
+ 活跃 AWS 账户的.
+ 具有管理员权限的 AWS Identity and Access Management (IAM) 用户。
+ 一个可与 Amazon RDS 或 Aurora PostgreSQL 兼容的实例。
+ 对关系数据库有基本的了解。

**限制**
+ 有些 AWS 服务 并非全部可用 AWS 区域。有关区域可用性，请参阅[按区域划分的AWS 服务](https://aws.amazon.com/about-aws/global-infrastructure/regional-product-services/)。有关特定端点，请参阅[服务端点和配额](https://docs.aws.amazon.com/general/latest/gr/aws-service-information.html)页面，然后选择相应服务的链接。

**产品版本**

此模式已使用以下版本进行测试：
+ 亚马逊 Aurora PostgreSQL 13.3
+ Amazon RDS for PostgreSQL 13.3
+ AWS SCT 1.0.674
+ Oracle 12c EE 12.2

## 架构
<a name="emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql-architecture"></a>

**源技术堆栈**
+ 本地 Oracle 数据库

**目标技术堆栈**
+ Amazon Aurora PostgreSQL
+ Amazon RDS for PostgreSQL

**目标架构**

![](http://docs.aws.amazon.com/zh_cn/prescriptive-guidance/latest/patterns/images/pattern-img/a62d038c-ca3c-41e1-aa7e-74282d2e54f4/images/13aacf00-655a-4149-a4e7-42b66dbea4e1.png)


此图显示以下内容：
+ 一个源 Amazon RDS for Oracle 数据库实例
+ 一个 AWS SCT 用于将 Oracle 函数转换为 PostgreSQL 等效函数的 Amazon EC2 实例
+ 一个可与 Amazon Aurora PostgreSQL 兼容的目标数据库

## 工具
<a name="emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql-tools"></a>

**Amazon Web Services**
+ [Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html) 是与 MySQL 和 PostgreSQL 兼容的完全托管式的云端关系数据库引擎。
+ [Amazon Aurora PostgreSQL 兼容版](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html)是一个完全托管的、与 ACID 兼容的关系数据库引擎，可帮助您建立、运行和扩缩 PostgreSQL 部署。
+ [亚马逊弹性计算云 (Amazon EC2)](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/concepts.html) 在中提供可扩展的计算容量 AWS Cloud。您可以根据需要启动任意数量的虚拟服务器，并快速纵向扩展或缩减这些服务器。
+ [Amazon Relational Database Service（Amazon RDS）](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Welcome.html)可帮助您在 AWS Cloud中设置、操作和扩展关系数据库。
+ [Amazon Relational Database Service（Amazon RDS）for Oracle](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html) 可帮助您在 AWS Cloud中设置、操作和扩缩 Oracle 关系数据库。
+ [Amazon Relational Database Service（Amazon RDS）for PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) 可帮助您在 AWS Cloud中设置、操作和扩展 PostgreSQL 关系数据库。
+ [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) 通过自动将源数据库架构和大部分自定义代码转换为与目标数据库兼容的格式来支持异构数据库迁移。

**其他工具**
+ [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) 是一个集成的开发环境，可简化传统部署和基于云的部署中 Oracle 数据库的开发和管理。
+ [pgAdmin](https://www.pgadmin.org/) 是一种适用于 PostgreSQL 的开源管理工具。它提供了一个图形界面，可帮助您创建、维护和使用数据库对象。在此模式下，pgAdmin 会连接到 RDS for PostgreSQL 数据库实例并查询数据。或者，您可使用 psql 命令行客户端。

## 最佳实践
<a name="emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql-best-practices"></a>
+ 测试数据集边界和边缘场景。
+ 考虑对 out-of-bounds索引条件实现错误处理。
+ 优化查询，以避免扫描稀疏数据集。

## 操作说明
<a name="emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql-epics"></a>

### Oracle 关联数组行为（源）
<a name="oracle-associative-array-behavior-source"></a>


| Task | 说明 | 所需技能 | 
| --- | --- | --- | 
| 在 Oracle 中创建源 PL/SQL 块。 | 在 Oracle 中创建使用以下关联数组的源 PL/SQL 块：<pre>DECLARE<br />    TYPE country_codes IS TABLE OF VARCHAR2(100) INDEX BY pls_integer;<br />    cc country_codes;<br />    cc_idx NUMBER := NULL;<br />BEGIN<br />    cc(7) := 'India';<br />    cc(3) := 'UK';<br />    cc(5) := 'USA';<br />    cc(0) := 'China';<br />    cc(-2) := 'Invalid';<br />    dbms_output.put_line('cc_length:' || cc.COUNT);<br />    IF (cc.COUNT > 0) THEN<br />        cc_idx := cc.FIRST;<br />        FOR i IN 1..cc.COUNT LOOP<br />            dbms_output.put_line('cc_idx:' || cc_idx || ' country:' || cc(cc_idx));<br />            cc_idx := cc.next(cc_idx);<br />        END LOOP;<br />    END IF;<br />END;</pre> | 数据库管理员 | 
| 运行方 PL/SQL 块。 | 在 Oracle 中运行源代码 PL/SQL 块。如果关联数组的索引值之间存在间隙，也不会在这些间隙中存储任何数据。这样，Oracle 循环就可以仅迭代索引位置。 | 数据库管理员 | 
| 检查输出。 | 五个元素会以非连续的间隔插入到数组 (`cc`) 中。数组计数如以下输出所示：<pre>cc_length:5<br />cc_idx:-2 country:Invalid<br />cc_idx:0 country:China<br />cc_idx:3 country:UK<br />cc_idx:5 country:USA<br />cc_idx:7 country:India</pre> | 数据库管理员 | 

### PostgreSQL 关联数组行为（目标）
<a name="postgresql-associative-array-behavior-target"></a>


| Task | 说明 | 所需技能 | 
| --- | --- | --- | 
| 在 PostgreSQL 中创建一个目标 PL/pgSQL 区块。 | 在 PostgreSQL 中创建一个使用以下关联数组的目标 PL/pgSQL 块：<pre>DO $$<br />DECLARE<br />    cc character varying(100)[];<br />    cc_idx integer := NULL;<br />BEGIN<br />    cc[7] := 'India';<br />    cc[3] := 'UK';<br />    cc[5] := 'USA';<br />    cc[0] := 'China';<br />    cc[-2] := 'Invalid';<br />    RAISE NOTICE 'cc_length: %', ARRAY_LENGTH(cc, 1);<br />    IF (ARRAY_LENGTH(cc, 1) > 0) THEN<br />        FOR i IN ARRAY_LOWER(cc, 1)..ARRAY_UPPER(cc, 1)<br />        LOOP<br />            RAISE NOTICE 'cc_idx:% country:%', i, cc[i];<br />        END LOOP;<br />    END IF;<br />END;<br />$$;</pre> | 数据库管理员 | 
| 运行方 PL/pgSQL 块。 | 在 PostgreSQL 中运行目标 PL/pgSQL 区块。如果关联数组的索引值之间存在间隙，也不会在这些间隙中存储任何数据。这样，Oracle 循环就可以仅迭代索引位置。 | 数据库管理员 | 
| 检查输出。 | 数组长度大于 5，因为 `NULL` 存储在索引位置之间的空隙中。如以下输出所示，该循环会完成 10 次迭代，以检索数组中的 5 个值。<pre>cc_length:10<br />cc_idx:-2 country:Invalid<br />cc_idx:-1 country:<NULL><br />cc_idx:0 country:China<br />cc_idx:1 country:<NULL><br />cc_idx:2 country:<NULL><br />cc_idx:3 country:UK<br />cc_idx:4 country:<NULL><br />cc_idx:5 country:USA<br />cc_idx:6 country:<NULL><br />cc_idx:7 country:India</pre> | 数据库管理员 | 

### 模拟 Oracle 关联数组行为
<a name="emulate-oracle-associative-array-behavior"></a>


| Task | 说明 | 所需技能 | 
| --- | --- | --- | 
| 使用数组和用户定义类型创建目标 PL/pgSQL 块。 | 为了优化性能并匹配 Oracle 的功能，我们可以创建用户定义的类型，进而存储索引位置及其对应的数据。这种方法可以保持索引和值之间的直接关联，进而减少不必要的迭代。<pre>DO $$<br />DECLARE<br />    cc country_codes[];<br />    cc_append country_codes := NULL;<br />    i record;<br />BEGIN<br />    cc_append.idx = 7;<br />    cc_append.val = 'India';<br />    cc := array_append(cc, cc_append);<br />    cc_append.idx = 3;<br />    cc_append.val = 'UK';<br />    cc := array_append(cc, cc_append);<br />    cc_append.idx = 5;<br />    cc_append.val = 'USA';<br />    cc := array_append(cc, cc_append);<br />    cc_append.idx = 0;<br />    cc_append.val = 'China';<br />    cc := array_append(cc, cc_append);<br />    cc_append.idx = - 2;<br />    cc_append.val = 'Invalid';<br />    cc := array_append(cc, cc_append);<br />    RAISE NOTICE 'cc_length: %', ARRAY_LENGTH(cc, 1);<br />    IF (ARRAY_LENGTH(cc, 1) > 0) THEN<br />        FOR i IN (<br />            SELECT<br />                *<br />            FROM<br />                unnest(cc)<br />            ORDER BY<br />                idx)<br />                LOOP<br />                    RAISE NOTICE 'cc_idx:% country:%', i.idx, i.val;<br />                END LOOP;<br />    END IF;<br />END;<br />$$;</pre> | 数据库管理员 | 
| 运行方 PL/pgSQL 块。 | 运行目标 PL/pgSQL 方块。如果关联数组的索引值之间存在间隙，也不会在这些间隙中存储任何数据。这样，Oracle 循环就可以仅迭代索引位置。 | 数据库管理员 | 
| 检查输出。 | 如以下输出所示，用户定义的类型仅存储已填充的数据元素，这意味着数组长度与值的数量相匹配。因此，`LOOP` 迭代经过优化，仅会处理现有数据，而无需跟踪空位置。<pre>cc_length:5<br />cc_idx:-2 country:Invalid<br />cc_idx:0 country:China<br />cc_idx:3 country:UK<br />cc_idx:5 country:USA<br />cc_idx:7 country:India</pre> | 数据库管理员 | 

## 相关的资源
<a name="emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql-resources"></a>

**AWS 文档**
+ [AWS 数据库博客](https://aws.amazon.com/blogs/database/)
+ [Oracle 至 Aurora PostgreSQL 迁移行动手册](https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.html)

**其他文档**
+ [Oracle 关联数组](https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/associative-arrays.html#GUID-8060F01F-B53B-48D4-9239-7EA8461C2170)
+ [PostgreSQL 数组函数和运算符](https://www.postgresql.org/docs/current/functions-array.html)
+ [PostgreSQL 用户定义的类型](https://www.postgresql.org/docs/current/sql-createtype.html)