Migra usuarios de Oracle a Cloud SQL para MySQL: consultas, procedimientos almacenados, funciones y activadores

Este documento es parte de una serie que proporciona información clave y orientación relacionada con la planificación y la realización de migraciones de bases de datos de Oracle® 11g o 12c a instancias de segunda generación con versión 5.7 de Cloud SQL para MySQL. En la serie, se incluyen las siguientes partes:

• Migra usuarios de Oracle a Cloud SQL para MySQL: terminología y funcionalidad
• Migra usuarios de Oracle a Cloud SQL para MySQL: tipos de datos, usuarios y tablas
• Migra usuarios de Oracle a Cloud SQL para MySQL: consultas, procedimientos almacenados, funciones y activadores (este documento)
• Migra usuarios de Oracle a Cloud SQL para MySQL: seguridad, operaciones, supervisión y registro

Consultas

Oracle y Cloud SQL para MySQL admiten el estándar ANSI SQL. En general, es sencillo migrar instrucciones de SQL mediante el uso de elementos de sintaxis básicos (por ejemplo, sin especificar ninguna función escalar o cualquier otra característica extendida de Oracle). En la siguiente sección, se analizan los elementos de consulta comunes de Oracle y sus equivalentes de Cloud SQL para MySQL.

Sintaxis básica SELECT y 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

Puedes leer más detalles sobre la sintaxis SELECT de MySQL.

• Vistas en línea
  • Las vistas en línea (también conocidas como tablas derivadas) son declaraciones SELECT, que se ubican en la cláusula FROM y se usan como una subconsulta.
  • Las vistas en línea pueden ayudar a simplificar las consultas complejas, ya que quitan los cálculos compuestos, o eliminan las operaciones de unión, a la vez que condensan varias consultas separadas en una sola consulta simplificada.
  • Nota de conversión: Las vistas en línea de Oracle no requieren el uso de alias, mientras que MySQL sí requiere alias específicos para cada vista en línea.

En la siguiente tabla, se presenta un ejemplo de conversión de Oracle a MySQL, como una vista en línea.

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 |

Instrucciones JOIN

Las sentencias JOIN de Oracle son compatibles con las sentencias JOIN de MySQL, excepto la cláusula FULL JOIN. Además, las declaraciones JOIN de MySQL admiten el uso de sintaxis alternativa, como la cláusula USING, la cláusula WHERE en lugar de la cláusula ON y el uso de SUBQUERY en la declaración JOIN.

En la siguiente tabla, se presenta un ejemplo de conversión 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 y MINUS

MySQL no admite las funciones INTERSECT y MINUS de Oracle, excepto las funciones UNION y UNION ALL:

• UNION: Adjunta los conjuntos de resultados de dos o más declaraciones SELECT y elimina los registros duplicados.
• UNION ALL: Adjunta los conjuntos de resultados de dos o más declaraciones SELECT sin eliminar los registros duplicados.
• INTERSECT: Muestra la intersección de dos o más declaraciones SELECT solo si existe un registro en ambos conjuntos de datos.
• MINUS: Compara dos o más declaraciones SELECT y muestra solo filas distintas de la primera consulta que no muestran las otras declaraciones.

Notas de la conversión

Cuando conviertas las funciones INTERSECT y MINUS de Oracle en MySQL, usa las declaraciones JOIN, IN y EXISTS como solución alternativa.

Ejemplos

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

Funciones escalares (de una sola fila) y de grupo

MySQL proporciona una lista extensa de funciones escalares (de una sola fila) y de agregación. Algunas de las funciones de MySQL son similares a sus contrapartes de Oracle (en cuanto al nombre y la funcionalidad, o tienen un nombre diferente, pero una funcionalidad similar). Aunque las funciones de MySQL pueden tener nombres idénticos a sus contrapartes de Oracle, pueden exhibir una funcionalidad diferente.

En las siguientes tablas, se describe en qué casos son equivalentes Oracle y MySQL por nombre y funcionalidad (especificado por "Sí") y en qué casos se recomienda una conversión (todos los casos distintos de "Sí").

Funciones de caracteres

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

Funciones numéricas

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

Funciones de fecha y hora

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

Funciones de codificación y decodificación

DECODE

CASE

DUMP

ORA_HASH

MD5/SHA

Funciones de conversión

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

Funciones SELECT condicionales

CASE

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

CASE

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

Funciones nulas

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

Funciones de identificador y entorno

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

Funciones de agregación (grupo)

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

Recuperación de Oracle 12c

FETCH

SELECT * FROM
EMPLOYEES
FETCH FIRST 10 ROWS ONLY;

SELECT * FROM
EMPLOYEES
LIMIT 10;

Filtrado básico, operadores y subconsultas

Durante la conversión, el filtrado básico, las funciones del operador y las subconsultas son relativamente sencillas y no requieren ningún esfuerzo adicional.

Notas de la conversión

