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 e indicazioni chiave relative alla pianificazione ed esecuzione delle migrazioni dei database Oracle® 11g/12c a Cloud SQL per MySQL versione 5.7, istanze di seconda generazione. La serie include le seguenti parti:

• Migrazione degli utenti Oracle a Cloud SQL per MySQL: terminologia e funzionalità
• Migrazione degli utenti Oracle a Cloud SQL per MySQL: tipi di dati, utenti e tabelle
• Migrazione degli utenti Oracle a Cloud SQL per MySQL: query, stored procedure, funzioni e trigger (questo documento)
• Migrazione degli utenti Oracle a Cloud SQL per MySQL: sicurezza, operazioni, monitoraggio e registrazione

Query

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

Sintassi di base di SELECT e FROM

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

SELECT 1 FROM DUAL

SELECT 1
OR
SELECT 1 FROM DUAL

SELECT COL1 AS C1

SELECT COL1 AS C1
OR
SELECT COL1 C1

Puoi leggere ulteriori dettagli sulla sintassi SELECT di MySQL.

• Visualizzazioni in linea
  • Le visualizzazioni in linea (note anche come tabelle derivate) sono istruzioni SELECT situate nella clausola FROM e utilizzate come sottoquery.
  • Le visualizzazioni in linea possono contribuire a semplificare le query complesse rimuovendo i calcoli composti o eliminando le operazioni di join, nonché comprimendo più query distinte in un'unica query semplificata.
  • Nota sulla conversione: le viste in linea di Oracle non richiedono l'utilizzo di alias, mentre MySQL richiede alias specifici per ogni vista in linea.

La tabella seguente mostra un esempio di conversione da Oracle a MySQL come visualizzazione in linea.

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

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

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

+-------------+---------------+----------+---------------------+
| 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 di Oracle sono supportate dalle istruzioni MySQL JOIN, tranne per la clausola FULL JOIN. Inoltre, le istruzioni JOIN di MySQL supportano l'uso di una sintassi alternativa, ad esempio la clausola USING, la clausola WHERE anziché la clausola ON e l'utilizzo di SUBQUERY nell'istruzione JOIN.

La seguente tabella mostra un esempio di conversione 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

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 e MINUS

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

• UNION: collega i set di risultati di due o più istruzioni SELECT ed elimina i record duplicati.
• UNION ALL: unisce i set 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 esiste un record in entrambi i set di dati.
• MINUS: confronta due o più istruzioni SELECT, restituendo solo le righe distinte della prima query che non vengono restituite dalle altre istruzioni.

Note sulle conversioni

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

Esempi

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
WHERE COL1 IN
(SELECT COL1 FROM TBL2)

MINUS

SELECT COL1 FROM TBL1
MINUS
SELECT COL1 FROM TBL2

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

Funzioni scalari (una riga) e di gruppo

MySQL fornisce un ampio elenco di funzioni scalari (una riga) e di aggregazione. Alcune funzioni di MySQL sono simili alle relative controparti di Oracle (per nome e funzionalità oppure con un nome diverso, ma con funzionalità simili). Sebbene le funzioni MySQL possano avere nomi identici alle loro controparti di Oracle, possono avere funzionalità diverse.

Le tabelle seguenti descrivono i casi in cui Oracle e MySQL sono equivalenti per nome e funzionalità (specificato da "Sì") e in cui è consigliata una conversione (tutti i casi diversi da "Sì").

Funzioni di carattere

CONCAT(str1,str2)

CONCAT('A', 1) = A1

CONCAT

CONCAT('A', 1) = A1

LOWER/UPPER

LOWER('SQL') = sql

LOWER/UPPER

LOWER('SQL') = sql

LPAD/RPAD(expr1,n,expr2)

LPAD('A',3,'*') = **A

LPAD/RPAD

LPAD('A',3,'*') = **A

SUBSTR(char,p,n)

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

SUBSTR(char,p,n)

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

INSTR(index,str)

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

INSTR

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

REPLACE(char,str1,str2)

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


= MySQLDB

REPLACE(char,str1,str2)

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


= MySQLDB

TRIM(str)

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


TRIM('  MySQL ')
= MySQL

TRIM(str)

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


