将 Oracle OUT 绑定变量迁移到 Postgre 数据库 SQL

由 Bikash Chandra Rout (AWS) 和 Vinay Paladi () 创作 AWS

环境：PoC 或试点	源：数据库关系	目标：RDS/Aurora Postgresql
R 类型：更换平台	工作负载：Oracle	技术：数据库；迁移
AWS服务：亚马逊 Aurora；亚马逊RDS；AWSSCT

Summary

此模式显示如何将 Oracle 数据库OUT绑定变量迁移到以下任一与 Postgre SQL 兼容的AWS数据库服务：

• 适用于 Postgre 的亚马逊关系数据库服务（亚马逊RDS）SQL

• 亚马逊 Aurora Postgre SQL-兼容版

Postgre SQL 不支持OUT绑定变量。要在 Python 语句中获得相同的功能，可以创建一个使用GET和SET包变量的自定义 PL/pg SQL 函数。要应用这些变量，此模式中提供的示例包装函数脚本使用了 S AWSchema Conversion Tool (AWSSCT) 扩展包。

注意：如果 Oracle EXECUTE IMMEDIATE 语句是最多可以返回一行的 SELECT 语句，则最佳做法是执行以下操作：

• 将 OUT 绑定变量（定义）放在 INTO 子句中

• 将 IN 绑定变量放在 USING 子句中

有关更多信息，请参阅 Oracle 文档中的EXECUTEIMMEDIATE语句。

先决条件和限制

先决条件

• 一个活跃的AWS账户 

• 一个本地数据中心中的 Oracle 数据库 10g（或更高版本）源数据库

• RDS适用于 Postgre 的亚马逊SQL数据库实例或兼容 Aurora Postgre SQL 的数据库实例

架构

源技术堆栈

• 本地 Oracle 数据库 10g（或更高版本）数据库 

目标技术堆栈

• RDS适用于 Postgre 的亚马逊SQL数据库实例或兼容 Aurora Postgre SQL 的数据库实例

目标架构

下图显示了将 Oracle 数据库OUT绑定变量迁移到 Postgre SQL 兼容AWS数据库的示例工作流程。

图表显示了以下工作流：

1. AWSSCT将源数据库架构和大部分自定义代码转换为与目标 Postgre 兼容数据库SQL兼容的AWS格式。

2. PL/p SQL g 函数会标记任何无法自动转换的数据库对象。然后手动转换已标记的对象以完成迁移。

工具

• Amazon Aurora Postgre SQL 兼容版是一款完全托管、ACID兼容的关系数据库引擎，可帮助您设置、操作和扩展 Postgre 部署。SQL

• 适用于 Postgre 的亚马逊关系数据库服务（亚马逊RDS）SQL可帮助您在云中设置、操作和扩展 Postgre SQL 关系数据库。AWS

• AWSSchema Conversion Tool (AWSSCT) 通过自动将源数据库架构和大部分自定义代码转换为与目标数据库兼容的格式来支持异构数据库迁移。

• pgAdmin是 Postgre SQL 的开源管理工具。它提供了一个图形界面，可帮助您创建、维护和使用数据库对象。

操作说明

使用自定义 PL/pg SQL 函数迁移 Oracle OUT 绑定变量以及 AWS SCT

任务	描述	所需技能
连接到与 Postgre SQL 兼容的数据库AWS。	创建数据库实例后，您可以使用任何标准SQL客户端应用程序连接到数据库集群中的数据库。例如，您可以使用pgAdmin连接到您的数据库实例。 有关更多信息，请参阅以下任一项： 在《亚马逊RDS用户指南》中连接到亚马逊RDS数据库实例 《Amazon Aurora 用户指南》中的连接到 Amazon Aurora 数据库集群	迁移工程师
将此模式中的示例包装函数脚本添加到目标数据库的主架构中。	从此模式的 “其他信息” 部分复制示例 PL/pg SQL 包装器函数脚本。然后，将该函数添加到目标数据库的主架构中。 有关更多信息，请参阅 Postgre SQL 文档CREATEFUNCTION中的。	迁移工程师
（可选）更新目标数据库主架构中的搜索路径，使其包含 Test_pg 架构。	为了提高性能，您可以更新 Postgre SQL search_path 变量，使其包含 test _pg 架构名称。如果在搜索路径中包含架构名称，则无需在调用 pl/pg SQL 函数时指定名称。 有关更多信息，请参阅 Post SQL gre 文档中的第 5.9.3 节 “架构搜索路径”。	迁移工程师

相关资源

• AWS架构转换工具

• OUT绑定变量（Oracle 文档）

