项目展示

项目展示

  • 首页
  • 项目展示
  • 将 Oracle 集合迁移到 Amazon Aurora PostgreSQL 兼容版或 Amazo

将 Oracle 集合迁移到 Amazon Aurora PostgreSQL 兼容版或 Amazo

从 Oracle 集合迁移到 Amazon Aurora PostgreSQL 兼容版或 Amazon RDS for PostgreSQL

作者 Sashikanta Pattanayak Anuradha Chintha 和 Vinay Paladi 文章发布时间 2024年2月8日标签 高级(300) Amazon Aurora Amazon RDS PostgreSQL 兼容 RDS for PostgreSQL 技术如何永久链接

文章要点

迁移 Oracle 到 PostgreSQL 需要多个步骤，尤其是在集合方面。PostgreSQL 提供原生支持，可以使用数组来实现 Oracle 的集合。本文将介绍从 Oracle 到 PostgreSQL 迁移简单集合、复合集合和多维集合的方法及其最佳实践。

迁移 Oracle 集合到 PostgreSQL 需要注意以下几点：Oracle 集合是在 Oracle 数据库中常用的一项功能，而 PostgreSQL 并不直接支持这些集合。迁移过程中需要做出多项决策以确保顺利转型。

Oracle 数据库开发人员在多个场景中使用集合，包括：

缓存静态值以减少数据库调用，从而提升速度和效率。在块或子程序中重复访问数据，通常涉及条件修改和储存以便在同一事务中未来调用。将集合作为函数或过程的参数或返回类型，传递多个值给其他调用的子程序。向应用程序传递值，应用程序层会定义一个列表、数组或地图来处理这些值。

在本文中，我们将探讨 Oracle 的简单集合、复合集合和多维集合，并介绍如何使用 PostgreSQL 的原生自定义类型和数组将这些 Oracle 特性迁移到 Amazon Aurora PostgreSQL 兼容版 或 Amazon RDS for PostgreSQL，同时遵循推荐的最佳实践。

PostgreSQL 中表示 Oracle 集合的概述

PostgreSQL 数据库提供的数组可以作为端口 Oracle 集合的选项。每个数组元素的类型可以与集合的类型相同。可以为任何内置或用户定义的数据类型创建数组。

在 PostgreSQL 中声明数组变量时，使用 []数组操作符。以下是如何在 PostgreSQL 中声明和使用数组：

简单数组创建：可以使用任何预定义数据类型的 [] 操作符在 PostgreSQL 中创建简单数组。例如，可以创建一个 varchar(20) 的数组并使用以下 SQL 语句赋值：

sql Arr1 varchar(20)[] Arr1[1] = ABC Arr1[2] = DEFGH

复合数组创建：在 PostgreSQL 中通过首先创建用户定义类型，然后使用该类型创建数组来实现复合数组。以下语句创建一个名为 persontype 的自定义类型，定义创建的自定义类型的数组变量，并为数组元素赋值：

sql 创建自定义类型 CREATE TYPE persontype AS ( firstname text lastname text age int )

定义复合数组 arr2 persontype[]

为复合数组赋值 arr2[1] = ROW(John Smith 30)

魔方加速器老版本

您还可以在 PostgreSQL 中使用 域 来定义数组变量。当您打算利用相同的现有结构时，可以创建域。例如，可以为 persontype[] 创建一个域，并按原样使用它，而无需使用操作符 [] 来声明数组：

sql 创建 persontype 的数组类型CREATE DOMAIN personarr AS persontype[]

定义域类型的数组变量arr3 personarr

为复合数组赋值arr3[1] = ROW(John Smith 30)

通过了解 PostgreSQL 中的数组，现在让我们深入探讨从 Oracle 数据库迁移集合时的用例和挑战。

前提条件

要开始本文中所述的示例，您必须具备以下前提条件：

