本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

# 在 Amazon Aurora PostgreSQL 和 Amazon RDS for PostgreSQL 中模擬 Oracle PL/SQL 關聯陣列
<a name="emulate-oracle-plsql-associative-arrays-in-aurora-and-rds-postgresql"></a>

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

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

此模式說明如何模擬 [Amazon Aurora PostgreSQL ](https://aws.amazon.com/rds/aurora/)和 [Amazon RDS for PostgreSQL ](https://aws.amazon.com/rds/postgresql/)環境中具有空索引位置的 Oracle PL/SQL 關聯陣列。它還描述了 Oracle PL/SQL 關聯陣列和 PostgreSQL 陣列在遷移期間如何處理空索引位置的一些差異。

我們提供 PostgreSQL 替代方案，除了在遷移 Oracle 資料庫時使用 `aws_oracle_ext`函數來處理空的索引位置。此模式使用額外的資料欄來存放索引位置，並維護 Oracle 處理稀疏陣列，同時整合原生 PostgreSQL 功能。

**Oracle**

在 Oracle 中，集合可以初始化為空白，並使用`EXTEND`集合方法填入，這會將`NULL`元素附加到陣列。使用由 編製索引的 PL/SQL 關聯陣列時`PLS_INTEGER`，`EXTEND`方法會依序新增`NULL`元素，但元素也可以在非連續索引位置初始化。任何未明確初始化的索引位置都會保持空白。

此彈性允許稀疏陣列結構，其中元素可以填入任意位置。使用`FOR LOOP`具有 `FIRST`和 `LAST` 邊界的 反覆查看集合時，只會處理初始化的元素 （無論 `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)頁面，然後選擇服務的連結。

**產品版本**

此模式已使用下列版本進行測試：
+ Amazon Aurora PostgreSQL 13.3
+ Amazon RDS for PostgreSQL 13.3
+ AWS SCT 1.0.674
+ Oracle 12c EE 12.2

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

**來源技術堆疊**
+ 內部部署 Oracle 資料庫

**目標技術堆疊**
+ Amazon Aurora PostgreSQL
+ Amazon RDS for PostgreSQL

**目標架構**

![\[alt text not found\]](http://docs.aws.amazon.com/zh_tw/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>

**AWS 服務**
+ [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 Elastic Compute Cloud (Amazon EC2)](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/concepts.html) 在 AWS 雲端中提供可擴展的運算容量。您可以視需要啟動任意數量的虛擬伺服器，，並快速進行擴展或縮減。
+ [Amazon Relational Database Service (Amazon RDS)](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Welcome.html) 可協助您在 中設定、操作和擴展關聯式資料庫 AWS 雲端。
+ [Amazon Relational Database Service (Amazon RDS) for Oracle](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html) 可協助您在 中設定、操作和擴展 Oracle 關聯式資料庫 AWS 雲端。
+ [適用於 PostgreSQL 的 Amazon Relational Database Service (Amazon RDS)](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) 可協助您在 中設定、操作和擴展 PostgreSQL 關聯式資料庫 AWS 雲端。
+ [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>


| 任務 | Description | 所需的技能 | 
| --- | --- | --- | 
| 在 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> | DBA | 
| 執行 PL/SQL 區塊。 | 在 Oracle 中執行來源 PL/SQL 區塊。如果關聯陣列的索引值之間存在間隙，則不會在這些間隙中存放任何資料。這可讓 Oracle 迴圈僅逐一查看索引位置。 | DBA | 
| 檢閱輸出。 | 以非連續間隔將五個元素插入陣列 (`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> | DBA | 

### PostgreSQL 關聯陣列行為 （目標）
<a name="postgresql-associative-array-behavior-target"></a>


| 任務 | Description | 所需的技能 | 
| --- | --- | --- | 
| 在 PostgreSQL 中建立目標 PL/pgSQL 區塊。 PostgreSQL | 在 PostgreSQL 中建立使用以下關聯陣列的目標 PL/pgSQL 區塊： PostgreSQL <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> | DBA | 
| 執行 PL/pgSQL 區塊。 | 在 PostgreSQL 中執行目標 PL/pgSQL 區塊。 PostgreSQL 如果關聯陣列的索引值之間存在間隙，則不會在這些間隙中存放任何資料。這可讓 Oracle 迴圈僅逐一查看索引位置。 | DBA | 
| 檢閱輸出。 | 陣列長度大於 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> | DBA | 

### 模擬 Oracle 關聯陣列行為
<a name="emulate-oracle-associative-array-behavior"></a>


| 任務 | Description | 所需的技能 | 
| --- | --- | --- | 
| 使用陣列和使用者定義的類型建立目標 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> | DBA | 
| 執行 PL/pgSQL 區塊。 | 執行目標 PL/pgSQL 區塊。如果關聯陣列的索引值之間存在間隙，則不會在這些間隙中存放任何資料。這可讓 Oracle 迴圈僅逐一查看索引位置。 | DBA | 
| 檢閱輸出。 | 如下列輸出所示，使用者定義的類型只會存放填入的資料元素，這表示陣列長度符合值的數量。因此，`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> | DBA | 

## 相關資源
<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)