Spostamento delle procedure da Oracle a Cloud SQL per MySQL

Mantieni tutto organizzato con le raccolte Salva e classifica i contenuti in base alle tue preferenze.

Questo documento illustra le differenze di base tra Oracle e Cloud SQL per MySQL in relazione a procedure, funzioni e trigger archiviati.

Importazione di stored procedure, funzioni e trigger

Questa sezione descrive diversi metodi di conversione da Oracle PL/SQL a Cloud SQL per MySQL e fornisce soluzioni alternative quando una conversione diretta non è supportata. Gli esempi utilizzati per illustrare queste conversioni si basano sullo schema di esempio della risorsa umana di Oracle, che include tabelle, dipendenti, reparti e località.

Blocchi anonimi

PL/SQL può essere eseguito con il termine blocchi anonimi, il che significa che l'utente può stabilire una connessione al motore PL/SQL ed eseguire un blocco di codice senza creare un oggetto archiviato. Cloud SQL per MySQL non ha un costrutto equivalente. In Cloud SQL per MySQL, i blocchi di codice devono essere creati all'interno di una funzione o funzione archiviata.

Blocco anonimo di Oracle Routine di Cloud SQL per MySQL archiviata equivalente
DECLARE
... DECLARATION SECTION
BEGIN
... PROCEDURAL SECTION
EXCEPTION
... EXCEPTION HANDLER
END;
CREATE PROCEDURE ProcedureName()

BEGIN
... DECLARATION SECTION
... PROCEDURAL SECTION
... DECLARE EXIT HANDLER
END

-- OR

START TRANSACTION;

-- OR

BEGIN;

Struttura generale di una stored procedure

Il comando Oracle PL/SQL per la creazione di procedure e funzioni archiviate include la clausola facoltativa OR REPLACE, utile per modificare una procedura. Cloud SQL per MySQL non supporta questo costrutto. Per modificare una procedura in Cloud SQL per MySQL, utilizza prima DROP PROCEDURE prima dell'istruzione CREATE PROCEDURE.

Quando crei funzioni o funzioni archiviate di Cloud SQL per MySQL, il codice deve specificare un delimitatore diverso da quello predefinito, che è";" (punto e virgola). Poiché Cloud SQL per MySQL considera ogni riga che termina con ";" come una nuova riga, ti consigliamo di utilizzare un delimitatore diverso, come $$, per analizzare tutte le procedure memorizzate. La parola chiave END$$ termina l'utilizzo di questo delimitatore.

Un'altra differenza è che la sezione della dichiarazione delle variabili di una procedura archiviata di Cloud SQL per MySQL si verifica dopo la parola chiave BEGIN. In Oracle, questa sezione si verifica prima della parola chiave BEGIN.

Oracle Cloud SQL per MySQL
CREATE OR REPLACE PROCEDURE SP_CODE
IS
... VARIABLES DECLARATION
BEGIN
... PL/SQL CODE
END;
DROP PROCEDURE IF EXISTS SP_CODE;

DELIMITER $$
CREATE PROCEDURE SP_CODE()
BEGIN
... VARIABLES DECLARATION
... MySQL CODE
END$$
DELIMITER ;

Esecuzione di stored procedure

L'esempio seguente non stampa alcun output, ma assegna in modo rigoroso la stringa 'Hello World' alla variabile v_str_val.

Oracle Cloud SQL per MySQL
CREATE OR REPLACE PROCEDURE SP_HELLO_WORLD
IS
v_str_val VARCHAR2(20);
BEGIN
v_str_val := 'Hello World';
END;
DROP PROCEDURE IF EXISTS SP_HELLO_WORLD;

DELIMITER $$
CREATE PROCEDURE SP_HELLO_WORLD()
BEGIN
DECLARE v_str_val VARCHAR(20);
SET v_str_val := 'Hello World';
END$$
DELIMITER ;
-- Execution example
EXEC SP_HELLO_WORLD();
-- Execution example
CALL SP_HELLO_WORLD();
EXECUTE SP_HELLO_WORLD();
BEGIN
SP_HELLO_WORLD();
END;

Parametri di input/output

I parametri di input vengono utilizzati per trasmettere i valori a una stored procedure. I parametri di output vengono utilizzati per passare un valore o risultare da una procedura archiviata. Se non specificato, il tipo di parametro viene impostato automaticamente su un parametro di input, true in Oracle e Cloud SQL per MySQL. Tuttavia, il posizionamento della parola chiave IN/OUT è diverso. Oracle posiziona IN/OUT tra il nome della variabile e il relativo tipo di dati, mentre Cloud SQL per MySQL la precede. Detto questo, sia Oracle che Cloud SQL per MySQL consentono di assegnare un valore a una variabile utilizzando la clausola INTO.

Procedura Oracle archiviata Routine di Cloud SQL per MySQL archiviata equivalente
CREATE OR REPLACE PROCEDURE SP_EMP_SAL (P_EMP_ID IN NUMBER,
P_EMP_SAL OUT NUMBER)
IS
BEGIN
SELECT SALARY INTO P_EMP_SAL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=P_EMP_ID;
END;
DROP PROCEDURE IF EXISTS SP_EMP_SAL;