有一个有效的 AWS 账户一个源 Oracle 数据库在本地或使用 Amazon RDS for Oracle一个目标 RDS for PostgreSQL 或 Aurora PostgreSQL 数据库在目标数据库中具有以下权限的数据库用户提供您自己的架构和用户：

sqlGRANT CONNECT ON DATABASE TO YOURDBUSERGRANT USAGE ON SCHEMA YOURSCHEMANAME TO YOURDBUSERGRANT SELECT INSERT UPDATE DELETE ON ALL TABLES IN SCHEMA YOURSCHEMANAME TO YOURDBUSER

请使用以下脚本创建表并插入数据，以便尝试本文所描述的示例。在 Oracle 和 PostgreSQL 数据库中运行对应的语句：

对于 Oracle，使用以下代码：

sqlCREATE TABLE StudentMarks( StudID INTEGER Subject VARCHAR2(20) ScoredMarks INTEGER TotalMarks INTEGER)

INSERT ALLINTO StudentMarks VALUES(10101 MATHS 62 75)INTO StudentMarks VALUES(10101 ENGLISH 70 100)INTO StudentMarks VALUES(10101 ZOOLOGY 32 50)INTO StudentMarks VALUES(10102 MATHS 35 50)INTO StudentMarks VALUES(10102 COMPUTERS 43 50)INTO StudentMarks VALUES(10102 PHYSICS 69 100)INTO StudentMarks VALUES(10102 ENGLISH 81 100)SELECT FROM DUAL

对于 PostgreSQL，使用以下代码：

sqlCREATE TABLE StudentMarks( StudID INTEGER Subject VARCHAR(20) ScoredMarks INTEGER TotalMarks INTEGER)

INSERT INTO StudentMarks VALUES(10101 MATHS 62 75)(10101 ENGLISH 70 100)(10101 ZOOLOGY 32 50)(10102 MATHS 35 50)(10102 COMPUTERS 43 50)(10102 PHYSICS 69 100)(10102 ENGLISH 81 100)

使用 BULK COLLECT 将简单集合迁移到 PostgreSQL

Oracle PL/SQL 开发人员在 SELECT 语句中使用 BULK COLLECT 特性，可以通过单次提取多行数据来提高数据检索速度。BULK COLLECT 目前在 PostgreSQL 数据库中不可用。如果将 BULK COLLECT 转换为传统的逐行处理方式，效率可能会出现问题。因此，最好使用 SQL 语句，而不是逐行循环处理。此外，您可以在这篇 文章 中了解更多关于在 PostgreSQL 中迁移 BULK COLLECT 的内容。

PostgreSQL 数据库提供了数组函数 arrayagg，使您能够实现类似于 Oracle 的处理方式。为说明这一点，考虑一个涉及 BULK COLLECT 的基本集合场景。然后我们可以调整 SQL 查询以便在一次提取操作中检索所有行。

以下 PL/SQL 程序从 StudentMarks 表中获取学生成绩数据，并将其存储在内存中作为 OUT 参数，供其他调用例程和应用程序进一步处理。以下 Oracle 示例演示了如何使用 BULK COLLECT 在一次数据库调用中提取所有行。

sql/ 包规范声明 /CREATE OR REPLACE PACKAGE studmarksIS TYPE MarksArray IS TABLE OF INTEGER INDEX BY PLSINTEGER PROCEDURE getstudmarks( iStudentID IN INTEGER IOStudMarks OUT MarksArray )END studmarks/ 包体声明 /CREATE OR REPLACE PACKAGE BODY studmarksIS / 子程序使用集合作为 INOUT/OUT 参数 / PROCEDURE getstudmarks( iStudentID IN INTEGER IOStudMarks OUT MarksArray ) AS BEGIN SELECT TotalMarks BULK COLLECT INTO IOStudMarks FROM StudentMarks WHERE StudID = iStudentID END getstudmarksEND studmarks

让我们通过匿名块运行这个包的过程，并查看结果：

