将 Oracle 用户迁移到 Cloud SQL for PostgreSQL：查询、存储过程、函数和触发器

本文档是系列文章中的一篇，该系列提供了有关规划和执行 Oracle® 11g/12c 数据库到 Cloud SQL for PostgreSQL 版本 12 的迁移的关键信息和指导。除了设置简介部分之外，本系列文章还包括以下部分：

查询

Oracle 和 Cloud SQL for PostgreSQL 支持 ANSI SQL 标准。因此，通常只需通过仅使用基本语法元素即可迁移 SQL 语句（例如，不指定任何标量函数或任何其他 Oracle 扩展功能）。以下部分讨论了常见的 Oracle 查询元素及其对应的 Cloud SQL for PostgreSQL 等效元素。

基本的 SELECT 和 FROM 语法

Oracle 功能名称或语法名称 Oracle 概览或实现 Cloud SQL for PostgreSQL 支持 Cloud SQL for PostgreSQL 的对应或替代解决方案
用于数据检索的 SQL 基本语法 SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY 		是 SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT
（用于显示输出内容）		 SELECT 1 FROM DUAL 是 SELECT 1
列别名 SELECT COL1 AS C1 是 SELECT COL1 AS C1
OR
SELECT COL1 C1
表名称区分大小写 不区分大小写
（例如，表名称可以是 orders 和/或 ORDERS）。		 是 除非另有说明，否则名称不区分大小写（例如，orders 和 ORDERS 被视为相同，而 "orders" 和 "ORDERS" 被视为不同）

如需详细了解 Cloud SQL for PostgreSQL 的 SELECT 语法，请参阅文档。

内嵌视图

  • 内嵌视图（也称为“派生表”）是 SELECT 语句，位于 FROM 子句中且用作子查询。
  • 内嵌视图可以通过移除复合计算或消除联接运算来帮助简化复杂查询，同时将多个单独的查询压缩为单个简化的查询。
  • 转换说明：Oracle 内嵌视图不需要使用别名，而 Cloud SQL for PostgreSQL 需要为每个内嵌视图提供特定的别名。

下表以内嵌视图的形式显示从 Oracle 转换为 Cloud SQL for PostgreSQL 的示例。

Oracle 11g/12c Cloud SQL for PostgreSQL 12
SQL> SELECT FIRST_NAME,
DEPARTMENT_ID,
SALARY,
DATE_COL
FROM EMPLOYEES,
(SELECT SYSDATE AS DATE_COL FROM DUAL);

输出类似于以下内容：

FIRST_NAME DEPARTMENT_ID SALARY DATE_COL
-------------------- ------------- ---------- ---------
Steven 90 24000 30-JUL-19
Neena 90 17000 30-JUL-19
Lex 90 17000 30-JUL-19

没有内嵌视图别名：

postgres=> SELECT FIRST_NAME,
postgres-> DEPARTMENT_ID,
postgres-> SALARY,
postgres-> DATE_COL
postgres-> FROM EMPLOYEES, (SELECT NOW() AS DATE_COL);

ERROR: subquery in FROM must have an alias
LINE 5: FROM EMPLOYEES, (SELECT NOW() AS DATE_COL);

HINT: For example, FROM (SELECT ...) [AS] foo.

向内嵌视图添加别名：

postgres=> SELECT FIRST_NAME,
postgres-> DEPARTMENT_ID,
postgres-> SALARY,
postgres-> DATE_COL
postgres-> FROM EMPLOYEES, (SELECT NOW() AS DATE_COL) AS C1;

输出类似于以下内容：

first_name | department_id | salary | date_col
-------------+---------------+----------+--------------------------------
Steven | 90 | 24000.00 | 10/16/2020 08:35:18.470089 UTC
Neena | 90 | 17000.00 | 10/16/2020 08:35:18.470089 UTC
Lex | 90 | 17000.00 | 10/16/2020 08:35:18.470089 UTC

JOIN 语句

Cloud SQL for PostgreSQL JOIN 语句支持 Oracle JOIN 语句。但是，Cloud SQL for PostgreSQL 不支持使用 Oracle 联接运算符 (+)。如需取得相同的结果，您需要转换为外部联接的标准 SQL 语法。

下表显示了 JOIN 转换示例。

Oracle JOIN 类型 受 Cloud SQL for PostgreSQL 支持 Cloud SQL for PostgreSQL JOIN 语法
INNER JOIN 是 SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
CROSS JOIN 是 SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E CROSS JOIN DEPARTMENTS D;
FULL JOIN [ OUTER ] 是 SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
LEFT JOIN [ OUTER ] 是 SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
RIGHT JOIN [ OUTER ] 是 SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
SUBQUERY 是 SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN (SELECT * FROM DEPARTMENTS)D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

UNION、UNION ALL、INTERSECT、MINUS