DELIMITER $$
CREATE PROCEDURE SP_EMP_SAL
(IN P_EMP_ID DOUBLE,
OUT P_EMP_SAL DOUBLE)
BEGIN
SELECT SALARY INTO P_EMP_SAL
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
END$$
DELIMITER ;
-- Set a variable for the out parameter
VAR v_get_sal NUMBER;
-- Execute the procedure
EXEC SP_EMP_SAL(100,:v_get_sal);
-- Execute the procedure
CALL SP_EMP_SAL(100, @emp_sal);
-- Print the result
PRINT v_sal;

V_SAL
----------
24000
-- Print the result
SELECT @emp_sal;

+----------+
| @emp_sal |
+----------+
| 24000 |
+----------+

Dichiarare le variabili e impostare i valori

Esistono vari metodi per impostare le variabili in Oracle PL/SQL. Uno dei metodi più comuni è l'utilizzo di una dichiarazione diretta, come quando si dichiara il nome della variabile con il tipo di dati allegato. Cloud SQL per MySQL funziona nello stesso modo utilizzando la parola chiave DECLARE.

Il tipo di dati della variabile può essere un tipo di dati SQL standard (NUMBER, VARCHAR2, DATE) o un tipo di dati PL/SQL (BOOLEAN, BINARY_INTEGER). Puoi gestire la conversione del tipo di dati Cloud SQL per MySQL nello stesso modo in cui eseguirai la conversione dei tipi di dati dei campi. In Oracle, le variabili possono anche essere dichiarate come tipi di riferimento come mostrato di seguito:

variable_name table.column%TYPE;
variable_row table%ROWTYPE;

Cloud SQL per MySQL non supporta i tipi di riferimento e non esiste un modulo equivalente. Per convertire un %ROWTYPE in Cloud SQL per MySQL, devi dichiarare una variabile per ogni colonna.

Procedura Oracle archiviata Routine di Cloud SQL per MySQL archiviata equivalente
CREATE OR REPLACE PROCEDURE SP_VAR
IS
v_num NUMBER;
v_int INT:=1;
v_var VARCHAR2(10);
v_null VARCHAR2(20) NULL;
v_date DATE:=SYSDATE;
v_default INT DEFAULT 5;
v_type EMP.EMAIL%TYPE;
v_row_type EMP%ROWTYPE;
BEGIN
SELECT v_default INTO v_int
FROM dual;
END;
DROP PROCEDURE IF EXISTS SP_VAR;
DELIMITER $$
CREATE PROCEDURE SP_VAR()
BEGIN
DECLARE v_num DOUBLE;
DECLARE v_int DECIMAL(38) DEFAULT 1;
DECLARE v_var VARCHAR(10);
DECLARE v_null VARCHAR(20);
DECLARE v_date SYSDATE();
DECLARE v_default DECIMAL(38) DEFAULT 5;

-- a variable defined with the EMAIL
-- column's datatype
DECLARE v_email VARCHAR(25);

-- a variable for each field in the EMP
-- table. For example:
DECLARE v_employee_id DECIMAL(6);
DECLARE v_first_name VARCHAR(20);
DECLARE v_last_name VARCHAR(25);

SELECT v_default INTO v_int FROM dual;
END$$
DELIMITER ;

Cloud SQL per MySQL supporta anche le variabili definite dall'utente, che non sono dichiarate come nella tabella precedente. Sono inizializzate utilizzando la parola chiave SET e richiedono che il nome della variabile sia preceduto dal segno @.

mysql> SET @var := 100;
mysql> SELECT @var;

Output di stampa

Oracle utilizza la funzione DBMS_OUTPUT.PUT_LINE per stampare l'output dal codice della procedura. Cloud SQL per MySQL non ha funzioni equivalenti equivalenti. In alternativa, crea una funzione in Cloud SQL per MySQL che implementi la stessa funzionalità utilizzando SELECT.

Oracle Funzione Cloud SQL per MySQL equivalente
CREATE OR REPLACE PROCEDURE SP_PRINT
IS
v_str_val VARCHAR2(20);
BEGIN
v_str_val := 'Hello World';
dbms_output.put_line(v_str_val);
END;
DROP PROCEDURE IF EXISTS SP_PRINT;

DELIMITER $$
CREATE PROCEDURE SP_PRINT()
BEGIN
DECLARE v_str_val VARCHAR(20);

SET v_str_val = 'Hello World';
SELECT v_str_val;
END$$
DELIMITER ;
SET SERVEROUTPUT ON;
EXEC SP_PRINT();
Hello World
CALL SP_PRINT();

+-------------+
| v_str_val |
+-------------+
| Hello World |
+-------------+

Loop e cursori

Confronto del supporto di loop e cursore di Oracle e Cloud SQL per MySQL:

Loop Oracle Supporto dei cicli Cloud SQL per MySQL (sì/no)
SIMPLE loop (Ripeti fino a ) Sì (Cloud SQL per MySQL supporta anche la sintassi di loop REPEAT)
WHILE loop
anello FOR | FORALL | BULK COLLECT No (utilizza i loop CURSOR e SIMPLE)
CURSOR
Cicli/cursori nidificati

Conversione del loop SIMPLE di Oracle in Cloud SQL per MySQL:

Oracle
CREATE OR REPLACE PROCEDURE SP_SIMPLE_LOOP
IS
v_start_num INT:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('ITERATION_NUM:' || v_start_num);
v_start_num:=v_start_num + 1;
EXIT WHEN v_start_num > 5;
END LOOP;
END;

