Разбиение строки по разделителю. Немного про CONNECT BY

Здравствуйте!

Я работаю PL/SQL разработчиком. Есть задача собирать некоторые данные для метрик, чтобы отслеживать загрузку систем. Есть некоторая функция, которая вызывается с параметром, состоящим из списка ID.

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

Задача заключается в следующем. Нужно разбить такую строку на элементы и записать их в целочисленную коллекцию.

Приступим.

Для начала нужны данные для работы. Напишем функцию, которая генерирует строку с числами, разделенными запятой. В функцию будем передавать целочисленный аргумент N – количество чисел в последовательности.

Мудрить не будем, последовательность сделаем с типом VARCHAR2, а не CLOB. Далее объясню, почему именно VARCHAR2.

Код функции для генерации последовательности:

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;

Вернёмся к нашей задаче.

Первое, что приходит на ум, это сделать цикл по строке, с уменьшением длинны строки на каждой итерации. Так как по условию задачи результат нужно поместить в коллекцию, то создадим соответствующую коллекцию.

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;

Результат:
0
1
2
…
421
422
423
…

Функция createNumber() принимает аргумент v_N = 1000. В функции createNumber() можно видеть обработку переполнения переменной v_str. Нехитрым подсчётом можно выяснить, что 4000 байт хватит для 1021 чисел. Наша 1000 без проблем влезает в этот размер.

Как видно, результат тот, который нужен был. Строка разделена.

Пусть даже в Oracle нет встроенной функции split(), как например в Java или Python, но данный вариант меня не устраивает, так как я считаю, что слишком много кода написано для такой простой задачи как разбиение строки.

На данном этапе я задумался, а можно ли разбить строку только средствами SQL? Я имею ввиду не классический SQL, а тот SQL, который предлагает Oracle.

Я вспомнил про конструкцию для построения иерархических запросов CONNECT BY.



Необязательный оператор START WITH говорит Oracle с чего начинать цикл, т.е. какая строка будет корневой. Условие может быть практически любым. Условие после CONNECT BY нужно указать обязательно. Тут надо сказать Oracle, как долго продолжать цикл.

Видно, что единственно важное условие для построения иерархического запроса – это оператор CONNECT BY, остальное «нанизывается» по мере надобности.

Также у этой конструкции есть псевдостолбец level, который возвращает уровень вложенности на текущей итерации.

На первый взгляд может показаться, что данная конструкция для разбиения строки не подходит. Это не совсем так. Если правильно задать условие, то рекурсивный обход можно превратить в циклический, как в циклах while или for.

Прежде чем писать запрос, обдумаем алгоритм обхода строки. Нужно, начиная от начала строки, отрезать некоторое количество символов, до символа разделителя. Выше я писал про псевдостолбец level. Мы его будем использовать, как номер текущей итерации.
Получается что-то такое:

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

Но если присмотреться, то можно увидеть, что данный алгоритм не сработает на самой первой итерации, так как третий аргумент функции INSTR() не может равняться 0.

Поэтому добавим небольшое условие с помощью функции 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;

Теперь самая первая итерации будет отрабатывать корректно.

Пора бы применить конструкцию CONNECT BY. Плюс вынесем нашу строку наверх.

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;

Я уже писал, что при правильном условии конструкция CONNECT BY сможет вести себя подобно циклу. Условие выполняется до тех пор, пока функция INSTR() может найти n-ую позицию символа разделителя, где n – это номер текущей итерации, а как мы помним за номер итерации отвечает псевдостолбец level.

Вроде бы задача решена? Нет.

Код может и работает, но его читаемость нулевая. Я уже думал вернуться к варианту с циклом, но придумал как улучшить вариант с CONNECT BY.

В Oracle есть такое мощное средство, как регулярные выражения. Конкретно функции regexp_instr() и regexp_substr().

regexp_instr(исходная_строка, шаблон[, начальная_позиция [, вхождение ] ]) — функция возвращает позицию символа, находящегося в начале или конце соответствия для шаблона, так же как и ее аналог INSTR().

regexp_substr(исходная_строка, шаблон[, позиция [, вхождение ]]) — функция возвращает подстроку, которая соответствует шаблону.

Перепишем запрос, используя регулярные выражения:

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;

Код читается намного лучше, чем в предыдущем примере. Такой вариант меня устраивает.

В конце было бы логичным привести сравнения времени выполнения разбора строки для трёх вариантов. Выше я обещал объяснить, почему вместо типа CLOB будем использовать тип VARCHAR2. Это нужно как раз для сравнения времени выполнения. Так как Oracle обрабатывает тип CLOB по-другому, чем VARCHAR2, что может исказить результаты.

Код для оценки трёх вариантов:

DECLARE 
  v_N         PLS_INTEGER := 1000;
  v_str       VARCHAR2(32767);
  v_startTime TIMESTAMP(9);
  v_endTime   TIMESTAMP(9);

  TYPE tab_number IS TABLE OF NUMBER(5);
  t_str tab_number := tab_number();
BEGIN
  v_str := createNumbers(v_N => v_N);
  v_startTime := SYSTIMESTAMP; 

  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;
   
  v_endTime := SYSTIMESTAMP; 
  dbms_output.put_line(v_endTime - v_startTime);
  t_str.DELETE;
  
  /*---------------------------------------------------------*/
  v_str := createNumbers(v_N => v_N);
  v_startTime := SYSTIMESTAMP; 
  
  SELECT TO_NUMBER(SUBSTR(v_str, 
                          DECODE(level, 1, 1, INSTR(v_str, ',', 1, level - 1) + 1),
                          INSTR(v_str, ',', 1, level) 
                          - DECODE(level, 1, 1, INSTR(v_str, ',', 1, level - 1) + 1)))
  BULK COLLECT INTO t_str
  FROM DUAL
  CONNECT BY NVL(INSTR(v_str, ',', 1, level), 0) <> 0;
  
  v_endTime := SYSTIMESTAMP;
  dbms_output.put_line(v_endTime - v_startTime);
  t_str.DELETE;
  
  /*---------------------------------------------------------*/
  v_str := createNumbers(v_N => v_N);
  v_startTime := SYSTIMESTAMP;
  
  SELECT TO_NUMBER(regexp_substr(v_str, '[^,]+', 1, level))
  BULK COLLECT INTO t_str
  FROM DUAL
  CONNECT BY NVL(regexp_instr(v_str, '[^,]+', 1, level), 0) <> 0;

  v_endTime := SYSTIMESTAMP;
  dbms_output.put_line(v_endTime - v_startTime);
  t_str.DELETE;
END;

Результаты сведены в таблицу:

Вариант разбиения/Количество чисел	10	100	1000
Цикл	0,05мс	0,15мс	1,52мс
CONNECT BY	0,18мс	0,68мс	18,1мс
CONNECT BY + регулярные выражения	0,25мс	12,1мс	1с 137мс

Честно говоря, результат для меня неожиданный. Я предполагал, что третий вариант будет самым быстрым. Ну что ж, будет уроком.

Спасибо, что прочитали!

Список использованных источников:

1. Иерархические (рекурсивные) запросы