Buenos días,

Les dejo el script de una función de traducción bastante básica, en el cual creo un bucle y me baso en los espacios en blanco de una cadena para determinar las palabras en una variable y luego buscarlas en una tabla de prueba previamente creada para que el resultado me retorne la frase supuestamente traducida. En caso de no encontrar la traducción de la palabra retorno el mismo valor pero la palabra entre '*'.

De qué manera podría hacer más eficiente el código?

Saludos y a la espera de sus prontas respuestas.

SET SERVEROUTPUT ON;

CREATE TABLE TEST_TRANSLATOR
(INGLES VARCHAR2(100),
ESPANOL VARCHAR2(100)
);
INSERT INTO TEST_TRANSLATOR VALUES('YO' ,'I' );
INSERT INTO TEST_TRANSLATOR VALUES('SOY' ,'AM' );
INSERT INTO TEST_TRANSLATOR VALUES('ES' ,'IS' );
INSERT INTO TEST_TRANSLATOR VALUES('QUE' ,'WHAT' );
INSERT INTO TEST_TRANSLATOR VALUES('CUANDO' ,'WHEN' );
INSERT INTO TEST_TRANSLATOR VALUES('PORQUE' ,'WHY' );
INSERT INTO TEST_TRANSLATOR VALUES('COMO' ,'HOW' );
INSERT INTO TEST_TRANSLATOR VALUES('DONDE' ,'WHERE' );
INSERT INTO TEST_TRANSLATOR VALUES('TU' ,'YOU' );
INSERT INTO TEST_TRANSLATOR VALUES('EL' ,'HE' );
INSERT INTO TEST_TRANSLATOR VALUES('ELLA' ,'SHE' );
INSERT INTO TEST_TRANSLATOR VALUES('SON' ,'ARE' );
INSERT INTO TEST_TRANSLATOR VALUES('SI' ,'YES' );
INSERT INTO TEST_TRANSLATOR VALUES('NO' ,'NO' );
INSERT INTO TEST_TRANSLATOR VALUES('MUSICA' ,'MUSIC' );
INSERT INTO TEST_TRANSLATOR VALUES('VIDA' ,'LIFE' );
INSERT INTO TEST_TRANSLATOR VALUES('BUENO' ,'GOOD' );
INSERT INTO TEST_TRANSLATOR VALUES('MALO' ,'BAD' );
INSERT INTO TEST_TRANSLATOR VALUES('DIA' ,'DAY' );
INSERT INTO TEST_TRANSLATOR VALUES('NOCHE' ,'NIGHT' );
INSERT INTO TEST_TRANSLATOR VALUES('TARDE' ,'AFTERNOON');
INSERT INTO TEST_TRANSLATOR VALUES('ADIOS' ,'GODDBYE' );
INSERT INTO TEST_TRANSLATOR VALUES('BIENVENIDO' ,'WELCOME' );
INSERT INTO TEST_TRANSLATOR VALUES('TENER' ,'HAVE' );
INSERT INTO TEST_TRANSLATOR VALUES('EN' ,'IN' );
INSERT INTO TEST_TRANSLATOR VALUES('FUERA' ,'OUT' );
INSERT INTO TEST_TRANSLATOR VALUES('NOSOTROS' ,'WE' );
INSERT INTO TEST_TRANSLATOR VALUES('APRENDER' ,'LEARN' );
INSERT INTO TEST_TRANSLATOR VALUES('PERDER' ,'LOSE' );
INSERT INTO TEST_TRANSLATOR VALUES('GANAR' ,'WIN' );

