安装 orafce
orafce 是 PostgreSQL 的一个扩展，主要是为了在 PostgreSQL 中兼容 Oracle 的部分语法、数据类型、函数、字典表等，有了 orafce 可以对使用 Oracle 数据的应用程序更加方便的移植到 PostgreSQL 数据库上，尽可能的减少应用程序的代码改动量，从而简化了许多迁移工作量。
orafce 的源码地址：https://github.com/orafce/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)
与 Oracle 数据库兼容的特性
数据类型
| 数据类型 | 说明 |
|---|---|
| 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 | - |
使用 orafce 的注意事项
在上面章节测试 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
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