sqlSET SERVEROUTPUT ONDECLARE vStudMarks studmarksMarksArrayBEGIN studmarksgetstudmarks(10101 vStudMarks) 调用过程 FOR I IN vStudMarksFIRSTvStudMarksLAST LOOP DBMSOUTPUTPUTLINE(i vStudMarks(i)) END LOOPEND

我们得到以下输出：

1752100350

PL/SQL 过程成功完成。

现在让我们将这个 Oracle 包函数转换为相应的 PL/pgSQL 代码。在将此类代码迁移到 PostgreSQL 时需要注意几点：

PostgreSQL 不支持 Oracle 的 PACKAGE 语法。为此，我们在此示例中使用 PostgreSQL 架构来帮助转换 Oracle 包。PostgreSQL 中的所有函数或过程本质上是独立的，您在架构中创建。不建议在过程或函数中创建类型或域，因为它们以数据库中的持久对象存在。如果您第二次运行 PL/pgSQL 代码，将报告“对象已存在”的错误消息。对于在 Oracle 包规范或体中定义的集合类型，您应该在 PostgreSQL 中创建类型和域。您应该将数组变量声明为域，并使用 arrayagg 函数来将所有行作为单个单元提取并填充数组。

了解这些之后，我们来将 Oracle 代码转换为 PostgreSQL。

sql/ 为包创建架构 /CREATE SCHEMA IF NOT EXISTS studmarks

/ 在架构中为集合类型创建域 /CREATE DOMAIN studmarksMarkArray AS VARCHAR[]

/ 在架构中创建函数 /CREATE OR REPLACE FUNCTION studmarksgetstudmarks( StudentID IN INTEGER IOMarkGradeArray OUT studmarksMarkArray)AS BEGIN SELECT arrayagg(TotalMarks) / arrayagg 作为 BULK COLLECT 的替代方案 / INTO IOMarkGradeArray FROM StudentMarks WHERE StudID = StudentIDEND LANGUAGE plpgsql

让我们通过匿名块运行 PostgreSQL 函数并检查结果：

sqlset clientminmessages = debug

将 Oracle 集合迁移到 Amazon Aurora PostgreSQL 兼容版或 Amazo

DO DECLARE vStudMarks studmarksMarkArrayBEGIN vStudMarks= studmarksgetstudmarks(10101)

 访问集合类型FOR i IN arraylower(vStudMarks 1)  arrayupper(vStudMarks 1)LOOP    RAISE DEBUG USING message = concatws(i  vStudMarks[i])END LOOPRAISE DEBUG 程序执行成功。

END

我们得到以下输出：

plaintextDEBUG 175DEBUG 2100DEBUG 350

DEBUG 程序执行成功。

需要注意的事项：

我们在 PostgreSQL 中使用 arrayagg 函数作为 Oracle 中 BULK COLLECT 的替代。如果您需要为 Oracle 中定义的类型创建域，您应该做好规划。如果该类型是全局对象，那么必须在 PostgreSQL 中创建域或类型。

接下来我们来探讨另一个场景，即复合集合，并理解如何在 PostgreSQL 中处理它们。

迁移复合集合到 PostgreSQL

Oracle 数据库允许集合是复合的，其中集合的元素可以包含多个列。由于集合的所有元素始终具有相同的数据类型，因此这种类型的集合总是指基于用户定义的数据类型创建的集合。

以下 PL/SQL 程序声明一个名为 RMarkGradeRec 的记录类型，由四个属性组成。创建一个名为 MarkGradeArray 的类型为 RMarkGradeRec 的数组，该数组可以存储学生成绩。过程 getgrades 获取并填充输出数组 (IOMarkGradeArray)，提供与指定学生 ID 相关的成绩信息。

我们在 Oracle 中使用以下代码：

sql/ 包规范声明 /CREATE OR REPLACE PACKAGE studgradesISTYPE RMarkGradeRec IS RECORD( SubjectID VARCHAR2(10) ScoredMarks INTEGER TotalMarks INTEGER Grade INTEGER)

