将 Oracle 关联数组迁移到 Amazon Aurora PostgreSQL 或 Amazon

• 30
• 2026-01-27 14:05:03

从 Oracle 迁移关联数组到 Amazon Aurora PostgreSQL 或 Amazon RDS for PostgreSQL

关键要点

发布于 2024 年 1 月 8 日，作者：Bikash Chandra Rout Gokul Gunasekaran Neha Sharma 和 Sashikanta Pattanayak

按照 AWS 的步骤迁移 Oracle 数据库到 Amazon Aurora PostgreSQL 兼容版 或 Amazon RDS for PostgreSQL 涉及到自动化与手动程序的结合。AWS Schema Conversion Tool (AWS SCT) 可以自动处理架构转换的任务，而对于无法自动迁移的特定数据库对象，需要手动进行架构迁移的修改。

大多数自定义代码和源数据库的结构可以通过 AWS SCT 自动转换为目标数据库兼容的格式。在从 Oracle 到 PostgreSQL 的数据库迁移过程中，AWS SCT 同样实现了 Oracle PL/SQL 代码到 PostgreSQL 中类似 PL/pgSQL 代码的自动转换。

Oracle 关联数组与 PostgreSQL 数组概述

一个关联数组是一组 keyvalue 对，其中 key 与 value 关联。key 是一个唯一的索引，用于查找关联的值，可为文本或整数类型，能够映射到任何值类型。

关联数组非常适合以下情况： 相对较小的查找表，可在每次调用子程序或初始化声明它的包时构建内存中的查找表。 在数据库服务器之间传递集合。

PostgreSQL 允许您定义列为任何有效数据类型的数组，包括内置类型、用户定义类型、枚举类型或复合类型。此外，PostgreSQL 中的 PL/pgSQL 允许我们将变量声明为 ARRAY 类型。

将 Oracle 关联数组迁移到 PostgreSQL 需要仔细考虑，因为 PostgreSQL 并没有直接等效的结构。尽管如此，PostgreSQL 提供了几种替代数据结构来处理 keyvalue 对，例如数组和 JSONB。通过使用自定义数据类型和数组，我们可以有效地模拟 Oracle 关联数组的功能。本文将提供一个实用的分步示例，帮助您理解并将这些概念应用于迁移项目中。

前提条件

要开始本篇文章中描述的解决方案，您需要具备以下资源： 一个有效的 AWS 账户 一个源 Oracle 数据库本地或 Amazon RDS for Oracle 目标 Amazon Aurora PostgreSQL 兼容版或 Amazon RDS PostgreSQL 具有目标数据库 SELECT 权限的数据库用户

遍历关联数组

在本节中，我们将比较在 Oracle 和 PostgreSQL 中遍历关联数组的不同方法。

Oracle 方法

以下是 Oracle 代码示例，用于遍历关联数组：

sqlDECLARE TYPE varcharassocarraytype IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100) varcharassocarray varcharassocarraytype currentkey VARCHAR2(100)BEGIN 用 keyvalue 对填充关联数组 varcharassocarray(Key 1) = Value 1 varcharassocarray(Key 2) = Value 2 varcharassocarray(Key 3) = Value 3

 循环遍历关联数组DBMSOUTPUTPUTLINE( 开始遍历关联数组 )currentkey = varcharassocarrayFIRSTWHILE currentkey IS NOT NULL LOOP    DBMSOUTPUTPUTLINE(Key   currentkey   Value   varcharassocarray(currentkey))    currentkey = varcharassocarrayNEXT(currentkey)END LOOP

END

输出：Statement processed 开始遍历关联数组 Key Key 1 Value Value 1Key Key 2 Value Value 2Key Key 3 Value Value 3

在这个示例中，我们定义了一个 varcharassocarraytype 作为一个以 VARCHAR2(100) 为索引的关联数组，填充三个示例 keyvalue 对。我们使用 FIRST 方法初始化 currentkey 变量为关联数组中的第一个 key，然后进入 WHILE 循环，只要 currentkey 不是 NULL，就继续循环。在循环内部，我们可以使用 varcharassocarray(currentkey) 获取相应的值，并对 keyvalue 对执行操作。

PostgreSQL 方法使用数组

以下是迁移到 PostgreSQL 的代码用于遍历关联数组。

首先，我们需要创建一个自定义数据类型，以模拟关联数组。我们创建一个复合类型，包括两个属性：indexcol 用于存储键，indexvalue 用于存储相应的值：

sqlCREATE TYPE varcharassocarraytype AS( indexcol character varying(10) indexvalue character varying(15))

接下来，使用自定义数据类型的数组填充 PostgreSQL 中的关联数组：

sqlDODECLARE varcharassocarray varcharassocarraytype[] I RECORDBEGIN 用 keyvalue 对填充关联数组 varcharassocarray = ARRAY[row(Key 1 Value 1) row(Key 2 Value 2) row(Key 3 Value 3)]

 循环遍历关联数组RAISE NOTICE  开始遍历关联数组 FOREACH i IN ARRAY varcharassocarrayLOOP    RAISE NOTICE KEY   VALUE    iindexcol iindexvalueEND LOOP

