百度360必应搜狗淘宝本站头条
nodejsbufferorg.jsoupsetnocountoncv2.bitwise_nottime.strptime
当前位置：网站首页 > 编程字典 > 正文

Oracle应用迁移到AnalyticDB for PostgreSQL指导

toyiye 2024-06-21 12:23 6 浏览 0 评论

AnalyticDB for PostgreSQL（简称：ADB for PG）对Oracle语法有着较好的兼容，本文介绍如何将Oracle应用迁移到AnalyticDB for PostgreSQL。

1 PL/SQL

PL/SQL（Procedural Language/SQL）是一种过程化的SQL语言，是Oracle对SQL语句的拓展，使得SQL的使用可以具有一般编程语言的特点，因此，可以用来实现复杂的业务逻辑。PL/SQL对应了ADB for PG中的PL/PGSQL

1.1Package

ADB for PG的plpgsql不支持package，需要把package 转换成 schema，并package里面的所有procedure和 function转换成ADB for PG的function。

例如：

create or replace package pkg is 
…
end;

可以转换成：

create schema pkg;
  1. Package定义的变量
 procedure/function的局部变量保持不变，全局变量在ADB for PG中可以使用临时表进行保存。详见1.4.5节。
  1. Package初始化块
如果可以删掉，就删掉，删不掉的话，可以使用function封装，在需要的时候主动调用该function。
  1. Package 内定义的procedure/function
Package 内定义的procedure和function 转成adb for pg的function，并把function 定义到package对应的schema内。
例如，有一个Package名为pkg中有如下函数：
FUNCTION test_func (args int) RETURN int is 
var number := 10;
BEGIN
… … 
END;
转换成如下ADB for PG的function：
CREATE OR REPLACE FUNCTION pkg. test_func(args int) RETURNS int AS 
$
 … … 
$
 LANGUAGE plpgsql;

1.2 Procedure/function

对于oracle的procedure和function，不论是package的还是全局的，都转换成adb for pg 的function。

例如：

CREATE OR REPLACE FUNCTION test_func (v_name varchar2, v_version varchar2)
RETURN varchar2 IS
 ret varchar(32);
BEGIN
 IF v_version IS NULL THEN
 ret := v_name;
ELSE
 ret := v_name || '/' || v_version;
 END IF;
 RETURN ret;
END;

转化成：

CREATE OR REPLACE FUNCTION test_func (v_name varchar, v_version varchar)
RETURNS varchar AS 
$
DECLARE
 ret varchar(32);
BEGIN
 IF v_version IS NULL THEN
 ret := v_name;
ELSE
 ret := v_name || '/' || v_version;
 END IF;
 RETURN ret;
END;
$
 LANGUAGE plpgsql;

Procedure/function转换的关键点：

  1. RETURN 关键字转成RETURNS
  2. 函数体使用$\$ ... $\$封装起来
  3. 函数语言声明
  4. Subprocedure需要转换成ADB for PG的function

1.3 PL statement

1.3.1 For语句

带有REVERSE的整数FOR循环的工作方式不同：PL/SQL中是从第二个数向第一个数倒数，而PL/pgSQL是从第一个数向第二个数倒数，因此在移植时需要交换循环边界。

示例：

FOR i IN REVERSE 1..3 LOOP
 DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
END LOOP;

转换成：

FOR i IN REVERSE 3..1 LOOP
 RAISE ‘%’ ,i;
END LOOP;

1.3.2 PRAGMA语句

ADB for PG 无PRAGMA语句，删除。

1.3.3 事务处理

ADB for PG 的function 内部无法使用事务控制语句，如begin，commit，rollback等。

修改方法：

  1. 删除函数体内的事务控制语句，把事务控制放在函数体外；
  2. 把函数按照commit/rollback 拆分成多个。

1.3.4 EXECUTE语句

ADB for PG支持类似oracle的动态sql语句，不同之处如下：

  1. 不支持using 语法，解决方法是把参数拼接到sql串中；
  2. 数据库标识符使用quote_ident包裹，数值使用quote_literal包裹。

示例：

EXECUTE 'UPDATE employees_temp SET commission_pct = :x' USING a_null;

转换成：

EXECUTE 'UPDATE employees_temp SET commission_pct = ' || quote_literal(a_null);

1.3.5 Pipe row

Pipe row函数，使用adb for pg的table function来替换。

示例：

TYPE pair IS RECORD(a int, b int);
TYPE numset_t IS TABLE OF pair;
FUNCTION f1(x int) RETURN numset_t PIPELINED IS
DECLARE
 v_p pair;
BEGIN
 FOR i IN 1..x LOOP
 v_p.a := i;
 v_p.b := i+10;
 PIPE ROW(v_p);
 END LOOP;
 RETURN;
END;
select * from f1(10);

转换成：

