orafce 是 postgresql 的一个扩展，主要是为了在 postgresql 中兼容 oracle 的部分语法、数据类型、函数、字典表等，有了 orafce 可以对使用 oracle 数据的应用程序更加方便的移植到 postgresql 数据库上，尽可能的减少应用程序的代码改动量，从而简化了许多迁移工作量。
orafce 的源码地址：
[root@pgtest1 soft]# tar -xvf orafce-version_3_18_1.tar.gz
[root@pgtest1 soft]# cd orafce-version_3_18_1
[root@pgtest1 orafce-version_3_18_1]# make
[root@pgtest1 orafce-version_3_18_1]# make install
[root@pgtest1 orafce-version_3_18_1]# psql -c "create extension orafce;"
这样就安装完成了，但是需要注意时区问题，orafce 默认使用的是 gmt 时区，由参数单独的参数 orafce.timezone 控制，即使 timezone 参数设置为 prc ，执行 sysdate() 函数返回的时间也会与系统相差8小时。
[root@pgtest1 ~]# psql
postgres=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
sysdate | now | current_timestamp | clock_timestamp
--------------------- ------------------------------- ------------------------------- -----------------------------
2022-04-01 02:31:53 | 2022-04-01 10:31:52.596967 08 | 2022-04-01 10:31:52.596967 08 | 2022-04-01 10:31:52.5971 08
(1 row)
postgres=# show timezone;
timezone
----------
prc
(1 row)
postgres=# show orafce.timezone;
orafce.timezone
-----------------
gmt
(1 row)
调整参数 orafce.timezone
[root@pgtest1 ~]# vi $pgdata/postgresql.conf
log_timezone = 'prc'
timezone = 'prc'
orafce.timezone = 'prc'
[root@pgtest1 ~]# systemctl restart postgres-13.service
[root@pgtest1 ~]# psql
postgres=# show orafce.timezone;
orafce.timezone
-----------------
prc
(1 row)
postgres=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
sysdate | now | current_timestamp | clock_timestamp
--------------------- ------------------------------- ------------------------------- -------------------------------
2022-04-01 10:32:42 | 2022-04-01 10:32:41.543877 08 | 2022-04-01 10:32:41.543877 08 | 2022-04-01 10:32:41.543979 08
(1 row)
数据类型
| 数据类型 | 说明 |
|---|---|
| varchar2 | 可变长度字符数据类型 |
| nvarchar2 | 可变长度国家字符数据类型 |
| date | 存储日期和时间的数据类型 |
postgres=# set search_path="$user", public, oracle;
set
postgres=# create table tt (
postgres(# name varchar2(64) not null,
postgres(# status nvarchar2(2000),
postgres(# update_time date default (sysdate())
postgres(# );
create table
postgres=# \d tt
table "public.tt"
column | type | collation | nullable | default
------------- ----------------- ----------- ---------- -----------
name | varchar2(64) | | not null |
status | nvarchar2(2000) | | |
update_time | date | | | sysdate()
# 测试在 date 数据类型的字段上创建分区表
create table test_range(id serial, create_time date) partition by range(create_time);
create table test_range_20220301 partition of test_range for values from ('2022-03-01 00:00:00') to ('2022-03-02 00:00:00');
create table test_range_20220302 partition of test_range for values from ('2022-03-02 00:00:00') to ('2022-03-03 00:00:00');
create table test_range_20220303 partition of test_range for values from ('2022-03-03 00:00:00') to ('2022-03-04 00:00:00');
create table test_range_20220304 partition of test_range for values from ('2022-03-04 00:00:00') to ('2022-03-05 00:00:00');
create table test_range_20220305 partition of test_range for values from ('2022-03-05 00:00:00') to ('2022-03-06 00:00:00');
create table test_range_default partition of test_range default;
postgres=# \d test_range
partitioned table "public.test_range"
column | type | collation | nullable | default | storage | stats target | description
------------- --------- ----------- ---------- ---------------------------------------- --------- -------------- -------------
id | integer | | not null | nextval('test_range_id_seq'::regclass) | plain | |
create_time | date | | | | plain | |
partition key: range (create_time)
partitions: test_range_20220301 for values from ('2022-03-01') to ('2022-03-02'),
test_range_20220302 for values from ('2022-03-02') to ('2022-03-03'),
test_range_20220303 for values from ('2022-03-03') to ('2022-03-04'),
test_range_20220304 for values from ('2022-03-04') to ('2022-03-05'),
test_range_20220305 for values from ('2022-03-05') to ('2022-03-06'),
test_range_default default
# 向分区表中插入数据
postgres=# insert into test_range (create_time) values (sysdate());
insert 0 1
# 查询分区表里的数据
postgres=# select * from test_range;
id | create_time
---- -------------
2 | 2022-03-31
(1 row)
postgres=# select to_char(create_time,'yyyy-mm-dd hh24:mi:ss') from test_range;
to_char
---------------------
2022-03-31 00:00:00
(1 row)
为什么这里的 date 数据类型似乎只存储 ‘年月日’，而 oracle 的 date 数据类型会存储 ‘年月日 时分秒’。
这里就要说一下使用 orafce 的注意事项，详见后面章节<使用 orafce 的注意事项>。
支持 dual 表
postgres=# \d dual
view "public.dual"
column | type | collation | nullable | default | storage | description
-------- ------------------- ----------- ---------- --------- ---------- -------------
dummy | character varying | | | | extended |
view definition:
select 'x'::character varying as dummy;
postgres=# \dv public.*
list of relations
schema | name | type | owner
-------- -------------------- ------ ----------
public | dual | view | postgres
public | pg_stat_statements | view | postgres
(2 rows)
postgres=# select 1 from dual;
?column?
----------
1
(1 row)
postgres=# select * from dual;
dummy
-------
x
(1 row)
postgres=# select current_date "date" from dual;
date
------------
2022-04-01
sql 函数
- 数学函数
| 函数名称 | 说明 | 对比pg13 |
|---|---|---|
| bitand | performs a bitwise and operation | 增强 |
| cosh | calculates the hyperbolic cosine of a number | 自带 |
| sinh | calculates the hyperbolic sine of a number | 自带 |
| tanh | calculates the hyperbolic tangent of a number | 自带 |
- 字符串函数
| 函数名称 | 说明 | 对比pg13 |
|---|---|---|
| instr | returns the position of a substring in a string | 新增 |
| length | returns the length of a string in number of characters | 增强 |
| lengthb | returns the length of a string in number of bytes | 新增 |
| lpad | left-pads a string to a specified length with a sequence of characters | 增强 |
| ltrim | removes the specified characters from the beginning of a string | 增强 |
| nlssort | returns a byte string used to sort strings in linguistic sort sequence based on locale | 新增 |
| regexp_count | searches a string for a regular expression, and returns a count of the matches | 新增 |
| regexp_instr | returns the beginning or ending position within the string where the match for a pattern was located | 新增 |
| regexp_like | condition in the where clause of a query, causing the query to return rows that match the given pattern | 新增 |
| regexp_substr | returns the string that matches the pattern specified in the call to the function | 新增 |
| regexp_replace | replace substring(s) matching a posix regular expression | 增强 |
| rpad | right-pads a string to a specified length with a sequence of characters | 增强 |
| rtrim | removes the specified characters from the end of a string | 增强 |
| substr | extracts part of a string using characters to specify position and length | 增强 |
| substrb | extracts part of a string using bytes to specify position and length | 新增 |
- date/time 函数
| 函数名称 | 说明 | 对比pg13 |
|---|---|---|
| add_months | adds months to a date | 新增 |
| dbtimezone | returns the value of the database time zone | 新增 |
| last_day | returns the last day of the month in which the specified date falls | 新增 |
| months_between | returns the number of months between two dates | 新增 |
| next_day | returns the date of the first instance of a particular day of the week that follows the specified date | 新增 |
| round | rounds a date | 增强 |
| sessiontimezone | returns the time zone of the session | 新增 |
| sysdate | returns the system date | 新增 |
| trunc | truncates a date | 增强 |
- 数据类型格式化函数
| 函数名称 | 说明 | 对比pg13 |
|---|---|---|
| to_char | converts a value to a string | 增强 |
| to_date | converts a string to a date in accordance with the specified format | 增强 |
| to_multi_byte | converts a single-byte string to a multibyte string | 新增 |
| to_number | converts a value to a number in accordance with the specified format | 增强 |
| to_single_byte | converts a multibyte string to a single-byte string | 新增 |
- 条件表达式函数
| 函数名称 | 说明 | 对比pg13 |
|---|---|---|
| decode | compares values, and if they match, returns a corresponding value | 增强 |
| greatest | returns the greatest of the list of one or more expressions | 增强 |
| least | returns the least of the list of one or more expressions | 增强 |
| lnnvl | evaluates if a value is false or unknown | 新增 |
| nanvl | returns a substitute value when a value is not a number (nan) | 新增 |
| nvl | returns a substitute value when a value is null | 新增 |
| nvl2 | returns a substitute value based on whether a value is null or not null | 新增 |
- 聚合函数
| 函数名称 | 说明 | 对比pg13 |
|---|---|---|
| listagg | returns a concatenated, delimited list of string values | 新增 |
| median | calculates the median of a set of values | 新增 |
- 返回内部信息的函数
| 函数名称 | 说明 | 对比pg13 |
|---|---|---|
| dump | returns internal information of a value | 新增 |
sql 运算符
| 名称 | 说明 | 对比pg13 |
|---|---|---|
| datetime operator | datetime operator for the date type | - |
packages 包
| 名称 | 说明 | 对比pg13 |
|---|---|---|
| dbms_alert | sends alerts to multiple sessions | - |
| dbms_assert | validates the properties of an input value | - |
| dbms_output | sends messages to clients | - |
| dbms_pipe | creates a pipe for inter-session communication | - |
| dbms_random | generates random numbers | - |
| dbms_utility | provides various utilities | - |
| utl_file | enables text file operations | - |
在上面章节测试 date 数据类型时，发现一个问题，为什么这里的 date 数据类型似乎只存储 ‘年月日’，而 oracle 的 date 数据类型会存储 ‘年月日 时分秒’。
orafce 提供的兼容 oracle 的函数被定义在创建数据库集群时默认创建的 “public” 模式中，因此它们可供所有用户使用，无需特殊设置，但是需要确保 “public”（不带双引号）包含在 search_path 参数中指定的模式搜索路径列表中。
orafce 提供的以下功能在 postgresql 和 orafce 中使用不同的外部规范实现。在 postgresql 的默认配置中，postgresql 的标准特性优先。
使用不同的外部规范在 postgresql 和 orafce 中实现的功能
-
数据类型
-
function
postgresql 默认配置中不能使用的特性
- function
- sysdate
- dbtimezone
- sessiontimezone
- to_char (date/time value)
- operator
- datetime operator
要使用这些功能，请在 postgresql.conf 的 “search_path” 参数中设置 “oracle” 和 “pg_catalog”。执行此操作时，必须在 “pg_catalog” 之前指定 “oracle”。
search_path = '"$user", public, oracle, pg_catalog'
-
search_path 参数指定搜索模式的顺序。与 oracle 数据库兼容的每个特性都在 oracle 模式中定义。
-
建议在 postgresql.conf 中设置 search_path。在这种情况下，它将对每个实例都有效。
-
search_path 的配置可以在用户级别或数据库级别进行。设置示例如下所示。
-
如果 postgresql 的标准特性优先，并且不需要使用 postgresql 的默认配置不能使用的特性，则无需更改 search_path 的设置。
-
用户级别的设置示例
- 这可以通过执行 sql 命令来设置。在此示例中，user1 用作用户名。
alter user user1 set search_path = "$user",public,oracle,pg_catalog;
- 数据库级别设置示例
- 这可以通过执行 sql 命令来设置。在此示例中，db1 用作数据库名称。
- 必须在 “pg_catalog” 之前指定 “oracle”。
alter database db1 set search_path = "$user",public,oracle,pg_catalog;
再次测试 date 数据类型，看看是否真的支持存储 ‘年月日 时分秒’
# 修改参数 search_path ，必须在 "pg_catalog" 之前指定 "oracle"
[root@pgtest1 ~]# vi $pgdata/postgresql.conf
search_path = '"$user", public, oracle, pg_catalog'
[root@pgtest1 ~]# systemctl restart postgres-13.service
drop table test_range;
create table test_range(id serial, create_time date) partition by range(create_time);
create table test_range_20220301 partition of test_range for values from ('2022-03-01 00:00:00') to ('2022-03-02 00:00:00');
create table test_range_20220302 partition of test_range for values from ('2022-03-02 00:00:00') to ('2022-03-03 00:00:00');
create table test_range_20220303 partition of test_range for values from ('2022-03-03 00:00:00') to ('2022-03-04 00:00:00');
create table test_range_20220304 partition of test_range for values from ('2022-03-04 00:00:00') to ('2022-03-05 00:00:00');
create table test_range_20220305 partition of test_range for values from ('2022-03-05 00:00:00') to ('2022-03-06 00:00:00');
create table test_range_default partition of test_range default;
postgres=# \d test_range
partitioned table "public.test_range"
column | type | collation | nullable | default | storage | stats target | description
------------- --------- ----------- ---------- ---------------------------------------- --------- -------------- -------------
id | integer | | not null | nextval('test_range_id_seq'::regclass) | plain | |
create_time | date | | | | plain | |
partition key: range (create_time)
partitions: test_range_20220301 for values from ('2022-03-01 00:00:00') to ('2022-03-02 00:00:00'),
test_range_20220302 for values from ('2022-03-02 00:00:00') to ('2022-03-03 00:00:00'),
test_range_20220303 for values from ('2022-03-03 00:00:00') to ('2022-03-04 00:00:00'),
test_range_20220304 for values from ('2022-03-04 00:00:00') to ('2022-03-05 00:00:00'),
test_range_20220305 for values from ('2022-03-05 00:00:00') to ('2022-03-06 00:00:00'),
test_range_default default
# 这里就看出了不一样，partitions 中 from 和 to 的时间有 '时分秒' 了('2022-03-01 00:00:00')。
# 向分区表中插入数据
postgres=# insert into test_range (create_time) values (sysdate());
insert 0 1
# 查询分区表里的数据，显示的和 oracle 一样了
postgres=# select * from test_range;
id | create_time
---- ---------------------
1 | 2022-04-01 10:08:18
(1 row)
最后修改时间：2022-04-02 11:34:09
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。