END

输出：NOTICE 开始遍历关联数组 NOTICE KEY Key 1 VALUE Value 1NOTICE KEY Key 2 VALUE Value 2NOTICE KEY Key 3 VALUE Value 3

在这个示例中，我们创建了一个名为 varcharassocarraytype 的类型，使用额外的成员属性 indexcol 来模拟 Oracle 关联数组的功能。我们使用 arraylength 函数来遍历关联数组。

PostgreSQL 方法使用 JSONB

若要使用 JSONB，我们首先创建一个包装函数。该函数名为 addupdateitemjsonmap，接受三个参数：一个名为 fieldentitymap 的现有 JSONB 对象，一个名为 keyitem 的键，以及一个名为 valueitem 的值。它返回一个修改过的 JSONB 对象，其中包含添加或更新的键值对。以下是代码：

sqlCREATE OR REPLACE FUNCTION addupdateitemjsonmap( fieldentitymap jsonb keyitem text valueitem text)RETURNS jsonbLANGUAGE plpgsqlAS BODYDECLAREBEGIN RETURN jsonbset(fieldentitymap ({ keyitem })text[] ( valueitem )jsonb) ENDBODY

我们可以使用包装函数 addupdateitemjsonmap 来填充关联数组，如下所示：

sqlDO DECLARE fieldentitymap jsonb = {}BEGIN RAISE NOTICE Jsonb processed

 用 keyvalue 对填充关联数组fieldentitymap = addupdateitemjsonmap(fieldentitymap Key 1text Value 1text)jsonbfieldentitymap = addupdateitemjsonmap(fieldentitymap Key 2text Value 2text)jsonbfieldentitymap = addupdateitemjsonmap(fieldentitymap Key 3text Value 3text)jsonb 查看关联数组RAISE NOTICE  关联数组 KeyValue   fieldentitymap

END

输出：NOTICE Jsonb processedNOTICE 关联数组 KeyValue {Key 1 Value 1 Key 2 Value 2 Key 3 Value 3}

在这个示例中，我们通过调用 addupdateitemjsonmap 函数，使用不同的 keyvalue 对填充 fieldentitymap JSONB 对象。该函数在 JSONB 对象中添加或更新每个键值对。

查找关联数组的第一个和最后一个元素，并获取当前大小

在本节中，我们将比较在 Oracle 和 PostgreSQL 中查找第一个和最后一个元素以及获取关联数组当前大小的不同方法。

Oracle 方法

以下是 Oracle 代码，用于查找关联数组中的第一个和最后一个元素：

sqlDECLARE TYPE varcharassocarraytype IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100) varcharassocarray varcharassocarraytype firstkey VARCHAR2(100) lastkey VARCHAR2(100) maxsize NUMBER BEGIN 用 keyvalue 对填充关联数组 varcharassocarray(Key 1) = Value 1 varcharassocarray(Key 2) = Value 2 varcharassocarray(Key 3) = Value 3

 查找关联数组中的第一个和最后一个元素DBMSOUTPUTPUTLINE( 查找关联数组中的第一个和最后一个元素 )firstkey = varcharassocarrayFIRSTlastkey = varcharassocarrayLAST 输出第一个和最后一个 keyvalue 对DBMSOUTPUTPUTLINE(First Key   firstkey   Value   varcharassocarray(firstkey))DBMSOUTPUTPUTLINE(Last Key   lastkey   Value   varcharassocarray(lastkey)) 获取关联数组的当前大小DBMSOUTPUTPUTLINE( 获取关联数组的当前大小 )maxsize = varcharassocarrayCOUNT 输出数组大小DBMSOUTPUTPUTLINE(关联数组的大小   maxsize)

END

输出：Statement processed 查找关联数组中的第一个和最后一个元素 First Key Key 1 Value Value 1Last Key Key 3 Value Value 3 获取关联数组的当前大小 关联数组的大小 3

在这个示例中，我们使用 FIRST 和 LAST 集合方法。FIRST 方法检索第一个键，LAST 方法检索关联数组中的最后一个键。最后，我们输出与第一个和最后一个键关联的 keyvalue 对。同时，我们使用 COUNT 方法查找关联数组的最大大小。

PostgreSQL 方法使用数组

以下是用于查找关联数组中第一个和最后一个元素的 PostgreSQL 代码：

