Migrazione degli utenti Oracle® a Cloud SQL per PostgreSQL: query, stored procedure, funzioni e trigger

Questo documento fa parte di una serie che fornisce informazioni chiave e indicazioni relative alla pianificazione e all'esecuzione delle migrazioni dei database Oracle® 11g/12c in Cloud SQL per PostgreSQL versione 12. Oltre alla parte introduttiva della configurazione, la serie include le seguenti parti:

Query

Oracle e Cloud SQL per PostgreSQL supportano lo standard ANSI SQL. In genere la migrazione delle istruzioni SQL è semplice, utilizzando solo elementi di sintassi di base (ad esempio, senza specificare funzioni scalari o altre funzionalità estese di Oracle). La seguente sezione illustra gli elementi di query Oracle comuni e i corrispondenti equivalenti Cloud SQL per PostgreSQL.

Sintassi di base SELECT e FROM

Nome della funzionalità Oracle o del nome della sintassi Panoramica o implementazione di Oracle Supporto di Cloud SQL per PostgreSQL Soluzione corrispondente o alternativa di Cloud SQL per PostgreSQL
Sintassi di base SQL per il recupero dei dati SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY 		Sì SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT
per la stampa di output		 SELECT 1 FROM DUAL Sì SELECT 1
Alias colonna SELECT COL1 AS C1 Sì SELECT COL1 AS C1
OR
SELECT COL1 C1
Sensibilità tra maiuscole e minuscole nei nomi delle tabelle Nessuna sensibilità alle maiuscole
(ad esempio, il nome della tabella può essere orders e/o ORDERS).		 Sì I nomi non fanno distinzione tra maiuscole e minuscole se non vengono tra virgolette (ad esempio, orders e ORDERS vengono trattati allo stesso modo, mentre "orders" e "ORDERS" vengono trattati in modo diverso)

Per ulteriori dettagli sulla sintassi SELECT di Cloud SQL per PostgreSQL, consulta la documentazione.

Visualizzazioni in linea

  • Le viste in linea (note anche come tabelle derivate) sono istruzioni SELECT, situate nella clausola FROM e utilizzate come sottoquery.
  • Le viste in linea possono contribuire a semplificare le query complesse rimuovendo i calcoli composti o eliminando le operazioni di join, condensando diverse query separate in un'unica query semplificata.
  • Nota di conversione: le viste in linea Oracle non richiedono l'utilizzo di alias, mentre Cloud SQL per PostgreSQL richiede alias specifici per ogni visualizzazione in linea.

La tabella seguente presenta un esempio di conversione da Oracle a Cloud SQL per PostgreSQL, sotto forma di visualizzazione in linea.

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

L'output è simile al seguente:

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

Senza alias per la visualizzazione in linea:

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.

Aggiunta di un alias alla visualizzazione in linea:

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

L'output è simile al seguente:

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

Istruzioni JOIN

Le istruzioni JOIN Oracle sono supportate dalle istruzioni JOIN di Cloud SQL per PostgreSQL. Tuttavia, l'utilizzo dell'operatore di join Oracle (+) non è supportato da Cloud SQL per PostgreSQL. Per ottenere lo stesso risultato, devi convertire nella sintassi SQL standard per i joiner.

La tabella seguente presenta un esempio di conversione JOIN.

Tipo di JOIN Oracle Supportato da Cloud SQL per PostgreSQL Sintassi JOIN di Cloud SQL per PostgreSQL
INNER JOIN Sì SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
CROSS JOIN Sì SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E CROSS JOIN DEPARTMENTS D;
FULL JOIN [ OUTER ] Sì SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
LEFT JOIN [ OUTER ] Sì SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
RIGHT JOIN [ OUTER ] Sì SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
SUBQUERY Sì 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 e MENO

Cloud SQL per PostgreSQL supporta gli operatori Oracle UNION, UNION ALL e INTERSECT. L'operatore MINUS non è supportato. Tuttavia, Cloud SQL per PostgreSQL implementa l'operatore EXCEPT, che equivale all'operatore MINUS in Oracle. Inoltre, Cloud SQL per PostgreSQL supporta gli operatori INTERSECT ALL e EXCEPT ALL, che non sono supportati da Oracle.

  • UNION: associa l'insieme di risultati di due o più istruzioni SELECT ed elimina i record duplicati.
  • UNION ALL: associa l'insieme di risultati di due o più istruzioni SELECT senza eliminare i record duplicati.
  • INTERSECT: restituisce l'intersezione di due o più istruzioni SELECT solo se un record esiste in entrambi i set di dati. I record duplicati non vengono eliminati.
  • INTERSECT ALL (solo Cloud SQL per PostgreSQL): restituisce l'intersezione di due o più istruzioni SELECT solo se esiste un record in entrambi i set di dati.
  • MINUS (EXCEPT in Cloud SQL per PostgreSQL): confronta due o più istruzioni SELECT, restituendo solo righe distinte per la prima query che non sono state restituite dalle altre istruzioni.
  • EXCEPT ALL (solo Cloud SQL per PostgreSQL): confronta due o più istruzioni SELECT, restituendo solo le righe della prima query che non sono restituite dalle altre istruzioni senza eliminare i record duplicati.

Note sulle conversioni

Quando esegui la conversione dagli operatori MINUS di Oracle a Cloud SQL per PostgreSQL, utilizza invece gli operatori EXCEPT.

Esempi