Examina y aborda los formatos de fecha porque los formatos de Oracle y MySQL muestran diferentes resultados predeterminados:

• La función SYSDATE de Oracle muestra 01-AUG-19 de forma predeterminada.
• La función SYSDATE() de MySQL muestra 2019-08-01 12:04:05 de forma predeterminada.
• Los formatos de fecha y hora se pueden configurar mediante las funciones [DATE_FORMAT](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format) de MySQL 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);

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

Funciones analíticas (o funciones analíticas y de clasificación)

Las funciones analíticas de Oracle amplían la funcionalidad de las funciones analíticas de SQL estándar, ya que proporcionan capacidades para calcular valores agregados basados en un grupo de filas. Estas funciones se pueden aplicar a conjuntos de resultados con particiones lógicas dentro del alcance de una sola expresión de consulta. Por lo general, se usan en combinación con informes de inteligencia empresarial y estadísticas, con el potencial de aumentar el rendimiento de las consultas como alternativa a fin de lograr el mismo resultado con un código SQL más analítico.

Notas de la conversión

• La versión 5.7 de MySQL no proporciona funciones analíticas para admitir una conversión de instrucciones de SQL sencilla. Sin embargo, esta funcionalidad se agregó de forma parcial en MySQL versión 8, lo que hace que la conversión de funciones analíticas sea un punto a tener en cuenta. Es probable que esto requiera esfuerzo manual en el proceso de migración.
• Una solución opcional es volver a escribir el código para quitar el uso de funciones analíticas, revertir a soluciones de código SQL más tradicionales o mover esta lógica a una capa de aplicación.

En la siguiente tabla, se enumeran las funciones analíticas comunes de 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

Expresión de tabla común (CTE)

Las CTE proporcionan una forma de implementar la lógica del código secuencial a fin de reutilizar el código SQL que puede ser demasiado complejo o no eficiente para varios usos. Las CTE se pueden nombrar y, luego, usar en varias partes de una instrucción de SQL mediante la cláusula WITH.

Notas de la conversión

• La versión 5.7 de MySQL no admite CTE, pero la versión 8 de MySQL sí.
• Como una solución alternativa, usa tablas derivadas o SubQueries, o reescribe la instrucción de SQL para eliminar la funcionalidad de CTE.

Ejemplos

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;

Declaración MERGE

La declaración MERGE (o UPSERT) proporciona un medio para especificar instrucciones de SQL individuales que realizan operaciones DML de forma condicional en una operación MERGE, en lugar de una sola operación DML, que se ejecuta por separado. Selecciona registros de la tabla de origen y, luego, especifica una estructura lógica a fin de realizar de forma automática varias operaciones DML en la tabla de destino. Esta función te ayuda a evitar el uso de varias inserciones, actualizaciones o eliminaciones. Ten en cuenta que MERGE es una declaración determinista, lo que significa que una vez que la declaración MERGE procesa una fila, no se puede volver a procesar mediante la misma declaración MERGE.

Notas de la conversión

A diferencia de Oracle, la versión 5.7 de MySQL no admite la funcionalidad MERGE. Para simular de forma parcial la funcionalidad de MERGE, MySQL proporciona las instrucciones REPLACE y INSERT… ON DUPLICATE KEY UPDATE:

• REPLACE: Funciona de la misma manera que una declaración INSERT, excepto que si una fila anterior de la tabla tiene el mismo valor que una fila nueva para un índice PRIMARY KEY o UNIQUE, la fila anterior se borra antes de insertar la fila nueva.

• INSERT… ON DUPLICATE KEY UPDATE: Si una fila insertada causara un valor duplicado en un índice PRIMARY KEY o UNIQUE, se produce un UPDATE de la fila anterior para eliminar la excepción de clave duplicada, por ejemplo:

  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;
  

Otra solución sería convertir la funcionalidad de MERGE en un procedimiento almacenado para administrar las operaciones de DML, mediante comandos INSERT, UPDATE y DELETE con excepciones y manejo de duplicaciones.

Sugerencias de instrucciones de SQL

Oracle proporciona una gran colección de sugerencias de consulta de SQL que permite a los usuarios influir en el comportamiento del optimizador y en su toma de decisiones, con el objetivo de producir planes de ejecución de consultas más eficientes. Oracle admite más de 60 sugerencias de bases de datos diferentes. MySQL proporciona un conjunto limitado de sugerencias de consulta.

En general, la versión 5.7 de MySQL admite dos tipos de sugerencias de consulta: INDEX HINTS y OPTIMIZER HINTS. Las sugerencias del optimizador de MySQL proporcionan la capacidad de controlar el comportamiento del optimizador dentro de instrucciones de SQL individuales, por ejemplo:

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

MySQL MySQL

BKA, NO_BKA

BNL, NO_BNL

MAX_EXECUTION_TIME

MRR, NO_MRR

NO_ICP

NO_RANGE_OPTIMIZATION

QB_NAME

