Quebre uma linha por um separador. Um pouco sobre CONNECT BY

Olá

Eu trabalho como desenvolvedor de PL / SQL. Há uma tarefa para coletar alguns dados para métricas para rastrear a carga do sistema. Há alguma função chamada com um parâmetro que consiste em uma lista de IDs.

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

A tarefa é a seguinte. É necessário quebrar essa linha em elementos e gravá-los em uma coleção inteira.

Vamos começar.

Primeiro você precisa de dados para trabalhar. Vamos escrever uma função que gere uma string com números separados por vírgula. Passaremos um argumento inteiro N para a função - o número de números na sequência.

Não seremos inteligentes, faremos a sequência com o tipo VARCHAR2, não CLOB. A seguir, explicarei por que o VARCHAR2.

Código de função para gerar a sequência:

 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; 

Vamos voltar à nossa tarefa.

A primeira coisa que vem à mente é percorrer a linha, reduzindo o comprimento da linha a cada iteração. Como, de acordo com as condições do problema, o resultado deve ser colocado na coleção, criaremos a coleção correspondente.

 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
…

A função createNumber () usa o argumento v_N = 1000. Na função createNumber (), é possível ver o tratamento de estouro da variável v_str. Com um cálculo simples, você pode descobrir que 4000 bytes são suficientes para 1021 números. Nosso 1000 se encaixa nesse tamanho sem problemas.

Como você pode ver, o resultado é o necessário. A cadeia é dividida.

Mesmo que o Oracle não tenha uma função split () interna, como em Java ou Python, essa opção não é adequada para mim, porque acredito que muito código foi escrito para uma tarefa tão simples como dividir uma string.

Nesta fase, pensei, é possível dividir uma string apenas usando SQL? Não quero dizer SQL clássico, mas o SQL que a Oracle oferece.

Lembrei-me do design para criar consultas CONNECT BY hierárquicas.



A instrução START WITH opcional informa ao Oracle por onde iniciar o loop, ou seja, qual linha será a raiz. A condição pode ser quase qualquer. A condição após CONNECT BY deve ser especificada. Aqui é necessário dizer à Oracle por quanto tempo continuar o ciclo.

Pode-se observar que a única condição importante para a construção de uma consulta hierárquica é o operador CONNECT BY, o restante é "amarrado" conforme necessário.

Além disso, essa construção possui um nível de pseudo-coluna, que retorna o nível de aninhamento na iteração atual.

À primeira vista, pode parecer que essa construção não seja adequada para quebrar uma linha. Isto não é inteiramente verdade. Se a condição estiver definida corretamente, o percurso recursivo pode ser transformado em um cíclico, como durante ou para loops.

Antes de escrever uma consulta, considere um algoritmo de passagem de linha. É necessário, a partir do início da linha, cortar um certo número de caracteres, para o caractere separador. Acima, escrevi sobre o nível da pseudo-coluna. Vamos usá-lo como o número da iteração atual.
Acontece algo como isto:

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

Mas se você olhar atentamente, poderá ver que esse algoritmo não funcionará na primeira iteração, pois o terceiro argumento para a função INSTR () não pode ser 0.

Portanto, adicionamos uma pequena condição usando a função 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; 

Agora, a primeira iteração funcionará corretamente.

É hora de aplicar a construção CONNECT BY. Além disso, seguimos nossa linha.

 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; 

Eu já escrevi que, sob a condição correta, o construto CONNECT BY pode se comportar como um loop. A condição é satisfeita até que a função INSTR () encontre a enésima posição do caractere delimitador, onde n é o número da iteração atual e, como lembramos, o nível da pseudo-coluna é responsável pelo número da iteração.

Parece que o problema está resolvido? Não.

O código pode funcionar, mas sua legibilidade é zero. Eu já estava pensando em voltar para a opção de loop, mas descobri como melhorar a opção CONNECT BY.

O Oracle tem uma ferramenta tão poderosa quanto expressões regulares. Especificamente, as funções regexp_instr () e regexp_substr ().

regexp_instr(_, [, _ [, ] ]) - a função retorna a posição do caractere no início ou no final da correspondência para o padrão, assim como seu INSTR () analógico.

regexp_substr(_, [, [, ]]) - a função retorna uma substring que corresponde ao padrão.

Reescreva a consulta usando expressões 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; 

O código lê muito melhor do que no exemplo anterior. Esta opção combina comigo.

No final, seria lógico comparar as comparações de análise de tempo de execução para as três opções. Acima, prometi explicar por que, em vez do tipo CLOB, usaremos o tipo VARCHAR2. Isso é apenas para comparar o tempo de execução. Como o Oracle lida com o tipo CLOB de maneira diferente do VARCHAR2, o que pode distorcer os resultados.


Os resultados são tabulados:

Opção de interrupção / número de números	10	100	1000
Ciclo	0.05ms	0.15ms	1.52ms
CONECTE POR	0.18ms	0.68ms	18.1ms
CONNECT BY + expressões regulares	0.25ms	12.1ms	1s 137ms

Honestamente, o resultado é inesperado para mim. Presumi que a terceira opção seria a mais rápida. Bem, isso será uma lição.

Obrigado pela leitura!

Lista de fontes utilizadas:

1. Consultas hierárquicas (recursivas)