Funzione Oracle Implementazione di Oracle Supporto di Cloud SQL per PostgreSQL Soluzione corrispondente o alternativa di Cloud SQL per PostgreSQL
UNION SELECT COL1 FROM TBL1
UNION
SELECT COL1 FROM TBL2 		Sì SELECT COL1 FROM TBL1
UNION
SELECT COL1 FROM TBL2
UNION ALL SELECT COL1 FROM TBL1
UNION ALL
SELECT COL1 FROM TBL2 		Sì SELECT COL1 FROM TBL1
UNION ALL
SELECT COL1 FROM TBL2
INTERSECT SELECT COL1 FROM TBL1
INTERSECT
SELECT COL1 FROM TBL2 		Sì SELECT COL1 FROM TBL1
INTERSECT
SELECT COL1 FROM TBL2
MINUS SELECT COL1 FROM TBL1
MINUS
SELECT COL1 FROM TBL2 		Sì (da Convert MINUS a EXCEPT in PostgreSQL) SELECT COL1 FROM TBL1
EXCEPT
SELECT COL1 FROM TBL2

Funzioni scalari (a riga singola) e di gruppo

Cloud SQL per PostgreSQL fornisce un elenco completo di funzioni scalari (a riga singola) e di aggregazione. Alcune funzioni di Cloud SQL per PostgreSQL sono simili alle rispettive controparti Oracle (per nome e funzionalità o con un nome diverso ma con funzionalità simile). Sebbene le funzioni di Cloud SQL per PostgreSQL possano avere nomi identici alle rispettive controparti Oracle, a volte mostrano funzionalità diverse.

Le seguenti tabelle descrivono dove Oracle e Cloud SQL per PostgreSQL sono equivalenti in base al nome e alla funzionalità (specificati con "Sì") e dove è consigliata una conversione (tutti i casi diversi da "Sì").

Funzioni caratteri
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente Cloud SQL per PostgreSQL Funzione corrispondente di Cloud SQL per PostgreSQL Specifiche o implementazione delle funzioni Cloud SQL per PostgreSQL
CONCAT Restituisce str1 concatenato con str2:

CONCAT('A', 1) = A1 		Sì CONCAT Equivalente a Oracle:

CONCAT('A', 1) = A1
LOWER/UPPER Restituisce char, con tutte le lettere minuscole o maiuscole:

LOWER('SQL') = sql 		Sì LOWER/UPPER Equivalente a Oracle:

LOWER('SQL') = sql
LPAD/RPAD Restituisce expr1, a sinistra o a destra con spaziatura interna a n caratteri con la sequenza di caratteri in expr2:

LPAD('A',3,'*') = **A 		Sì LPAD/RPAD Equivalente a Oracle:

LPAD('A',3,'*') = **A
SUBSTR Restituisce una parte di char, che inizia nella posizione del carattere, sottostringa-
lunghezza caratteri:

SUBSTR('PostgreSQL', 8, 3)
= SQL 		Parzialmente SUBSTR Equivale a Oracle quando la posizione iniziale è un numero positivo.

SUBSTR('PostgreSQL', 8, 3)
= SQL
Quando viene fornito un numero negativo come posizione iniziale in Oracle, esegue un'operazione di sottostringa dalla fine della stringa, che è diversa da Cloud SQL per PostgreSQL. Utilizza la funzione RIGHT in sostituzione se desideri il comportamento di Oracle.
INSTR Restituisce la posizione (indice) di una stringa specifica da una determinata stringa:

INSTR('PostgreSQL', 'e')
= 7 		No N/A Cloud SQL per PostgreSQL non ha una funzione instr integrata. Una funzione instr compatibile con Oracle può essere implementata utilizzando PL/pgSQL.
REPLACE Restituisce un carattere con ogni occorrenza di una stringa di ricerca sostituita da una
stringa di sostituzione:

REPLACE('ORADB', 'ORA', 'PostgreSQL')
= PostgreSQLDB 		Parzialmente REPLACE Il parametro della stringa sostitutivo è facoltativo in Oracle mentre è obbligatorio in Cloud SQL per PostgreSQL. Se il parametro viene omesso, Oracle rimuove tutte le occorrenze delle stringhe di ricerca. Lo stesso comportamento può essere ottenuto in Cloud SQL per PostgreSQL fornendo una stringa vuota come stringa di sostituzione.

REPLACE('ORADB', 'ORA', 'PostgreSQL')
= PostgreSQLDB
TRIM Taglia i caratteri iniziali e finali (o entrambi) da una stringa:

TRIM(both '-' FROM '-PostgreSQL-')
= PostgreSQL
TRIM(' PostgreSQL ')
= PostgreSQL 		Sì TRIM Equivalente a Oracle:

TRIM(both '-' FROM '-PostgreSQL-')
= PostgreSQL
TRIM(' PostgreSQL ')
= PostgreSQL
LTRIM/RTRIM Rimuove dall'estremità sinistra o destra della stringa tutti i caratteri
visualizzati nella ricerca:

LTRIM(' PostgreSQL', ' ')
= PostgreSQL 		Sì LTRIM/RTRIM Equivalente a Oracle:

LTRIM(' PostgreSQL', ' ') = PostgreSQL
ASCII Restituisce la rappresentazione decimale nel set di caratteri del database del primo carattere del carattere:

ASCII('A') = 65 		Sì ASCII Equivalente a Oracle:

ASCII('A') = 65
CHR Restituisce il valore del codice ASCII, che è un valore numerico compreso tra 0 e 225, in un carattere:

CHR(65) = A 		Sì CHAR Equivalente a Oracle:

CHR(65) = A
LENGTH Restituisce la lunghezza di una determinata stringa:

