將 Oracle® 使用者遷移至 PostgreSQL 適用的 Cloud SQL：查詢、預存程序、函式和觸發條件

本文件是一系列文件的一部分，提供有關規劃及執行 Oracle® 11g/12c 資料庫遷移至 PostgreSQL 適用的 Cloud SQL 12 版的相關重要資訊和指引。除了初步設定部分外，本系列還包含以下部分：

查詢

Oracle 和 PostgreSQL 適用的 Cloud SQL 支援 ANSI SQL 標準。因此，只要使用基本語法元素 (例如，不指定任何標量函式或任何其他 Oracle 擴充功能)，通常就能輕鬆遷移 SQL 陳述式。以下章節將說明常見的 Oracle 查詢元素，以及其對應的 PostgreSQL 適用 Cloud SQL 等效項目。

基本 SELECT 和 FROM 語法

Oracle 功能名稱或語法名稱 Oracle 總覽或實作 PostgreSQL 適用的 Cloud SQL 支援 PostgreSQL 適用的 Cloud SQL 對應或替代解決方案
資料擷取的 SQL 基本語法 SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY 		是 SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT
for output print		 SELECT 1 FROM DUAL 是 SELECT 1
欄別別名 SELECT COL1 AS C1 是 SELECT COL1 AS C1
OR
SELECT COL1 C1
資料表名稱區分大小寫 不區分大小寫
(例如，資料表名稱可以是 orders 和/或 ORDERS)。		 是 名稱不區分大小寫，除非使用引號 (例如 orders 和 ORDERS 會視為相同，而 "orders" 和 "ORDERS" 會視為不同)

如要進一步瞭解 PostgreSQL 適用的 Cloud SQL 的 SELECT 語法，請參閱說明文件。

內嵌檢視畫面

  • 內嵌檢視畫面 (也稱為衍生資料表) 是 SELECT 陳述式，位於 FROM 子句中，用於做為子查詢。
  • 內嵌檢視畫面可移除複合計算或排除彙整作業，同時將多個個別查詢濃縮為單一簡易查詢，有助於簡化複雜查詢。
  • 轉換注意事項：Oracle 內嵌檢視畫面不需要使用別名，但 PostgreSQL 適用的 Cloud SQL 需要為每個內嵌檢視畫面使用特定別名。

下表列出從 Oracle 轉換至 PostgreSQL 適用的 Cloud SQL 的內嵌檢視畫面範例。

Oracle 11g/12c PostgreSQL 適用的 Cloud SQL 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 陳述式

PostgreSQL 適用的 Cloud SQL JOIN 陳述式支援 Oracle JOIN 陳述式。不過，Cloud SQL for PostgreSQL 不支援使用 Oracle 彙整運算子 (+)。如要取得相同的結果，您需要將外部聯結轉換為標準 SQL 語法。

下表列出 JOIN 轉換的範例。

Oracle JOIN 類型 由 PostgreSQL 適用的 Cloud SQL 支援 PostgreSQL 適用的 Cloud SQL 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