create type pair as (a int, b int);
create or replace function f1(x int) returns setof pair as 
$
declare
rec pair;
begin
 for i in 1..x loop
 rec := row(i, i+10);
 return next rec;
 end loop;
 return ;
end
$
 language 'plpgsql';
select * from f1(10);

说明：

  1. 自定义类型pair转换成adb for pg的复合类型pair
  2. Table of类型不需要定义，使用adb for pg的setof 替换
  3. Pipe row 语句转换成下面两个语句：
 rec := row(i);
 return next rec;
  1. 上面的oracle function还可以转换成如下：
create or replace function f1(x int) returns setof record as 
$
declare
rec record;
begin
 for i in 1..x loop
 rec := row(i, i+10);
 return next rec;
 end loop;
 return ;
end
$
language 'plpgsql';

与第一种改法的不同支持是，不需要提前定义数据类型numset_t.正因为这一点所以在查询的时候需要指定返回的类型，如下：select * from f1(10) as (a int, b int);

1.3.6 异常处理

  1. 使用raise抛出异常
  2. Catch异常后，不能rollback事务，只能在udf外做rollback
  3. ADB for PG支持的error，可以参考: https://www.postgresql.org/docs/8.3/errcodes-appendix.html

1.3.7 function中同时有Return和OUT参数

在adb pg中，不允许fucntion同时有return和out参数，因此，可以把需要返回的参数改写成out类型参数。

示例：

CREATE OR REPLACE FUNCTION test_func(id int, name varchar(10), out_id out int) returns varchar(10)
AS $body$
BEGIN
 out_id := id + 1;
 return name;
end
$body$
LANGUAGE PLPGSQL;

改写成：

CREATE OR REPLACE FUNCTION test_func(id int, name varchar(10), out_id out int, out_name out varchar(10))
AS $body$
BEGIN
 out_id := id + 1;
 out_name := name;
end
$body$
LANGUAGE PLPGSQL;

然后select * from test_func(1,’1’) into rec;从rec中取对应字段的返回值即可。

1.4 PL数据类型

1.4.1 Record

使用ADB for PG的复合数据类型替换

示例：

TYPE rec IS RECORD (a int, b int)；

改写成：

CREATE TYPE rec AS (a int, b int);

1.4.2 Nest table

  1. Nest table 作为pl 变量，可以使用ADB for PG的array类型替换。
  2. 示例：
DECLARE
 TYPE Roster IS TABLE OF VARCHAR2(15);
 names Roster := 
 Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
BEGIN
 FOR i IN names.FIRST .. names.LAST
 LOOP
 IF names(i) = 'J Hamil' THEN
 DBMS_OUTPUT.PUT_LINE(names(i));
 END IF;
 END LOOP;
END;

改写成：

create or replace function f1() returns void as 
$
declare
 names varchar(15)[] := '{"D Caruso", "J Hamil", "D Piro", "R Singh"}';
 len int := array_length(names, 1);
begin
 for i in 1..len loop
 if names[i] = 'J Hamil' then
 raise notice '%', names[i];
 end if;
 end loop;
 return ;
end
$
 language 'plpgsql';
select f();
  1. 作为function返回值，则可以使用table function替换，参考1.3.5节。

1.4.3 Associative Array

无替换类型。

1.4.4 Variable-Size Arrays

与nest table 一样，使用array类型替换。

1.4.5 Global variables

目前ADB for PG不支持global variables，一种方法是把一个package中的所有global variables存入一张临时表（temporary table）中, 然后定义修改、获取global variables的函数。

示例：

create temporary table global_variables (
 id int,
 g_count int,
 g_set_id varchar(50),
 g_err_code varchar(100)
);
insert into global_variables values(0, 1, null，null);
CREATE OR REPLACE FUNCTION get_variable() returns setof global_variables AS
$
DECLARE
 rec global_variables%rowtype;
BEGIN
 execute 'select * from global_variables' into rec;
 return next rec;
END;
$
 LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION set_variable(in param varchar(50), in value anyelement) returns void AS
$
BEGIN
 execute 'update global_variables set ' || quote_ident(param) || ' = ' || quote_literal(value);
END;
$
 LANGUAGE plpgsql;

其中，临时表global_variables中，字段id为这个表的分布列，因为ADB for PG中不允许对于分布列的修改，需要多加一个这样的字段。

tmp_rec record;

修改一个全局变量时，使用：select * from set_variable(‘g_error_code’, ‘error’::varchar) into tmp_rec;

获取一个全局变量时，使用：select * from get_variable() into tmp_rec; error_code := tmp_rec.g_error_code;

1.5 SQL

1.5.1 Connect by

Oracle 层次查询，adb for pg没有等价替换的sql语句。转换思路是使用循环按层次遍历。

示例：