LENGTH ('PostgreSQL') = 10 		Sì LENGTH Equivalente a Oracle:

LENGTH ('PostgreSQL') = 10
REGEXP_REPLACE Cerca un pattern di espressione regolare in una stringa:

REGEXP_REPLACE('John', '[hn].', '1') = Jo1 		Sì REGEXP_REPLACE Equivalente a Oracle:

REGEXP_REPLACE('John', '[hn].', '1') = Jo1
REGEXP_SUBSTR Estende la funzionalità della funzione SUBSTR cercando un modello di espressione regolare in una
stringa:

REGEXP_SUBSTR('https://console.cloud.google.com/sql/instances','https://([[:alnum:]]+\.?){3,4}/?')
= https://console.cloud.google.com/ 		No N/A Utilizza REGEXP_MATCH di PostgreSQL per ottenere funzionalità simili.
REGEXP_COUNT Restituisce il numero di volte in cui un pattern si verifica in una stringa di origine. No N/A Utilizza REGEXP_MATCH di PostgreSQL per ottenere funzionalità simili.
REGEXP_INSTR Cerca una posizione della stringa (indice) per un pattern di
espressione regolare.		 No N/A Convertire la funzionalità al livello di applicazione.
REVERSE Restituisce una stringa invertita.

REVERSE('PostgreSQL') = LQSergtsoP 		Sì REVERSE Equivalente a Oracle:

REVERSE('PostgreSQL') = LQSergtsoP
Funzioni numeriche
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente Cloud SQL per PostgreSQL Funzione corrispondente di Cloud SQL per PostgreSQL Specifiche o implementazione delle funzioni Cloud SQL per PostgreSQL
ABS Valore assoluto di n:

ABS(-4.6) = 4.6 		Sì ABS Equivalente a Oracle:

ABS(-4.6) = 4.6
CEIL Restituisce il numero intero più piccolo maggiore di o uguale a n:

CEIL(21.4) = 22 		Sì CEIL Equivalente a Oracle:

CEIL(21.4) = 22
FLOOR Restituisce il numero intero più grande uguale o inferiore a n:

FLOOR(-23.7) = -24 		Sì FLOOR Equivalente a Oracle:

FLOOR(-23.7) = -24
MOD Restituisce il resto di m diviso per n:

MOD(10, 3) = 1 		Sì MOD Equivalente a Oracle:

MOD(10, 3) = 1
ROUND Restituisce n arrotondato al numero intero a destra del punto decimale:

ROUND(1.39, 1) = 1.4 		Sì ROUND Equivalente a Oracle:

ROUND(1.39, 1) = 1.4
TRUNC
(numero)		 Restituisce n1 troncati a n2 cifre decimali:

TRUNC(99.999) = 99
TRUNC(99.999, 0) = 99 		Sì TRUNCATE
(numero)		 Equivalente a Oracle:

TRUNC(99.999) = 99
TRUNC(99.999, 0) = 99
Funzioni DateTime
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente Cloud SQL per PostgreSQL Funzione corrispondente di Cloud SQL per PostgreSQL Specifiche o implementazione delle funzioni Cloud SQL per PostgreSQL
SYSDATE Restituisce la data e l'ora correnti impostate per il sistema operativo su cui si trova il server di database:

SELECT SYSDATE FROM DUAL
= 31-JUL-2019 		Parzialmente con formattazione e nome della funzione diversi CURRENT_TIMESTAMP CURRENT_TIMESTAMP restituisce un formato di data/ora diverso rispetto alla funzione SYSDATE di Oracle:

SELECT CURRENT_TIMESTAMP
= 2019-07-31 06:46:40.171477+00
SYSTIMESTAMP Restituisce la data di sistema, inclusi i secondi frazionari e il fuso orario:

SELECT SYSTIMESTAMP FROM DUAL
= 01-JAN-19 07.37.11.622187000 AM +00:00 		Parzialmente con nome funzione diverso CURRENT_TIMESTAMP Cloud SQL per PostgreSQL restituisce un formato di data/ora diverso rispetto a Oracle. La formattazione della data deve corrispondere ai formati originali di data/ora:

SELECT CURRENT_TIMESTAMP
= 2019-01-31 07:37:11.622187+00
LOCAL TIMESTAMP Restituisce la data e l'ora correnti nel fuso orario della sessione in un valore del tipo di dati TIMESTAMP:

SELECT LOCALTIMESTAMP FROM DUAL
= 01-JAN-19 10.01.10.123456 PM 		Parzialmente con formattazione di data/ora diversa LOCAL TIMESTAMP Cloud SQL per PostgreSQL restituisce un formato di data/ora diverso rispetto a Oracle. La formattazione della data deve corrispondere al formato di data/ora originale:

SELECT LOCALTIMESTAMP
= 2019-01-31 07:37:11.622187+00
CURRENT_DATE Restituisce la data corrente nel fuso orario della sessione:

SELECT CURRENT_DATE FROM DUAL
= 31-JAN-19 		Parzialmente con formattazione di data/ora diversa CURRENT_ DATE Cloud SQL per PostgreSQL restituisce un formato di data/ora diverso da Oracle. La formattazione della data deve corrispondere al formato di data/ora originale:

SELECT CURRENT_DATE
= 2019-01-31
CURRENT_TIMESTAMP Restituisce la data e l'ora correnti nel fuso orario della sessione:

SELECT CURRENT_TIMESTAMP FROM DUAL
= 31-JAN-19 06.54.35.543146 AM +00:00 		Parzialmente con una formattazione data/ora diversa CURRENT_TIMESTAMP Cloud SQL per PostgreSQL restituisce un formato data/ora diverso da Oracle. Il formato della data dovrà corrispondere al formato data/ora originale:

SELECT CURRENT_TIMESTAMP FROM DUAL
= 2019-01-31 07:37:11.622187+00s
ADD_MONTHS Restituisce la data più mesi interi:

ADD_MONTHS(SYSDATE, 1)
= 31-JAN-19 		No N/A Per ottenere la stessa funzionalità in Cloud SQL per PostgreSQL, utilizza gli operatori + / - e specifica l'intervallo di tempo:

SELECT CURRENT_TIMESTAMP + INTERVAL '1 MONTH'
= 2019-01-31 07:37:11.622187+00s
EXTRACT
(parte della data)		 Restituisce il valore di un campo data/ora specificato da un'espressione di data/ora o intervallo:

EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019 		Sì EXTRACT
(parte della data)		 Equivalente a Oracle:

EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019
LAST_DAY Restituisce la data dell'ultimo giorno del mese che contiene la data specificata:

LAST_DAY('01-JAN-2019')
= 31-JAN-19 		No N/A Come soluzione alternativa, utilizza DATE_TRUNC e un operatore + per calcolare l'ultimo giorno del mese. La formattazione della data deve corrispondere a quella originale di data e ora:

SELECT DATE_TRUNC('MONTH', DATE '01-JAN-2019') + INTERVAL '1 MONTH -1 DAY'
= 2019-01-31
MONTH_BETWEEN Restituisce il numero di mesi compresi tra le date date1 e data 2:

MONTHS_BETWEEN(SYSDATE, SYSDATE-60)
= 1.96 		Parzialmente
con
Funzione: formattazione della data/ora diversa		 AGE La funzione AGE di Cloud SQL per PostgreSQL restituisce l'intervallo tra due timestamp:

AGE(DATE '01-JAN-2019', DATE '01-JAN-2019' - 60)
= 1 mon 29 days
Per ottenere gli stessi valori della funzione MONTH_BETWEEN Oracle, è richiesta una conversione più specifica.
TO_CHAR (data/ora) Converte una data/ora o un timestamp in un tipo di dati in un valore del tipo di dati VARCHAR2
nel formato specificato dal formato data:

TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS')
= 01-01-2019 10:01:01 		Sì To_CHAR Equivalente a Oracle:

TO_CHAR(CURRENT_TIMESTAMP,'DD-MM-YYYY HH24:MI:SS');
01-01-2019 10:01:01
Funzioni di codifica e decodifica
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente Cloud SQL per PostgreSQL Funzione corrispondente di Cloud SQL per PostgreSQL Specifiche o implementazione delle funzioni Cloud SQL per PostgreSQL
DECODE Confronta l'espressione con ogni valore di ricerca uno alla volta utilizzando un'istruzione IF-THEN-ELSE. No CASE Utilizza l'istruzione CASE di Cloud SQL per PostgreSQL per ottenere una funzionalità simile.
DUMP Restituisce un valore VARCHAR2 contenente il codice del tipo di dati, la lunghezza in byte e la rappresentazione interna dell'espressione. No N/A Non supportati.
ORA_HASH Calcola un valore hash per una data espressione. No MD5 / SHA224 / SHA256 / SHA385 / SHA512 Utilizza la funzione MD5 di Cloud SQL per PostgreSQL per il checksum a 128 bit o la funzione SHA per il checksum a 160 bit
per generare valori hash.
Funzioni di conversione
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente Cloud SQL per PostgreSQL Funzione corrispondente di Cloud SQL per PostgreSQL Specifiche o implementazione delle funzioni Cloud SQL per PostgreSQL
CAST Converte un tipo di dati integrato o un valore di raccolta in un altro tipo di dati integrato o valore di raccolta:

CAST('1' as int) + 1
= 2 		Parzialmente CAST La funzione CAST di Cloud SQL per PostgreSQL è simile alla CAST funzionalità di Oracle, ma in alcuni casi deve essere modificata a causa delle differenze nei tipi di dati tra i due database:

CAST('1' as int) + 1
= 2
CONVERT Converte una stringa di caratteri da un set di caratteri a un altro:

CONVERT ( 'Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')
= ?? ?? ?? A B C 		Parzialmente CONVERT La funzione CONVERT di Cloud SQL per PostgreSQL restituisce un valore bytea, ovvero una stringa binaria anziché VARCHAR o TEXT. Anche i set di caratteri supportati da PostgreSQL sono diversi da Oracle.

CONVERT('Ä Ê Í Õ Ø A B C D E', 'UTF8', 'LATIN1')
= [Binary representation of the string in LATIN1 encoding]
TO_CHAR
(stringa/numerico)		 La funzione converte un numero o una data in una stringa:

TO_CHAR(22.73,'$99.9')
= $22.7 		Parzialmente TO_CHAR La funzione TO_CHAR di Cloud SQL per PostgreSQL ha funzionalità simile a Oracle. Cloud SQL per PostgreSQL supporta un elenco di stringhe di formattazione leggermente diverso. Per impostazione predefinita, Cloud SQL per PostgreSQL prenota una colonna per il segno, quindi ci sarà uno spazio prima dei numeri positivi. Questo può essere eliminato utilizzando il prefisso FM:

TO_CHAR(22.73,'FM$99.9')
= $22.7
TO_DATE La funzione Oracle TO_DATE converte una stringa in una data in base al formato data/ora specifico dell'origine:

TO_DATE('2019/01/01', 'yyyy-mm-dd')
= 01-JAN-2019 		Parzialmente TO_DATE La funzione TO_DATE di Cloud SQL per PostgreSQL ha funzionalità simile a Oracle. Cloud SQL per PostgreSQL supporta un elenco di stringhe di formattazione leggermente diverso:

TO_DATE('2019/01/01', 'yyyy-mm-dd')
= 2019-01-01
TO_NUMBER Converte l'espressione in un valore di un tipo di dati NUMBER:

TO_NUMBER('01234')
= 1234 		Parzialmente TO_NUMBER La funzione TO_NUMBER di Cloud SQL per PostgreSQL richiede una stringa di formattazione come input, mentre è facoltativa in Oracle:

TO_NUMBER('01234','99999')
= 1234
In alternativa, puoi usare la funzione CAST per le conversioni che non richiedono stringhe di formattazione complesse:

CAST('01234' AS NUMERIC)
= 1234
Funzioni SELECT condizionali
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente Cloud SQL per PostgreSQL Funzione corrispondente di Cloud SQL per PostgreSQL Specifiche o implementazione delle funzioni Cloud SQL per PostgreSQL
CASE L'istruzione CASE sceglie in una sequenza di condizioni ed esegue un'
istruzione corrispondente con la seguente sintassi:

CASE WHEN condition THEN result [WHEN ...] [ELSE result] END 		Sì CASE Equivalente a Oracle:

CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
Funzioni nulle
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente Cloud SQL per PostgreSQL Funzione corrispondente di Cloud SQL per PostgreSQL Specifiche o implementazione delle funzioni Cloud SQL per PostgreSQL
COALESCE Restituisce la prima espressione con valore non null nell'elenco di espressioni:

COALESCE(null, '1', 'a')
= a 		Sì COALESCE Equivalente a Oracle:

COALESCE(null, '1', 'a')
= 1
NULLIF Confronta expr1 ed expr2. Se sono uguali, la funzione restituisce null. Se non sono uguali, la funzione restituisce expr1:

NULLIF('1', '2')
= 1 		Sì NULLIF Equivalente a Oracle:

NULLIF('1', '2')
= 1
NVL Sostituisci null (restituito come vuoto) con una stringa nei risultati di una query:

NVL(null, 'a')
= a 		No COALESCE In alternativa, utilizza la funzione COALESCE:

COALESCE(null, 'a')
= a
NVL2 Determinare il valore restituito da una query a seconda che un'espressione specificata
sia nulla o meno.		 No COALESCE In alternativa, utilizza la funzione COALESCE:

COALESCE(null, 1, 'a')
= 1
Funzioni di ambiente e identificatore
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente Cloud SQL per PostgreSQL Funzione corrispondente di Cloud SQL per PostgreSQL Specifiche o implementazione delle funzioni Cloud SQL per PostgreSQL
SYS_GUID Genera e restituisce un identificatore univoco globale (valore RAW) formato
da 16 byte:

SELECT SYS_GUID() FROM DUAL
= 8EFA4A31468B4C6DE05011AC0200009E 		Parzialmente con nome e formato della funzione diversi UUID_GENERATE_V4 Cloud SQL per Cloud SQL per PostgreSQL supporta l'estensione uuid-ossp che fornisce un elenco di funzioni che generano UUID come UUID_GENERATE_V4:

SELECT UUID_GENERATE_v4()
= eaf356a6-2847-478d-af3b-6883f5ac6af2
UID Restituisce un numero intero che identifica in modo univoco l'utente della sessione (l'utente
che ha eseguito l'accesso):

SELECT UID FROM DUAL
= 43 		No N/A N/A
USER Restituisce il nome del nome utente della sessione corrente:

SELECT USER FROM DUAL
= UserName 		Sì USER Equivalente a Oracle:

SELECT USER;
= postgres
USERENV Restituisce informazioni sulla sessione utente corrente con la configurazione del parametro attuale:

SELECT USERENV('LANGUAGE') FROM DUAL
= ENGLISH_AMERICA.AL32UTF8 		No N/A Sebbene non esista una funzione USERENV equivalente in Cloud SQL per PostgreSQL, è possibile recuperare singoli parametri come USERENV('SID') utilizzando funzioni di informazione di sistema come PG_BACKGROUND_PID().
ROWID Il server Oracle assegna a ogni riga in ogni tabella un valore ROWID univoco per identificare la riga nella tabella. ROWID è l'indirizzo della riga che contiene il numero dell'oggetto dati, il blocco dati della riga, la posizione della riga e il file di dati. Parzialmente con nome funzione diverso ctid ctid in Cloud SQL per PostgreSQL identifica la posizione fisica della versione della riga all'interno della tabella, che è simile a ROWID di Oracle.
ROWNUM Restituisce un numero che rappresenta l'ordine con cui una riga viene selezionata da Oracle da una tabella o da tabelle unite. No LIMIT or ROW_NUMBER() Anziché limitare il numero di risultati restituiti dalle query utilizzando ROWNUM, Cloud SQL per PostgreSQL supporta LIMIT e OFFSET per scopi simili.