TRIM('  MySQL ')
= MySQL

LTRIM/RTRIM(str)

LTRIM('   MySQL', ' ')
= MySQL

LTRIM/RTRIM(str)

LTRIM('   MySQL')
= MySQL

ASCII(char)

ASCII('A') = 65

ASCII(char)

ASCII('A') = 65

CHR(char)

CHR(65) = A

CHAR(char)

CHAR(65) = A

LENGTH(str)

LENGTH ('MySQL') = 5

LENGTH(str)

LENGTH('MySQL') = 5

REGEXP_REPLACE(str1,expr,str2)

REGEXP_REPLACE('John', '[hn].', '1') = Jo1

REGEXP_SUBSTR(str,expr)

REGEXP_SUBSTR('https://console.cloud.google.com/sql/instances','https://([[:alnum:]]+\.?){3,4}/?')
= https://console.cloud.google.com/

REGEXP_COUNT(str,expr)

REGEXP_INSTR(index,expr)

REVERSE(str)

REVERSE('MySQL')
= LQSyM

REVERSE

REVERSE('MySQL')
= LQSyM

Funzioni numeriche

ABS(n)

ABS(-4.6) = 4.6

ABS

ABS(-4.6) = 4.6

CEIL(n)

CEIL(21.4) = 22

CEIL

CEIL(21.4) = 22

FLOOR(n)

FLOOR(-23.7) = -24

FLOOR

FLOOR(-23.7) = -24

MOD(m,n)

MOD(10, 3) = 1

MOD(m,n)

MOD(10,3) = 1

ROUND(m,n)

ROUND(1.39,1) = 1.4

ROUND

ROUND(1.39,1) = 1.4

TRUNC(n1, n2)

TRUNC(99.999) = 99
TRUNC(99.999,0) = 99

TRUNCATE(n1, n2)

TRUNCATE(99.999,0) = 99

Funzioni di data e ora

SYSDATE

SELECT SYSDATE
FROM DUAL
= 31-JUL-2019

SYSDATE()

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

SYSTIMESTAMP

SELECT SYSTIMESTAMP FROM DUAL
= 01-JAN-19 07.37.11.622187000 AM +00:00

CURRENT_TIMESTAMP

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

LOCAL_TIMESTAMP

SELECT LOCAL_TIMESTAMP
FROM DUAL
= 01-JAN-19 10.01.10.123456 PM

LOCAL_TIMESTAMP

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

CURRENT_DATE

SELECT CURRENT_DATE
FROM DUAL
= 31-JAN-19

CURRENT_DATE

SELECT CURRENT_DATE
FROM DUAL
= 2019-01-31

CURRENT_TIMESTAMP

SELECT CURRENT_TIMESTAMP
FROM DUAL
= 31-JAN-19 06.54.35.543146 AM +00:00

CURRENT_TIMESTAMP

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

ADD_MONTHS

ADD_MONTHS(SYSDATE, 1)
= 31-JAN-19

ADDDATE

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

EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019

EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019

LAST_DAY

LAST_DAY('01-JAN-2019')
= 31-JAN-19

LAST_DAY

LAST_DAY('2019-01-01')
= 2019-01-31

MONTH_BETWEEN

MONTHS_BETWEEN(
SYSDATE, SYSDATE-60)
= 1.96

PERIOD_DIFF(date1,date2)

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

TO_CHAR(
SYSDATE,'DD-MM-YYYY HH24:MI:SS')
= 01-01-2019 10:01:01

DATE_FORMAT

DATE_FORMAT(
SYSDATE(),'%d-%m-%Y %H:%i:%s')
= 01-01-2019 10:01:01

Funzioni di codifica e decodifica

DECODE

CASE

DUMP

ORA_HASH

MD5/SHA

Funzioni di conversione

CAST

CAST('1' as int) + 1
= 2

CAST

CAST('1' AS SIGNED) + 1
= 2

CONVERT

CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')
= ?? ?? ?? A B C

CONVERT

CONVERT('Ä Ê Í A B C ' USING utf8)
= Ä Ê Í A B C

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

FORMAT

CONCAT('$',
FORMAT(22.73, 1))
= $22.7

TO_DATE

TO_DATE(
'2019/01/01', 'yyyy-mm-dd')
= 01-JAN-2019