SEMIJOIN, NO_SEMIJOIN

SUBQUERY

Las sugerencias de índice de MySQL proporcionan al optimizador información sobre cómo elegir índices durante el procesamiento de consultas. Las palabras clave USE, FORCE o IGNORE se usan para controlar el proceso de uso del índice del optimizador, por ejemplo:

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

Notas de la conversión

Debido a que existen diferencias fundamentales entre Oracle y el optimizador de MySQL, y dado que existe alguna superposición entre las sugerencias de consulta de Oracle y MySQL, recomendamos que conviertas cualquier instrucción de SQL de Oracle que contenga sugerencias de consulta no especificadas sobre la base de datos de MySQL de destino.

Realiza ajustes de rendimiento de MySQL a través de herramientas de MySQL (por ejemplo, MySQL Workbench para paneles de rendimiento en tiempo real), y características como examinar consultas mediante planes de ejecución y ajustar la instancia o los parámetros de sesión según el caso práctico.

Planes de ejecución

El objetivo principal de los planes de ejecución es proporcionar una mirada interna a las elecciones que realiza el optimizador de consultas para acceder a los datos de la base de datos. El optimizador de consultas genera planes de ejecución para las declaraciones SELECT, INSERT, UPDATE y DELETE para los usuarios de la base de datos, lo que también permite que los administradores tengan una mejor visión de las consultas y operaciones de DML específicas. Son útiles en especial cuando necesitas ajustar el rendimiento de las consultas, por ejemplo, para determinar el rendimiento del índice o determinar si faltan índices que deben crearse.

Los planes de ejecución pueden verse afectados por los volúmenes de datos, las estadísticas de datos y los parámetros de instancia (parámetros globales o de sesión).

Consideraciones sobre las conversiones

Los planes de ejecución no son objetos de base de datos que se deban migrar; en su lugar, son una herramienta para analizar las diferencias de rendimiento entre Oracle y MySQL que ejecutan la misma declaración en conjuntos de datos idénticos.

MySQL no admite la misma sintaxis, funcionalidad o salida del plan de ejecución que Oracle.

Ejemplos

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

Procedimientos almacenados, funciones y activadores

PL/SQL es el lenguaje de procedimiento extendido de Oracle que se usa para crear, almacenar y aplicar soluciones basadas en código dentro de la base de datos. En general, las funciones y los procedimientos almacenados en la base de datos son elementos de código que constan de ANSI SQL y del lenguaje de procedimiento extendido de SQL, por ejemplo, PL/SQL para Oracle, PL/pgSQL para PostgreSQL, y lenguaje de procedimiento de MySQL para MySQL. MySQL usa el mismo nombre que la base de datos para su propio lenguaje de procedimiento extendido.

El propósito de estos procedimientos almacenados y funciones es proporcionar soluciones para aquellos requisitos que son más adecuados para ejecuciones desde la base de datos y no desde la aplicación (por ejemplo, rendimiento, compatibilidad y seguridad). Aunque los procedimientos almacenados y las funciones usan PL/SQL, los procedimientos almacenados se usan en especial para realizar operaciones DDL/DML, y las funciones se usan en especial a fin de realizar cálculos con el objetivo de mostrar resultados específicos.

Lenguaje de procedimiento de PL/SQL a MySQL

Desde la perspectiva de la migración de código de PL/SQL a MySQL, la implementación de procedimientos de MySQL es diferente de la de Oracle. Por lo tanto, se requiere la migración de código para convertir la funcionalidad de PL/SQL de Oracle en procedimientos almacenados y funciones en MySQL. Además, MySQL no es compatible con Oracle Package y Package Body, por lo que, cuando realices la conversión de código, conviértelos en unidades individuales de código de MySQL (o analízalos). Ten en cuenta que los procedimientos almacenados y las funciones de MySQL también se denominan routines.

Propietario de objetos de código

En Oracle, el propietario de un procedimiento almacenado o una función es un usuario específico. En MySQL, el propietario es un esquema específico (creado en una base de datos por un usuario de la base de datos).

Seguridad y privilegios de objetos de código

En Oracle, para crear un procedimiento almacenado o una función, el usuario debe tener el privilegio del sistema CREATE PROCEDURE (a fin de crear procedimientos o funciones con otros usuarios, los usuarios de la base de datos deben tener el privilegio CREATE ANY PROCEDURE). Para ejecutar un procedimiento almacenado o una función, los usuarios de la base de datos deben tener el privilegio EXECUTE.

En MySQL, para crear un elemento de código, el usuario debe tener el privilegio CREATE ROUTINE y el privilegio EXECUTE para ejecutar. La cláusula DEFINER de MySQL define el creador del usuario para el objeto de código, y el usuario debe tener los privilegios adecuados, como CREATE ROUTINE.

Sintaxis de funciones y procedimiento almacenados de MySQL

En el siguiente ejemplo, se muestra la sintaxis de la función y el procedimiento almacenado de 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