Cloud SQL for PostgreSQL 支持 Oracle UNION、UNION、ALL 和 INTERSECT 运算符。不支持 MINUS 运算符。但是，Cloud SQL for PostgreSQL 实现了 EXCEPT 运算符，它与 Oracle 中的 MINUS 运算符等效。此外，Cloud SQL for PostgreSQL 支持 Oracle 不支持的 INTERSECT ALL 和 EXCEPT ALL 运算符。

  • UNION：附加两个或更多 SELECT 语句的结果集并消除重复记录。
  • UNION ALL：附加两个或更多 SELECT 语句的结果集，不消除重复记录。
  • INTERSECT：仅当两个数据集中都存在某条记录时，才返回两个或更多 SELECT 语句的交集。 系统不会消除重复记录。
  • INTERSECT ALL（仅限 Cloud SQL for PostgreSQL）：仅当两个数据集中都存在某条记录时，才返回两个或更多 SELECT 语句的交集。
  • Cloud SQL for PostgreSQL 中的 MINUS (EXCEPT：比较两个或更多 SELECT 语句，仅返回第一个查询中未由其他语句返回的不同行。
  • EXCEPT ALL（仅限 Cloud SQL for PostgreSQL）：比较两个或更多 SELECT 语句，仅返回第一个查询中未由其他语句返回的唯一行，而不消除重复记录。

转换说明

从 Oracle MINUS 运算符转换为 Cloud SQL for PostgreSQL 时，请改用 EXCEPT 运算符。

示例

Oracle 函数 Oracle 实现 Cloud SQL for PostgreSQL 支持 Cloud SQL for PostgreSQL 的对应或替代解决方案
UNION SELECT COL1 FROM TBL1
UNION
SELECT COL1 FROM TBL2 		是 SELECT COL1 FROM TBL1
UNION
SELECT COL1 FROM TBL2
UNION ALL SELECT COL1 FROM TBL1
UNION ALL
SELECT COL1 FROM TBL2 		是 SELECT COL1 FROM TBL1
UNION ALL
SELECT COL1 FROM TBL2
INTERSECT SELECT COL1 FROM TBL1
INTERSECT
SELECT COL1 FROM TBL2 		是 SELECT COL1 FROM TBL1
INTERSECT
SELECT COL1 FROM TBL2
MINUS SELECT COL1 FROM TBL1
MINUS
SELECT COL1 FROM TBL2 		是（在 PostgreSQL 中从 Convert MINUS 转换到 EXCEPT） SELECT COL1 FROM TBL1
EXCEPT
SELECT COL1 FROM TBL2

标量（单行）和分组函数

Cloud SQL for PostgreSQL 提供了一个详尽的标量（单行）和聚合函数列表。一些 Cloud SQL for PostgreSQL 函数与其对应的 Oracle 函数类似（通过名称和功能，或使用不同的名称但功能类似）。虽然 Cloud SQL for MySQL 函数在名称上可以与其对应的 Oracle 函数相同，但它们有时候可以展示不同的功能。

下表按名称和功能描述了 Oracle 和 Cloud SQL for PostgreSQL 等效的情况（由“是”指明），以及建议转换的情况（除了“是”以外的所有情况）。

字符函数
Oracle 函数 Oracle 函数规范或实现 Cloud SQL for PostgreSQL 等效项 Cloud SQL for PostgreSQL 的对应函数 Cloud SQL for PostgreSQL 函数规范或实现
CONCAT 返回 str1 与 str1 的串联结果：

CONCAT('A', 1) = A1		 是 CONCAT 等效于 Oracle：

CONCAT('A', 1) = A1
LOWER/UPPER 返回 char，所有字母一律小写或一律大写：

LOWER('SQL') = sql		 是 LOWER/UPPER 等效于 Oracle：

LOWER('SQL') = sql
LPAD/RPAD 返回 expr1，它是使用 expr1 中的字符序列向左或向右填充 expr1 个字符后得到的结果：

LPAD('A',3,'*') = **A		 是 LPAD/RPAD 等效于 Oracle：

LPAD('A',3,'*') = **A
SUBSTR 返回 char 的一部分，从字符位置开始，子字符串长度为：


SUBSTR('PostgreSQL', 8, 3)
= SQL		 部分 SUBSTR 当起始位置是正数时，等效于 Oracle。

SUBSTR('PostgreSQL', 8, 3)
= SQL
在 Oracle 中将负数提供为起始位置时，它会从字符串末尾执行子字符串操作，这与 Cloud SQL for PostgreSQL 不同。如果需要 Oracle 的行为，请使用 RIGHT 函数作为替代项。
INSTR 返回某一字符串在给定字符串中的位置（索引）：

INSTR('PostgreSQL', 'e')
= 7 		否 不适用 Cloud SQL for PostgreSQL 没有内置的 instr 函数。可使用 PL/pgSQL 实现与 Oracle 兼容的 instr 函数。
REPLACE 返回给定字符中出现的每一个搜索字符串均替换为替换字符串后的字符：


REPLACE('ORADB', 'ORA', 'PostgreSQL')
= PostgreSQLDB		 部分 REPLACE 替换字符串参数在 Oracle 中是可选参数，但在 Cloud SQL for PostgreSQL 中是必需参数。如果省略此参数，则 Oracle 将移除所有出现的搜索字符串。在 Cloud SQL for PostgreSQL 中，可以通过提供空字符串作为替代字符串来实现相同行为。

REPLACE('ORADB', 'ORA', 'PostgreSQL')
= PostgreSQLDB
TRIM 去除字符串的开头和/或结尾字符：

TRIM(both '-' FROM '-PostgreSQL-')
= PostgreSQL
TRIM(' PostgreSQL ')
= PostgreSQL 		是 TRIM 等效于 Oracle：

TRIM(both '-' FROM '-PostgreSQL-')
= PostgreSQL
TRIM(' PostgreSQL ')
= PostgreSQL
LTRIM/RTRIM 从字符串的左侧或右侧移除搜索中出现的所有字符：


LTRIM(' PostgreSQL', ' ')
= PostgreSQL		 是 LTRIM/RTRIM 等效于 Oracle：

LTRIM(' PostgreSQL', ' ') = PostgreSQL
ASCII 返回 char 的第一个字符的数据库字符集中的十进制表示法：

ASCII('A') = 65		 是 ASCII 等效于 Oracle：

ASCII('A') = 65
CHR 将 ASCII 码值（介于 0 到 225 之间的数字值）转换为字符：

CHR(65) = A		 是 CHAR 等效于 Oracle：

CHR(65) = A
LENGTH 返回给定字符串的长度：

LENGTH ('PostgreSQL') = 10		 是 LENGTH 等效于 Oracle：

LENGTH ('PostgreSQL') = 10
REGEXP_REPLACE 在字符串中搜索正则表达式模式：

REGEXP_REPLACE('John', '[hn].', '1') = Jo1		 是 REGEXP_REPLACE 等效于 Oracle：

REGEXP_REPLACE('John', '[hn].', '1') = Jo1
REGEXP_SUBSTR 通过在字符串中搜索正则表达式模式来扩展 SUBSTR 函数的功能：


REGEXP_SUBSTR('https://console.cloud.google.com/sql/instances','https://([[:alnum:]]+\.?){3,4}/?')
= https://console.cloud.google.com/		 否 不适用 使用 PostgreSQL 的 REGEXP_MATCH 实现类似功能。
REGEXP_COUNT 返回模式在源字符串中出现的次数。 否 不适用 使用 PostgreSQL REGEXP_MATCH 实现类似功能。
REGEXP_INSTR 在字符串位置 (index) 中搜索正则表达式模式。
 否 不适用 将此功能转换为应用层。
REVERSE 返回倒序字符串。

REVERSE('PostgreSQL') = LQSergtsoP 		是 REVERSE 等效于 Oracle：

REVERSE('PostgreSQL') = LQSergtsoP
数值函数
Oracle 函数 Oracle 函数规范或实现 Cloud SQL for PostgreSQL 等效项 Cloud SQL for PostgreSQL 的对应函数 Cloud SQL for PostgreSQL 函数规范或实现
ABS n 的绝对值：

ABS(-4.6) = 4.6		 是 ABS 等效于 Oracle：

ABS(-4.6) = 4.6
CEIL 返回大于或等于 n 的最小整数：

CEIL(21.4) = 22		 是 CEIL 等效于 Oracle：

CEIL(21.4) = 22
FLOOR 返回等于或小于 n 的最大整数：

FLOOR(-23.7) = -24		 是 FLOOR 等效于 Oracle：

FLOOR(-23.7) = -24
MOD 返回 m 除以 n 所得的余数：

MOD(10, 3) = 1 		是 MOD 等效于 Oracle：

MOD(10, 3) = 1
ROUND 返回四舍五入到小数点右侧某一整数位的 n：

ROUND(1.39, 1) = 1.4		 是 ROUND 等效于 Oracle：

ROUND(1.39, 1) = 1.4
TRUNC
（数值）		 返回 n1 截断到 n2 个小数位的结果：

TRUNC(99.999) = 99
TRUNC(99.999, 0) = 99		 是 TRUNCATE
（数值）		 等效于 Oracle：

TRUNC(99.999) = 99
TRUNC(99.999, 0) = 99
日期时间函数
Oracle 函数 Oracle 函数规范或实现 Cloud SQL for PostgreSQL 等效项 Cloud SQL for PostgreSQL 的对应函数 Cloud SQL for PostgreSQL 函数规范或实现
SYSDATE 返回针对数据库服务器所在操作系统设置的当前日期和时间：

SELECT SYSDATE FROM DUAL
= 31-JUL-2019		 部分等效；函数名称和格式不同 CURRENT_TIMESTAMP CURRENT_TIMESTAMP 将返回与 Oracle SYSDATE 函数不同的日期时间格式：

SELECT CURRENT_TIMESTAMP
= 2019-07-31 06:46:40.171477+00
SYSTIMESTAMP 返回系统日期，包括小数秒和时区：

SELECT SYSTIMESTAMP FROM DUAL
= 01-JAN-19 07.37.11.622187000 AM +00:00		 部分等效；函数名称不同 CURRENT_TIMESTAMP Cloud SQL for PostgreSQL 与 Oracle 返回的日期/时间格式不同。日期格式必须与原始日期/时间格式匹配：

SELECT CURRENT_TIMESTAMP
= 2019-01-31 07:37:11.622187+00
LOCAL TIMESTAMP 以数据类型 TIMESTAMP 的值返回会话时区的当前日期和时间：

SELECT LOCALTIMESTAMP FROM DUAL
= 01-JAN-19 10.01.10.123456 PM		 部分等效；日期/时间格式不同 LOCAL TIMESTAMP Cloud SQL for PostgreSQL 与 Oracle 返回的日期/时间格式不同。日期格式必须与原始日期/时间格式匹配：

SELECT LOCALTIMESTAMP
= 2019-01-31 07:37:11.622187+00
CURRENT_DATE 返回会话时区的当前日期：

SELECT CURRENT_DATE FROM DUAL
= 31-JAN-19		 部分等效；日期/时间格式不同 CURRENT_ DATE Cloud SQL for PostgreSQL 与 Oracle 返回的日期/时间格式不同。日期格式必须与原始日期/时间格式匹配：

SELECT CURRENT_DATE
= 2019-01-31
CURRENT_TIMESTAMP 返回会话时区的当前日期和时间：

SELECT CURRENT_TIMESTAMP FROM DUAL
= 31-JAN-19 06.54.35.543146 AM +00:00		 部分等效；日期时间格式不同 CURRENT_TIMESTAMP Cloud SQL for PostgreSQL 与 Oracle 返回的日期时间格式不同。日期格式必须与原始日期时间格式匹配：

SELECT CURRENT_TIMESTAMP FROM DUAL
= 2019-01-31 07:37:11.622187+00s
ADD_MONTHS 返回日期加整数月数：

ADD_MONTHS(SYSDATE, 1)
= 31-JAN-19 		否 不适用 如需在 Cloud SQL for PostgreSQL 中实现相同的功能，请使用 + / - 运算符并指定时间间隔：

SELECT CURRENT_TIMESTAMP + INTERVAL '1 MONTH'
= 2019-01-31 07:37:11.622187+00s
EXTRACT
（日期部分）		 从日期/时间或时间间隔表达式中返回指定日期/时间字段的值：

EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019		 是 EXTRACT
（日期部分）		 等效于 Oracle：

EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019
LAST_DAY 返回包含指定日期的月份的最后一天：

LAST_DAY('01-JAN-2019')
= 31-JAN-19		 否 不适用 如需解决此问题，请使用 DATE_TRUNC 和 + 运算符来计算当月的最后一天。日期格式必须与原始日期/时间格式匹配：

SELECT DATE_TRUNC('MONTH', DATE '01-JAN-2019') + INTERVAL '1 MONTH -1 DAY'
= 2019-01-31
MONTH_BETWEEN 返回日期 date1 和 date2 之间的月数：
MONTHS_BETWEEN(SYSDATE, SYSDATE-60)
= 1.96
 部分等效；

函数的日期时间格式不同		 AGE Cloud SQL for PostgreSQL AGE 函数返回两个时间戳之间的时间间隔：

AGE(DATE '01-JAN-2019', DATE '01-JAN-2019' - 60)
= 1 mon 29 days
如需实现与 Oracle MONTH_BETWEEN 函数相同的值，需要进行更具体的转换。
TO_CHAR（日期/时间） 将日期时间或时间戳数据类型转换为 VARCHAR2 数据类型的值（采用日期格式所指定的格式）：


TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS')
= 01-01-2019 10:01:01		 是 To_CHAR 等效于 Oracle：

TO_CHAR(CURRENT_TIMESTAMP,'DD-MM-YYYY HH24:MI:SS');
01-01-2019 10:01:01
编码和解码函数
Oracle 函数 Oracle 函数规范或实现 Cloud SQL for PostgreSQL 等效项 Cloud SQL for PostgreSQL 的对应函数 Cloud SQL for PostgreSQL 函数规范或实现
DECODE 使用 IF-THEN-ELSE 语句将表达式与每个搜索值逐一进行比较。 否 CASE 使用 Cloud SQL for PostgreSQL CASE 语句来实现类似功能。
DUMP 返回一个 VARCHAR2 值，此值包含表达式的数据类型代码、长度（字节数）和内部表示。 否 不适用 不受支持。
ORA_HASH 计算给定表达式的哈希值。 否 MD5 / SHA224 / SHA256 / SHA385 / SHA512 使用 Cloud SQL for PostgreSQL MD5 函数处理 128 位校验和，或使用 SHA 函数处理 160 位
校验和以便生成哈希值。
转换函数
Oracle 函数 Oracle 函数规范或实现 Cloud SQL for PostgreSQL 等效项 Cloud SQL for PostgreSQL 的对应函数 Cloud SQL for PostgreSQL 函数规范或实现
CAST 将一个内置数据类型或集合类型值转换为另一个内置数据类型或集合类型值：

CAST('1' as int) + 1
= 2		 部分 CAST Cloud SQL for PostgreSQL CAST 函数类似于 Oracle 的 CAST 功能，但在某些情况下，必须根据两个数据库的数据类型差异进行调整：

CAST('1' as int) + 1
= 2
CONVERT 将字符串从一个字符集转换为另一个字符集：

CONVERT ( 'Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')
= ?? ?? ?? A B C		 部分 CONVERT Cloud SQL for PostgreSQL CONVERT 函数返回 bytea 值（一个二进制字符串），而不是 VARCHAR 或 TEXT。PostgreSQL 支持的字符集也与 Oracle 不同。

CONVERT('Ä Ê Í Õ Ø A B C D E', 'UTF8', 'LATIN1')
= [Binary representation of the string in LATIN1 encoding]
TO_CHAR
(string/numeric)		 该函数会将数字或日期转换为字符串：

TO_CHAR(22.73,'$99.9')
= $22.7		 部分 TO_CHAR Cloud SQL for PostgreSQL TO_CHAR 函数的功能与 Oracle 类似。Cloud SQL for PostgreSQL 支持略有不同的格式字符串列表。默认情况下，Cloud SQL for PostgreSQL 会为符号预留一列，因此在正数前面会有一个空格。可以使用 FM 前缀禁止此问题：

TO_CHAR(22.73,'FM$99.9')
= $22.7
TO_DATE Oracle TO_DATE 函数会按来源特定的日期/时间格式将字符串转换为日期：

TO_DATE('2019/01/01', 'yyyy-mm-dd')
= 01-JAN-2019		 部分 TO_DATE Cloud SQL for PostgreSQL TO_DATE 函数的功能与 Oracle 类似。Cloud SQL for PostgreSQL 支持略有不同的格式字符串列表：

TO_DATE('2019/01/01', 'yyyy-mm-dd')
= 2019-01-01
TO_NUMBER 将表达式转换为 NUMBER 数据类型的值：

TO_NUMBER('01234')
= 1234		 部分 TO_NUMBER Cloud SQL for PostgreSQL TO_NUMBER 函数需要将格式化字符串作为输入，而它在 Oracle 中是可选的：

TO_NUMBER('01234','99999')
= 1234
另一种方法是对不需要复杂格式字符串的转化使用 CAST 函数：

CAST('01234' AS NUMERIC)
= 1234
条件式 SELECT 函数
Oracle 函数 Oracle 函数规范或实现 Cloud SQL for PostgreSQL 等效项 Cloud SQL for PostgreSQL 的对应函数 Cloud SQL for PostgreSQL 函数规范或实现
CASE CASE 语句会从一系列条件中进行选择，并使用以下语法运行相应的语句：


CASE WHEN condition THEN result [WHEN ...] [ELSE result] END		 是 CASE 等效于 Oracle：

CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
Null 函数
Oracle 函数 Oracle 函数规范或实现 Cloud SQL for PostgreSQL 等效项 Cloud SQL for PostgreSQL 的对应函数 Cloud SQL for PostgreSQL 函数规范或实现
COALESCE 返回表达式列表中的第一个非 null 表达式：

COALESCE(null, '1', 'a')
= a		 是 COALESCE 等效于 Oracle：

COALESCE(null, '1', 'a')
= 1
NULLIF 比较 expr1expr1 和 expr2expr1。如果它们相等，则函数会返回 null。如果它们不相等，则函数会返回 expr1：expr1

NULLIF('1', '2')
= 1		 是 NULLIF 等效于 Oracle：

NULLIF('1', '2')
= 1
NVL 在查询结果中将 null（返回为空）替换为字符串：

NVL(null, 'a')
= a		 否 COALESCE 您可以使用 COALESCE 函数：

COALESCE(null, 'a')
= a
NVL2 根据指定的表达式是 null 还是非 null 确定
查询返回的值。		 否 COALESCE 您可以使用 COALESCE 函数：

COALESCE(null, 1, 'a')
= 1
环境和标识符函数
Oracle 函数 Oracle 函数规范或实现 Cloud SQL for PostgreSQL 等效项 Cloud SQL for PostgreSQL 的对应函数 Cloud SQL for PostgreSQL 函数规范或实现
SYS_GUID 生成并返回由 16 个字节组成的全局唯一标识符（RAW 值）：


SELECT SYS_GUID() FROM DUAL
= 8EFA4A31468B4C6DE05011AC0200009E		 部分等效；函数名称和格式不同 UUID_GENERATE_V4 CloudSQL for Cloud SQL for PostgreSQL 支持 uuid-ossp 扩展，它提供了用于生成函数（例如 UUID_GENERATE_V4）的 UUID 列表：

SELECT UUID_GENERATE_v4()
= eaf356a6-2847-478d-af3b-6883f5ac6af2
UID 返回一个整数，该整数唯一标识会话用户
（已登录的用户）：

SELECT UID FROM DUAL
= 43		 否 不适用 不适用
USER 返回当前会话用户名：

SELECT USER FROM DUAL
= UserName		 是 USER 等效于 Oracle：

SELECT USER;
= postgres
USERENV 返回当前用户会话和当前参数配置的相关信息：

SELECT USERENV('LANGUAGE') FROM DUAL
= ENGLISH_AMERICA.AL32UTF8		 否 不适用 Cloud SQL for PostgreSQL 中没有等效的 USERENV 函数，但可以使用 PG_BACKGROUND_PID() 等系统信息函数检索 USERENV('SID') 等各个参数。
ROWID Oracle 服务器会为每个表的每一行分配唯一的 ROWID，以标识表中的行。ROWID 是数据对象编号、行的数据块、行位置以及数据文件所在行的地址。 部分等效；函数名称不同 ctid Cloud SQL for PostgreSQL 中的 ctid 可识别行版本在其表中的实际位置，此功能类似于 Oracle 的 ROWID。
ROWNUM 返回一个数字，该数字表示 Oracle 从表或联接表中选择行的顺序。 否 LIMIT or ROW_NUMBER() Cloud SQL for PostgreSQL 支持将 LIMIT 和 OFFSET 用于类似目的，而不是限制使用 ROWNUM 的查询返回的结果数。

ROW_NUMBER() 窗口函数在其他场景中能够临时替换 Oracle 的 ROWNUM。但必须先考虑结果排序和性能增量，然后再改用它。
聚合（分组）函数
Oracle 函数 Oracle 函数规范或实现 Cloud SQL for PostgreSQL 等效项 Cloud SQL for PostgreSQL 的对应函数 Cloud SQL for PostgreSQL 函数规范或实现
AVG 返回列或表达式的平均值。 是 AVG 等效于 Oracle
COUNT 返回查询所返回的行数。 是 COUNT 等效于 Oracle
COUNT (DISTINCT) 返回列或表达式中唯一值的数量。 是 COUNT
(DISTINCT) 		等效于 Oracle
MAX 返回列或表达式的最大值。 是 MAX 等效于 Oracle
MIN 返回列或表达式的最小值。 是 MIN 等效于 Oracle
SUM 返回列或表达式的值的总和。 是 SUM 等效于 Oracle
LISTAGG 通过串联测量列的值，按 ORDER BY 子句中指定的一行显示每组中的数据：

SELECT LISTAGG(
DEPARTMENT_NAME, ', ')
WITHIN GROUP
(ORDER BY DEPARTMENT_NAME) DEPT
FROM DEPARTMENTS;

-- Single line results
= Accounting, Administration, Benefits, Construction		 否 STRING_AGG 使用 Cloud SQL for PostgreSQL STRING_AGG 函数返回与 Oracle 类似的结果，除了语法在某些情况下有所不同：

SELECT STRING_AGG(DEPARTMENT_NAME, ', ' order by DEPARTMENT_NAME) FROM DEPARTMENTS;

-- Single line results
= Accounting, Administration, Benefits, Construction
Oracle 12c Fetch
Oracle 函数 Oracle 函数规范或实现 Cloud SQL for PostgreSQL 等效项 Cloud SQL for PostgreSQL 的对应函数 Cloud SQL for PostgreSQL 函数规范或实现
FETCH 从多行查询的结果集中检索数据行：

SELECT * FROM
EMPLOYEES
FETCH FIRST 10 ROWS ONLY;		 否 LIMIT 使用 Cloud SQL for PostgreSQL LIMIT 子句仅检索一组特定的记录：

SELECT * FROM EMPLOYEES LIMIT 10;

基本过滤、运算符和子查询

在转换期间，基本过滤、运算符函数和子查询相对简单，几乎不需要执行额外的操作。

转换说明

检查日期格式并解决相关问题，因为 Oracle 和 Cloud SQL for PostgreSQL 格式会返回不同的默认结果：

  • Oracle SYSDATE 函数默认返回 01-AUG-19。
  • PostgreSQL CURRENT_DATE 函数默认返回 2019-08-01（即使设置了格式，也不返回时间）。如需检索当前日期和时间，请使用 CURRENT_TIMESTAMP 函数，该函数默认返回 2019-08-01 00:00:00.000000+00。
  • 您可以使用 Cloud SQL for PostgreSQL TO_CHAR 函数设置日期和时间格式。
Oracle 函数或子查询 Cloud SQL for PostgreSQL 等效项 Cloud SQL for PostgreSQL 对应的函数或子查询 Cloud SQL for PostgreSQL 函数规范或实现
EXISTS/ NOT EXISTS 是 EXISTS/ NOT EXISTS SELECT * FROM DEPARTMENTS D
WHERE EXISTS (SELECT 1
FROM EMPLOYEES E
WHERE
E.DEPARTMENT_ID =
D.DEPARTMENT_ID);
IN/NOT IN 是 IN/NOT IN SELECT * FROM DEPARTMENTS D
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID
FROM EMPLOYEES E);