PostgreSQL 適用的 Cloud SQL 支援 Oracle UNION、UNION ALL 和 INTERSECT 運算子。系統不支援 MINUS 運算子。不過，PostgreSQL 適用的 Cloud SQL 會實作 EXCEPT 運算子，這與 Oracle 中的 MINUS 運算子相同。此外，PostgreSQL 適用的 Cloud SQL 支援 INTERSECT ALL 和 EXCEPT ALL 運算子，而 Oracle 不支援這些運算子。

  • UNION：附加兩個以上 SELECT 陳述式的結果集，並刪除重複的記錄。
  • UNION ALL：附加兩個以上 SELECT 陳述式的結果集，但不刪除重複的記錄。
  • INTERSECT：只有在兩個資料集中都有記錄時，才會傳回兩個或多個 SELECT 陳述式的交集。系統不會移除重複的記錄。
  • INTERSECT ALL (僅限 PostgreSQL 適用的 Cloud SQL)：僅在兩個資料集皆有記錄時，才會傳回兩個或更多 SELECT 陳述式的交集。
  • MINUS (EXCEPT (在 PostgreSQL 適用的 Cloud SQL 中)：比較兩個或多個 SELECT 陳述式，只傳回第一個查詢中其他陳述式未傳回的唯一資料列。
  • EXCEPT ALL (僅限 PostgreSQL 適用的 Cloud SQL)：比較兩個或多個 SELECT 陳述式，只傳回第一個查詢中其他陳述式未傳回的資料列，而不刪除重複的記錄。

轉換附註

從 Oracle MINUS 運算子轉換至 PostgreSQL 適用的 Cloud SQL 時，請改用 EXCEPT 運算子。

範例

Oracle 函式 Oracle 實作 PostgreSQL 適用的 Cloud SQL 支援 PostgreSQL 適用的 Cloud SQL 對應或替代解決方案
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

純量 (單一資料列) 和群組函式

PostgreSQL 適用的 Cloud SQL 提供大量的標量 (單列) 和匯總函式。部分 PostgreSQL 適用的 Cloud SQL 函式與 Oracle 函式相似 (名稱和功能相同，或名稱不同但功能相似)。雖然 PostgreSQL 適用的 Cloud SQL 函式名稱可能與 Oracle 函式相同，但有時會顯示不同的功能。

下表說明 Oracle 和 PostgreSQL 適用的 Cloud SQL 在名稱和功能上的相似之處 (以「是」表示)，以及建議轉換的情況 (所有「否」以外的情況)。

字元函式
Oracle 函式 Oracle 函式規格或實作 PostgreSQL 適用的 Cloud SQL 等同項目 PostgreSQL 適用的 Cloud SQL 對應函式 PostgreSQL 適用的 Cloud SQL 函式規格或實作
CONCAT 傳回與 str2 連接的 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，左或右以 expr2 中的字元序列填入 n 個字元的長度：

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 中以負數做為起始位置，系統會從字串結尾執行子字串運算，這與 PostgreSQL 適用的 Cloud SQL 不同。如果希望採用 Oracle 的行為，請改用 RIGHT 函式。
INSTR 傳回指定字串中特定字串的位置 (索引)：

INSTR('PostgreSQL', 'e')
= 7 		否 不適用 PostgreSQL 適用的 Cloud SQL 沒有內建 instr 函式。您可以使用 PL/pgSQL 實作與 Oracle 相容的 instr 函式。
REPLACE 傳回 char，其中每個搜尋字串都已替換為
替換字串：

REPLACE('ORADB', 'ORA', 'PostgreSQL')
= PostgreSQLDB 		部分支援 REPLACE 在 Oracle 中，取代字串參數為選用參數，但在 PostgreSQL 適用的 Cloud SQL 中為必填參數。省略參數時，Oracle 會移除所有搜尋字串。在 PostgreSQL 適用的 Cloud SQL 中，只要提供空字串做為替換字串，即可達到相同的行為。

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 傳回資料庫字元集的十進位表示法，該字元集為字元的第一個字元：

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 搜尋字串位置 (索引) 是否符合規則運算式模式。
 否 不適用 將功能轉換至應用程式層。
REVERSE 傳回反轉的字串。

REVERSE('PostgreSQL') = LQSergtsoP 		是 REVERSE 等同於 Oracle：

REVERSE('PostgreSQL') = LQSergtsoP
數值函式
Oracle 函式 Oracle 函式規格或實作 PostgreSQL 適用的 Cloud SQL 等同項目 PostgreSQL 適用的 Cloud SQL 對應函式 PostgreSQL 適用的 Cloud SQL 函式規格或實作
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
DateTime 函式
Oracle 函式 Oracle 函式規格或實作 PostgreSQL 適用的 Cloud SQL 等同項目 PostgreSQL 適用的 Cloud SQL 對應函式 PostgreSQL 適用的 Cloud SQL 函式規格或實作
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 PostgreSQL 適用的 Cloud SQL 會傳回與 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 PostgreSQL 適用的 Cloud SQL 會傳回與 Oracle 不同的日期/時間格式。日期格式必須與原始日期/時間格式相符：

SELECT LOCALTIMESTAMP
= 2019-01-31 07:37:11.622187+00
CURRENT_DATE 以工作階段時區傳回目前日期：

SELECT CURRENT_DATE FROM DUAL
= 31-JAN-19 		部分日期/時間格式不同 CURRENT_ DATE PostgreSQL 適用的 Cloud SQL 會傳回與 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 PostgreSQL 適用的 Cloud SQL 會傳回與 Oracle 不同的日期時間格式。日期格式必須與原始日期時間格式相符：

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

ADD_MONTHS(SYSDATE, 1)
= 31-JAN-19 		否 不適用 如要在 PostgreSQL 適用的 Cloud SQL 中實現相同功能，請使用 + / - 運算子並指定時間間隔：

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 PostgreSQL 適用的 Cloud SQL 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 函式規格或實作 PostgreSQL 適用的 Cloud SQL 等同項目 PostgreSQL 適用的 Cloud SQL 對應函式 PostgreSQL 適用的 Cloud SQL 函式規格或實作
DECODE 使用 IF-THEN-ELSE 陳述式，逐一將運算式與每個搜尋值進行比較。 否 CASE 使用 PostgreSQL 適用的 Cloud SQL CASE 陳述式，即可實現類似的功能。
DUMP 傳回 VARCHAR2 值，其中包含資料類型代碼、長度 (以位元組為單位) 和運算式的內部表示法。 否 不適用 不支援。
ORA_HASH 計算指定運算式的雜湊值。 否 MD5 / SHA224 / SHA256 / SHA385 / SHA512 如要產生雜湊值，請使用 PostgreSQL 適用的 Cloud SQL MD5 函式 (128 位元) 或 SHA 函式 (160 位元)
雜湊值。
轉換函式
Oracle 函式 Oracle 函式規格或實作 PostgreSQL 適用的 Cloud SQL 等同項目 PostgreSQL 適用的 Cloud SQL 對應函式 PostgreSQL 適用的 Cloud SQL 函式規格或實作
CAST 將一個內建資料類型或集合型值轉換為另一個內建資料類型或集合型值：

CAST('1' as int) + 1
= 2 		部分支援 CAST PostgreSQL 適用的 Cloud SQL CAST 函式與 Oracle 的 CAST 功能類似，但在某些情況下，由於兩個資料庫的資料類型不同，因此必須調整：

CAST('1' as int) + 1
= 2
CONVERT 將字元字串從一種字元集轉換為另一種字元集：

CONVERT ( 'Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')
= ?? ?? ?? A B C 		部分支援 CONVERT PostgreSQL 適用的 Cloud SQL CONVERT 函式會傳回 bytea 值，這是二進位字串，而非 VARCHAR 或 TEXT。PostgreSQL 支援的字元集也與 Oracle 不同。

CONVERT('Ä Ê Í Õ Ø A B C D E', 'UTF8', 'LATIN1')
= [Binary representation of the string in LATIN1 encoding]
TO_CHAR
(字串/數字)		 這個函式會將數字或日期轉換為字串：

TO_CHAR(22.73,'$99.9')
= $22.7 		部分支援 TO_CHAR PostgreSQL 適用的 Cloud SQL TO_CHAR 函式功能與 Oracle 相似。PostgreSQL 適用的 Cloud SQL 支援略有不同的格式化字串清單。根據預設，PostgreSQL 適用的 Cloud SQL 會保留一個資料欄來儲存符號，因此正數會在前方加上空格。您可以使用 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 PostgreSQL 適用的 Cloud SQL TO_DATE 函式功能與 Oracle 相似。PostgreSQL 適用的 Cloud SQL 支援略有不同的格式字串清單：

TO_DATE('2019/01/01', 'yyyy-mm-dd')
= 2019-01-01
TO_NUMBER 將運算式轉換為 NUMBER 資料類型的值：

TO_NUMBER('01234')
= 1234 		部分支援 TO_NUMBER PostgreSQL 適用的 Cloud SQL TO_NUMBER 函式需要格式字串做為輸入內容，但在 Oracle 中則為選用項目：

TO_NUMBER('01234','99999')
= 1234
如果轉換作業不需要複雜的格式字串，則可以改用 CAST 函式：

CAST('01234' AS NUMERIC)
= 1234
條件式 SELECT 函式
Oracle 函式 Oracle 函式規格或實作 PostgreSQL 適用的 Cloud SQL 等同項目 PostgreSQL 適用的 Cloud SQL 對應函式 PostgreSQL 適用的 Cloud SQL 函式規格或實作
CASE CASE 陳述式會從一系列條件中選擇，並執行
對應的陳述式，語法如下：

CASE WHEN condition THEN result [WHEN ...] [ELSE result] END 		是 CASE 等同於 Oracle：

CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
空函式
Oracle 函式 Oracle 函式規格或實作 PostgreSQL 適用的 Cloud SQL 等同項目 PostgreSQL 適用的 Cloud SQL 對應函式 PostgreSQL 適用的 Cloud SQL 函式規格或實作
COALESCE 傳回運算式清單中的第一個非空值運算式：

COALESCE(null, '1', 'a')
= a 		是 COALESCE 等同於 Oracle：

COALESCE(null, '1', 'a')
= 1
NULLIF 比較 expr1 和 expr2。如果相等，函式會傳回空值。如果不相等，函式會傳回 expr1：

NULLIF('1', '2')
= 1 		是 NULLIF 等同於 Oracle：

NULLIF('1', '2')
= 1
NVL 在查詢結果中，將空值 (傳回為空白) 替換為字串：

NVL(null, 'a')
= a 		否 COALESCE 您也可以改用 COALESCE 函式：

COALESCE(null, 'a')
= a
NVL2 根據指定的
運算式是否為空值，判斷查詢傳回的值。		 否 COALESCE 您也可以改用 COALESCE 函式：

COALESCE(null, 1, 'a')
= 1
環境和 ID 函式
Oracle 函式 Oracle 函式規格或實作 PostgreSQL 適用的 Cloud SQL 等同項目 PostgreSQL 適用的 Cloud SQL 對應函式 PostgreSQL 適用的 Cloud SQL 函式規格或實作
SYS_GUID 產生並傳回由 16 個位元組組成的全域專屬 ID (RAW 值)：

SELECT SYS_GUID() FROM DUAL
= 8EFA4A31468B4C6DE05011AC0200009E
部分使用不同的函式名稱和格式 UUID_GENERATE_V4 CloudSQL for PostgreSQL 支援 uuid-ossp 擴充功能，可提供 UUID 產生函式清單，例如 UUID_GENERATE_V4：

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 		否 不適用 雖然 PostgreSQL 適用的 Cloud SQL 中沒有等同的 USERENV 函式，但您可以使用 系統資訊函式 (例如 PG_BACKGROUND_PID()) 擷取 USERENV('SID') 等個別參數。
ROWID Oracle 伺服器會為每個資料表中的每個資料列指派專屬的 ROWID，用於識別資料表中的資料列。ROWID 是資料行位址，其中包含資料物件編號、資料列的資料區塊、資料列位置和資料檔案。 部分使用不同的函式名稱 ctid Cloud SQL for PostgreSQL 中的 ctid 會指出資料表內的資料行版本實際位置，這與 Oracle 的 ROWID 類似。
ROWNUM 傳回數字，代表 Oracle 從資料表或已彙整的資料表中選取資料列的順序。 否 LIMIT or ROW_NUMBER() 與使用 ROWNUM 限制查詢傳回的結果數量不同，Cloud SQL for PostgreSQL 支援 LIMIT 和 OFFSET 來達到類似的目的。

ROW_NUMBER() window 函式 可能是 Oracle ROWNUM 的替代解決方案，適用於其他情況。不過，在使用替代方案之前，請務必考量結果排序和效能差異。
匯總 (群組) 函式
Oracle 函式 Oracle 函式規格或實作 PostgreSQL 適用的 Cloud SQL 等同項目 PostgreSQL 適用的 Cloud SQL 對應函式 PostgreSQL 適用的 Cloud SQL 函式規格或實作
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 使用 PostgreSQL 適用的 Cloud SQL 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 函式規格或實作 PostgreSQL 適用的 Cloud SQL 等同項目 PostgreSQL 適用的 Cloud SQL 對應函式 PostgreSQL 適用的 Cloud SQL 函式規格或實作
FETCH 從多列查詢的結果集合中擷取資料列：

SELECT * FROM
EMPLOYEES
FETCH FIRST 10 ROWS ONLY; 		否 LIMIT 使用 PostgreSQL 適用的 Cloud SQL LIMIT 子句，只擷取特定記錄組：

SELECT * FROM EMPLOYEES LIMIT 10;

基本篩選、運算子和子查詢

在轉換期間，基本篩選、運算子函式和子查詢都相對簡單，幾乎不需要額外付出任何努力。

轉換附註

請檢查並解決日期格式問題，因為 Oracle 和 PostgreSQL 適用的 Cloud SQL 格式會傳回不同的預設結果：

  • Oracle SYSDATE 函式預設會傳回 01-AUG-19。
  • PostgreSQL CURRENT_DATE 函式預設會傳回 2019-08-01 (即使經過格式設定，也不會有任何時間)。如要擷取目前的日期和時間，請使用 CURRENT_TIMESTAMP 函式，預設會傳回 2019-08-01 00:00:00.000000+00。
  • 您可以使用 PostgreSQL 適用的 Cloud SQL TO_CHAR 函式設定日期和時間格式。
Oracle 函式或子查詢 PostgreSQL 適用的 Cloud SQL 等同項目 PostgreSQL 適用的 Cloud SQL 對應函式或子查詢 PostgreSQL 適用的 Cloud SQL 函式規格或實作
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 PostgreSQL 適用的 Cloud SQL 支援 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);
運算子 是 運算子 PostgreSQL 適用的 Cloud SQL 支援所有基本運算子：

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

分析函式 (或時間區間和排名函式)

Oracle 分析函式可擴充標準 SQL 作業的功能，提供計算一組資料列匯總值的功能 (例如 RANK()、ROW_NUMBER()、FIRST_VALUE())。這些函式會套用至單一查詢運算式範圍內邏輯分割的記錄。這些資料通常用於資料倉儲，並與商業智慧報表和分析結合使用。

轉換附註

PostgreSQL 適用的 Cloud SQL 支援許多分析函式，在 PostgreSQL 中稱為匯總函式和視窗函式。如果應用程式使用 Postgres 不支援的較不常見函式，您需要尋找支援的擴充功能，或將邏輯移至應用程式層。

下表列出 Oracle 最常見的分析函式。

函式系列 相關函式 由 PostgreSQL 適用的 Cloud SQL 支援
分析和排名 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 和 PostgreSQL 適用的 Cloud SQL 都支援 CTE。

範例
Oracle 和 PostgreSQL 適用的 Cloud SQL
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 不同，PostgreSQL 適用的 Cloud SQL 不支援 MERGE 功能。為部分模擬 MERGE 功能，PostgreSQL 適用的 Cloud SQL 提供 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;

另一個解決方案是將 MERGE 功能轉換為儲存程序，以便使用 INSERT、UPDATE 和 DELETE 指令，搭配例外狀況和重複項目處理機制來管理 DML 作業。

SQL 陳述式提示

Oracle 提供大量 SQL 查詢提示，讓使用者能影響最佳化器行為，以便產生更有效率的查詢執行計畫。PostgreSQL 適用的 Cloud SQL 並未提供類似的 SQL 陳述式層級提示機制，以便影響最佳化器。

為了影響查詢規劃器選擇的查詢計畫，Cloud SQL for PostgreSQL 提供可在工作階段層級套用的一組設定參數。這些設定參數的效果包括啟用/停用特定存取方法，以及調整企劃工具費用常數。例如，下列陳述式會停用查詢規劃工具使用序列掃描計畫類型 (例如完整資料表掃描) 的功能：

SET ENABLE_SEQSCAN=FALSE;

如要調整規劃工具對隨機磁碟頁面擷取作業的預估費用 (預設為 4.0)，請使用下列陳述式：

SET RANDOM_PAGE_COST=2.0;

降低這個值會導致 PostgreSQL 適用的 Cloud SQL 偏好索引掃描。反之，如果將其放大，則會降低亮度。

轉換附註

由於 Oracle 和 PostgreSQL 適用的 Cloud SQL 最佳化工具之間存在根本差異，且 PostgreSQL 適用的 Cloud SQL 不支援 Oracle 風格的 SQL 查詢提示，因此建議您在遷移至 PostgreSQL 適用的 Cloud SQL 時移除所有查詢提示。接著，透過 PostgreSQL 適用的 Cloud SQL 工具執行嚴格的效能測試，使用執行計畫檢查查詢，並根據用途調整執行個體或工作階段參數。