STR_TO_DATE

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

TO_NUMBER

TO_NUMBER('01234')
= 1234

CAST

CAST('01234' as SIGNED)
= 1234

Funzioni SELECT condizionali

CASE

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

CASE

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

Funzioni null

COALESCE

COALESCE(
null, '1', 'a')
= a

COALESCE

COALESCE(
null, '1', 'a')
= 1

NULLIF

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

NULLIF

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

NVL

NVL(null, 'a')
= a

IFNULL

IFNULL(null, 'a')
= a

NVL2

CASE

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

Funzioni di ambiente e identificatori

SYS_GUID

SELECT SYS_GUID()
FROM DUAL
= 8EFA4A31468B4C6DE05011AC0200009E

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

UID

SELECT UID FROM DUAL
= 43

USER

SELECT USER FROM DUAL
= UserName

USER + INSTR + SUBSTR

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

USERENV

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

SHOW SESSION
VARIABLES

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

ROWID

ROWNUM

Funzioni di aggregazione (di gruppo)

AVG

AVG

COUNT

COUNT

COUNT
(DISTINCT)

COUNT
(DISTINCT)

MAX

MAX

MIN

MIN

SUM

SUM

LISTAGG

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


-- Single line results


= Accounting, Administration, Benefits, Construction

GROUP_CONCAT

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


-- Single line results


= Accounting, Administration, Benefits, Construction

Oracle 12c Fetch

FETCH

SELECT * FROM
EMPLOYEES
FETCH FIRST 10 ROWS ONLY;

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

• Per impostazione predefinita, la funzione SYSDATE di Oracle restituisce 01-AUG-19.
• Per impostazione predefinita, la funzione SYSDATE() di MySQL restituisce 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).

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

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

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

Funzioni analitiche (o funzioni finestra e di classificazione)

Le funzioni di analisi di Oracle estendono la funzionalità delle funzioni di analisi SQL standard fornendo funzionalità per calcolare i valori aggregati in base a un gruppo di righe. Queste funzioni possono essere applicate a insiemi di risultati suddivisi in modo logico nel contesto di una singola espressione di query. Di solito vengono utilizzati in combinazione con report e analisi di business intelligence, con il potenziale di migliorare le prestazioni delle query come alternativa per ottenere lo stesso risultato utilizzando un codice SQL non analitico più complesso.

Note sulle conversioni

• La versione 5.7 di MySQL non fornisce funzioni di analisi per supportare una conversione semplice delle istruzioni SQL. Tuttavia, questa funzionalità è stata aggiunta parzialmente nella versione 8 di MySQL, rendendo la conversione delle funzioni analitiche un punto da considerare, che probabilmente richiede un intervento manuale nel processo di migrazione.
• Una soluzione facoltativa è riscrivere il codice per rimuovere l'utilizzo di funzioni analitiche, ripristinare soluzioni di codice SQL più tradizionali o spostare questa logica in un livello di applicazione.

La tabella seguente elenca le funzioni di analisi comuni di Oracle.

RANK
AVERAGE_RANK
DENSE_RANK
RANK ROW_NUMBER
PERCENT_RANK
CUME_DIST
NTILE
FIRST_VALUE
LAST_VALUE
OVER (PARTITION BY...)

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

Espressione di tabella comune (CTE)

Le CTE forniscono un modo per implementare la logica del codice sequenziale al fine di riutilizzare il codice SQL che potrebbe essere troppo complesso o non efficiente per un uso multiplo. Le CTE possono essere nominate e poi utilizzate più volte in parti diverse di un'istruzione SQL utilizzando la clausola WITH.

Note sulle conversioni

• La versione 5.7 di MySQL non supporta le CTE, ma la versione 8 di MySQL sì.
• Per una soluzione alternativa, utilizza tabelle derivate o sottoquery oppure riscrivi l'istruzione SQL per eliminare la funzionalità CTE.