-- OR
SELECT * FROM EMPLOYEES
WHERE (EMPLOYEE_ID, DEPARTMENT_ID)
IN((100, 90));
LIKE/NOT LIKE 是 LIKE/NOT LIKE SELECT * FROM EMPLOYEES
WHERE FIRST_NAME LIKE '_e_n%';
BETWEEN/ NOT BETWEEN 是 BETWEEN/
NOT BETWEEN 		SELECT * FROM EMPLOYEES
WHERE EXTRACT(YEAR FROM HIRE_DATE)
NOT BETWEEN 2001 and 2004;
AND/OR 是 AND/OR SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(100, 101)
AND (SALARY >= 1000 OR HIRE_DATE <= '2006-02-05');
SubQuery 是 SubQuery Cloud SQL for PostgreSQL 支持在 SELECT 级别、针对 JOIN 语句以及针对 WHERE/AND 子句中的过滤操作使用子查询：

-- SELECT SubQuery
SELECT D.DEPARTMENT_NAME,
(SELECT AVG(SALARY) AS AVG_SAL
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID =
D.DEPARTMENT_ID) AVG_SAL
FROM DEPARTMENTS D;

-- JOIN SubQuery
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES E JOIN
(SELECT *
FROM DEPARTMENTS
WHERE LOCATION_ID = 2700) D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- Filtering Subquery
SELECT FIRST_NAME,
LAST_NAME,
SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
FROM EMPLOYEES);
运算符 是 运算符 Cloud SQL for PostgreSQL 支持所有基本运算符：

