Migrazione degli utenti Oracle a Cloud SQL per MySQL: 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 MySQL versione 5.7, istanze di seconda generazione. La serie include le seguenti parti:

Query

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

Sintassi di base SELECT e FROM

Nome della funzionalità Oracle o del nome della sintassi Panoramica o implementazione di Oracle Supporto per MySQL Soluzione equivalente o alternativa MySQL
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 dell'output 

SELECT 1 FROM DUAL
Sì 

SELECT 1
OR
SELECT 1 FROM DUAL
Alias colonna 

SELECT COL1 AS C1
Sì 

SELECT COL1 AS C1
OR
SELECT COL1 C1
Nome tabella
sensibilità alle maiuscole 		Nessuna sensibilità alle maiuscole
(ad esempio, il nome della tabella può essere orders e/o ORDERS).		 No Distingue tra maiuscole e minuscole in base al nome della tabella definito (ad esempio, il nome della tabella può essere solo orders o ORDERS).

Ulteriori dettagli sulla sintassi SELECT di MySQL.

  • 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 semplificare le query complesse rimuovendo i calcoli composti o le operazioni di join, e accorpando al contempo diverse query separate in un'unica query semplificata.
    • Nota di conversione: le viste in linea Oracle non richiedono l'uso di alias, mentre MySQL richiede alias specifici per ogni visualizzazione in linea.

La tabella seguente presenta un esempio di conversione da Oracle a MySQL, sotto forma di visualizzazione in linea.

Oracle 11g/12c 

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
Cloud SQL per MySQL 5.7
Senza alias per la visualizzazione in linea:

mysql> SELECT FIRST_NAME,
              DEPARTMENT_ID,
              SALARY,
              DATE_COL
       FROM EMPLOYEES, (SELECT SYSDATE() AS DATE_COL FROM DUAL);


ERROR 1248 (42000): Every derived table must have its own alias

Aggiunta di un alias alla visualizzazione in linea:

mysql> SELECT FIRST_NAME,
              DEPARTMENT_ID,
              SALARY,
              DATE_COL
       FROM EMPLOYEES, (SELECT SYSDATE() AS DATE_COL FROM DUAL) AS A1;

L'output è simile al seguente:

+-------------+---------------+----------+---------------------+
| FIRST_NAME  | DEPARTMENT_ID | SALARY   | DATE_COL            |
+-------------+---------------+----------+---------------------+
| Steven      |            90 | 23996.00 | 2019-07-30 09:28:00 |
| Neena       |            90 | 22627.00 | 2019-07-30 09:28:00 |
| Lex         |            90 | 22627.00 | 2019-07-30 09:28:00 |

Istruzioni JOIN

Le istruzioni JOIN Oracle sono supportate dalle istruzioni MySQL JOIN, ad eccezione della clausola FULL JOIN. Inoltre, le istruzioni JOIN di MySQL supportano l'utilizzo di una sintassi alternativa, come la clausola USING, la clausola WHERE anziché la clausola ON e l'uso di SUBQUERY nell'istruzione JOIN.

La tabella seguente presenta un esempio di conversione JOIN.

Tipo di JOIN Oracle Supportato da MySQL Sintassi JOIN MySQL 

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
No Per trovare una soluzione alternativa, valuta l'utilizzo di UNION con le istruzioni LEFT e RIGHT JOIN. 

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

MySQL non supporta le funzioni Oracle INTERSECT e MINUS, ad eccezione delle funzioni UNION e UNION ALL:

  • 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.
  • MINUS: confronta due o più istruzioni SELECT, restituendo solo righe distinte dalla prima query che non sono restituite dalle altre istruzioni.

Note sulle conversioni

Quando esegui la conversione dalle funzioni Oracle INTERSECT e MINUS a MySQL, utilizza le istruzioni JOIN e IN e EXISTS come soluzione alternativa.

Esempi

Funzione Oracle Implementazione di Oracle Supporto per MySQL Soluzione equivalente o alternativa MySQL 

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
No 

SELECT COL1 FROM TBL1
WHERE COL1 IN
(SELECT COL1 FROM TBL2)


MINUS


SELECT COL1 FROM TBL1
MINUS
SELECT COL1 FROM TBL2
No 

SELECT A.COL1
FROM TBL1 A LEFT JOIN TBL2 B
ON USING(COL1)
WHERE B.COL1 IS NULL