ROW_NUMBER() La funzione finestra potrebbe essere una soluzione alternativa a ROWNUM di Oracle per altri scenari. Tuttavia, l'ordine dei risultati e i delta di prestazioni devono essere considerati prima di utilizzarli come sostituzione.
Funzioni di aggregazione (gruppo)
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente Cloud SQL per PostgreSQL Funzione corrispondente di Cloud SQL per PostgreSQL Specifiche o implementazione delle funzioni Cloud SQL per PostgreSQL
AVG Restituisce il valore medio della colonna o dell'espressione. Sì AVG Equivalente a Oracle
COUNT Restituisce il numero di righe restituite da una query. Sì COUNT Equivalente a Oracle
COUNT (DISTINCT) Restituisce il numero di valori univoci nella colonna o nell'espressione. Sì COUNT
(DISTINCT) 		Equivalente a Oracle
MAX Restituisce il valore massimo della colonna o dell'espressione. Sì MAX Equivalente a Oracle
MIN Restituisce il valore minimo della colonna o dell'espressione. Sì MIN Equivalente a Oracle
SUM Restituisce la somma dei valori della colonna o dell'espressione. Sì SUM Equivalente a Oracle
LISTAGG Visualizza i dati all'interno di ciascun gruppo in base a una singola riga specificata nella clausola ORDER BY concatenando i valori della colonna di misurazione:

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

-- Single line results
= Accounting, Administration, Benefits, Construction 		No STRING_AGG Utilizza la funzione STRING_AGG di Cloud SQL per PostgreSQL per restituire risultati simili a quelli di Oracle, in caso di differenze di sintassi in alcuni casi:

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

-- Single line results
= Accounting, Administration, Benefits, Construction
Recupero Oracle 12c
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente Cloud SQL per PostgreSQL Funzione corrispondente di Cloud SQL per PostgreSQL Specifiche o implementazione delle funzioni Cloud SQL per PostgreSQL
FETCH Recupera le righe di dati dal set di risultati di una query su più righe:

SELECT * FROM
EMPLOYEES
FETCH FIRST 10 ROWS ONLY; 		No LIMIT Utilizza la clausola LIMIT di Cloud SQL per PostgreSQL per recuperare solo un insieme specifico di record:

SELECT * FROM EMPLOYEES LIMIT 10;

Filtri, operatori e sottoquery di base

Durante la conversione, i filtri di base, le funzioni degli operatori e le sottoquery sono relativamente semplici e richiedono uno sforzo minimo o nullo.

Note sulle conversioni

Esamina e gestisci i formati di data perché i formati Oracle e Cloud SQL per PostgreSQL restituiscono risultati predefiniti diversi:

  • La funzione Oracle SYSDATE restituisce per impostazione predefinita 01-AUG-19.
  • La funzione PostgreSQL CURRENT_DATE restituisce per impostazione predefinita 2019-08-01 (nessuna ora del giorno anche con la formattazione). Per recuperare la data e l'ora correnti, utilizza la funzione CURRENT_TIMESTAMP che per impostazione predefinita restituisce 2019-08-01 00:00:00.000000+00.
  • I formati di data e ora possono essere impostati utilizzando le funzioni Cloud SQL per PostgreSQL TO_CHAR.
Funzione o sottoquery Oracle Equivalente Cloud SQL per PostgreSQL Funzione o sottoquery corrispondente di Cloud SQL per PostgreSQL Implementazione o specifica delle funzioni di Cloud SQL per PostgreSQL
EXISTS/ NOT EXISTS Sì EXISTS/ NOT EXISTS SELECT * FROM DEPARTMENTS D
WHERE EXISTS (SELECT 1
FROM EMPLOYEES E
WHERE
E.DEPARTMENT_ID =
D.DEPARTMENT_ID);
IN/NOT IN Sì 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 Sì LIKE/NOT LIKE SELECT * FROM EMPLOYEES
WHERE FIRST_NAME LIKE '_e_n%';
BETWEEN/ NOT BETWEEN Sì BETWEEN/
NOT BETWEEN 		SELECT * FROM EMPLOYEES
WHERE EXTRACT(YEAR FROM HIRE_DATE)
NOT BETWEEN 2001 and 2004;
AND/OR Sì AND/OR SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(100, 101)
AND (SALARY >= 1000 OR HIRE_DATE <= '2006-02-05');
SubQuery Sì SubQuery Cloud SQL per PostgreSQL supporta le sottoquery a livello di SELECT, per le istruzioni JOIN e per l'applicazione di filtri nelle clausole 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);
Operatori Sì Operatori Cloud SQL per PostgreSQL supporta tutti gli operatori di base:

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

Funzioni analitiche (o funzioni di finestra e ranking)

Le funzioni analitiche Oracle estendono la funzionalità delle operazioni SQL standard fornendo capacità di calcolare valori aggregati su un gruppo di righe (ad esempio, RANK(), ROW_NUMBER(), FIRST_VALUE()). Queste funzioni vengono applicate a record partizionati logicamente all'interno dell'ambito di una singola espressione di query. Sono comunemente utilizzati nel data warehousing, insieme a report e analisi di Business Intelligence.

Note sulle conversioni

Cloud SQL per PostgreSQL supporta molte funzioni analitiche. Sono note in Postgres come funzioni aggregate e funzioni finestra. Se la tua applicazione utilizza una funzione meno comune non supportata in Postgres, dovrai cercare un'estensione supportata o spostare la logica al livello di applicazione.

La tabella seguente elenca le funzioni analitiche più comuni di Oracle.