> | >= | < | <= | = | <> | !=

分析函数（或窗口函数和排名函数）

通过提供基于一组行（例如 RANK()、ROW_NUMBER()、FIRST_VALUE()）来计算聚合值的功能，Oracle 分析函数可以扩展标准 SQL 操作的功能。这些函数适用于单个查询表达式范围内按逻辑划分的记录。分析函数通常用于数据仓储，以及与业务智能报告和分析配合使用。

转换说明

Cloud SQL for PostgreSQL 支持许多分析函数，这些函数在 Postgres 中称为聚合函数和窗口函数。如果您的应用使用不太常见的函数且在 Postgres 中不受支持，您将需要查找受支持的扩展程序或将逻辑移动到应用层。

下表列出了 Oracle 最常见的分析函数。

函数系列 相关函数 受 Cloud SQL for PostgreSQL 支持
分析和排名 RANK
AVERAGE_RANK
DENSE_RANK
ROW_NUMBER
PERCENT_RANK
CUME_DIST
NTILE
FIRST_VALUE
LAST_VALUE
OVER (PARTITION BY...) 		是（AVERAGE_RANK 除外）
分层 CONNECT BY
HIER_ANCESTOR
HIER_CHILD_COUNT
HIER_DEPTH
HIER_LEVEL
HIER_ORDER
HIER_PARENT
HIER_TOP 		否
延迟 LAG
LAG_VARIANCE LAG_VARIANCE_PERCENT LEAD
LEAD_VARIANCE LEAD_VARIANCE_PERCENT 		是（仅限 LAG 和 LEAD）

