select звёздочка

Иной раз возникает потребность вывести куда-нибудь результаты запроса. Например, Могучая Программа обработала сто миллионов тыщ строк и надо показать журнал работы — несложный запрос типа
select status, count(*) from log group by status.

Однако это легко только в SQL*Plus. А в PL/SQL работать не будет: будьте любезны процедурно открыть курсор, в цикле прочитать данные и вывести их в соответствии_с. Да, и курсор за собой закрыть.

Беда, конечно, небольшая, можно и написать. Но ведь обидно каждый раз делать и отлаживать одно и то же, зная, что есть Простое Решение из декларативного мира.

Итак, ставим задачу: нужна процедура, принимающая произвольный запрос и печатающая результат этого запроса. На эту тему у Оракла есть пакет dbms_sql, и с его помощью вырисовывается такая последовательность действий:

1. open_cursor
2. parse
3. execute
4. describe_columns — можем узнать столбцы запроса и их типы данных
5. для каждого столбца: define_column — некий аналог into
6. fetch_rows — построчная выборка данных
   • для каждого столбца: column_value — переносим прочитанные данные в переменную
7. close_cursor

И на этом можно было бы остановиться, если бы не Злобные Раундтрипы. Вот если, скажем, надо сходить в магазин за хлебом, молоком и сосиской, пойдём ли мы три раза за каждым продуктом отдельно? Вряд ли. Зачем, если они влезают в одну авоську? А зачем же тогда ходить до базы данных отдельно за каждой строкой?

К счастью, dbms_sql умеет делать и bulk collect. Для этого надо вместо define_column сказать define_array, подсунуть ему коллекцию и сообщить размер выборки. Тогда fetch_rows будет читать данные не построчно, а кусками указанного размера.

Таким образом, выборка данных побеждена на 99%. Мелкие пакости, например тип данных long, идут в сад.

Правда, ещё остаётся довольно крупная пакость, связанная с выводом прочитанного в виде таблички à la SQL*Plus: невозможно заранее узнать ширину колонки. Например, у нас есть столбец типа date. Какой ширины выводить колонку, чтобы любые данные этого столбца гарантированно в ней уместились? Да, это можно попробовать вычислить, распарсив NLS_DATE_FORMAT. Но взять и просто спросить у базы — нельзя. А ведь типов много, и у каждого свои заморочки.

Поэтому единственное разумное решение, которое приходит в голову, состоит в том, чтобы анализировать первые N строк и ограничивать ширину колонки по ним. Если же дальше встретятся более широкие данные — ну, не повезло, будем обрезать.

Ну и, наконец, ( собственно процедура )