Rompe una línea por un separador. Un poco sobre CONECTAR POR

Hola

Trabajo como desarrollador PL / SQL. Hay una tarea para recopilar algunos datos para las métricas para rastrear la carga del sistema. Hay alguna función que se llama con un parámetro que consiste en una lista de ID.

1,2,3,4…1001,1002,1003…

La tarea es la siguiente. Es necesario dividir dicha línea en elementos y escribirlos en una colección de enteros.

Empecemos

Primero necesitas datos para trabajar. Escribamos una función que genere una cadena con números separados por una coma. Pasaremos un argumento entero N a la función: el número de números en la secuencia.

No seremos inteligentes, haremos la secuencia con el tipo VARCHAR2, no CLOB. A continuación, explicaré por qué VARCHAR2.

Código de función para generar la secuencia:

 CREATE OR REPLACE FUNCTION createNumbers(v_N IN PLS_INTEGER) RETURN VARCHAR2 IS v_str VARCHAR2(4000) := '0'; too_small EXCEPTION; PRAGMA EXCEPTION_INIT(too_small, -6502); BEGIN FOR i IN 1..v_N LOOP BEGIN v_str := v_str || ',' || TO_CHAR(i); EXCEPTION WHEN too_small THEN EXIT; END; END LOOP; RETURN v_str || ','; END; 

Volvamos a nuestra tarea.

Lo primero que viene a la mente es recorrer la línea, reduciendo la longitud de la línea en cada iteración. Dado que, de acuerdo con las condiciones del problema, el resultado debe colocarse en la colección, crearemos la colección correspondiente.

 DECLARE v_N PLS_INTEGER := 1000; v_str VARCHAR2(4000) := createNumbers(v_N => v_N); TYPE tab_number IS TABLE OF NUMBER(5); t_str tab_number := tab_number(); BEGIN WHILE v_str IS NOT NULL LOOP t_str.EXTEND; t_str(t_str.COUNT) := SUBSTR(v_str, 1, INSTR(v_str, ',', 1) - 1); v_str := SUBSTR(v_str, INSTR(v_str, ',', 1) + 1); END LOOP; FOR i IN t_str.FIRST..t_str.LAST LOOP dbms_output.put_line(t_str(i)); END LOOP; t_str.DELETE; END; 

Resultado:
0
1
2
…
421
422
423
…

La función createNumber () toma el argumento v_N = 1000. En la función createNumber (), puede ver el manejo de desbordamiento de la variable v_str. Con un cálculo simple, puede descubrir que 4000 bytes son suficientes para 1021 números. Nuestro 1000 encaja en este tamaño sin ningún problema.

Como puede ver, el resultado es el que se necesitaba. La cadena está dividida.

Aunque Oracle no tiene una función de división () incorporada, como en Java o Python, esta opción no me conviene, porque creo que se escribe demasiado código para una tarea tan simple como dividir una cadena.

En esta etapa, pensé, ¿es posible dividir una cadena solo usando SQL? No me refiero al SQL clásico, sino al SQL que ofrece Oracle.

Recordé el diseño para construir consultas jerárquicas de CONECTAR POR.



La instrucción opcional START WITH le dice a Oracle dónde comenzar el ciclo, es decir, qué fila será la raíz. La condición puede ser casi cualquiera. Se debe especificar la condición después de CONNECT BY. Aquí es necesario decirle a Oracle cuánto tiempo continuará el ciclo.

Se puede ver que la única condición importante para construir una consulta jerárquica es el operador CONECTAR POR, el resto está "encadenado" según sea necesario.

Además, esta construcción tiene un nivel de pseudocolumna, que devuelve el nivel de anidamiento en la iteración actual.

A primera vista, puede parecer que esta construcción no es adecuada para romper una línea. Esto no es del todo cierto. Si la condición se establece correctamente, el recorrido recursivo se puede convertir en cíclico, como en while o para bucles.

Antes de escribir una consulta, considere un algoritmo de recorrido de línea. Es necesario, desde el principio de la línea, cortar un cierto número de caracteres, hasta el carácter delimitador. Arriba, escribí sobre el nivel de pseudocolumna. Lo usaremos como el número de iteración actual.
Resulta algo como esto:

 SELECT SUBSTR(str, INSTR(str, ',', 1, level - 1) + 1, INSTR(str, ',', 1, level) - INSTR(str, ',', 1, level - 1) + 1)) FROM DUAL; 

Pero si observa de cerca, puede ver que este algoritmo no funcionará en la primera iteración, ya que el tercer argumento para la función INSTR () no puede ser 0.

Por lo tanto, agregamos una pequeña condición usando la función DECODE ().

 SELECT SUBSTR(str, DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1), INSTR(str, ',', 1, level) - DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1)) FROM DUAL; 

Ahora la primera iteración funcionará correctamente.

Es hora de aplicar la construcción CONNECT BY. Además tomamos nuestra línea.

 WITH TMain AS (SELECT '100,200,300,400,500' || ',' AS str FROM DUAL) SELECT SUBSTR(str, DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1), INSTR(str, ',', 1, level) - DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1)) FROM TMain CONNECT BY NVL(INSTR(str, ',', 1, level), 0) <> 0; 

Ya escribí que, bajo la condición correcta, la construcción CONNECT BY puede comportarse como un bucle. La condición se cumple hasta que la función INSTR () puede encontrar la enésima posición del carácter delimitador, donde n es el número de la iteración actual y, como recordamos, el nivel de pseudocolumna es responsable del número de iteración.

Parece que el problema está resuelto? No

El código puede funcionar, pero su legibilidad es cero. Ya estaba pensando en volver a la opción de bucle, pero descubrí cómo mejorar la opción CONECTAR POR.

Oracle tiene una herramienta tan poderosa como las expresiones regulares. Específicamente, las funciones regexp_instr () y regexp_substr ().

regexp_instr(_, [, _ [, ] ]) : la función devuelve la posición del carácter al principio o al final de la coincidencia para el patrón, así como su INSTR analógico ().

regexp_substr(_, [, [, ]]) : la función devuelve una subcadena que coincide con el patrón.

Reescribe la consulta usando expresiones regulares:

 WITH TMain AS (SELECT '100,200,300,400,500' || ',' AS str FROM DUAL) SELECT regexp_substr(str, '[^,]+', 1, level) FROM TMain CONNECT BY NVL(regexp_instr(str, '[^,]+', 1, level), 0) <> 0; 

El código se lee mucho mejor que en el ejemplo anterior. Esta opción me queda bien.

Al final, sería lógico comparar las comparaciones de análisis de tiempo de ejecución para las tres opciones. Arriba, prometí explicar por qué en lugar del tipo CLOB usaremos el tipo VARCHAR2. Esto es solo para comparar el tiempo de ejecución. Dado que Oracle maneja el tipo CLOB de manera diferente que VARCHAR2, lo que puede distorsionar los resultados.


Los resultados están tabulados:

Opción de interrupción / número de números	10	100	1000
Ciclo	0.05ms	0,15 ms	1,52 ms
CONECTAR POR	0,18 ms	0.68ms	18,1 ms
CONNECT BY + expresiones regulares	0.25ms	12,1 ms	1s 137ms

Honestamente, el resultado es inesperado para mí. Asumí que la tercera opción sería la más rápida. Bueno, eso será una lección.

Gracias por leer!

Lista de fuentes utilizadas:

1. Consultas jerárquicas (recursivas)