通用表表达式 (CTE)

CTE 提供了一种方法来实现顺序代码的逻辑，以重复使用可能过于复杂或对多次使用效率不高的 SQL 代码。您可以为 CTE 命名，然后使用 WITH 子句在 SQL 语句的不同部分多次使用 CTE。 Oracle 和 Cloud SQL for PostgreSQL 均支持 CTE。

示例
Oracle 和 Cloud SQL for PostgreSQL
WITH DEPT_COUNT
(DEPARTMENT_ID, DEPT_COUNT) AS
(SELECT DEPARTMENT_ID,
COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID)

SELECT E.FIRST_NAME ||' '|| E.LAST_NAME AS EMP_NAME,
D.DEPT_COUNT AS EMP_DEPT_COUNT
FROM EMPLOYEES E JOIN DEPT_COUNT D
USING (DEPARTMENT_ID)
ORDER BY 2 DESC;

MERGE 语句

MERGE（或 UPSERT）语句提供了一种方法，用于指定单条 SQL 语句，这些语句有条件地在一个 MERGE 操作中执行 DML 操作，而不是像单个 DML 操作那样单独运行。它会从源表中选择记录，然后通过指定逻辑结构自动对目标表执行多个 DML 操作。此功能可帮助您避免使用多个插入、更新或删除操作。请注意，MERGE 是一条确定性语句，这意味着 MERGE 语句一旦处理了一行，您就无法再次使用同一 MERGE 语句处理该行。