EXEC SP_SIMPLE_LOOP;

ITERATION_NUM:1
ITERATION_NUM:2
ITERATION_NUM:3
ITERATION_NUM:4
ITERATION_NUM:5
Equivalente di Cloud SQL per MySQL
DROP PROCEDURE IF EXISTS SP_SIMPLE_LOOP;

DELIMITER $$
CREATE PROCEDURE SP_SIMPLE_LOOP()
BEGIN
DECLARE v_start_num INT DEFAULT 1;
Label:
LOOP
SELECT CONCAT('ITERATION_NUM:', v_start_num);
SET v_start_num:=v_start_num + 1;

IF v_start_num > 5 THEN
LEAVE label;
END IF;
END LOOP;
END$$
DELIMITER ;

CALL SP_SIMPLE_LOOP();

ITERATION_NUM:1
ITERATION_NUM:2
ITERATION_NUM:3
ITERATION_NUM:4
ITERATION_NUM:5

Conversione del loop WHILE di Oracle in Cloud SQL per MySQL:

Oracle
CREATE OR REPLACE PROCEDURE SP_WHILE_LOOP
IS
v_start_num INT:=0;
v_end_num INT:=5;
BEGIN
WHILE v_start_num <= v_end_num
LOOP
DBMS_OUTPUT.PUT_LINE('ITERATION_NUM:' || v_start_num);
v_start_num:=v_start_num + 1;
END LOOP;
END;

SET SERVEROUTPUT ON;

EXEC SP_WHILE_LOOP;

ITERATION_NUM:0
ITERATION_NUM:1
ITERATION_NUM:2
ITERATION_NUM:3
ITERATION_NUM:4
ITERATION_NUM:5
Equivalente di Cloud SQL per MySQL
DROP PROCEDURE IF EXISTS SP_WHILE_LOOP;

DELIMITER $$
CREATE PROCEDURE SP_WHILE_LOOP()
BEGIN
DECLARE v_start_num INT DEFAULT 0;
DECLARE v_end_num INT DEFAULT 5;

WHILE v_start_num <= v_end_num DO
SELECT CONCAT('ITERATION_NUM:', v_start_num);
SET v_start_num := v_start_num + 1;
END WHILE;
END$$
DELIMITER ;

CALL SP_WHILE_LOOP();

ITERATION_NUM:0
ITERATION_NUM:1
ITERATION_NUM:2
ITERATION_NUM:3
ITERATION_NUM:4
ITERATION_NUM:5

L'esempio seguente mostra la conversione di CURSOR di Oracle in Cloud SQL per MySQL. L'esempio utilizza un CURSOR per eseguire l'iterazione nell'elenco dei dipendenti.

Oracle
CREATE OR REPLACE PROCEDURE SP_CURSOR
IS
CURSOR cur_emp_name IS
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES
WHERE EMPLOYEE_ID <= 105;

v_first_name VARCHAR2(100);
v_last_name VARCHAR2(100);
v_user_suffix VARCHAR2(12):='@company.com';
BEGIN
OPEN cur_emp_name;
LOOP
FETCH cur_emp_name INTO v_first_name, v_last_name;
EXIT WHEN cur_emp_name%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMP User Name: '|| v_first_name || substr(v_last_name, 1, 1) || v_user_suffix);
END LOOP;
CLOSE cur_emp_name;
END;

SET SERVEROUTPUT ON;

EXEC SP_CURSOR;

EMP User Name: StevenK@company.com
EMP User Name: NeenaK@company.com
EMP User Name: LexD@company.com
EMP User Name: AlexanderH@company.com
EMP User Name: BruceE@company.com
EMP User Name: DavidA@company.com
Equivalente di Cloud SQL per MySQL
DROP PROCEDURE IF EXISTS SP_CURSOR;

DELIMITER $$
CREATE PROCEDURE SP_CURSOR()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_first_name VARCHAR (100);
DECLARE v_last_name VARCHAR (100);
DECLARE v_user_suffix VARCHAR (12) DEFAULT '@company.com';
DECLARE cur_emp_name CURSOR FOR
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES
WHERE EMPLOYEE_ID <= 105;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done := 1;
OPEN cur_emp_name;

label:
LOOP
FETCH cur_emp_name INTO v_first_name, v_last_name;

IF done THEN
LEAVE label;
END IF;
SELECT CONCAT('EMP User Name: ', v_first_name,
SUBSTR(v_last_name, 1, 1), v_user_suffix);
END LOOP;
CLOSE cur_emp_name;
END$$
DELIMITER ;

CALL SP_CURSOR();

EMP User Name: StevenK@company.com
EMP User Name: NeenaK@company.com
EMP User Name: LexD@company.com
EMP User Name: AlexanderH@company.com
EMP User Name: BruceE@company.com
EMP User Name: DavidA@company.com

SQL dinamico

Per generare ed eseguire istruzioni SQL dinamiche, Oracle fornisce le utilità DBMS_SQL e EXECUTE IMMEDIATE, che non sono disponibili in Cloud SQL per MySQL. Cloud SQL per MySQL supporta le operazioni SQL dinamiche utilizzando le istruzioni PREPARE e EXECUTE. Puoi incorporare queste istruzioni all'interno o all'esterno di una procedura archiviata di una funzione, come illustrato nei seguenti esempi.