Esempi

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;

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) fornisce un mezzo per specificare singole istruzioni SQL che eseguono condizionatamente operazioni DML in un'operazione MERGE, diversamente da una singola operazione DML, in esecuzione separatamente. Seleziona i record dalla tabella di origine e, specificando una struttura logica, esegue automaticamente più operazioni DML sulla tabella di destinazione. Questa funzionalità ti aiuta a evitare di utilizzare più inserimenti, aggiornamenti o eliminazioni. Tieni presente che MERGE è un statement deterministico, il che significa che una volta elaborata una riga dall'istruzione MERGE, non può essere elaborata di nuovo utilizzando la stessa istruzione MERGE.

Note sulle conversioni

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

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

• INSERT… ON DUPLICATE KEY UPDATE: se una riga inserita causa un valore duplicato in un indice PRIMARY KEY o UNIQUE, viene eseguita un'operazione 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 memorizzata per gestire le operazioni DML, utilizzando i comandi INSERT, UPDATE e DELETE con gestione di eccezioni e duplicazioni.

Suggerimenti per le istruzione SQL

Oracle fornisce una vasta raccolta di suggerimenti per query SQL che consentono agli utenti di influenzare il comportamento dell'ottimizzatore e la sua presa di decisioni, con l'obiettivo di produrre piani di esecuzione delle query più efficienti. Oracle supporta oltre 60 diversi suggerimenti per database. MySQL fornisce un insieme limitato di suggerimenti per le query.

In generale, la versione 5.7 di MySQL supporta due tipi di suggerimenti per le query: OPTIMIZER HINTS e INDEX HINTS. Gli suggerimenti per l'ottimizzatore di MySQL offrono la possibilità 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 per l'ottimizzatore disponibili in MySQL versione 5.7

BKA, NO_BKA

BNL, NO_BNL

MAX_EXECUTION_TIME

MRR, NO_MRR

NO_ICP

NO_RANGE_OPTIMIZATION

QB_NAME

SEMIJOIN, NO_SEMIJOIN

SUBQUERY

Gli suggerimenti per gli indici MySQL forniscono all'ottimizzatore informazioni su come scegliere gli indici durante l'elaborazione delle query. Le parole chiave USE, FORCE o IGNORE vengono utilizzate per controllare la procedura di utilizzo dell'indice di Optimizer, 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 quello MySQL, e poiché le sovrapposizioni tra gli hint di query di Oracle e MySQL sono limitate, se presenti, consigliamo di convertire qualsiasi istruzione SQL di Oracle contenente hint di query non specificati nel database MySQL di destinazione.

Esegui l'ottimizzazione delle prestazioni di MySQL tramite gli strumenti MySQL (ad esempio MySQL Workbench per le dashboard sul rendimento in tempo reale) e funzionalità come l'esame delle query con l'utilizzo di 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 un'analisi approfondita delle scelte fatte dall'ottimizzatore delle query per accedere ai dati del database. L'ottimizzatore 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 e operazioni DML specifiche. Sono particolarmente utili quando devi ottimizzare le prestazioni delle query, ad esempio per determinare il rendimento degli indici o per stabilire se sono presenti indici mancanti che devono essere creati.

I piani di esecuzione possono essere influenzati dai volumi di dati, dalle statistiche dei dati e dai 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 sono 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 stessa sintassi, funzionalità o output del piano di esecuzione di Oracle.

Esempi

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

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 di Oracle utilizzato per creare, archiviare e applicare soluzioni basate su codice all'interno del database. In generale, le funzioni e le stored procedure del database sono elementi di codice costituiti da ANSI SQL e linguaggio procedurale SQL esteso, ad esempio PL/SQL per Oracle, PL/pgSQL per PostgreSQL e linguaggio procedurale MySQL per MySQL. MySQL utilizza lo stesso nome del database per il proprio linguaggio procedurale esteso.

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

Linguaggio procedurale PL/SQL a MySQL

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

Proprietario dell'oggetto di codice

In Oracle, il proprietario di una procedura o funzione memorizzata è 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 procedura o una funzione memorizzata, 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 del privilegio CREATE ROUTINE e del privilegio EXECUTE per l'esecuzione. La clausola DEFINER MySQL definisce l'utente che ha creato l'oggetto codice e l'utente deve disporre dei privilegi appropriati, ad esempio CREATE ROUTINE.

Sintassi delle funzioni e delle stored procedure MySQL

L'esempio seguente mostra la sintassi delle funzioni e delle stored procedure di 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