由于Crunchy Data专注于Postgres，我们经常与那些希望减少对其他数据库依赖并实现数据栈现代化的人交谈。其中很大一部分是在Postgres上迁移和开发新应用。从Oracle数据库迁移的一个常用工具是Orafce，它模仿了PostgreSQL中的许多Oracle函数。使用Orafce这样的工具可以极大地帮助迁移过程。然而，从长远来看，慢慢地重构应用程序代码以直接与Postgres对话可能是一种很好的做法。
每个调用ora.function_name()的语句都会增加计算开销。对于任何给定的调用，这种开销通常都非常低。但是，在非常繁忙的数据库上使用它的查询数量可以通过删除这些函数获得一些性能改进。
本文是关于如何用Postgres函数替换Orafce函数的一组注释。为了找到最常用的函数，您可以参考您的日志，只替换您看到的最常用的函数。
以下记录我们发现的一些例子以及如何更新它们。
空语句
ora.nvl(MY_FIELD1,'N/A') ,
ora.nvl(character varying,character varying)
ora.nvl(numeric,numeric)
ora.nvl(timestamp without time zone,timestamp without time zone)
nvl()在 Oracle 中返回列表中的第一个非空值。
PostgreSQL 中的替换是微不足道的，只需使用 coalesce() 代替：
select coalesce(NULL,1,2);
coalesce
----------
1
(1 row)
select coalesce(NULL,'N/A');
coalesce
----------
N/A
(1 row)
请注意：
select coalesce('','N/A');
coalesce
----------
(1 row)
这意味着在 PostgreSQL''中不等于！NULL（参见上面的最后一个 URL/PDF）
因为空链文本不是NULL值......所以在这里，''返回而不是'N/A'！
更多信息： PostgreSQL 教程文章 和 PostgreSQL 文档。
特别警告：PostgreSQL 中的 NULL 是一个特殊值……可能会令 Oracle 开发人员和数据库管理员感到不安。 Bruce Momjian 的必读文章 是一个很好的起点。
数字
ora.round(numeric,integer)
这种东西用 PostgreSQL 替换是微不足道的：
select round('2.0034',1);
round
-------
2.0
(1 row)
在此示例中，数字数据类型由 PostgreSQL 假定。
借助特殊运算符，可以在 PostgreSQL 中的任何地方实现显式转换 ::type：
select round('2.0034'::numeric,1);
round
-------
2.0
(1 row)
select round('2.0034'::numeric,1)::integer;
round
-------
2
(1 row)
ora.round(interval,integer)
每个 Orafce 文档：
round(date, text) date - will round dates according to the specified format
ound(date '2005-07-12', 'yyyy') -> 2006-01-01
如果您只需要在您的应用程序中“从日期中提取一年”，请替换为 extract()：
select extract('year' from current_timestamp);
extract
---------
2022
(1 row)
此提取将给出确切的年份（在示例中，与Day 或Month也一起使用..）。但不会按照 Orafce 文档示例四舍五入到最近的年份。
为了实现这一点，如果您在业务案例中确实需要它，我们必须将age()日期与年初和年末进行比较。较小的年龄意味着“更接近下一年”，所以这是有效的：
select
case when
age('2022-12-31'::date, current_timestamp)
>
age(current_timestamp,'2022-01-01'::date)
then
extract('year' from current_timestamp)
else extract('year' from current_timestamp)+1
end as closest_year;
closest_year
--------------
2023
(1 row)
请注意 中的日期顺序age()>age()，如果您不这样排序，可能会导致 NEGATIVE 年龄，因此>不会按您预期的方式工作，因为负年龄总是与正年龄进行比较。
ora.to_number替代品
在日志中看到：
ora.to_number(interval)
ora.to_number(character varying)
ora.to_number(numeric)
根据 Orafce 文档::
oracle.to_number(text) - converts a string to a number
oracle.to_number(numeric) - converts a string to a number
oracle.to_number(numeric,numeric) - converts a string to a number
前两个是微不足道的：使用::type正确的类型，如果你不介意，numeric 可以解决任何数字：
select '12'::smallint;
int2
------
12
(1 row)
select '1202348'::smallint;
ERROR: value "1202348" is out of range for type smallint
select '1202348'::numeric;
numeric
---------
1202348
(1 row)
第三oracle.to_number(numeric,numeric)：我们假设它是关于精度的，因为在 Orafce 中没有太多记录......这可以通过以下方式实现ROUNDING：
select round('1202348.045'::numeric,2);
round
------------
1202348.05
(1 row)
如果您不想四舍五入，那么您也可以TRUNCATE针对不同的行为：
select trunc('1202348.045'::numeric,2);
trunc
------------
1202348.04
(1 row)
日期
ora.sysdate()替代品
在日志中看到：
ora.SYSDATE()
这对于 PostgreSQL 来说又是微不足道的，它被称为 current_date，如果您需要更高的精度，current_timestamp（服务器时区中的时间）：
select current_date;
current_date
--------------
2022-09-15
(1 row)
select current_timestamp;
current_timestamp
-----------------------------
2022-09-15 11:32:27.6182+02
(1 row)
将current_date（或任何日期）转换为时间戳将添加 00:00:00 小时：
select current_date::timestamp;
current_date
---------------------
2022-09-15 00:00:00
(1 row)
根据 Orafce 文档：
oracle.sysdate() - Returns statement timestamp at server timezone (orafce.timezone)
oracle.sysdate() -> 2015-12-09 17:47:56
正如您之前阅读的那样+2，时间戳上有这个：它显示时区。存储时区始终是最佳实践，但如果您想与提供 Orafce 的内容完美匹配，那么我们必须使用LOCALTIME和/或LOCALTIMESTAMP：
select localtime;
localtime
-----------------
11:37:58.731723
(1 row)
只给出服务器的本地时间，没有时区。
select localtimestamp;
localtimestamp
----------------------------
2022-09-15 11:38:19.591779
(1 row)
现在，为了完美匹配，我们需要告诉 PostgreSQL 我们根本不需要精度：
select localtimestamp(0);
localtimestamp
---------------------
2022-09-15 11:38:40
(1 row)
简而言之，在您的应用程序中，替换ora.SYSDATE()为localtimestamp(0).
只是添加了一个很酷的技巧：使用 PostgreSQL 可以很容易地计算时间戳 + 间隔：您可以使用以下命令向时间戳或日期添加（或删除）一些间隔：
select current_timestamp(0)+'1 day'::interval as tomorrow_same_hour;
tomorrow_same_hour
------------------------
2022-09-16 11:39:24+02
(1 row)
select current_timestamp(0)-'1 year'::interval as one_year_ago;
one_year_ago
------------------------
2021-09-15 11:39:40+02
(1 row)
select current_timestamp(0)-'1 year 3 days 15 minutes'::interval as one_year_ago_and_less;
one_year_ago_and_less
------------------------
2021-09-12 11:25:01+02
(1 row)
ora.to_date()替代品
在日志中看到：
ora.to_date('20220312','YYYYMMDD')
ora.to_date(character varying,character varying,character varying)
ora.to_date(days_keep_dm_transaction, 'YYYYMMDD')
用 PostgreSQL 替换这很简单：相同的函数和参数：
select to_date('20220312','YYYYMMDD');
to_date
------------
2022-03-12
(1 row)
无需在此处致电 Orafce。
ora.trunc替代品
在日志中看到：
ora.trunc(Event_Date,'DD')
ora.trunc(Event_Date,'HH')
ora.trunc(Event_Date,'MONTH')
ora.trunc(Event_Date + ora.numtodsinterval(1/24,'Days'),'HH')
ora.trunc(Event_Date + ora.numtodsinterval(1,'Days'),'DD')
根据 Orafce 文档::
trunc(date, text) date - truncate date according to the specified format
trunc(date '2005-07-12', 'iw') -> 2005-07-11
这里 PostgreSQL 中对应的函数是date_trunc. 在处理日期时，它接受一个参数作为日期/时间的模板模式（参见上面的链接），然后是一个时间戳，并返回一个时间戳。
所以首先要注意修改参数的顺序，这里是反过来的。
其次，明智地选择您需要的模板模式。
为了匹配返回日期的 Orafce 文档示例，只需将参数中给出的日期转换为时间戳，并转换函数的结果：
select date_trunc('week','2005-07-12'::timestamp)::date;
date_trunc
--------------
2005-07-11
(1 row)更多信息：
ora.add_months替代品
在日志中看到：
ora.trunc(ora.add_months(Dr.Event_Date,1),'MONTH')
只需将给定月份的间隔添加到您的日期或时间戳：
select localtimestamp+'1 month'::interval;
?column?
----------------------------
2022-10-15 11:43:12.417268
(1 row)
最后的想法
- Crunchy Data 支持客户迁移。我们不会止步于此，我们将与您合作，在您的数据库生命周期内实现长期性能改进。
- 如果您使用 Orafce 迁移到 Postgres，请考虑在应用程序代码中一次替换一个查询。
- 从您的日志开始，找到最常用的查询。这就是这项工作将产生最大影响的地方。
原文标题：Oracle to Postgres Post-Migration Improvements
原文作者：Jean-Paul Argudo
原文地址：https://www.crunchydata.com/blog/oracle-to-postgres-post-migration-improvements