sqlDODECLARE varcharassocarray varcharassocarraytype[] firstkey integer lastkey integerBEGIN 用 keyvalue 对填充关联数组 varcharassocarray = ARRAY[row(Key 1Value 1) row(Key 2Value 2) row(Key 3Value 3)]

 查找关联数组中的第一个和最后一个元素RAISE NOTICE  查找关联数组中的第一个和最后一个元素 firstkey = coalesce(arraylower(varcharassocarray 1) 0)lastkey  = coalesce(arrayupper(varcharassocarray 1) 0) 输出第一个和最后一个 keyvalue 对RAISE NOTICE First Key   Value   varcharassocarray[firstkey]indexcol varcharassocarray[firstkey]indexvalueRAISE NOTICE Last Key   Value   varcharassocarray[lastkey]indexcol varcharassocarray[lastkey]indexvalue

END

输出：NOTICE 查找关联数组中的第一个和最后一个元素 NOTICE First Key Key 1 Value Value 1NOTICE Last Key Key 3 Value Value 3

在 PostgreSQL 中，我们使用 arraylower 和 arrayupper 方法。arraylower 方法返回数组的下界 (索引)，而 arrayupper 方法返回数组的上界 (索引)。由于 PostgreSQL 数组是从 1 开始的不同于 Oracle 的从 0 开始，我们用 coalesce 函数处理空数组，设置默认值为 0。

PostgreSQL 方法使用 JSONB

要使用 JSONB，我们需要之前创建的包装函数 addupdateitemjsonmap。接着，我们创建另一个包装函数 getitemjsonmap，该函数从 JSONB 对象中检索指定键的值。它接受两个参数：JSONB 对象 (fieldentitymap) 和键(keyitem)。返回值为指定键对应的值，类型为文本。以下是代码：

sqlCREATE OR REPLACE FUNCTION getitemjsonmap( fieldentitymap jsonb keyitem text)RETURNS textLANGUAGE plpgsqlAS BODYDECLAREBEGIN RETURN fieldentitymapgtgt(keyitem) ENDBODY

以下是主要逻辑的代码实现：

sqlDO DECLARE fieldentitymap jsonb = {} keys text[] firstkey text lastkey text firstvalue text lastvalue textBEGIN 用 keyvalue 对填充 JSONB 对象 fieldentitymap = addupdateitemjsonmap(fieldentitymap Key 1text Value 1text)jsonb fieldentitymap = addupdateitemjsonmap(fieldentitymap Key 2text Value 2text)jsonb fieldentitymap = addupdateitemjsonmap(fieldentitymap Key 3text Value 3text)jsonb

 遍历键，并填充键数组FOR keyvalue IN SELECT jsonbobjectkeys(fieldentitymap)LOOP    keys = keys  keyvalueEND LOOP 获取第一个和最后一个键firstkey = keys[1]lastkey = keys[arrayupper(keys 1)] 获取第一个和最后一个键对应的值firstvalue = getitemjsonmap(fieldentitymap firstkey)lastvalue = getitemjsonmap(fieldentitymap lastkey) 输出结果RAISE NOTICE  查找关联数组中的第一个和最后一个元素 RAISE NOTICE First Key  Value  firstkey firstvalueRAISE NOTICE Last Key  Value  lastkey lastvalue

END

在这个示例中，我们通过使用 keys[1] 从已排序的键数组中获取第一个键，使用 keys[arrayupper(keys 1)] 获取最后一个键。getitemjsonmap 函数检索与第一个和最后一个键关联的值。

查找索引是否存在于关联数组中并更新其索引值

在本节中，我们将比较在 Oracle 和 PostgreSQL 中查找索引是否存在于关联数组中并更新其索引值的方法。

Oracle 方法

以下是 Oracle 代码，用于检查是否特定键存在于数组中，并在存在时更新该键对应的值：

sqlDECLARE TYPE varcharassocarraytype IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100) varcharassocarray varcharassocarraytypeBEGIN 用 keyvalue 对填充关联数组 varcharassocarray(Key 1) = Value 1 varcharassocarray(Key 2) = Value 2 varcharassocarray(Key 3) = Value 3

 检查特定键是否存在于关联数组中并仅在存在时更新 IF varcharassocarrayEXISTS(Key 2) THEN    DBMSOUTPUTPUTLINE( Key 2 存在，Value    varcharassocarray(Key 2))    DBMSOUTPUTPUTLINE( 更新数组中一个特定元素 )    varcharassocarray(Key 2) = New Value 2    DBMSOUTPUTPUTLINE( Key 2 更新后的值    varcharassocarray(Key 2))ELSE    DBMSOUTPUTPUTLINE(Key 2 不存在于关联数组中。)END IF

END

输出：Statement processed Key 2 存在，Value Value 2 更新数组中一个特定元素 Key 2 更新后的值 New Value 2

在这个示例中，我们使用关联数组的 EXISTS 方法检查 ‘Key 2’ 是否存在于数组中。如果存在，我们进入IF块，并将 ‘Key 2’ 的值更新为 ‘New Value 2’。

PostgreSQL 方法使用数组

以下是用于实现相同目标的 PostgreSQL 代码：

sqlDODECLARE varcharassocarray varcharassocarraytype[] vpos intBEGIN 用 keyvalue 对填充关联数组 varcharassocarray = ARRAY[row(Key 1Value