select звёздочка

[egorius]

Иной раз возникает потребность вывести куда-нибудь результаты запроса. Например, Могучая Программа обработала сто миллионов тыщ строк и надо показать журнал работы — несложный запрос типа
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;

Comments

[hardsign.livejournal.com]

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

[sergg-rw.livejournal.com]

+1

[egorius.livejournal.com]

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

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

[n1919.livejournal.com]

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

[egorius.livejournal.com]

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

[n1919.livejournal.com]

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

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

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

least(l_max_reasonable_len, l_approximate_db_len)

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