Famiglia di funzioni Funzioni correlate Supportato da Cloud SQL per PostgreSQL
Analisi e ranking RANK
AVERAGE_RANK
DENSE_RANK
ROW_NUMBER
PERCENT_RANK
CUME_DIST
NTILE
FIRST_VALUE
LAST_VALUE
OVER (PARTITION BY...) 		Sì (tranne AVERAGE_RANK)
Gerarchica CONNECT BY
HIER_ANCESTOR
HIER_CHILD_COUNT
HIER_DEPTH
HIER_LEVEL
HIER_ORDER
HIER_PARENT
HIER_TOP 		No
Sfasamento LAG
LAG_VARIANCE LAG_VARIANCE_PERCENT LEAD
LEAD_VARIANCE LEAD_VARIANCE_PERCENT 		Sì (solo LAG e LEAD)

Espressione della tabella comune (CTE)

Le CTE forniscono un modo per implementare la logica del codice sequenziale per riutilizzare codice SQL che potrebbe essere troppo complesso o non efficiente per un uso multiplo. Le CTE possono essere denominate e utilizzate più volte in diverse parti di un'istruzione SQL con la clausola WITH. Le CTE sono supportate sia da Oracle che da Cloud SQL per PostgreSQL.

Esempi
Oracle e Cloud SQL per 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;

Istruzione MERGE

L'istruzione MERGE (o UPSERT) consente di specificare singole istruzioni SQL che eseguono operazioni DML in modo condizionale in un'operazione MERGE, anziché in una singola operazione DML eseguita separatamente. Seleziona i record dalla tabella di origine e poi, specificando una struttura logica, esegue automaticamente più operazioni DML sulla tabella di destinazione. Questa funzione consente di evitare di utilizzare più inserti, aggiornamenti o eliminazioni. Tieni presente che MERGE è un'istruzione deterministica, il che significa che una volta che una riga è stata elaborata dall'istruzione MERGE, non può essere elaborata di nuovo utilizzando la stessa istruzione MERGE.

Note sulle conversioni

Cloud SQL per PostgreSQL non supporta la funzionalità MERGE, a differenza di Oracle. Per simulare parzialmente la funzionalità MERGE, Cloud SQL per PostgreSQL fornisce le istruzioni INSERT ... ON CONFLICT DO UPDATE:

  • INSERT… ON CONFLICT DO UPDATE: se una riga inserita causa un errore univoco di violazione o di violazione di un vincolo di esclusione, viene intrapresa l'azione alternativa specificata nella clausola ON CONFLICT DO UPDATE, ad esempio:
INSERT INTO tbl (a,b,c) VALUES (1,2,3)
  ON CONFLICT (a) DO UPDATE SET b = 2, c = 3;

Un'altra soluzione consiste nel convertire la funzionalità MERGE in una procedura archiviata per gestire le operazioni DML, utilizzando i comandi INSERT, UPDATE e DELETE con gestione delle eccezioni e dei duplicati.

Suggerimenti istruzione SQL

Oracle offre una vasta raccolta di suggerimenti di query SQL che consentono agli utenti di influenzare il comportamento dell'ottimizzazione, con l'obiettivo di produrre piani di esecuzione delle query più efficienti. Cloud SQL per PostgreSQL non offre un livello di istruzione SQL simile, che suggerisce un meccanismo per influenzare l'ottimizzatore.

Per influenzare i piani di query scelti dallo strumento di pianificazione delle query, Cloud SQL per PostgreSQL fornisce un set di parametri di configurazione che possono essere applicati a livello di sessione. Gli effetti di questi parametri di configurazione spaziano dall'attivazione/disattivazione di un determinato metodo di accesso alla regolazione delle costanti dei costi di pianificazione. Ad esempio, la seguente istruzione disabilita l'utilizzo da parte dello strumento di pianificazione delle query di tipi di piano di scansione sequenziale, come le scansioni complete delle tabelle:

SET ENABLE_SEQSCAN=FALSE;

Per modificare la stima dei costi dello strumento di pianificazione per il recupero di una pagina del disco casuale (il valore predefinito è 4,0), utilizza la seguente istruzione:

SET RANDOM_PAGE_COST=2.0;

La riduzione di questo valore fa sì che Cloud SQL per PostgreSQL preferisca le analisi dell'indice. L'ingrandimento avviene al contrario.

Note sulle conversioni

Poiché esistono differenze fondamentali tra gli ottimizzatori di Oracle e Cloud SQL per PostgreSQL e il fatto che Cloud SQL per PostgreSQL non supporta i suggerimenti di query SQL in stile Oracle, ti consigliamo di rimuovere qualsiasi suggerimento sulle query durante la migrazione a Cloud SQL per PostgreSQL. Quindi, esegui rigorosi test delle prestazioni tramite gli strumenti Cloud SQL per PostgreSQL, esamina le query utilizzando i piani di esecuzione e regola i parametri dell'istanza o della sessione in base al caso d'uso.

Piani di esecuzione

Lo scopo principale dei piani di esecuzione è fornire uno sguardo dall'interno alle scelte effettuate dall'ottimizzatore di query per accedere ai dati del database. Lo strumento di ottimizzazione delle query genera piani di esecuzione per le istruzioni SELECT, INSERT, UPDATE e DELETE per gli utenti del database, consentendo inoltre agli amministratori di avere una visione migliore su query specifiche e operazioni DML. Sono particolarmente utili quando devi ottimizzare le prestazioni delle query, ad esempio per determinare le prestazioni degli indici o determinare se mancano indici da creare.

I piani di esecuzione possono essere influenzati da volumi di dati, statistiche dei dati e parametri di istanza (parametri globali o di sessione).

Considerazioni sulle conversioni

I piani di esecuzione non sono oggetti di database di cui è necessario eseguire la migrazione, ma uno strumento per analizzare le differenze di prestazioni tra Oracle e Cloud SQL per PostgreSQL che eseguono la stessa istruzione su set di dati identici.