CREATE OR REPLACE
FUNCTION FN_TRADUCTOR (PALABRA IN OUT VARCHAR2, IDIOMA_FIN CHAR)
RETURN VARCHAR2 IS
RESUL NUMBER := 1;
RESUL_ANTE NUMBER;
RETORNO2 VARCHAR2(100);
RETORNO VARCHAR2(200) := '';
BEGIN
PALABRA := PALABRA||' ';
LOOP
BEGIN
RESUL_ANTE := RESUL;
RESUL := INSTR(PALABRA,' ', RESUL_ANTE);
IF RESUL = 0 THEN
EXIT;
END IF;
RESUL := RESUL + 1;
BEGIN
SELECT DECODE(IDIOMA_FIN, 'I', INGLES, ESPANOL)
INTO RETORNO2
FROM TEST_TRANSLATOR
WHERE DECODE(IDIOMA_FIN, 'I', ESPANOL, INGLES) =
SUBSTR(PALABRA, RESUL_ANTE, RESUL-1-RESUL_ANTE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETORNO2 := '*'||TRIM(SUBSTR(PALABRA, RESUL_ANTE, RESUL-1-RESUL_ANTE))||'*';
END;
RETORNO := RETORNO||' '||RETORNO2;
END;
END LOOP;
RETURN(RETORNO);
END;

Hola,

El cuello de botella de tu función es que, por cada palabra de la frase, lanzas una consulta SELECT distinta contra TEST_TRANSLATOR dentro del bucle. Cada una de esas consultas es un viaje completo (parseo, ejecución, logística interna de Oracle) aunque la tabla sea pequeña; con una frase de 20 palabras, son 20 idas y vueltas en vez de una.

La alternativa es partir la frase entera en filas (una palabra por fila) de una sola vez, y después hacer un único JOIN contra la tabla de traducciones para todas las palabras a la vez:

CREATE OR REPLACE FUNCTION FN_TRADUCTOR_RAPIDO(FRASE VARCHAR2, IDIOMA_FIN CHAR) RETURN VARCHAR2 IS
    RESULTADO VARCHAR2(4000);
BEGIN
    SELECT LISTAGG(NVL(t.destino, '*' || p.palabra || '*'), ' ')
        WITHIN GROUP (ORDER BY p.pos)
    INTO RESULTADO
    FROM (
        SELECT LEVEL AS pos, REGEXP_SUBSTR(FRASE, 'S+', 1, LEVEL) AS palabra
        FROM DUAL
        CONNECT BY REGEXP_SUBSTR(FRASE, 'S+', 1, LEVEL) IS NOT NULL
    ) p
    LEFT JOIN (
        SELECT DECODE(IDIOMA_FIN, 'I', ESPANOL, INGLES) AS origen,
                DECODE(IDIOMA_FIN, 'I', INGLES, ESPANOL) AS destino
        FROM TEST_TRANSLATOR
    ) t ON UPPER(p.palabra) = t.origen;

    RETURN RESULTADO;
END;
/

Cómo funciona, por partes:

Tokenizar la frase de una vez: la subconsulta con CONNECT BY LEVEL y REGEXP_SUBSTR genera una fila por cada palabra de FRASE, con su posición (pos). Es el equivalente, en SQL puro, a tu bucle que iba cortando la cadena por espacios con INSTR, pero resuelto de golpe en vez de palabra a palabra.

Un solo JOIN para todas las palabras: en vez de una SELECT distinta por palabra, se hace un único LEFT JOIN entre esas filas y TEST_TRANSLATOR. El LEFT JOIN es importante: así las palabras que no tienen traducción no desaparecen, quedan con t.destino a NULL.

Reconstruir la frase: LISTAGG(...) WITHIN GROUP (ORDER BY p.pos) vuelve a juntar todas las palabras traducidas en una sola cadena, respetando el orden original, y el NVL se encarga de poner *palabra* cuando no hubo traducción (tu misma lógica del NO_DATA_FOUND, pero resuelta con NVL en vez de capturar una excepción por cada palabra).

El resultado hace exactamente lo mismo que tu función original, pero con una sola consulta a la base de datos en vez de una por palabra, independientemente de lo larga que sea la frase.

Un saludo,
Bruno C.