TYPE MarkGradeArray IS TABLE OF RMarkGradeRec INDEX BY PLSINTEGER

Procedure getgrades( StudentID IN INTEGER IOMarkGradeArray OUT MarkGradeArray)END studgrades/ 包体声明 /CREATE OR REPLACE PACKAGE BODY studgradesIS/ 子程序使用集合作为 INOUT/OUT 参数 / Procedure getgrades( StudentID IN INTEGER IOMarkGradeArray OUT MarkGradeArray ) AS CgetMarks SYSREFCURSOR lsql VARCHAR2(1000) lsubjID VARCHAR2(10) lScoredMarks INTEGER lTotalMarks INTEGER lcounter INTEGER = 1 BEGIN lsql = SELECT Subject ScoredMarks TotalMarks FROM StudentMarks WHERE StudID = StudentID

    OPEN CgetMarks FOR lsql    LOOP        FETCH CgetMarks INTO lsubjID lScoredMarks lTotalMarks        EXIT WHEN CgetMarksNOTFOUND        IOMarkGradeArray(lcounter)SubjectID = lsubjID        IOMarkGradeArray(lcounter)ScoredMarks = lScoredMarks        IOMarkGradeArray(lcounter)TotalMarks = lTotalMarks        IOMarkGradeArray(lcounter)Grade = TOCHAR(lScoredMarks/lTotalMarks  100 99)        lcounter = lcounter 1    END LOOP    CLOSE CgetMarksEND getgrades

END studgrades

让我们运行该脚本，检索并显示特定学生的成绩信息通过 ID 10102 识别，使用自定义数组类型 MarkGradeArray。它将为每个成绩条目打印 SubjectID 和 TotalMarks。

sqlSET SERVEROUTPUT ONDECLARE vMarkGradeArray studgradesMarkGradeArrayBEGIN studgradesgetgrades(10102 vMarkGradeArray) FOR i IN vMarkGradeArrayFIRST vMarkGradeArrayLAST LOOP DBMSOUTPUTPUTLINE(vMarkGradeArray(i)SubjectID vMarkGradeArray(i)TotalMarks) END LOOPEND

我们得到以下输出：

MATHS50COMPUTERS50PHYSICS100ENGLISH100

PL/SQL 过程成功完成。

您可以通过完成以下步骤，将上述 Oracle 代码迁移到 PostgreSQL：

创建结构与 Oracle 中用户定义类型相同的用户定义类型。使用创建的类型在 PostgreSQL 中创建一个域。声明一个数组变量，并使用 arrayagg 和行操作符修改游标 SELECT 语句。

我们在 PostgreSQL 中使用以下代码：

sqlCREATE SCHEMA IF NOT EXISTS studgrades

CREATE TYPE studgradesRMarkGradeArray AS( SubjectID VARCHAR(10) ScoredMarks INTEGER TotalMarks INTEGER Grade INTEGER)

CREATE DOMAIN studgradesMarkGradeArray AS studgradesRMarkGradeArray[]

CREATE OR REPLACE FUNCTION studgradesgetgrades( StudentID IN INTEGER IOMarkGradeArray OUT studgradesMarkGradeArray)AS DECLARE cgetMarks REFCURSOR lsql VARCHAR(1000) lsubjID VARCHAR(10) lScoredMarks INTEGER lTotalMarks INTEGER lcounter INTEGER = 1BEGIN lsql = SELECT Subject ScoredMarks TotalMarks FROM StudentMarks WHERE StudID = 1 OPEN CgetMarks FOR EXECUTE lsql USING StudentID LOOP FETCH CgetMarks INTO lsubjID lScoredMarks lTotalMarks EXIT WHEN NOT FOUND IOMarkGradeArray[lcounter] = row(lsubjID lScoredMarks lTotalMarks (lScoredMarks/lTotalMarks)INTEGER 100) lcounter = lcounter 1 END LOOPEND LANGUAGE plpgsql

让我们运行该脚本：

sqlset clientminmessages = debug

DO