Funzioni scalari (a riga singola) e di gruppo

MySQL offre un elenco completo di funzioni scalari (a riga singola) e di aggregazione. Alcune funzioni MySQL sono simili alle rispettive controparti Oracle (per nome e funzionalità o con un nome diverso ma con funzionalità simili). Anche se le funzioni MySQL possono avere nomi identici alle loro controparti Oracle, possono presentare funzionalità diverse.

Le seguenti tabelle descrivono dove Oracle e MySQL sono equivalenti per nome e funzionalità (specificati con "Yes") e dove è consigliata una conversione (tutti i casi diversi da "Yes").

Funzioni caratteri
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente MySQL Funzione corrispondente MySQL Implementazione o specifica della funzione MySQL 

CONCAT(str1,str2)
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(expr1,n,expr2)
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(char,p,n)
Restituisce una parte di char, che inizia nella posizione del carattere p, lunghezza della lunghezza della sottostringa n caratteri:

SUBSTR('MySQL', 3, 3)
= SQL
Sì 

SUBSTR(char,p,n)
Equivalente a Oracle:

SUBSTR('MySQL', 3, 3)
= SQL


INSTR(index,str)
Restituisce la posizione (indice) della stringa str:

INSTR('MySQL', 'y')
= 2
Sì 

INSTR
Equivalente a Oracle:

INSTR('MySQL', 'y')
= 2


REPLACE(char,str1,str2)
Restituisce un carattere con ogni occorrenza di una stringa di ricerca sostituita da una stringa di sostituzione: 

REPLACE('ORADB', 'ORA', 'MySQL')


= MySQLDB
Sì 

REPLACE(char,str1,str2)
Equivalente a Oracle:

REPLACE('ORADB', 'ORA', 'MySQL')


= MySQLDB


TRIM(str)
Elimina i caratteri iniziali e finali (o entrambi) da una stringa:

TRIM(both '-' FROM '-MySQL-')
= MySQL


TRIM('  MySQL ')
= MySQL
Sì 

TRIM(str)
Equivalente a Oracle:

TRIM(both '-' FROM '-MySQL-')
= MySQL


TRIM('  MySQL ')
= MySQL


LTRIM/RTRIM(str)
Rimuove dall'estremità sinistra o destra della stringa tutti i caratteri visualizzati nella ricerca:

LTRIM('   MySQL', ' ')
= MySQL
Parzialmente 

LTRIM/RTRIM(str)
Funzione Oracle R/LTRIM tranne una sostituzione del parametro (spazio vuoto o stringa). MySQL R/LTRIM elimina solo gli spazi vuoti, accettando solo la stringa di input:

LTRIM('   MySQL')
= MySQL


ASCII(char)
Restituisce la rappresentazione decimale nel set di caratteri del database del primo carattere di char: 

ASCII('A') = 65
Sì 

ASCII(char)
Equivalente a Oracle:

ASCII('A') = 65


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

CHR(65) = A
Parzialmente con nome funzione diverso 

CHAR(char)
MySQL utilizza la funzione CHAR per la stessa funzionalità; pertanto, devi modificare il nome di una funzione:

CHAR(65) = A


LENGTH(str)
Restituisce la lunghezza di una determinata stringa:


LENGTH ('MySQL') = 5
Sì 

LENGTH(str)
Equivalente a Oracle:

LENGTH('MySQL') = 5


REGEXP_REPLACE(str1,expr,str2)
Cerca un pattern di espressione regolare in una stringa:

REGEXP_REPLACE('John', '[hn].', '1') = Jo1
No N/A Supportata solo da MySQL versione 8. Come soluzione alternativa, utilizza la funzione REPLACE, se possibile, o convertila a livello di applicazione 

REGEXP_SUBSTR(str,expr)
Estende la funzionalità della funzione SUBSTR cercando un pattern 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 Funzionalità supportata solo da MySQL versione 8. Per trovare una soluzione alternativa, utilizza la funzione SUBSTR, se possibile, oppure converti la funzionalità al livello di applicazione. 

REGEXP_COUNT(str,expr)
Restituisce il numero di volte in cui un pattern si verifica in una stringa di origine. No N/A Per una soluzione alternativa, converti la funzionalità al livello di applicazione. 