執行計畫

執行計畫的主要目的，是讓您深入瞭解查詢最佳化工具用於存取資料庫資料的選擇。查詢最佳化工具會為資料庫使用者產生 SELECT、INSERT、UPDATE 和 DELETE 陳述式的執行計畫，並讓管理員更清楚查看特定查詢和 DML 作業。當您需要調整查詢效能時，這些指標就特別實用，例如判斷索引效能或判斷是否有缺少的索引需要建立。

執行計畫可能會受到資料量、資料統計資料和執行個體參數 (全域或工作階段參數) 的影響。

轉換考量事項

執行計畫並非需要遷移的資料庫物件，而是用於分析 Oracle 和 PostgreSQL 適用的 Cloud SQL 之間，在相同資料集上執行相同陳述式時的效能差異。

PostgreSQL 適用的 Cloud SQL 不支援與 Oracle 相同的執行計畫語法、功能或輸出內容。

以下是執行計畫的範例：

Oracle 執行計畫 PostgreSQL 適用的 Cloud SQL 執行計畫
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 擴充程序語言的程式碼元素，例如 Oracle 的 PL/SQL 和 MySQL 的 MySQL 程序語言。PL/pgSQL 是 PostgreSQL 專用的擴充程序語言。

這些儲存程序和函式的目的，是針對更適合在資料庫中執行而非在應用程式中執行的必要條件提供解決方案 (例如效能、相容性和安全性)。雖然預存程序和函式都使用 PL/SQL，但預存程序主要用於執行 DDL/DML 作業，而函式主要用於執行運算以傳回特定結果。

