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

关键要点

1. 迁移 Oracle 数据库到 Amazon Aurora PostgreSQL 或 Amazon RDS for PostgreSQL 需要自动化和手动程序的结合。
2. AWS Schema Conversion Tool (AWS SCT) 可处理模式转换的自动化任务。
3. PostgreSQL 与 Oracle 的关联数组实现有所不同，需寻求替代数据结构。
4. 可使用 PostgreSQL 的数组和 JSONB 来有效管理 key-value 对。

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

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

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

Oracle 关联数组与 PostgreSQL 数组概述

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

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

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

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

前提条件

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

遍历关联数组

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

Oracle 方法

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

'Value 1'; varchar_assoc_array('Key 2') := 'Value 2'; varchar_assoc_array('Key3') := 'Value 3';

    
    
    -- 循环遍历关联数组
    DBMS_OUTPUT.PUT_LINE('*** 开始遍历关联数组 ***');
    current_key := varchar_assoc_array.FIRST;
    
    WHILE current_key IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('Key: ' || current_key || ', Value: ' || varchar_assoc_array(current_key));
        current_key := varchar_assoc_array.NEXT(current_key);
    END LOOP;
    

END; ```

**输出：** `Statement processed. *** 开始遍历关联数组 *** Key: Key 1, Value: Value 1 Key:
Key 2, Value: Value 2 Key: Key 3, Value: Value 3`

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

### PostgreSQL 方法使用数组

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

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

`sql CREATE TYPE varchar_assoc_array_type AS ( index_col charactervarying(10), index_value character varying(15) );`

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

```sql DO $$ DECLARE varchar_assoc_array varchar_assoc_array_type[]; I RECORD;
BEGIN \-- 用 key-value 对填充关联数组 varchar_assoc_array := ARRAY[row('Key 1', 'Value1'), row('Key 2', 'Value 2'), row('Key 3', 'Value 3')];

    
    
    -- 循环遍历关联数组
    RAISE NOTICE '*** 开始遍历关联数组 ***';
    
    FOREACH i IN ARRAY varchar_assoc_array
    LOOP
        RAISE NOTICE 'KEY : % VALUE : % ', i.index_col, i.index_value;
    END LOOP;
    

END; $$; ```

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

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

### PostgreSQL 方法使用 JSONB

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

`sql CREATE OR REPLACE FUNCTION add_update_item_json_map ( field_entity_mapjsonb, key_item text, value_item text ) RETURNS jsonb LANGUAGE 'plpgsql' AS
$BODY$ DECLARE BEGIN RETURN jsonb_set(field_entity_map, ('{' || key_item ||
'}')::text[], ('"' || value_item || '"')::jsonb); END; $BODY$;`

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

```sql DO $$ DECLARE field_entity_map jsonb := '{}'; BEGIN RAISE NOTICE 'Jsonbprocessed.';

    
    
    -- 用 key-value 对填充关联数组
    field_entity_map = add_update_item_json_map(field_entity_map, 'Key 1'::text, 'Value 1'::text)::jsonb;
    field_entity_map = add_update_item_json_map(field_entity_map, 'Key 2'::text, 'Value 2'::text)::jsonb;
    field_entity_map = add_update_item_json_map(field_entity_map, 'Key 3'::text, 'Value 3'::text)::jsonb;
    
    -- 查看关联数组
    RAISE NOTICE '*** 关联数组 Key:Value *** %', field_entity_map;
    

END; $$; ```

**输出：** `NOTICE: Jsonb processed. NOTICE: *** 关联数组 Key:Value *** {"Key 1":
"Value 1", "Key 2": "Value 2", "Key 3": "Value 3"}`

在这个示例中，我们通过调用 _**add_update_item_json_map**_ 函数，使用不同的 key-value 对填充
_**field_entity_map**_ JSONB 对象。该函数在 JSONB 对象中添加或更新每个键值对。

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

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

### Oracle 方法

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

```sql DECLARE TYPE varchar_assoc_array_type IS TABLE OF VARCHAR2(100) INDEXBY VARCHAR2(100); varchar_assoc_array varchar_assoc_array_type; first_keyVARCHAR2(100); last_key VARCHAR2(100); max_size NUMBER; BEGIN \-- 用 key-value对填充关联数组 varchar_assoc_array('Key 1') := 'Value 1'; varchar_assoc_array('Key2') := 'Value 2'; varchar_assoc_array('Key 3') := 'Value 3';

    
    
    -- 查找关联数组中的第一个和最后一个元素
    DBMS_OUTPUT.PUT_LINE('*** 查找关联数组中的第一个和最后一个元素 ***');
    
    first_key := varchar_assoc_array.FIRST;
    last_key := varchar_assoc_array.LAST;
    
    -- 输出第一个和最后一个 key-value 对
    DBMS_OUTPUT.PUT_LINE('First Key: ' || first_key || ', Value: ' || varchar_assoc_array(first_key));
    DBMS_OUTPUT.PUT_LINE('Last Key: ' || last_key || ', Value: ' || varchar_assoc_array(last_key));
    
    -- 获取关联数组的当前大小
    DBMS_OUTPUT.PUT_LINE('*** 获取关联数组的当前大小 ***');
    max_size := varchar_assoc_array.COUNT;
    
    -- 输出数组大小
    DBMS_OUTPUT.PUT_LINE('关联数组的大小: ' || max_size);
    