转换说明

与 Oracle 不同的是，Cloud SQL for PostgreSQL 不支持 MERGE 功能。为了部分模拟 MERGE 功能，Cloud SQL for PostgreSQL 提供了 INSERT ... ON CONFLICT DO UPDATE 语句：

  • INSERT… ON CONFLICT DO UPDATE：如果插入的行会导致唯一违规或排除项限制违规错误，则系统会采用 ON CONFLICT DO UPDATE 子句中指定的替代操作，例如：
INSERT INTO tbl (a,b,c) VALUES (1,2,3)
  ON CONFLICT (a) DO UPDATE SET b = 2, c = 3;

另一种解决方案是使用 INSERT、UPDATE、DELETE 命令以及异常和重复处理功能，将 MERGE 功能转换为存储过程来管理 DML 操作。

SQL 语句提示

Oracle 提供了大量 SQL 查询提示，可让用户影响优化器行为，从而生成更高效的查询执行计划。Cloud SQL for PostgreSQL 不提供类似的 SQL 语句级别和提示机制来影响优化器。

为影响查询规划程序所选的查询计划，Cloud SQL for PostgreSQL 提供了一组配置参数，您可以在会话级应用这些参数。这些配置参数的效果包括启用/停用特定访问方法和调整规划程序费用常量。例如，以下语句会阻止查询规划程序使用依序扫描方案类型，例如全表扫描：

