?

Log in

No account? Create an account

Пн, 4 июл, 2011, 00:49
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 строк и ограничивать ширину колонки по ним. Если же дальше встретятся более широкие данные — ну, не повезло, будем обрезать.

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

  1. procedure print(
  2.   p_sql varchar2
  3. )
  4. is
  5.   c_prefetch constant integer := 100;
  6.  
  7.   -- место под читаемые данные
  8.   subtype column_t is dbms_sql.varchar2_table;
  9.   type table_t is table of column_t index by binary_integer;
  10.   l_column column_t;
  11.   l_table table_t;
  12.  
  13.   l_desc_tab dbms_sql.desc_tab;
  14.   l_cursor integer;
  15.   l_cols integer;
  16.   l_res integer;
  17.   l_rows integer;
  18.   l_base_row integer;
  19.  
  20.   procedure calc_max_lens
  21.   is
  22.     l_max integer;
  23.   begin
  24.     for col in 1 .. l_cols loop
  25.       l_max := l_desc_tab(col).col_name_len;
  26.       if l_rows > 0 then
  27.         for row in 1 .. l_rows loop
  28.           l_max := greatest( l_max, nvl(length(l_table(col)(l_base_row + row)),0) );
  29.         end loop;
  30.       end if;
  31.       l_desc_tab(col).col_name_len := l_max;
  32.     end loop;
  33.   end;
  34.  
  35.   procedure print_header
  36.   is
  37.   begin
  38.     for col in 1 .. l_cols loop
  39.       dbms_output.put( rpad( l_desc_tab(col).col_name, l_desc_tab(col).col_name_len+1 ) );
  40.     end loop;
  41.     dbms_output.new_line;
  42.     for col in 1 .. l_cols loop
  43.       dbms_output.put( rpad( '-', l_desc_tab(col).col_name_len, '-' ) || ' ' );
  44.     end loop;
  45.     dbms_output.new_line;
  46.   end;
  47.  
  48.   procedure print_rows
  49.   is
  50.     l_cell varchar2(2000);
  51.   begin
  52.     if l_rows = 0 then
  53.       return;
  54.     end if;
  55.     for row in 1 .. l_rows loop
  56.       for col in 1 .. l_cols loop
  57.         l_cell := nvl(l_table(col)(l_base_row + row),' ');
  58.         dbms_output.put(
  59.           case
  60.             when length(l_cell) > l_desc_tab(col).col_name_len
  61.               then substr( l_cell, 1, l_desc_tab(col).col_name_len ) || '>'
  62.             else rpad( l_cell, l_desc_tab(col).col_name_len+1 )
  63.           end
  64.         );
  65.       end loop;
  66.       dbms_output.new_line;
  67.     end loop;
  68.   end;
  69.  
  70. begin
  71.   -- открываем, парсим, выполняем
  72.   l_cursor := dbms_sql.open_cursor;
  73.   dbms_sql.parse(l_cursor, p_sql, dbms_sql.native);
  74.   l_res := dbms_sql.execute(l_cursor);
  75.  
  76.   -- получаем список столбцов
  77.   dbms_sql.describe_columns(l_cursor, l_cols, l_desc_tab);
  78.   for col in 1 .. l_cols loop
  79.     l_table(col) := l_column; -- инициализация, без нее получим no_data_found
  80.     dbms_sql.define_array(l_cursor, col, l_table(col), c_prefetch, 1);
  81.   end loop;
  82.  
  83.   -- выборка данных
  84.   l_base_row := 0;
  85.   loop
  86.     l_rows := dbms_sql.fetch_rows(l_cursor);
  87.     for col in 1 .. l_cols loop
  88.       l_table(col).delete;
  89.       dbms_sql.column_value(l_cursor, col, l_table(col));
  90.     end loop;
  91.     if l_base_row = 0 then
  92.       calc_max_lens;
  93.       print_header;
  94.     end if;
  95.     print_rows;
  96.     l_base_row := l_base_row + l_rows;
  97.     exit when l_rows < c_prefetch;
  98.   end loop;
  99.   dbms_sql.close_cursor(l_cursor);
  100. end;

Пн, 4 июл, 2011 06:15 (UTC)
hardsign

В этой интересной и, без сомнения, поучительной статье, автор обошёл вниманием испанский вопрос: нахуа?

Пн, 4 июл, 2011 13:34 (UTC)
egorius

Автор посвятил испанскому вопросу самый первый абзац статьи.

На углу двое юношей возились с каким-то механическим устройством. Один убежденно говорил: «Конструкторская мысль не может стоять на месте. Это закон развития общества. Мы изобретём его. Обязательно изобретём. Вопреки бюрократам вроде Чинушина и консерваторам вроде Твердолобова». Другой юноша нёс своё: «Я нашёл, как применить здесь нестирающиеся шины из полиструктурного волокна с вырожденными аминными связями и неполными кислородными группами. Но я не знаю пока, как использовать регенерирующий реактор на субтепловых нейтронах. Миша, Мишок! Как быть с реактором?» Присмотревшись к устройству, я без труда узнал велосипед.

Пн, 4 июл, 2011 09:28 (UTC)
n1919

если строка длиннее X, резать её на куски по X

Пн, 4 июл, 2011 13:41 (UTC)
egorius

Можно и так, но тоже не сахар. Как отличать строку-продолжение от обычной строки? Что делать, если в первых N строках данных не было вообще и мы установили ширину колонки один символ, а потом встретили строку длиной 4096 символов?

Пн, 4 июл, 2011 15:08 (UTC)
n1919

"Как отличать строку-продолжение от обычной строки? "

а зачем ? при выводе-то

"Что делать, если..."

least(l_max_reasonable_len, l_approximate_db_len)

l_approximate_db_len спросить у базы