END; ```

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

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

### PostgreSQL 方法使用数组

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

```sql DO $$ DECLARE varchar_assoc_array varchar_assoc_array_type[]; first_keyinteger; last_key integer; BEGIN \-- 用 key-value 对填充关联数组 varchar_assoc_array
:= ARRAY[row('Key 1','Value 1'), row('Key 2','Value 2'), row('Key 3','Value3')];

    
    
    -- 查找关联数组中的第一个和最后一个元素
    RAISE NOTICE '*** 查找关联数组中的第一个和最后一个元素 ***';
    
    first_key := coalesce(array_lower(varchar_assoc_array, 1), 0);
    last_key  := coalesce(array_upper(varchar_assoc_array, 1), 0);
    
    -- 输出第一个和最后一个 key-value 对
    RAISE NOTICE 'First Key: % , Value: % ', varchar_assoc_array[first_key].index_col, varchar_assoc_array[first_key].index_value;
    RAISE NOTICE 'Last Key: % , Value: % ', varchar_assoc_array[last_key].index_col, varchar_assoc_array[last_key].index_value;
    

END; $$; ```

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

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

### PostgreSQL 方法使用 JSONB

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

`sql CREATE OR REPLACE FUNCTION get_item_json_map( field_entity_map jsonb,
key_item text ) RETURNS text LANGUAGE 'plpgsql' AS $BODY$ DECLARE BEGIN RETURNfield_entity_map->>(key_item); END; $BODY$;`

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

```sql DO $$ DECLARE field_entity_map jsonb := '{}'; keys text[]; first_keytext; last_key text; first_value text; last_value text; BEGIN \-- 用 key-value对填充 JSONB 对象 field_entity_map = add_update_item_json_map(field_entity_map,
'Key 1'::text, 'Value 1'::text)::jsonb; field_entity_map =
add_update_item_json_map(field_entity_map, 'Key 2'::text, 'Value2'::text)::jsonb; field_entity_map =
add_update_item_json_map(field_entity_map, 'Key 3'::text, 'Value3'::text)::jsonb;

    
    
    -- 遍历键，并填充键数组
    FOR key_value IN SELECT jsonb_object_keys(field_entity_map)
    LOOP
        keys := keys || key_value;
    END LOOP;
    
    -- 获取第一个和最后一个键
    first_key := keys[1];
    last_key := keys[array_upper(keys, 1)];
    
    -- 获取第一个和最后一个键对应的值
    first_value := get_item_json_map(field_entity_map, first_key);
    last_value := get_item_json_map(field_entity_map, last_key);
    
    -- 输出结果
    RAISE NOTICE '*** 查找关联数组中的第一个和最后一个元素 ***';
    RAISE NOTICE 'First Key: %, Value: %', first_key, first_value;
    RAISE NOTICE 'Last Key: %, Value: %', last_key, last_value;
    

END; $$; ```

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

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

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

### Oracle 方法

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

```sql DECLARE TYPE varchar_assoc_array_type IS TABLE OF VARCHAR2(100) INDEXBY VARCHAR2(100); varchar_assoc_array varchar_assoc_array_type; BEGIN \-- 用
key-value 对填充关联数组 varchar_assoc_array('Key 1') := 'Value 1';
varchar_assoc_array('Key 2') := 'Value 2'; varchar_assoc_array('Key 3') :=
'Value 3';

    
    
    -- 检查特定键是否存在于关联数组中并仅在存在时更新 
    IF varchar_assoc_array.EXISTS('Key 2') THEN
        DBMS_OUTPUT.PUT_LINE('*** Key 2 存在，Value : ' || varchar_assoc_array('Key 2'));
        DBMS_OUTPUT.PUT_LINE('*** 更新数组中一个特定元素 ***');
        varchar_assoc_array('Key 2') := 'New Value 2';
        DBMS_OUTPUT.PUT_LINE('*** Key 2 更新后的值 : ' || varchar_assoc_array('Key 2'));
    ELSE
        DBMS_OUTPUT.PUT_LINE('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 代码：

```sql DO $$ DECLARE varchar_assoc_array varchar_assoc_array_type[]; v_posint; BEGIN \-- 用 key-value 对填充关联数组 varchar_assoc_array := ARRAY[row('Key1','Value