Esempio di SQL dinamico Oracle
CREATE OR REPLACE PROCEDURE SP_DYNAMIC_SQL (p_emp_ID INT)
IS
v_emp_email VARCHAR2(30);
BEGIN
EXECUTE IMMEDIATE
'SELECT EMAIL FROM EMPLOYEES WHERE
EMPLOYEE_ID='||p_emp_ID INTO v_emp_email;

DBMS_OUTPUT.PUT_LINE('Employee Email: '|| v_emp_email);
END;

SET SERVEROUTPUT ON;

EXEC SP_DYNAMIC_SQL(100);

Employee Email: SKING
Equivalente di Cloud SQL per MySQL
DROP PROCEDURE IF EXISTS SP_DYNAMIC_SQL;

DELIMITER $$
CREATE PROCEDURE SP_DYNAMIC_SQL(IN P_EMP_ID DOUBLE)
BEGIN
DECLARE v_emp_email VARCHAR (30);
SET @prep_stmt := CONCAT('SELECT EMAIL INTO @v_emp_email
FROM EMPLOYEES
WHERE EMPLOYEE_ID = ', p_emp_ID);

PREPARE sql_stmt_name FROM @prep_stmt;
EXECUTE sql_stmt_name;
DEALLOCATE PREPARE sql_stmt_name;

SET v_emp_email := @v_emp_email;
SELECT CONCAT('Employee Email: ', v_emp_email);

END$$
DELIMITER ;

CALL SP_DYNAMIC_SQL(100);

Employee Email: SKING

Durante l'esecuzione di SQL dinamico al di fuori dell'ambito di una procedura o funzione archiviata, utilizza il segno di associazione"?" (punto di domanda):

mysql> PREPARE prep_stat FROM 'SELECT EMAIL FROM EMPLOYEES WHERE EMP_ID=?';
mysql> SET @emp_id = 102;
mysql> EXECUTE prep_stat USING @emp_id;
+---------+
| EMAIL   |
+---------+
| LDEHAAN |
+---------+

Esempio di DDL

mysql> PREPARE prep_stat FROM 'CREATE TABLE tbl (col1 INT)';
mysql> EXECUTE prep_stat;
Query OK, 0 rows affected (0.11 sec)

Esempio di DML

mysql> PREPARE prep_stat FROM 'INSERT INTO tbl (col1) VALUES (?)';
mysql> SET @insert_val = 3;
mysql> EXECUTE prep_stat USING @insert_val;
Query OK, 1 row affected (0.09 sec)

mysql> DEALLOCATE PREPARE prep_stat;

Controllo del flusso

L'esecuzione condizionale delle istruzioni SQL e del flusso generale del programma viene eseguita utilizzando la funzionalità IF/ELSE insieme alla clausola CASE. Cloud SQL per MySQL supporta entrambi i costrutti. A questo proposito, una piccola differenza di sintassi tra Oracle e Cloud SQL per MySQL è che Oracle utilizza ELSIF per la clausola ELSE IF, mentre Cloud SQL per MySQL utilizza ELSEIF.

L'esempio seguente calcola il trimestre corrente in base alla data corrente, utilizzando la gestione condizionale IF/ELSE.

Esempio di flusso di controllo Oracle
CREATE OR REPLACE PROCEDURE SP_GET_QUARTER
IS
v_cur_month INT;
v_q1 INT:=3;
v_q2 INT:=6;
v_q3 INT:=9;
v_q4 INT:=12;
BEGIN
v_cur_month:=EXTRACT(MONTH FROM SYSDATE);

IF v_cur_month <= v_q1 THEN
DBMS_OUTPUT.PUT_LINE('Current Quarter Is: Q1');

ELSIF v_cur_month > v_q1 and v_cur_month <= v_q2 THEN
DBMS_OUTPUT.PUT_LINE('Current Quarter Is: Q2');

ELSIF v_cur_month > v_q2 and v_cur_month <= v_q3 THEN
DBMS_OUTPUT.PUT_LINE('Current Quarter Is: Q3');

ELSE
DBMS_OUTPUT.PUT_LINE('Current Quarter Is: Q4');

END IF;
END;

EXEC SP_GET_QUARTER;

Current Quarter Is: Q3
Equivalente di Cloud SQL per MySQL
DROP PROCEDURE IF EXISTS SP_GET_QUARTER;

DELIMITER $$
CREATE PROCEDURE SP_GET_QUARTER()
BEGIN
DECLARE v_cur_month INT;
DECLARE v_q1 INT DEFAULT 3;
DECLARE v_q2 INT DEFAULT 6;
DECLARE v_q3 INT DEFAULT 9;
DECLARE v_q4 INT DEFAULT 12;
SET v_cur_month := EXTRACT(MONTH FROM SYSDATE());

IF v_cur_month <= v_q1 THEN
SELECT 'Current Quarter Is: Q1';

ELSEIF v_cur_month > v_q1 AND v_cur_month <= v_q2 THEN
SELECT 'Current Quarter Is: Q2';

ELSEIF v_cur_month > v_q2 AND v_cur_month <= v_q3 THEN
SELECT 'Current Quarter Is: Q3';

ELSE
SELECT 'Current Quarter Is: Q4';
END IF;
END$$
DELIMITER ;

CALL SP_GET_QUARTER;

Current Quarter Is: Q3

Controllo delle transazioni

Le transazioni Oracle richiedono un COMMIT/ROLLBACK specifico per ogni operazione DML. Al contrario, Cloud SQL per MySQL è configurato per funzionare in modalità AUTOCOMMIT. Questa differenza ha alcune implicazioni. Per le stored procedure, significa che è richiesto un START TRANSACTION esplicito per controllare una transazione. Cloud SQL per MySQL supporta anche la transazione SAVEPOINTs per eseguire il rollback delle transazioni a una posizione salvata in precedenza nella procedura archiviata. A livello di sessione, ciò significa che SET autocommit è tenuto a non eseguire automaticamente il commit delle modifiche.

-- Turn OFF AutoCommit mode
mysql> SET autocommit = 0;

-- Turn ON AutoCommit mode
mysql> SET autocommit = 1;

-- Verify AutoCommit mode
mysql> SHOW VARIABLES LIKE 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

L'esempio seguente mostra il controllo delle transazioni tramite SAVEPOINT, COMMIT e ROLLBACK. Il codice esegue un aggiornamento SALARY e verifica la somma di uno stipendio di un reparto dopo l'aggiornamento. Se la somma supera la soglia di 10.000, viene eseguito il rollback della seconda istruzione UPDATE, mentre viene applicato il primo aggiornamento.

Esempio di elaborazione delle transazioni Oracle
CREATE OR REPLACE PROCEDURE SP_TRAN_CTL
IS
v_sal_chk INT;
BEGIN
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE DEPARTMENT_ID=90;

SAVEPOINT S1;

UPDATE EMPLOYEES
SET SALARY = SALARY * 1.15
WHERE DEPARTMENT_ID=60;

SELECT SUM(SALARY) INTO v_sal_chk
FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;

IF v_sal_chk > 10000 THEN
ROLLBACK TO S1;
COMMIT;
ELSE
COMMIT;
END IF;
END;
Equivalente di Cloud SQL per MySQL
DROP PROCEDURE IF EXISTS SP_TRAN_CTL;

DELIMITER $$
CREATE PROCEDURE SP_TRAN_CTL()
BEGIN
DECLARE v_sal_chk INT;
START TRANSACTION;

UPDATE EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE DEPARTMENT_ID = 90;

SAVEPOINT S1;

UPDATE EMPLOYEES
SET SALARY = SALARY * 1.15
WHERE DEPARTMENT_ID = 60;
SELECT
SUM(SALARY) INTO v_sal_chk FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

IF v_sal_chk > 10000 THEN
ROLLBACK TO SAVEPOINT S1;
COMMIT;
ELSE
COMMIT;
END IF;
END$$
DELIMITER ;

CALL SP_TRAN_CTL();

Eccezione e gestione degli errori

Oracle offre un'ampia gamma di tipi di eccezioni predefiniti, tra cui NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE. Oracle consente inoltre all'utente di creare eccezioni definite dall'utente. Cloud SQL per MySQL offre tipi di eccezione predefiniti e definiti dall'utente, con differenze di sintassi rispetto a Oracle, che richiedono modifiche al codice.

L'esempio seguente illustra la differenza relativa alla gestione delle eccezioni tra le due piattaforme. Il codice genera l'errore divisor is equal to zero from dividing 1 by 0 (1/0). Un gestore di eccezioni rileva questo errore e restituisce un messaggio personalizzato all'utente.

Esempio di gestione delle eccezioni Oracle
CREATE OR REPLACE PROCEDURE SP_ERR_HANDLE
(p_num_1 INT, p_num_2 INT)
IS
v_result INT;
BEGIN
SELECT p_num_1/p_num_2 INTO v_result FROM dual;
DBMS_OUTPUT.PUT_LINE(v_result);

EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Sorry, you cannot divide by zero');
END;

EXEC SP_ERR_HANDLE(10, 0);

Attention: Cannot Divide by Zero
Equivalente di Cloud SQL per MySQL
DROP PROCEDURE IF EXISTS SP_ERR_HANDLE;

DELIMITER $$
CREATE PROCEDURE SP_ERR_HANDLE
(p_num_1 INT, p_num_2 INT)
BEGIN
DECLARE v_result INT;

DECLARE EXIT HANDLER FOR 1365 /* MySQL Division by 0 Build Errors */
BEGIN
SELECT 'Sorry, you cannot divide by zero';
END;

SELECT p_num_1/p_num_2 INTO v_result FROM dual;
SELECT v_result;

END$$
DELIMITER ;

CALL SP_ERR_HANDLE(10, 0);

Attention: Cannot Divide by Zero

Il seguente esempio di gestione degli errori utilizza l'eccezione WHEN OTHERS THEN di Oracle. Questo esempio mostra come gestire l'errore generato quando si tenta di inserirli in una tabella che non esiste. L'esempio utilizza SQL dinamico per l'inserimento. L'istruzione EXECUTE IMMEDIATE di Oracle viene convertita in istruzioni PREPARE e EXECUTE in Cloud SQL per MySQL.

Esempio di gestione delle eccezioni Oracle
CREATE OR REPLACE PROCEDURE SP_ERR_HANDLE1(p_tbl_name VARCHAR2, p_val INT)
IS
BEGIN
EXECUTE IMMEDIATE
'INSERT INTO ' || p_tbl_name ||' (col1) VALUES(' || p_val || ')';

EXCEPTION WHEN OTHERS THEN
EXECUTE IMMEDIATE 'CREATE TABLE ' || p_tbl_name ||' (col1 INT)';
EXECUTE IMMEDIATE 'INSERT INTO '|| p_tbl_name ||' (col1)
VALUES(' ||p_val ||')';

COMMIT;
END;

EXEC SP_ERR_HANDLE1 ('tbl', 1);

SELECT * FROM tbl;

COL1
----------
1
Equivalente di Cloud SQL per MySQL
DROP PROCEDURE IF EXISTS SP_ERR_HANDLE1;

DELIMITER $$
CREATE PROCEDURE SP_ERR_HANDLE1
(IN p_tbl_name VARCHAR(100), IN p_val INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
START TRANSACTION;
SET @SQL_TBL:=CONCAT('CREATE TABLE ', p_tbl_name, '(col1 INT)');
PREPARE prep_tbl FROM @SQL_TBL;
EXECUTE prep_tbl;

SET @SQL_INSERT:=CONCAT('INSERT INTO ', p_tbl_name, ' VALUES(',
p_val, ')');
PREPARE prep_insert FROM @SQL_INSERT;
EXECUTE prep_insert;
COMMIT;
END;

START TRANSACTION;
SET @SQL_INSERT:=CONCAT('INSERT INTO ', p_tbl_name, ' VALUES(',
p_val, ')');
PREPARE prep_insert FROM @SQL_INSERT;
EXECUTE prep_insert;
COMMIT;

DEALLOCATE PREPARE prep_tbl;
DEALLOCATE PREPARE prep_insert;
END$$
DELIMITER ;

CALL SP_ERR_HANDLE1('tbl', 1);

SELECT * FROM tbl;
+------+
| col1 |
+------+
| 1 |
+------+

Cursori

Il metodo Oracle per restituire set di record da una funzione o da una funzione archiviata è utilizzare il tipo SYS_REFCURSOR. Anche se Cloud SQL per MySQL non ha una struttura del cursore, supporta i set di record di ritorno.

Esempio di cursore Oracle
CREATE OR REPLACE PROCEDURE SP_SYS_REF
(p_ResultSet OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_ResultSet FOR
SELECT first_name, Salary FROM employees
WHERE employee_id between 100 and 105;
END;

VAR resultSet REFCURSOR;
EXEC SP_SYS_REF(:resultSet);

PRINT resultSet;

FIRST_NAME SALARY
-------------------- ----------
Steven 24000
Neena 17000
Lex 17000
Alexander 9000
Bruce 6000
David 4800
Equivalente di Cloud SQL per MySQL
DROP PROCEDURE IF EXISTS SP_SYS_REF;

DELIMITER $$
CREATE PROCEDURE SP_SYS_REF()
BEGIN
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID BETWEEN 100 AND 105;
END$$
DELIMITER ;

CALL SP_SYS_REF();

+------------+----------+
| FIRST_NAME | SALARY |
+------------+----------+
| Steven | 24000.00 |
| Neena | 17000.00 |
| Lex | 17000.00 |
| Alexander | 9000.00 |
| Bruce | 6000.00 |
| David | 4800.00 |
+------------+----------+

Conversione delle funzioni

Molti dei concetti fondamentali della codifica di Cloud SQL per MySQL della sezione precedente si applicano anche alle funzioni definite dall'utente (UDF). Tuttavia, esistono alcune differenze significative:

  • Le funzioni definite dall'utente di Cloud SQL per MySQL possono restituire solo valori scalari invece che set di record da una tabella.
  • Cloud SQL per MySQL non consente l'esistenza di istruzioni di transazione come COMMIT o ROLLBACK in una funzione definita dall'utente. Quando dichiari una funzione definita dall'utente, definisci che la funzione è deterministic o che non modifichi i dati quando viene attivato il logging binario.

Esistono inoltre differenze significative tra le funzioni definite dall'utente di Oracle e Cloud SQL per MySQL. Le parole chiave IS e AS nelle stored procedure e nelle funzioni Oracle non sono applicabili in Cloud SQL per MySQL e devono essere rimosse dalla dichiarazione delle funzioni Cloud SQL per MySQL. A differenza di Oracle, Cloud SQL per MySQL consente di creare funzioni che fanno riferimento a tabelle inesistenti. Cloud SQL per MySQL consente le modifiche di dati e schemi all'interno di una funzione purché il tipo di determinismo della funzione sia esplicitamente dichiarato, a differenza di Oracle il cui compilatore PL/SQL deduce il tipo di determinismo dal codice.

L'esempio seguente mostra la conversione di una funzione che calcola i bonus dipendenti in base a anni di servizio, un fattore bonus e lo stipendio attuale.

Esempio di funzione definita dall'utente Oracle
CREATE OR REPLACE FUNCTION FUNC_EMP_SAL_BONUS
(p_hire_date DATE, p_current_sal NUMBER, p_bonus_factor DECIMAL)
RETURN NUMBER
AS
v_years_of_service INT;
v_calculated_bonus NUMBER;
BEGIN
-- Calculate years of service from hire date
SELECT EXTRACT(YEAR FROM SYSDATE) -
EXTRACT(YEAR FROM to_date(p_hire_date))
INTO v_years_of_service
FROM dual;

-- Calculate bonus based on years of service, bonus factor, and salary
v_calculated_bonus :=
(v_years_of_service * p_bonus_factor * p_current_sal) + p_current_sal;

RETURN v_calculated_bonus;
END;

-- Run function
SELECT FIRST_NAME,
LAST_NAME,
SALARY AS CURRENT_SALARY,
FUNC_EMP_SAL_BONUS(HIRE_DATE, SALARY, 0.005) as Bonus_Func
FROM EMPLOYEES
where EMPLOYEE_ID <= 105;

FIRST_NAME LAST_NAME CURRENT_SALARY BONUS_FUNC
-------------------- ------------------------- -------------- ----------
Steven King 24000 25920
Neena Kochhar 17000 18190
Lex De Haan 17000 18530
Alexander Hunold 9000 9585
Bruce Ernst 6000 6360
David Austin 4800 5136
Equivalente di Cloud SQL per MySQL
DROP FUNCTION IF EXISTS FUNC_EMP_SAL_BONUS;

DELIMITER $$
CREATE FUNCTION FUNC_EMP_SAL_BONUS
(P_HIRE_DATE DATETIME, P_CURRENT_SAL DOUBLE, P_BONUS_FACTOR DOUBLE)
RETURNS DOUBLE
BEGIN
DECLARE v_years_of_service DECIMAL (38);
DECLARE v_calculated_bonus DOUBLE;

-- Calculate years of service from hire date
SELECT EXTRACT(YEAR FROM SYSDATE()) -
EXTRACT(YEAR FROM p_hire_date)
INTO v_years_of_service
FROM dual;

-- Calculate bonus based on years of service, bonus factor, and salary
SET v_calculated_bonus :=
(v_years_of_service * p_bonus_factor * p_current_sal) + p_current_sal;

RETURN v_calculated_bonus;
END $$
DELIMITER ;

-- Run function
SELECT FIRST_NAME,
LAST_NAME,
SALARY AS CURRENT_SALARY,
FUNC_EMP_SAL_BONUS(HIRE_DATE, SALARY, 0.005) as Bonus_Func
FROM EMPLOYEES
WHERE EMPLOYEE_ID <= 105;

+------------+-----------+----------------+------------+
| FIRST_NAME | LAST_NAME | CURRENT_SALARY | Bonus_Func |
+------------+-----------+----------------+------------+
| Steven | King | 24000.00 | 25920 |
| Neena | Kochhar | 17000.00 | 18190 |
| Lex | De Haan | 17000.00 | 18530 |
| Alexander | Hunold | 9000.00 | 9585 |
| Bruce | Ernst | 6000.00 | 6360 |
| David | Austin | 4800.00 | 5136 |
+------------+-----------+----------------+------------+

Conversione dei trigger

Oracle fornisce tre tipi di trigger, trigger DML, instead of trigger e system event trigger. Di questi, solo i trigger DML sono supportati in modo nativo da Cloud SQL per MySQL. I trigger Cloud SQL per MySQL possono essere modificati e concatenati utilizzando le clausole FOLLOWS o PRECEDES.

Tipo di trigger Oracle Equivalente di Cloud SQL per MySQL
Trigger DML
  • Possono essere assegnate a tabelle o viste ed eseguite durante l'inserimento, l'aggiornamento o l'eliminazione di record di dati.
  • Può essere eseguito PRIMA o DOPO un comando DML.
Supporto completo per differenze di sintassi.
Al posto degli attivatori
  • Un tipo speciale di trigger DML creato su una vista non modificabile.
  • I trigger INSTEAD OF forniscono un metodo trasparente per le applicazioni per modificare i record in un metodo automatico.
Non supportato. Prendi in considerazione l'utilizzo di Cloud SQL per MySQL REPLACE o INSERT ON DUPLICATE KEY UPDATE come soluzione alternativa.
Trigger di eventi di sistema
  • Definita a livello di database o schema.
  • Esegui in risposta a eventi di database specifici, come l'avvio, lo spegnimento o l'errore.
Non supportato. Converti questa funzionalità nel livello dell'applicazione.
Esempio di trigger Oracle
CREATE OR REPLACE TRIGGER TRIG_LOG_EMP_SAL;
BEFORE UPDATE ON EMPLOYEES
FOR EACH ROW
BEGIN
IF :OLD.SALARY != :NEW.SALARY THEN
INSERT INTO EMP_SAL_LOG
VALUES(:OLD.EMPLOYEE_ID, :OLD.SALARY, SYSDATE());

END IF;

EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
UPDATE EMP_SAL_LOG
SET salary=:OLD.SALARY,
upd_date=SYSDATE;
END;
Equivalente di Cloud SQL per MySQL
DROP TRIGGER IF EXISTS TRIG_LOG_EMP_SAL;

DELIMITER $$
CREATE TRIGGER TRIG_SET_NULL
BEFORE UPDATE ON EMPLOYEES
FOR EACH ROW
BEGIN
IF OLD.SALARY != NEW.SALARY THEN
INSERT INTO EMP_SAL_LOG
VALUES (OLD.EMPLOYEE_ID, OLD.SALARY, SYSDATE())
ON DUPLICATE KEY UPDATE salary=OLD.SALARY, upd_date=SYSDATE();
END IF;
END $$
DELIMITER ;

In Oracle, le stored procedure, le funzioni e i trigger sono di proprietà di un utente. In Cloud SQL per MySQL, sono di proprietà di un database. In Cloud SQL per MySQL, all'utente del database che ha creato l'oggetto archiviato viene automaticamente assegnato il privilegio CREATE DEFINER e può agire come concedente per altri utenti del database. La seguente istruzione concede privilegi di esecuzione dal giorno HR.SP_PROC_1 al giorno 'user':

mysql> GRANT EXECUTE ON PROCEDURE HR.SP_PROC_1 to 'user'@'%';

Verifica della configurazione corretta

L'obiettivo della verifica è garantire che anche i parametri comuni e speciali configurati nell'ambiente di origine di Oracle siano stati configurati correttamente nell'ambiente di destinazione Cloud SQL per MySQL. Ad esempio, potresti aver bisogno di verificare quanto segue:

  • Parametro lingua NLS (supporta set di caratteri diversi dal latino)
  • Parametri di sicurezza e di controllo che gestiscono la gestione delle autorizzazioni del database, i criteri di controllo e i metodi di autenticazione configurati in base alle normative aziendali.

Verificare le taglie

Verifica che la configurazione della memoria di Cloud SQL per MySQL possa supportare i carichi di lavoro dell'ambiente Oracle di origine. Ad esempio, Oracle SGA/AMM a Cloud SQL per MySQL INNODB_BUFFER_POOL_SIZE (questo parametro viene impostato in base alle dimensioni dell'istanza). Assicurati che le dimensioni del disco allocato possano supportare la stessa dimensione iniziale attuale dei dati Oracle (inclusi log di archiviazione Oracle, log di ripetizione e file di dati/indice).

Rendimento benchmark

Consigliamo di eseguire istruzioni e procedure SQL identiche in base agli stessi dati su ambienti Oracle e Cloud SQL per MySQL. Questo approccio può identificare problemi di prestazioni che potrebbero richiedere una configurazione aggiuntiva sul lato Cloud SQL per MySQL, ad esempio la modifica dei parametri o la modifica dell'infrastruttura, come la modifica dell'archiviazione di Cloud SQL per MySQL per consentire più IOP. Puoi utilizzare strumenti come sysbench per eseguire test di confronto degli IOPS che simulano gli stessi carichi di lavoro su piattaforme di database diverse.

Monitoraggio dei carichi di lavoro medi e alti

Usa le dashboard di Cloud SQL per eseguire il monitoraggio in tempo reale di elementi come CPU, archiviazione, memoria o Cloud SQL per le query MySQL. Oppure, quando si simulano carichi di lavoro diversi, puoi utilizzare le dashboard per identificare i problemi di prestazioni che potrebbero verificarsi dopo la migrazione da carichi di lavoro medi o alti Oracle.

Ottimizzazione della configurazione per Cloud SQL

Attiva il database performance_schema Cloud SQL per MySQL

Se vuoi eseguire un'analisi di Cloud SQL per MySQL più granulare a livello di istanza/istruzione SQL, ti consigliamo di abilitare il database performance_schema.

Questo database contiene molti metadati e tabelle di sistema di statistiche in tempo reale. Queste tabelle e visualizzazioni sono in grado di descrivere dettagliatamente eventuali eventi in corso in processi di database, transazioni, carichi di lavoro ed eventi collo di bottiglia.

Best practice di Cloud SQL per MySQL

Utilizza la documentazione di Google Cloud per seguire le best practice di Cloud SQL per MySQL per l'architettura dei dati, l'implementazione delle applicazioni, l'importazione e l'esportazione dei dati e altro ancora.

Repliche di lettura e Proxy di Cloud SQL

Consigliamo di separare le operazioni WRITE e READ tra i nodi master Cloud SQL per MySQL e le repliche di lettura. Questa separazione migliora la distribuzione dei carichi di lavoro e sfrutta meglio le funzionalità di Cloud SQL.

Il proxy di Cloud SQL contribuisce a garantire una connessione sicura ai database Cloud SQL per MySQL utilizzando il traffico criptato (implementando TLS 1.2 con una crittografia AES a 128 bit). Inoltre, il proxy Cloud SQL elimina la necessità di utilizzare un indirizzo IP statico, perché è in grado di gestire tutta l'autenticazione con Cloud SQL per MySQL.

IP privato

L'utilizzo dell'IP privato per Cloud SQL per MySQL può migliorare la sicurezza della rete e garantire una latenza inferiore rispetto all'IP pubblico.

Configurazione dell'alta disponibilità di Cloud SQL per MySQL

Per prepararti agli errori dell'infrastruttura del database, è importante configurare l'alta disponibilità per la tua istanza Cloud SQL per MySQL di produzione. Utilizzando un'istanza di standby, Cloud SQL può eseguire un failover automatico da un'istanza principale che non risponde. Dal punto di vista della migrazione, l'utilizzo di Cloud SQL per MySQL ad alta disponibilità è una soluzione alternativa per il ripristino di emergenza e l'utilizzo di Oracle Active Data Guard.

Backup automatici

Per simulare i backup fisici programmati e Oracle tramite RMAN, Cloud SQL per MySQL fornisce backup automatici (oltre ai backup on demand) per consentire il ripristino dei dati in caso di errore di un'istanza. Per simulare Oracle Datadump come strumento di backup logico (esportazione/importazione), puoi utilizzare l'utilità di esportazione Cloud SQL per MySQL mysqldump.

Passaggi successivi