Cloud SQL per PostgreSQL non supporta la sintassi, le funzionalità o lo stesso output del piano di esecuzione di Oracle.

Ecco un esempio di piano di esecuzione:

Piano di esecuzione Oracle Piano di esecuzione di Cloud SQL per 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)

Stored procedure, funzioni e trigger

PL/SQL è il linguaggio procedurale esteso Oracle per la creazione, l'archiviazione e l'applicazione di soluzioni basate su codice all'interno del database. In generale, le procedure e le funzioni archiviate di database sono elementi di codice costituiti da SQL ANSI e SQL procedurale esteso, ad esempio PL/SQL per Oracle e linguaggio procedurale MySQL per MySQL. PL/pgSQL è per il linguaggio procedurale esteso di PostgreSQL.

Lo scopo di queste funzioni e stored procedure è fornire soluzioni per i requisiti più adatti per l'esecuzione dall'interno del database e non dall'applicazione (ad esempio prestazioni, compatibilità e sicurezza). Sebbene sia le stored procedure che le funzioni utilizzino PL/SQL, le stored procedure vengono utilizzate principalmente per eseguire operazioni DDL/DML e le funzioni vengono utilizzate principalmente per eseguire calcoli per restituire risultati specifici.

Da PL/SQL a PL/pgSQL

Dal punto di vista della migrazione da Oracle PL/SQL a Cloud SQL per PostgreSQL, PL/pgSQL è simile a Oracle PL/SQL in termini di struttura e sintassi. Tuttavia, esistono alcune differenze principali che richiedono una migrazione del codice. Ad esempio, i tipi di dati di Oracle e Cloud SQL per PostgreSQL sono diversi ed è spesso necessaria una traduzione per assicurarsi che il codice di cui è stata eseguita la migrazione utilizzi i nomi dei tipi di dati corrispondenti supportati da Cloud SQL per PostgreSQL. Per una discussione dettagliata delle differenze tra i due linguaggi, consulta Trasferimento da Oracle PL/SQL.

Privilegi e sicurezza degli oggetti di codice

In Oracle, per creare una stored procedure o una funzione, l'utente deve disporre del privilegio di sistema CREATE PROCEDURE (per creare procedure o funzioni con altri utenti diversi, gli utenti del database devono disporre del privilegio CREATE ANY PROCEDURE). Per eseguire una stored procedure o una funzione, gli utenti del database devono disporre del privilegio EXECUTE.

In PostgreSQL, per creare una procedura di codice o una funzione, l'utente deve disporre del privilegio USAGE. Per eseguire una procedura o una funzione, l'utente deve disporre del privilegio EXECUTE sulla procedura o sulla funzione.

Per impostazione predefinita, una procedura o una funzione PL/pgSQL è definita come SECURITY INVOKER, il che significa che la procedura o la funzione deve essere eseguita con i privilegi dell'utente che la chiama. In alternativa, è possibile specificare SECURITY DEFINER in modo che la funzione venga eseguita con i privilegi dell'utente proprietario.

Sintassi delle stored procedure e delle funzioni di Cloud SQL per PostgreSQL

L'esempio seguente mostra la procedura e la sintassi delle funzioni archiviate di Cloud SQL per 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'
  } ...

Trigger

Un trigger è una stored procedure che viene attivata quando si verifica un evento specifico. In Oracle, l'evento di trigger è associato a una tabella, una vista, uno schema o il database. Il tipo di eventi di attivazione include:

  • Istruzioni DML (Data Manipulation Language) (ad esempio INSERT, UPDATE, DELETE)
  • Istruzioni DDL (Data Definition Language) (ad esempio CREATE, ALTER, DROP)
  • Eventi di database (ad esempio LOGON, STARTUP, SHUTDOWN)

I trigger Oracle possono essere dei seguenti tipi:

  • Trigger semplice: attivato esattamente una volta, prima o dopo l'evento di trigger specificato
  • Trigger composto: attivato in corrispondenza di più eventi
  • Trigger di INSTEAD OF: un tipo speciale di trigger DML per fornire un meccanismo di aggiornamento trasparente per viste complesse e non modificabili
  • Trigger di sistema: attivato in corrispondenza di eventi di database specifici

In Cloud SQL per PostgreSQL, un trigger viene attivato prima o dopo un'operazione DML su una tabella, vista o tabella esterna specifica. L'attivatore INSTEAD OF è supportato per fornire un meccanismo di aggiornamento delle viste. Un trigger nelle operazioni DDL è chiamato attivatore di eventi. Cloud SQL per PostgreSQL non supporta i trigger di sistema di Oracle basati su eventi del database.

A differenza dei trigger Oracle, quelli di Cloud SQL per PostgreSQL non supportano l'utilizzo di un blocco PL/pgSQL anonimo come corpo del trigger. Una funzione con nome che accetta zero o più argomenti e restituisce un trigger di tipo deve essere fornita nella dichiarazione del trigger. Questa funzione viene eseguita quando si attiva l'attivatore.

Sintassi dei trigger di trigger e trigger di eventi di Cloud SQL per PostgreSQL

L'esempio seguente mostra la sintassi dei trigger e dei trigger di eventi di Cloud SQL per 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 può essere uno dei seguenti: 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 può essere uno dei seguenti: ddl_command_start, ddl_command_end, table_rewrite, sql_drop

filter_value può essere solo: TAG

filter_value può essere uno dei tag comando supportati.

Passaggi successivi