create table employee(
 emp_id numeric(18),
 lead_id numeric(18),
 emp_name varchar(200),
 salary numeric(10,2),
 dept_no varchar(8)
);
insert into employee values('1',0,'king','1000000.00','001');
insert into employee values('2',1,'jack','50500.00','002');
insert into employee values('3',1,'arise','60000.00','003');
insert into employee values('4',2,'scott','30000.00','002');
insert into employee values('5',2,'tiger','25000.00','002');
insert into employee values('6',3,'wudde','23000.00','003');
insert into employee values('7',3,'joker','21000.00','003');
insert into employee values('3',7,'joker','21000.00','003');
select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
 from employee
 start with lead_id=0
 connect by prior emp_id = lead_id

转换成：

create or replace function f1(tablename text, lead_id int, nocycle boolean) returns setof employee as 
$
declare
 idx int := 0;
 res_tbl varchar(265) := 'result_table';
 prev_tbl varchar(265) := 'tmp_prev';
 curr_tbl varchar(256) := 'tmp_curr';
 current_result_sql varchar(4000);
 tbl_count int;
 rec record;
begin
 
 execute 'truncate ' || prev_tbl;
 execute 'truncate ' || curr_tbl;
 execute 'truncate ' || res_tbl;
 loop
 -- 查询当前层次结果，并插入到tmp_curr表
 current_result_sql := 'insert into ' || curr_tbl || ' select t1.* from ' || tablename || ' t1';
 if idx > 0 then
 current_result_sql := current_result_sql || ', ' || prev_tbl || ' t2 where t1.lead_id = t2.emp_id';
 else
 current_result_sql := current_result_sql || ' where t1.lead_id = ' || lead_id;
 end if;
 execute current_result_sql;
 -- 如果有环，删除已经遍历过的数据
 if nocycle is false then
 execute 'delete from ' || curr_tbl || ' where (lead_id, emp_id) in (select lead_id, emp_id from ' || res_tbl || ') ';
 end if;
 -- 如果没有数据，则退出
 execute 'select count(*) from ' || curr_tbl into tbl_count;
 exit when tbl_count = 0;
 -- 把tmp_curr数据保存到result表
 execute 'insert into ' || res_tbl || ' select * from ' || curr_tbl;
 execute 'truncate ' || prev_tbl;
 execute 'insert into ' || prev_tbl || ' select * from ' || curr_tbl;
 execute 'truncate ' || curr_tbl;
 idx := idx + 1;
 end loop;
 -- 返回结果
 current_result_sql := 'select * from ' || res_tbl;
 for rec in execute current_result_sql loop
 return next rec;
 end loop;
 return;
end
$
 language plpgsql;

1.5.2 Rownum

  1. 限定查询结果集大小，可以使用limit替换
  2. 示例：
select * from t where rownum < 10;

转换成：

select * from t limit 10;
  1. 使用row_number() over()生成rownum
  2. 示例：

select rownum, * from t;

转换成：

select row_number() over() as rownum, * from t;

1.5.3 Dual表

  1. 去掉dual
  2. 示例：
select sysdate from dual;

转换成：

select current_timestamp;
  1. 创建一个叫dual的表。

1.5.4 Select中的udf

ADB for PG支持在select中调用udf，但是udf中不能有sql语句，否则会收到如下的错误信息：

ERROR: function cannot execute on segment because it accesses relation "public.t2" (functions.c:155) (seg1 slice1 127.0.0.1:25433 pid=52153) (cdbdisp.c:1326)

DETAIL:

SQL statement "select b from t2 where a = $1 "

转换方法是把select中的udf转换成sql表达式或者子查询等

示例：

create or replace FUNCTION f1(arg int) RETURN int IS
 v int;
BEGIN
 select b into v from t2 where a = arg;
 return v;
END;
select a, f1(b) from t1;

转换成：

select t1.a, t2.b from t1, t2 where t1.b = t2.a;

1.5.5 （+）多表外链接

ADB for PG 不支持(+)这样的语法形式，需要转换成标准的outer join语法。

示例：

oracle
select * from a,b where a.id=b.id(+)

转换成：

select * from a left join b on a.id=b.id

如果在(+)中有三表的join，需要先用wte做两表的join，再用+号那个表跟wte表做outer join。

示例：

Select * from test1 t1, test2 t2, test3 t3 where t1.col1(+) between NVL(t2.col1, t3.col1) and NVL(t3.col1, t2.col1);

转换成：

with cte as (select t2.col1 as low, t2.col2, t3.col1 as high, t3.col2 as c2 from t2, t3)
select * from t1 right outer join cte on t1.col1 between coalesce(cte.low, cte.high) and coalesce(cte.high,cte.low);

1.5.6 Merge into

对于merge into语法的转换，在ADB for PG中先使用update进行更新，然后使用GET DIAGNOSTICS rowcount := ROW_COUNT;语句获取update更新的行数，如果update更新的行数为0，那么再使用insert语句进行插入。