REGEXP_INSTR(index,expr)
Cerca una posizione stringa (indice) per un modello di espressione regolare. No N/A Funzionalità supportata solo da MySQL versione 8. 

REVERSE(str)
Restituisce una stringa invertita

REVERSE('MySQL')
= LQSyM
Sì 

REVERSE
Equivalente a Oracle:

REVERSE('MySQL')
= LQSyM
Funzioni numeriche
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente MySQL Funzione corrispondente MySQL Implementazione o specifica della funzione MySQL 

ABS(n)
Valore assoluto di n: 

ABS(-4.6) = 4.6
Sì 

ABS
Equivalente a Oracle:

ABS(-4.6) = 4.6


CEIL(n)
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(n)
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(m,n)
Restituisce il resto di m diviso per n:

MOD(10, 3) = 1
Sì 

MOD(m,n)
Equivalente a Oracle:

MOD(10,3) = 1


ROUND(m,n)
Restituisce m arrotondato a n posizioni interi a destra del punto decimale:

ROUND(1.39,1) = 1.4
Sì 

ROUND
Equivalente a Oracle:

ROUND(1.39,1) = 1.4


TRUNC(n1, n2)
 Restituisce n1 troncato a n2 posizioni decimali:

TRUNC(99.999) = 99
TRUNC(99.999,0) = 99
Parzialmente con nome funzione diverso 

TRUNCATE(n1, n2)
 La funzione TRUNCATE di MySQL deve accettare un numero di input e un numero intero per specificare il livello di accuratezza alla destra del punto decimale:

TRUNCATE(99.999,0) = 99
Funzioni di data e ora
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente MySQL Funzione corrispondente MySQL Implementazione o specifica della funzione MySQL 

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 

SYSDATE()
SYSDATE() MySQL deve includere parentesi e restituire un formato di data/ora diverso rispetto alla funzione SYSDATE di Oracle:

SELECT SYSDATE()
FROM DUAL;
= 2019-01-31 10:01:01.0

Tieni presente che la formattazione di data e ora può essere modificata a livello di sessione.		 

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
MySQL restituisce una formattazione di data/ora diversa da quella di Oracle. È necessaria una formattazione della data (o una funzione di data diversa) per corrispondere alla formattazione originale di data/ora:

SELECT CURRENT_TIMESTAMP
FROM DUAL
= 2019-01-31 06:55:07


LOCAL_TIMESTAMP
Restituisce la data e l'ora correnti nel fuso orario della sessione in un valore del tipo di dati TIMESTAMP:

SELECT LOCAL_TIMESTAMP
FROM DUAL
= 01-JAN-19 10.01.10.123456 PM
Parzialmente con una formattazione di data/ora diversa. 

LOCAL_TIMESTAMP
MySQL restituisce una formattazione di data/ora diversa da quella di Oracle. È necessaria la formattazione di data/ora (o una funzione di data diversa) per rispettare la formattazione originale di data/ora:

SELECT LOCAL_TIMESTAMP
FROM DUAL
= 2019-01-01 10:01:01.0


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
MySQL restituisce una formattazione di data/ora diversa da quella di Oracle. È necessaria la formattazione di data/ora (o una funzione di data diversa) per rispettare la formattazione originale di data/ora:

SELECT CURRENT_DATE
FROM DUAL
= 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 formattazione di data/ora diversa 