SET ENABLE_SEQSCAN=FALSE;

如需调整规划程序对随机磁盘页面提取的费用估算（默认为 4.0），请使用以下语句：

SET RANDOM_PAGE_COST=2.0;

降低此值可使 Cloud SQL for PostgreSQL 优先选择索引扫描。提高此值则会执行相反操作。

转换说明

由于 Oracle 与 Cloud SQL for PostgreSQL 优化器之间存在基本差异，并且 Cloud SQL for PostgreSQL 不支持 Oracle 样式的 SQL 查询提示，因此建议您在将数据迁移到 Cloud SQL for PostgreSQL 的期间移除所有查询提示。然后，通过 Cloud SQL for PostgreSQL 工具执行严格的性能测试，使用执行方案来检查查询，并根据使用场所来调整实例或会话参数。

执行计划

执行计划的主要目的是让您深入了解查询优化器为访问数据库数据所做的选择。查询优化器为数据库用户生成 SELECT、INSERT、UPDATE、DELETE 语句的执行计划，还可以让管理员更好地查看特定查询和 DML 操作。当您需要调整查询的性能（例如，确定索引性能，或确定是否需要创建缺少的索引时）时执行计划特别有用。

执行计划可能会受到数据量、数据统计信息和实例参数（全局或会话参数）的影响。