• 使用绑定变量提高SQL查询性能（Oracle 博客）

其他信息

pl/pg 函数SQL示例

/* Oracle */

CREATE or replace PROCEDURE test_pg.calc_stats_new1 (
                                              a NUMBER,
                                              b NUMBER,
                                              result out NUMBER
                                            )                                                                                   
IS
BEGIN
result:=a+b;
END;
/
/* Testing */
set serveroutput on 
DECLARE
  a NUMBER := 4;
  b NUMBER := 7;
  plsql_block VARCHAR2(100);
  output number;
BEGIN
  plsql_block := 'BEGIN test_pg.calc_stats_new1(:a, :b,:output); END;';
  EXECUTE IMMEDIATE plsql_block USING a, b,out output;  -- calc_stats(a, a, b, a)
  DBMS_OUTPUT.PUT_LINE('output:'||output);
END;

output:11


PL/SQL procedure successfully completed.

--Postgres--

/* Example : 1 */
CREATE OR REPLACE FUNCTION test_pg.calc_stats_new1(
                                                    w integer,
                                                    x integer
                                                  )
RETURNS integer
AS 
$BODY$
begin
      return w + x ;
end;
$BODY$
LANGUAGE  plpgsql;


CREATE OR REPLACE FUNCTION aws_oracle_ext.set_package_variable(
                                                                package_name name,
                                                                variable_name name,
                                                                variable_value anyelement
                                                              )
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
begin
    perform set_config
      ( format( '%s.%s',package_name, variable_name )
      , variable_value::text
      , false );
  end;
$BODY$;

CREATE OR REPLACE FUNCTION aws_oracle_ext.get_package_variable_record(
                                                                      package_name name,
                                                                      record_name name
                                                                    )
RETURNS text
LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
begin
    execute 'select ' || package_name || '$Init()';

    return aws_oracle_ext.get_package_variable
        ( 
         package_name := package_name
        , variable_name := record_name || '$REC' );
end;
$BODY$;

--init()--
CREATE OR REPLACE FUNCTION test_pg.init()
RETURNS void
AS
$BODY$
BEGIN
if aws_oracle_ext.is_package_initialized('test_pg' ) then
      return;
    end if;
    perform aws_oracle_ext.set_package_initialized
      ('test_pg' );
PERFORM aws_oracle_ext.set_package_variable('test_pg', 'v_output', NULL::INTEGER);
PERFORM aws_oracle_ext.set_package_variable('test_pg', 'v_status', NULL::text);
END;
$BODY$
LANGUAGE  plpgsql;

/* callable for 1st Example */

DO $$ 
declare
v_sql text;
v_output_loc int; 
a integer :=1;
b integer :=2;
BEGIN 
perform  test_pg.init();
--raise notice 'v_sql %',v_sql;
execute 'do $a$ declare v_output_l int; begin select * from test_pg.calc_stats_new1('||a||','||b||') into v_output_l;
PERFORM aws_oracle_ext.set_package_variable(''test_pg'', ''v_output'', v_output_l) ; end; $a$'  ; 
v_output_loc := aws_oracle_ext.get_package_variable('test_pg', 'v_output');
raise notice 'v_output_loc %',v_output_loc; 
END ; 
$$

/*In above Postgres example we have set the value of v_output using v_output_l in the dynamic anonymous block to mimic the 
  behaviour of oracle out-bind variable .*/

--Postgres Example : 2 --
CREATE OR REPLACE FUNCTION test_pg.calc_stats_new2(
 w integer,
 x integer,
inout status text,
 out result integer)
AS
$BODY$
DECLARE
begin
result :=  w + x ;
status := 'ok';
end;
$BODY$
LANGUAGE  plpgsql;

/* callable for 2nd Example */
DO $$ 
declare
v_sql text;
v_output_loc int; 
v_staus text:= 'no';
a integer :=1;
b integer :=2;
BEGIN 
perform  test_pg.init();
execute 'do $a$ declare v_output_l int; v_status_l text; begin select * from test_pg.calc_stats_new2('||a||','||b||','''||v_staus||''') into v_status_l,v_output_l;
PERFORM aws_oracle_ext.set_package_variable(''test_pg'', ''v_output'', v_output_l) ; 
PERFORM aws_oracle_ext.set_package_variable(''test_pg'', ''v_status'', v_status_l) ; 
end; $a$'  ; 
v_output_loc := aws_oracle_ext.get_package_variable('test_pg', 'v_output');
v_staus := aws_oracle_ext.get_package_variable('test_pg', 'v_status');
raise notice 'v_output_loc %',v_output_loc; 
raise notice 'v_staus %',v_staus; 
END ; 
$$