從 PL/SQL 轉換為 PL/pgSQL

從 Oracle PL/SQL 遷移至 PostgreSQL 適用的 Cloud SQL 的角度來看，PL/pgSQL 的結構和語法與 Oracle PL/SQL 相似。不過，兩者之間仍有一些主要差異，因此需要進行程式碼遷移作業。舉例來說，Oracle 和 PostgreSQL 適用的 Cloud SQL 之間的資料類型不同，因此通常需要進行轉譯，確保遷移的程式碼使用 PostgreSQL 適用的 Cloud SQL 支援的對應資料類型名稱。如要進一步瞭解這兩種語言的差異，請參閱「從 Oracle PL/SQL 移植」一文。

程式碼物件權限和安全性

在 Oracle 中，使用者必須具備 CREATE PROCEDURE 系統權限，才能建立已儲存的程序或函式 (如要以其他使用者身分建立程序或函式，資料庫使用者必須具備 CREATE ANY PROCEDURE 權限)。如要執行已儲存的程序或函式，資料庫使用者必須具備 EXECUTE 權限。

在 PostgreSQL 中，使用者必須具備 USAGE 權限，才能建立程式碼程序或函式。如要執行程序或函式，使用者必須對程序或函式具備 EXECUTE 權限。

根據預設，PL/pgSQL 程序或函式會定義為 SECURITY INVOKER，這表示程序或函式會以呼叫該函式的使用者權限執行。或者，您也可以指定 SECURITY DEFINER，讓函式以擁有函式的使用者權限執行。

PostgreSQL 適用的 Cloud SQL 儲存程序和函式語法

以下範例顯示 PostgreSQL 適用的 Cloud SQL 儲存程序和函式語法：

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 觸發條件，可為複雜的不可編輯檢視畫面提供透明更新機制
  • 系統觸發條件：在特定資料庫事件時觸發

在 PostgreSQL 適用的 Cloud SQL 中，觸發事件會在特定資料表、檢視或外部資料表的 DML 作業前或後觸發。支援 INSTEAD OF 觸發事件，為檢視畫面提供更新機制。DDL 作業的觸發條件稱為「事件觸發條件」。PostgreSQL 適用的 Cloud SQL 不支援 Oracle 的系統觸發事件，該系統會根據資料庫事件觸發事件。

與 Oracle 觸發事件不同，PostgreSQL 適用的 Cloud SQL 觸發事件不支援使用匿名 PL/pgSQL 區塊做為觸發事件主體。觸發事件宣告中必須提供可接收零個或多個引數，並傳回類型觸發事件的已命名函式。這個函式會在觸發條件觸發時執行。

PostgreSQL 適用的 Cloud SQL 觸發事件和事件觸發事件的語法

以下範例說明 PostgreSQL 適用的 Cloud SQL 觸發事件和事件觸發事件的語法：

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 可以是支援的指令代碼之一。

後續步驟