转换注意事项

执行计划不是需要迁移的数据库对象；而是一个工具，可用于分析 Oracle 和 Cloud SQL for PostgreSQL 针对相同的数据集运行相同语句时的性能差异。

Cloud SQL for PostgreSQL 不支持与 Oracle 相同的执行计划语法、功能或输出结果。

以下是执行计划的示例：

Oracle 执行计划 Cloud SQL for PostgreSQL 执行计划
SQL> EXPLAIN PLAN FOR
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));

Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------- 		postgres=> EXPLAIN SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105; QUERY PLAN -------------------------------------------------------------------------------- Index Scan using emp_emp_id_pk on employees (cost=0.14..8.16 rows=1 width=71) Index Cond: (employee_id = '105'::numeric) (2 rows)

存储过程、函数和触发器

PL/SQL 是 Oracle 扩展过程语言，用于在数据库中创建、存储和应用基于代码的解决方案。通常，数据库存储过程和函数是由 ANSI SQL 和 SQL 扩展过程语言（例如 PL/SQL for Oracle 和适用于 MySQL 的 MySQL 过程语言）组成的代码元素。PL/pgSQL 适用于 PostgreSQL 自己的扩展过程语言。

这些存储过程和函数的目的是为更适合从数据库内部（而不是应用）运行的要求（例如性能、兼容性和安全性）提供解决方案。虽然存储过程和函数都使用 PL/SQL，但存储过程主要用于执行 DDL/DML 操作，而函数主要用于执行计算以返回特定结果。

从 PL/SQL 到 PL/pgSQL

从 Oracle PL/SQL 到 Cloud SQL for PostgreSQL 迁移的角度来看，PL/pgSQL 在结构和语法方面类似于 Oracle PL/SQL。但是，有几个主要的区别使得需要代码迁移。例如，Oracle 与 Cloud SQL for PostgreSQL 的数据类型不同，并且通常需要转换才能确保迁移的代码使用 Cloud SQL for PostgreSQL 支持的相应数据类型名称。如需详细了解这两种语言之间的差异，请参阅从 Oracle PL/SQL 移植。

代码对象特权和安全性

在 Oracle 中，若要创建存储过程或函数，用户必须拥有 CREATE PROCEDURE 系统特权（若要在其他不同用户名义下创建过程或函数，数据库用户必须拥有 CREATE ANY PROCEDURE 特权）。若要执行存储过程或函数，数据库用户必须拥有 EXECUTE 特权。

在 PostgreSQL 中，为了创建代码过程或函数，用户必须具有 USAGE 权限。如需执行过程或函数，用户必须具有该过程或函数的 EXECUTE 权限。

默认情况下，PL/pgSQL 过程或函数定义为 SECURITY INVOKER，这意味着该过程或函数将使用调用它的用户的权限执行。此外，也可以指定 SECURITY DEFINER，以便使用拥有该函数的用户的权限执行该函数。

Cloud SQL for PostgreSQL 存储过程和函数语法

以下示例展示了 Cloud SQL for PostgreSQL 存储过程和函数语法：

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

触发器

触发器是在特定事件发生时触发的存储过程。在 Oracle 中，触发事件与表、视图、架构或数据库相关联。触发事件的类型包括：

  • 数据操纵语言 (DML) 语句（例如 INSERT、UPDATE、DELETE）
  • 数据定义语言 (DDL) 语句（例如 CREATE、ALTER、DROP）
  • 数据库事件（例如 LOGON、STARTUP、SHUTDOWN）

Oracle 触发器可以是以下类型：

  • 简单触发器：在指定触发事件发生之前或之后仅触发一次
  • 复合触发器：在多个事件发生时触发
  • INSTEAD OF 触发器：一种特殊类型的 DML 触发器，用于为复杂的不可修改视图提供透明更新机制
  • 系统触发器：在特定的数据库事件发生时触发

在 Cloud SQL for PostgreSQL 中，在对特定表、视图或外部表进行 DML 操作之前或之后会触发触发器。支持 INSTEAD OF 触发器为视图提供更新机制。对 DDL 操作的触发器称为事件触发器。Cloud SQL for PostgreSQL 不支持基于数据库事件的 Oracle 系统触发器。

与 Oracle 触发器不同，Cloud SQL for PostgreSQL 触发器不支持使用匿名 PL/pgSQL 块作为触发器正文。必须在触发器声明中提供采用零个或多个参数并返回类型触发器的命名函数。此函数会在触发器触发时执行。

Cloud SQL for PostgreSQL 触发器和事件触发器语法

以下示例展示了 Cloud SQL for PostgreSQL 触发器和事件触发器语法：

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

event 可以是 INSERT、UPDATE [ OF column_name [, ... ] ]、DELETE、TRUNCATE

CREATE EVENT TRIGGER name
    ON event
    [ WHEN filter_value IN (filter_value [, ... ]) [ AND ... ] ]
    EXECUTE { FUNCTION | PROCEDURE } function_name()

event 可以是 ddl_command_start、ddl_command_end、table_rewrite、sql_drop

filter_value 只能是 TAG

filter_value 可以是受支持的命令标记之一。

后续步骤