MERGE INTO test1 t1
 USING (SELECT t2.col1 col1, t3.col2 col2,
 FROM test2 t2, test3 t3) S
 ON S.col1 = 1 and S.col2 = 2 
WHEN MATCHED THEN
 UPDATE
 SET test1.col1 = S.col1+1,
 test1.col2 = S.col2+2
WHEN NOT MATCHED THEN
 INSERT (col1, col2)
 VALUES
 (S.col1+1, S.col2+2);

转换成：

Update test1 t1 SET t1.col1 = test2.col1+1, test3.col2 = S.col2+2 where test2.col1 = 1 and test2.col2 = 2;
GET DIAGNOSTICS rowcount := ROW_COUNT;
if rowcount = 0 then
 insert into test1 values(test2.col1+1, test3.col2+2);

2 系统函数转换对照表

3 数据类型转换对照表

作者：陆封

相关推荐

为何越来越多的编程语言使用JSON（为什么编程）

JSON是JavascriptObjectNotation的缩写，意思是Javascript对象表示法，是一种易于人类阅读和对编程友好的文本数据传递方法，是JavaScript语言规范定义的一个子...

何时在数据库中使用 JSON（数据库用json格式存储）

在本文中，您将了解何时应考虑将JSON数据类型添加到表中以及何时应避免使用它们。每天?分享?最新?软件?开发?，Devops，敏捷?，测试?以及?项目?管理?最新?，最热门?的?文章?，每天?花?...

MySQL 从零开始:05 数据类型（mysql数据类型有哪些,并举例）

前面的讲解中已经接触到了表的创建，表的创建是对字段的声明，比如：上述语句声明了字段的名称、类型、所占空间、默认值和是否可以为空等信息。其中的int、varchar、char和decimal都...

JSON对象花样进阶（json格式对象）

一、引言在现代Web开发中，JSON（JavaScriptObjectNotation）已经成为数据交换的标准格式。无论是从前端向后端发送数据，还是从后端接收数据，JSON都是不可或缺的一部分。...

深入理解 JSON 和 Form-data（json和formdata提交区别）

在讨论现代网络开发与API设计的语境下，理解客户端和服务器间如何有效且可靠地交换数据变得尤为关键。这里，特别值得关注的是两种主流数据格式：...

JSON 语法（json 语法 priority）

JSON语法是JavaScript语法的子集。JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔花括号保存对象方括号保存数组JS...

JSON语法详解（json的语法规则）

JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔大括号保存对象中括号保存数组注意：json的key是字符串,且必须是双引号，不能是单引号...

MySQL JSON数据类型操作（mysql的json）

概述mysql自5.7.8版本开始，就支持了json结构的数据存储和查询，这表明了mysql也在不断的学习和增加nosql数据库的有点。但mysql毕竟是关系型数据库，在处理json这种非结构化的数据...

JSON的数据模式（json数据格式示例）

像XML模式一样，JSON数据格式也有Schema，这是一个基于JSON格式的规范。JSON模式也以JSON格式编写。它用于验证JSON数据。JSON模式示例以下代码显示了基本的JSON模式。{"...

前端学习——JSON格式详解（后端json格式）

JSON(JavaScriptObjectNotation)是一种轻量级的数据交换格式。易于人阅读和编写。同时也易于机器解析和生成。它基于JavaScriptProgrammingLa...

什么是 JSON:详解 JSON 及其优势（什么叫json）

现在程序员还有谁不知道JSON吗？无论对于前端还是后端，JSON都是一种常见的数据格式。那么JSON到底是什么呢？JSON的定义...

PostgreSQL JSON 类型:处理结构化数据

PostgreSQL提供JSON类型，以存储结构化数据。JSON是一种开放的数据格式，可用于存储各种类型的值。什么是JSON类型？JSON类型表示JSON(JavaScriptO...

JavaScript:JSON、三种包装类（javascript 包）

JOSN:我们希望可以将一个对象在不同的语言中进行传递，以达到通信的目的，最佳方式就是将一个对象转换为字符串的形式JSON（JavaScriptObjectNotation）-JS的对象表示法...

Python数据分析 只要1分钟 教你玩转JSON 全程干货

Json简介：Json，全名JavaScriptObjectNotation,JSON(JavaScriptObjectNotation(记号、标记))是一种轻量级的数据交换格式。它基于J...

比较一下JSON与XML两种数据格式?（json和xml哪个好）

JSON（JavaScriptObjectNotation）和XML（eXtensibleMarkupLanguage）是在日常开发中比较常用的两种数据格式，它们主要的作用就是用来进行数据的传...

取消回复欢迎 你 发表评论:

请填写验证码
一周热门
最近发表
标签列表