数据库从PostgreSQL迁移至Oracle指导书（三）

前言：近日，公司的一套使用 postgresql 数据库的应用软件要兼容oracle。本文系统性地整理了PostgreSQL 和 Oracle的一些差异点，和应用程序中的改动点。

 

4    应用程序的改造

4.1 JDBC 配置

下面是PostgreSQL和Oracle的JDBC配置差异：

 

	PostgreSQL 11	Oracle 19c
driver_class	org.postgresql.Driver	oracle.jdbc.driver.OracleDriver
url	jdbc:postgresql://127.0.0.1:5432/postgres	jdbc:oracle:thin:@127.0.0.1:1521:ORCL

 

4.2 常用函数和运算符

下面是PostgreSQL和Oracle中，对应的常用的数据库函数和运算符：

 

	PostgreSQL 11	Oracle 19c
类型转换	value :: type 或 CAST(value AS type)	CAST(value AS type)
序列取值	nextval('sequence_name'）	sequence_name.nextval
获取uuid	gen_random_uuid() 或者uuid_generate_v1mc()	sys_guid()
模糊匹配且不区分大小写	select * from tb_usergroup where group_name ilike 'group%'	select * from tb_usergroup where upper(group_name) like upper('group%');
时间加减	select now() - INTERVAL '3 months'; 或 select now() - INTERVAL '3' month	select now() - INTERVAL '3' month from dual;
获取当前时间	获取事务开始时间戳： select now(); select current_timestamp;   获取当前命令执行的时间戳： select clock_timestamp();	获取事务开始时间戳： 无   获取当前命令执行的时间戳： select current_timestamp from dual;
获取会话当前时刻UTC毫秒数	select extract(epoch from now()) * 1000;	select (CAST(SYS_EXTRACT_UTC(current_timestamp) AS date) - to_date('1970-01-01', 'YYYY-MM-DD')) * 86400*1000  + mod(extract(second from current_timestamp), 1) * 1000  from dual;
获取会话当前时区的名称	select current_setting('timezone');	select SESSIONTIMEZONE FROM DUAL;
获取会话当前时区的偏移量（格式：+08:00）	select left(to_char(now(),'OF') || ':00', 6);	SELECT TZ_OFFSET(SESSIONTIMEZONE) FROM DUAL;
字符串聚集	select string_agg(name, ',') from man;	select listagg(name, ',') from man;
计算一个子串在字符串中出现的第一个位置	select strpos('abcd','c');	select instr('abcd','c') from dual;

正则表达式匹配

select 1 where  'abcd' ~ '^[a-z]*$';

select 1 from dual where regexp_like('abcd', '^[a-z]*$');

 

4.3 一些 sql 语法

下面是一些应用程序中需要改造的语法，这里列举的都是DML语句。

 

	PostgreSQL 11	Oracle 19c
查询表之外的数据	select 1;	select 1 from dual;   注意：from dual 是固定的语法。
分页查询	select * FROM tablename offset m  limit n  或  select * FROM tablename  limit n offset m	select * from (SELECT rownum rn, tablename.* FROM tablename where ROWNUM <= m+n) where rn > m;
NULL和''	NULL和''不同	ORACLE认为''等同于NULL
批量插入	insert into tb01 (id) values(1),(2),(3);	insert into tb01 (id) values(1); insert into tb01 (id) values(2); insert into tb01 (id) values(3);
集合的减	except	minus

 

注意：分页查询中，m 表示起始位置，最小值为0，n 代表所取的行数。

4.4 JSON 功能

目前 iSecure Center （以下简称ISC）平台的基线版本使用了一些PostgreSQL （PostgreSQL 11）的NO-SQL 特性，包括数组和json。Oracle同样提供了JSON和数组的处理功能。

下面是一些json功能的改造。

4.4.1 JSON 字段的类型

PostgreSQL 有原生类型json和jsonb。它们接受相同的输入格式。它们实际的主要差别是效率。json 数据类型存储输入的文本内容，处理函数在处理时必须将它解析为JSON；而jsonb数据以分解的二进制格式存储，这使得它由于添加了转换机制而在输入上稍微慢些，但是在处理上明显更快，因为不需要重新解析。jsonb支持索引，这是一个明显的优势。        实际应用中，我们使用jsonb类型。

Oracle中，JSON字段用varchar或clob类型表示。在创建了这样的字段后，需要用一个检查约束确保数据是真正的json格式的。

下面是在 PostgreSQL 中创建一个包含jsonb类型的表的示例：

      

create table tb_test

(

            id int,

            json_column jsonb,

);

insert into tb_test (id, json_column) values (1, '{"name":"zhangsan","gender":"male","age":22}');

 

下面是在 Oracle 中创建一个包含json字段的表的示例：

create table tb_test

(

        id int,

         json_column clob,

       CHECK (json_column IS JSON)

);

insert into tb_test (id, json_column) values (1, '{"name":"zhangsan","gender":"male","age":22}');

4.4.2 JSON 的查询

如果你要查询JSON 中的值，则需要给出JSON 键的名称或键的完整路径。注意，对于JSON对象{"a":{"b":{"c": "foo"}}}，在PostgreSQL 中JSON键路径的表示为 {a,b,c}；而在Oracle中，表示为 $.a.b.c

下面是两种数据库中，常用的json查询功能的对比：

 

	PostgreSQL 11	Oracle 19c
判断JSON的最外层是否包含某个键	{"a":1, "b":2}'::jsonb ? 'a'	json_exists('{"a":1, "b":2}', '$.a')
给定一个键，它的值是普通文本	{"a":"1",  "b":{"c": "foo"}}' ->> 'a'   结果： 1	json_value('{"a":"1",  "b":{"c": "foo"}}' , '$.a')   结果： 1
给定一个键，它的值是一个json对象或json数组	’{"a":"1",  "b":{"c": "foo"}}‘ -> 'b'  （返回值的类型是jsonb/json）   结果：{"c": "foo"}   或   '{"a":"1",  "b":{"c": "foo"}}' ->> 'b' （返回值的类型是text）   结果：{"c": "foo"}	json_query('{"a":"1",  "b":{"c": "foo"}}' , '$.b')   结果：{"c": "foo"}
给定一个键的路径，它的值是普通文本	{"a":"1",  "b":{"c": "foo"}}' #>> ‘{b, c}’   结果：foo	json_value('{"a":"1",  "b":{"c": "foo"}}' , '$.b.c')
给定一个键的路径，它的值是 json 对象或json数组格式	{"a":"1", "b":{"c": "foo"}, "d":{"e":{"f":"1"}}}' #> ‘{d, e}’ （返回值的类型是jsonb/json）   结果：{"f":"1"}   或   {"a":"1", "b":{"c": "foo"}, "d":{"e":{"f":"1"}}}' #>> ‘{d, e}’ （返回值的类型是text）   结果：{"f":"1"}	json_query('{"a":"1", "b":{"c": "foo"}, "d":{"e":{"f":"1"}}}', '$.d.e')   结果：{"f":"1"}
取 json 数组中的第一个元素， 注意数组的下标从0开始	'{"a":[1,2,3,4]}'::jsonb #>> '{a,0}'	json_value('{"a":[1,2,3,4]}','$.a[0]')

 

4.4.3 JSON 的修改

下面是两种数据库中，添加，修改和删除JSON 键值对的方法：

 

	PostgreSQL 11	Oracle 19c
合并或插入json 键值对	{"a":"1"}'::jsonb || '{"b":"1"}'::jsonb   结果：{"a":"1", "b" :"1"}	JSON_MERGEPATCH('{"a":"1"}',  '{"b":"1"}')   结果：{"a":"1", "b" :"1"}
删除json 最外层的键值对	{"a":1, "b":2}'::jsonb - 'b'   结果：{"a":"1"}	JSON_MERGEPATCH('{"a":"1", "b" :"1"}', '{"b":null}')   结果：{"a":"1"}
修改json 最外层键值对	{"a":"1", "b":"2"}'::jsonb || '{"b":"1"}'::jsonb   结果：{"a":"1", "b" :"1"}	JSON_MERGEPATCH('{"a":"1", "b":"2"}',  '{"b":"1"}')   结果：{"a":"1", "b" :"1"}
根据键的路径在JSON中插入或修改值	jsonb_set('{"a":1,"b":{"c":"1"}}', '{b,c}','"2"', false)   结果： {"a": 1, "b": {"c": "2"}}	JSON_MERGEPATCH('{"a":1,"b":{"c":"1"}}', '{"b":{"c":"2"}}')   结果： {"a": 1, "b": {"c": "2"}}

 

4.4.4 JSON的比较

下面的是两种数据库中比较json的方法，返回结果是布尔类型：

 

	PostgreSQL 11	Oracle 19c
比较json是否相等	'{"a":"1"}' :: jsonb = '{"a":"1"}'::jsonb	json_equal('{"a":"1"}', '{"a":"1"}')
左边的json 是否包含右边的json	{"a":1, "b":2}'::jsonb @> '{"a":1}'::jsonb	json_equal(JSON_MERGEPATCH('{"a":"1", "b" :"1"}', '{"a":"1"}'), '{"a":"1", "b" :"1"}')

 

4.4.5 其他JSON 功能

下面是一些常用其他的JSON函数：

 

	PostgreSQL 11	Oracle 19c	备注
创建一个json对象	jsonb_build_object('a',‘1’,'b',‘2’)   结果： {"a": "1", "b": "2"}	json_object(key 'a' value '1', key 'b' value '2')   结果： {"a": "1", "b": "2"}
把一个json中的键变成列	select * from jsonb_populate_record(null::myrowtype, '{"a":1,"b":2}')   结果： a | b ---+---  1 | 2	SELECT * FROM json_table('{"a":1, b:"2"}', '$' columns (a PATH '$.a', b PATH '$.b'))   结果： a | b ---+---  1 | 2	可以看出，Oracle中把json键转成列，需要指定列名和json的键。
把一个含有json数组中的json对象的键变成列	select * from jsonb_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')   结果： a | b ---+---  1 | 2  3 | 4	SELECT * FROM json_table('[{"a":1,"b":2},{"a":3,"b":4}]', '$[*]' columns (a PATH '$.a', b PATH '$.b'))   结果： a | b ---+---  1 | 2  3 | 4	可以看出，Oracle中把json键转成列，需要指定列名和json的键。
将两列多行数据聚集为一个json对象	select jsonb_object_agg(name, id) from tb_man;   结果： {"Tom":1,"Bob":2}	select json_objectagg(key name value id) from tb_man;   结果： {"Tom":1,"Bob":2}

 

4.5 数组功能

PostgreSQL 中提供了两种的数组可作为字段类型，一种是数组类型，一种是类型是jsonb的json数组。而Oracle则只支持JSON数组。

现在我们介绍这两种数据库中数组的知识和对应的函数和运算符。

4.5.1 数组的类型

PostgreSQL 支持的数组有两种，一种是数组类型，一种是json数组。

下面的案例是创建一张表，它的字段类型是整型数组类型：

create table tb_test

(

    id int,

    codes integer [],

);

insert into tb_test (id, codes) values (1, '{1, 2, 3, 4}');

insert into tb_test (id, codes) values (2, array[1, 2, 3, 4]);

 

下面的案例是创建一张表，它的字段类型是jsonb，格式是json数组：

create table tb_test

(

    id int,

    codes jsonb

);

insert into tb_test (id, codes) values (1,'[1, 2, 3, 4]');

 

而Oracle中，json数组的类型是varchar。创建包含数组字段的表的示例如下：

create table tb_test

(

    id int,

    codes  varchar(512),

    CHECK (attribute  IS JSON)

);

insert into tb_test (id, json_column) values (1, '[1, 2, 3, 4]');

4.5.2 数组的查询

下面是两种数据库中，常见的涉及数组的查询功能：

 

	PostgreSQL 11		Oracle 19c
数组的类型	array 类型的数组	json 数组，类型为jsonb	json 数组，类型为varchar
判断数组是否包含某个元素	array[1,2,3,4] @> array[1]	cast('[1, 2, 3, 4]' as jsonb) @> cast('[1]' as jsonb)	json_exists('[1, 2, 3]','$?(@ == "1")')
取数组中的第一个元素	select (array[1,2,3,4])[1];   注意，数组下标从1开始，[1] 表示第1个元素	select '[1,2,3,4]'::jsonb #>> '{0}'   注意，数组下标从0开始	SELECT  json_value('[1,2,3,4]','$[0]') from dual   注意，数组下标从0开始

 

4.5.3 数组的修改

下面是两种数据库中，常见的修改数组的方法：

 

 

	PostgreSQL 11		Oracle 19c
数组的类型	array 类型的数组	json 数组，类型为jsonb	json 数组，类型为varchar
向数组末端追加一个元素	array_append(array[1,2], 3) 或 array[1,2] || array[3]	cast( '[1, 2]' as jsonb) || cast( '[3]' as jsonb)	substr('[1, 2]', 1, length('[1, 2]') - 1) || '3]'
合并两个数组	array[1,2] || array[3, 4] 或 array_cat(array[1,2], array[3,4])	cast( '[1, 2]' as jsonb) || cast( '[3, 4]' as jsonb)	substr('[1, 2]', 1, length('[1, 2]') - 1) || '3, 4]'
从数组中删除值为2的数字元素	array_remove(ARRAY[1,2,3,2], 2)   结果： {1,3}	不支持，实现复杂	不支持，实现复杂
从数组中删除值为“b”的字符串元素	array_remove(ARRAY['a ', 'b', 'c'], 'b')   结果： ['a', 'c ']	Cast('["a", "b", "c"]' as jsonb) -'b'   结果： ['a', 'c ']	不支持，实现复杂

  

4.5.4 数组的比较 

下面是两种数据库中，常见的数组的比较方法：

 

	PostgreSQL 11		Oracle 19c
数组的类型	array 类型的数组	json 数组，类型为jsonb	json 数组，类型为varchar
比较数组是否相等	array[1,2,3,4] = array[1,2,3]	cast('[1, 2, 3, 4]' as jsonb) = cast('[1,2,3]' as jsonb)	json_equal('[1, 2, 3, 4]', '[1,2,3]')
判断左边的数组是否包含右边的数组	array[1,2,3,4] @> array[1,2]	cast('[1, 2, 3, 4]' as jsonb) @> cast('[1,2]' as jsonb)	不支持直接比较数组，需要把右边的数组拆分： json_exists('[1, 2, 3]','$?(@ == "1")') and json_exists('[1, 2, 3]','$?(@ == "2")')

 

4.5.5 其他数组功能

下面是一些常用其他的数组功能：

 

	PostgreSQL 11		Oracle 19c
数组的类型	array 类型的数组	json 数组，类型为jsonb	json 数组，类型为varchar
计算数组的元素个数	select array_length(array[1,2,3], 1)	select jsonb_array_length('[1, 2, 3, 4]')	SELECT count(*) FROM json_table('[1, 2, 3]', '$[*]' COLUMNS (value PATH '$'))
将不同的行聚合成一个数组	select array_agg(code) from tb_device where device_type = 'super';	select jsonb_agg(code) from tb_device where device_type = 'super';	select json_arrayagg(code) from tb_device where device_type = 'super';
将数组元素转成多个行	select unnest(array[1,2,3]);	select cast(jsonb_array_elements('[1,2,3]' as text);	SELECT value FROM json_table( '[1, 2, 3]' , '$[*]' COLUMNS (value PATH '$') )

 

从上面的例子可以看出，PostgreSQL 11 对 数组的支持要比Oracle多。