CURRENT_TIMESTAMP
MySQL restituisce una formattazione di data/ora diversa da quella di Oracle. La formattazione di data/ora è obbligatoria (o l'utilizzo di una funzione di data diversa) affinché corrisponda alla formattazione originale di data/ora:

SELECT CURRENT_TIMESTAMP
FROM DUAL
= 2019-01-31 06:55:07


ADD_MONTHS
Restituisce la data più mesi interi:

ADD_MONTHS(SYSDATE, 1)
= 31-JAN-19
Parzialmente con nome funzione diverso 

ADDDATE
Per ottenere la stessa funzionalità, MySQL utilizza la funzione ADDDATE:

ADDDATE(SYSDATE(), 1)
= 2019-08-01 06:42:49.0

Per impostazione predefinita, MySQL restituisce data/ora e intervallo/formato diversi rispetto a Oracle. La formattazione di data/ora è obbligatoria (o una funzione di data diversa) per corrispondere alla formattazione di data/ora originale.
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:

LAST_DAY('01-JAN-2019')
= 31-JAN-19
Parzialmente con formattazione di data/ora diversa 

LAST_DAY
MySQL restituisce una formattazione di data/ora diversa da quella di Oracle. La formattazione di data/ora è obbligatoria (o una funzione di data diversa) per corrispondere alla formattazione originale di data/ora:

LAST_DAY('2019-01-01')
= 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 nome funzione diverso 

PERIOD_DIFF(date1,date2)
La funzione MySQL PERIOD_DIFF restituisce la differenza in mesi come numero intero tra due periodi (formattati come YYMM o YYYYMM):

PERIOD_DIFF(
'201903', '201901')
= 2

Per ottenere gli stessi valori della funzione MONTH_BETWEEN Oracle, sarà necessaria una conversione più specifica
TO_CHAR (data/ora) Converte un tipo di dati data/ora o timestamp 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
Parzialmente con nome funzione diverso 

DATE_FORMAT
La funzione MySQL DATE_FORMAT formatta una data come specificato da una definizione del formato data:

DATE_FORMAT(
SYSDATE(),'%d-%m-%Y %H:%i:%s')
= 01-01-2019 10:01:01
Funzioni di codifica e decodifica
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente MySQL Funzione corrispondente MySQL Implementazione o specifica della funzione MySQL 

DECODE
Confronta l'espressione con ogni valore di ricerca uno alla volta utilizzando la funzionalità di un'istruzione IF-THEN-ELSE No 

CASE
Utilizza l'istruzione CASE di MySQL 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 di una data espressione. No N/A Non supportati. 

ORA_HASH
Calcola un valore hash per una data espressione. No 

MD5/SHA
Utilizza MySQL MD5 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 MySQL Funzione corrispondente MySQL Implementazione o specifica della funzione MySQL 

CAST
Converte un tipo di dati incorporato 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 MySQL è simile alla funzionalità di Oracle, ma in alcuni casi deve essere modificata a seconda che sia richiesta una conversione esplicita o implicita:

CAST('1' AS SIGNED) + 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 MySQL richiede alcune modifiche alla sintassi e ai parametri per restituire i risultati esatti come Oracle:

CONVERT('Ä Ê Í A B C ' USING utf8)
= Ä Ê Í A B C
TO_CHAR
(stringa/numerico)		 La funzione converte un numero o una data in una stringa: 

TO_CHAR(22.73,'$99.9')
= $22.7
No 

FORMAT
La funzione FORMAT di MySQL esegue il formato "#,###.##" da un numero, arrotondandolo a un certo numero di cifre decimali e poi restituisce il risultato come stringa. Ha una funzionalità diversa da Oracle:

CONCAT('$',
FORMAT(22.73, 1))
= $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 con nome della funzione e formattazione di data/ora diversa 

STR_TO_DATE
La funzione MySQL STR_TO_DATE accetta una stringa e restituisce una data specificata tramite il formato di data/ora:

STR_TO_DATE(
'2019/01/01', '%Y/%m/%d')
= 2019-01-01


TO_NUMBER
Converte l'espressione in un valore di un tipo di dati NUMBER:

TO_NUMBER('01234')
= 1234
No 

CAST
In alternativa, utilizza la funzione CAST di MySQL per restituire lo stesso risultato di Oracle TO_NUMBER:

CAST('01234' as SIGNED)
= 1234
Funzioni SELECT condizionali
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente MySQL Funzione corrispondente MySQL Implementazione o specifica della funzione MySQL 

CASE
L'istruzione CASE sceglie da una sequenza di condizioni ed esegue un'istruzione corrispondente con la sintassi seguente:

CASE WHEN condition THEN result
[WHEN ...] [ELSE result]
END
Sì 

CASE
Oltre alla funzione CASE, MySQL supporta anche l'utilizzo della gestione condizionale IF/ELSE all'interno dell'istruzione SELECT:

CASE WHEN condition THEN result
[WHEN ...] [ELSE result]
END
Funzioni nulle
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente MySQL Funzione corrispondente MySQL Implementazione o specifica della funzione MySQL 

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 espr1 ed espr2m. Se sono uguali, la funzione restituisce null. Se non sono uguali, la funzione restituisce expr1: 

NULLIF('1', '2')
= a
Sì 

NULLIF
Equivalente a Oracle:

NULLIF('1', '2')
= a


NVL
Sostituisci null (restituito come vuoto) con una stringa nei risultati di una query: 

NVL(null, 'a')
= a
No 

IFNULL
La funzione equivalente MySQL è la funzione IFNULL , che sostituisce i valori nulli con una determinata stringa:

IFNULL(null, 'a')
= a


NVL2
Determinare il valore restituito da una query a seconda che un'espressione specificata
sia nulla o meno.		 No 

CASE
L'istruzione CASE
sceglie da una sequenza di condizioni ed esegue un'istruzione corrispondente:

CASE WHEN condition THEN result
[WHEN ...] [ELSE result]
END
Funzioni di ambiente e identificatore
Funzione Oracle Specifica o implementazione della funzione Oracle Equivalente MySQL Funzione corrispondente MySQL Implementazione o specifica della funzione MySQL 

SYS_GUID
Genera e restituisce un identificatore univoco globale (valore RAW) composto da 16 byte:

SELECT SYS_GUID()
FROM DUAL
= 8EFA4A31468B4C6DE05011AC0200009E
No REPLACE e UUID Per trovare una soluzione alternativa, utilizza le funzioni MySQL REPLACE e UUID per simulare la funzione Oracle SYS_GUID:

REPLACE(
UUID(), '-', '')


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
Parzialmente 

USER + INSTR + SUBSTR
La funzione UTENTE MySQL restituisce il nome utente insieme al server di connessione (root@IP). Per recuperare solo il nome utente, utilizza altre funzioni di supporto:

SELECT
SUBSTR(USER(), 1, INSTR(USER(), '@') -1) FROM DUAL
= root


USERENV
Restituisce informazioni sulla sessione utente corrente con la configurazione del parametro attuale:

SELECT USERENV('LANGUAGE')
FROM DUAL
= ENGLISH_AMERICA.AL32UTF8
No 

SHOW SESSION
VARIABLES
Utilizza l'istruzione MySQL SHOW SESSION
VARIABLES per visualizzare le impostazioni per la sessione corrente:

SHOW SESSION VARIABLES LIKE '%collation%';
= utf8_general_ci


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. No N/A Se possibile, prova a emulare la stessa funzionalità con altre funzioni MySQL. 

ROWNUM
Restituisce un numero che rappresenta l'ordine con cui una riga viene selezionata da Oracle da una tabella o da tabelle unite. No N/A Se possibile, prova a emulare la stessa funzionalità con altre funzioni o variabili di sessione MySQL.
Funzioni di aggregazione (gruppo)
Funzione Oracle Specifica o implementazione
della funzione Oracle		 Equivalente
MySQL 		Funzione corrispondente MySQL Implementazione o specifica della funzione MySQL 

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 di una colonna o di un'espressione. Sì 

MAX
Equivalente a Oracle 

MIN
Restituisce il valore minimo di una colonna o di un'espressione. Sì 

MIN
Equivalente a Oracle 

SUM
Restituisce la somma del valore di una colonna o di un'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 

GROUP_CONCAT
Utilizza la funzione MySQL GROUP_CONCAT per restituire risultati simili a quelli di Oracle, aspettando differenze di sintassi in alcuni casi:

SELECT GROUP_CONCAT(
DEPARTMENT_NAME ORDER BY DEPARTMENT_NAME SEPARATOR ', ') DEPT
FROM DEPARTMENTS;


-- Single line results


= Accounting, Administration, Benefits, Construction
Recupero Oracle 12c
Funzione Oracle Specifica o implementazione
della funzione Oracle		 Equivalente
MySQL 		Funzione corrispondente MySQL Implementazione o specifica della funzione MySQL 

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 MySQL LIMIT 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 data perché i formati Oracle e MySQL restituiscono risultati predefiniti diversi:

  • La funzione Oracle SYSDATE restituisce per impostazione predefinita 01-AUG-19.
  • La funzione MySQL SYSDATE() restituisce per impostazione predefinita 2019-08-01 12:04:05.
  • I formati di data e ora possono essere impostati utilizzando le funzioni MySQL [DATE_FORMAT](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format) o [STR_TO_DATE](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date).
Funzione o sottoquery Oracle Equivalente MySQL Funzione o sottoquery corrispondente a MySQL Implementazione o specifica della funzione MySQL 

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
MySQL 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 MySQL supporta tutti gli operatori di base:

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

Funzioni analitiche (o funzioni di finestra e ranking)

Le funzioni analitiche Oracle estendono la funzionalità delle funzioni analitiche SQL standard fornendo capacità per calcolare i valori aggregati in base a un gruppo di righe. Queste funzioni possono essere applicate a set di risultati partizionati logicamente all'interno dell'ambito di una singola espressione di query. Di solito vengono utilizzati in combinazione con analisi e report di business intelligence, con il potenziale di aumentare le prestazioni delle query come alternativa all'ottenimento dello stesso risultato utilizzando codice SQL più complesso e non analitico.

Note sulle conversioni

  • MySQL versione 5.7 non fornisce funzioni analitiche che supportano una conversione di istruzioni SQL semplici. Tuttavia, questa funzionalità è stata aggiunta parzialmente in MySQL versione 8, rendendo le funzioni analitiche di conversione un punto da considerare, che probabilmente richiedono sforzi manuali nel processo di migrazione.
  • Una soluzione facoltativa è riscrivere il codice per eliminare l'uso di funzioni analitiche, ripristinare le soluzioni di codice SQL più tradizionali o spostare questa logica a un livello di applicazione.

Nella tabella seguente sono elencate le funzioni analitiche comuni di Oracle.

Famiglia di funzioni Funzioni correlate Supportato da MySQL 5.7
Analisi e ranking 

RANK
AVERAGE_RANK
DENSE_RANK
RANK ROW_NUMBER
PERCENT_RANK
CUME_DIST
NTILE
FIRST_VALUE
LAST_VALUE
OVER (PARTITION BY...)
No
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
No

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.

Note sulle conversioni

  • MySQL versione 5.7 non supporta le CTE, al contrario di MySQL versione 8.
  • Per una soluzione alternativa, utilizza le tabelle derivate o le query secondarie oppure riscrivi l'istruzione SQL per eliminare la funzionalità CTE.

Esempi

Oracle 

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;
MySQL 

SELECT * FROM (
SELECT CONCAT(E.FIRST_NAME, ' ', E.LAST_NAME) AS EMP_NAME,
       (SELECT COUNT(*)
        FROM EMPLOYEES D
        WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
        GROUP BY DEPARTMENT_ID) AS EMP_DEPT_COUNT
FROM EMPLOYEES E
ORDER BY 2 DESC) TBL
WHERE EMP_DEPT_COUNT IS NOT NULL;

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

A differenza di Oracle, MySQL versione 5.7 non supporta la funzionalità MERGE. Per simulare parzialmente la funzionalità MERGE, MySQL fornisce le istruzioni REPLACE e INSERT… ON DUPLICATE KEY UPDATE:

  • REPLACE: funziona come un'istruzione INSERT, ma se una riga precedente della tabella ha lo stesso valore di una nuova riga per un indice PRIMARY KEY o UNIQUE, la riga precedente viene eliminata prima dell'inserimento della nuova riga.

  • INSERT… ON DUPLICATE KEY UPDATE: se una riga inserita causa un valore duplicato in un indice PRIMARY KEY o UNIQUE, viene generato UPDATE della riga precedente per eliminare l'eccezione di chiave duplicata, ad esempio:

    INSERT INTO tbl (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE tbl SET c=c+1 WHERE a=1;

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 un'ampia raccolta di hint delle query SQL che consentono agli utenti di influenzare il comportamento di Optimize e il suo processo decisionale, con l'obiettivo di produrre piani di esecuzione delle query più efficienti. Oracle supporta oltre 60 diversi hint di database. MySQL fornisce un insieme limitato di suggerimenti sulle query.

In generale, MySQL versione 5.7 supporta due tipi di suggerimenti di query: OPTIMIZER HINTS e INDEX HINTS. I suggerimenti di ottimizzazione MySQL consentono di controllare il comportamento dell'ottimizzatore all'interno di singole istruzioni SQL, ad esempio:

SELECT /*+ NO_RANGE_OPTIMIZATION(tbl PRIMARY, col1_idx) */ col1 FROM tbl;

Suggerimenti disponibili dello strumento di ottimizzazione per MySQL versione 5.7

Nome suggerimento Panoramica dei suggerimenti Ambiti applicabili 

BKA, NO_BKA
Interessa l'elaborazione del join di accesso alle chiavi in batch Blocco query, tabella 

BNL, NO_BNL
Interessa l'elaborazione del blocco dei join a loop nidificato Blocco query, tabella 

MAX_EXECUTION_TIME
Limita il tempo di esecuzione dell'istruzione Globale 

MRR, NO_MRR
Interessa l'ottimizzazione della lettura su più intervalli Tabella, indice 

NO_ICP
Interessa l'ottimizzazione push-down delle condizioni dell'indice Tabella, indice 

NO_RANGE_OPTIMIZATION
Interessa l'ottimizzazione dell'intervallo Tabella, indice 

QB_NAME
Assegna un nome al blocco di query Blocco query 

SEMIJOIN, NO_SEMIJOIN
Interessa le strategie semi-unione Blocco query 

SUBQUERY
Interessa le strategie di materializzazione da IN a EXISTS. Blocco query

I suggerimenti indice MySQL forniscono allo strumento di ottimizzazione informazioni su come scegliere gli indici durante l'elaborazione delle query. Le parole chiave USE, FORCE o IGNORE vengono utilizzate per controllare il processo di utilizzo dell'indice di Optimize, ad esempio:

SELECT * FROM tbl USE INDEX (col1_index, col2_index);
-- OR
SELECT * FROM tbl IGNORE INDEX (col1_index, col2_index);

Note sulle conversioni

Poiché esistono differenze fondamentali tra l'ottimizzatore Oracle e MySQL e esiste una sovrapposizione limitata di eventuali sovrapposizioni tra i suggerimenti di query Oracle e MySQL, ti consigliamo di convertire qualsiasi istruzione Oracle SQL contenente suggerimenti di query non specificati sul database MySQL di destinazione.

Esegui l'ottimizzazione delle prestazioni di MySQL tramite gli strumenti MySQL (ad esempio MySQL Workbench per dashboard delle prestazioni in tempo reale) e funzionalità come l'esame delle query utilizzando i piani di esecuzione e la regolazione dei 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 per 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 MySQL che eseguono la stessa istruzione su set di dati identici.

MySQL non supporta la sintassi, le funzionalità o lo stesso output del piano di esecuzione di Oracle.

Esempi

Piano di esecuzione Oracle 

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 |
---------------------------------------------------------------------------------------------
Piano di esecuzione MySQL 

mysql> EXPLAIN SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105;


+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | EMPLOYEES | NULL       | const | PRIMARY       | PRIMARY | 3       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

Stored procedure, funzioni e trigger

PL/SQL è il linguaggio procedurale esteso Oracle utilizzato per creare, archiviare e applicare soluzioni basate su codice all'interno del database. In generale, le procedure e le funzioni archiviate nei database sono elementi di codice costituiti da linguaggio procedurale esteso ANSI SQL e SQL, ad esempio PL/SQL per Oracle, PL/pgSQL per PostgreSQL e linguaggio procedurale MySQL per MySQL. MySQL usa lo stesso nome del database per il proprio linguaggio procedurale esteso.

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.

Linguaggio procedurale da PL/SQL a MySQL

Dal punto di vista della migrazione del codice da Oracle PL/SQL a MySQL, l'implementazione procedurale di MySQL è diversa da quella di Oracle. Di conseguenza, la migrazione del codice è necessaria per convertire la funzionalità PL/SQL da Oracle in stored procedure e funzioni MySQL. Inoltre, il corpo del pacchetto e del pacchetto Oracle non sono supportati da MySQL, quindi quando esegui la conversione del codice, converti questi elementi (o analizzali) in singole unità di codice MySQL. Tieni presente che le stored procedure e le funzioni MySQL sono chiamate anche routine.

Proprietario oggetto di codice

In Oracle, il proprietario di una stored procedure o di una funzione è un utente specifico. In MySQL, il proprietario è uno schema specifico (creato in un database da un utente del database).

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 MySQL, per creare un elemento di codice, l'utente deve disporre dei privilegi CREATE ROUTINE e EXECUTE per l'esecuzione. La clausola DEFINER di MySQL definisce l'autore dell'oggetto di codice e l'utente deve disporre dei privilegi appropriati, ad esempio CREATE ROUTINE.

Sintassi delle stored procedure e delle funzioni MySQL

L'esempio seguente mostra la stored procedure e la syntax della funzione MySQL:

CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = user]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement