SQL Plus - мощное средство работы с базой и создания отчетов : Oracle

█ 10.09.2011 10:40

Много кто не понимает, насколько мощным инструментом является SQL Plus. С его помощью можно генерировать вполне приличные отчеты. Поскольку SQL Plus инсталлирован практически на каждой машине, где есть клиент Oracle, то, приучив себя им пользоваться и запомнив основные команды форматирования, вы будете себя нормально чувствовать за любой консолью. Итак, возьмем простейший запрос:

Код:

SQL>  select segment_type,sum(bytes) from dba_segments group by segment_type;

SEGMENT_TYPE       SUM(BYTES)
------------------ ----------
INDEX              1505492992
TABLE SUBPARTITION  169607168
ROLLBACK               786432
LOBINDEX             14909440
TABLE PARTITION      53805056
NESTED TABLE           393216
INDEX SUBPARTITION 4194304000
TABLE              2188017664
LOBSEGMENT          435027968
TYPE2 UNDO           28639232
CLUSTER             102825984

SEGMENT_TYPE       SUM(BYTES)
------------------ ----------
INDEX PARTITION      54001664
LOB PARTITION          196608

для начала избавимся от слишком маленького ограничения, разбивающего на две страницы цельный вывод:

Код:

set pagesize 1000

тут же можно настроить более широкие строки, чтобы избежать лишних переносов:

Код:

set linesize 1000

для нашего простенького отчета это не нужно, но все же...

Код:

SQL> r
  1*  select segment_type,sum(bytes) from dba_segments group by segment_type

SEGMENT_TYPE       SUM(BYTES)
------------------ ----------
INDEX              1505492992
TABLE SUBPARTITION  169607168
ROLLBACK               786432
LOBINDEX             14909440
TABLE PARTITION      53805056
NESTED TABLE           393216
INDEX SUBPARTITION 4194304000
TABLE              2188017664
LOBSEGMENT          435027968
TYPE2 UNDO           28639232
CLUSTER             102825984
INDEX PARTITION      54001664
LOB PARTITION          196608

Обращаю внимание, что с помощью r можно вызвать предыдущий запрос, а командой ed - отредактировать его в редакторе (после чего запустить опять же командой r). Теперь добавим немного красоты и понятности.

Код:

col segment_type heading "Тип сегментов"
col SUM(BYTES) heading "Объем"

эти команды меняют заголовки столбцов и получается

Код:

SQL> r
  1*  select segment_type,sum(bytes) from dba_segments group by segment_type

Тип сегментов           Объем
------------------ ----------
INDEX              1505492992
TABLE SUBPARTITION  169607168
ROLLBACK               786432
LOBINDEX             14909440
TABLE PARTITION      53805056
NESTED TABLE           393216
INDEX SUBPARTITION 4194304000
TABLE              2188017664
LOBSEGMENT          435027968
TYPE2 UNDO           28639232
CLUSTER             102825984
INDEX PARTITION      54001664
LOB PARTITION          196608

немного увеличим ширину первого столбца

Код:

col SEGMENT_TYPE format A40

что означает форматирование столбца segment_type в 40 символов
и попробуем отформатировать колонку чисел

Код:

col sum(bytes) format 0000000000000000000000000

что обозначает форматирование до этого разряда, с дополнением нулями (если без дополнения нулями, то надо 0 заменить на 9)

Код:

SQL> r
  1*  select segment_type,sum(bytes) from dba_segments group by segment_type

Тип сегментов                                                 Объем
---------------------------------------- --------------------------
INDEX                                     0000000000000001505492992
TABLE SUBPARTITION                        0000000000000000169607168
ROLLBACK                                  0000000000000000000786432
LOBINDEX                                  0000000000000000014909440
TABLE PARTITION                           0000000000000000053805056
NESTED TABLE                              0000000000000000000393216
INDEX SUBPARTITION                        0000000000000004194304000
TABLE                                     0000000000000002188017664
LOBSEGMENT                                0000000000000000435027968
TYPE2 UNDO                                0000000000000000028639232
CLUSTER                                   0000000000000000102825984
INDEX PARTITION                           0000000000000000054132736
LOB PARTITION                             0000000000000000000196608

вернем предыдущее форматирование цифр и поменяем выравнивание

Код:

col sum(bytes) format 9999999999999

а теперь представим, что это не байты, а рубли

Код:

col sum(bytes) format 9999999999999L

Код:

SQL> r
  1* select segment_type,sum(bytes) from dba_segments group by segment_type

Тип сегментов                                               Объем
---------------------------------------- ------------------------
LOBINDEX                                               9043968р.
INDEX PARTITION                                     1044905984р.
TABLE SUBPARTITION                                 11764498432р.
NESTED TABLE                                            262144р.
TABLE PARTITION                                     1834876928р.
ROLLBACK                                                393216р.
LOB PARTITION                                            65536р.
INDEX SUBPARTITION                                  9312403456р.
LOBSEGMENT                                           174325760р.
TABLE                                              58476789760р.
INDEX                                              63436488704р.
CLUSTER                                               62849024р.
TYPE2 UNDO                                         14064615424р.

если честно, уже забыл, какое форматирование я применял к колонке, это можно посмотреть командой

Код:

col sum(bytes)

вот ее вывод

Код:

SQL> col sum(bytes)
COLUMN	 sum(bytes) ON
HEADING  'Объем'
FORMAT	 9999999999999L

без указания колонок будет выведено описание всех колонок
Как говорится, поигрались и будет...

Код:

col sum(bytes) clear

очистит все то, что мы навертели на эту колонку

Код:

SQL> r
  1* select segment_type,sum(bytes) from dba_segments group by segment_type

Тип сегментов                            SUM(BYTES)
---------------------------------------- ----------
LOBINDEX				    9043968
INDEX PARTITION 			 1044905984
TABLE SUBPARTITION			 1,1764E+10
NESTED TABLE				     262144
TABLE PARTITION 			 1834876928
ROLLBACK				     393216
LOB PARTITION				      65536
INDEX SUBPARTITION			 9312403456
LOBSEGMENT				  174325760
TABLE					 5,8477E+10
INDEX					 6,3436E+10
CLUSTER 				   62849024
TYPE2 UNDO				 1,4065E+10

Обратил внимание, что не добавил сортировку, быстро можно заменить кусок кода командой c

Код:

SQL> c/by segment_type/by segment_type order by 1/
  1* select segment_type,sum(bytes) from dba_segments group by segment_type order by 1
SQL> r
  1* select segment_type,sum(bytes) from dba_segments group by segment_type order by 1

Тип сегментов                            SUM(BYTES)
---------------------------------------- ----------
CLUSTER 				   62849024
INDEX					 6,3436E+10
INDEX PARTITION 			 1050214400
INDEX SUBPARTITION			 9312403456
LOB PARTITION				      65536
LOBINDEX				    9043968
LOBSEGMENT				  174325760
NESTED TABLE				     262144
ROLLBACK				     393216
TABLE					 5,8477E+10
TABLE PARTITION 			 1843396608
TABLE SUBPARTITION			 1,1764E+10
TYPE2 UNDO				 1,4065E+10

Добавим оформление покрасивее, с переносом строки заголовка.

Код:

col SUM(BYTES) ON HEADING 'Моя/будущая/зарплата'  headsep '/'

получится следующее

Код:

SQL> r
  1* select segment_type,sum(bytes) from dba_segments group by segment_type order by 1

                                                Моя
                                            будущая
Тип сегментов                              зарплата
---------------------------------------- ----------
CLUSTER 				   62849024
INDEX					 6,3436E+10
INDEX PARTITION 			 1050214400
INDEX SUBPARTITION			 9312403456
LOB PARTITION				      65536
LOBINDEX				    9043968
LOBSEGMENT				  174325760
NESTED TABLE				     262144
ROLLBACK				     393216
TABLE					 5,8477E+10
TABLE PARTITION 			 1843396608
TABLE SUBPARTITION			 1,1764E+10
TYPE2 UNDO				 1,4876E+10

Еще SQL Plus умеет вычислять значения, как самый заправский отчетный движок, например (для начала почистим оформление)

Код:

col sum(bytes) clear
col segment_type clear

(я просто перезапускал SQL Plus) и укажем, что сумму необходимо вычислять в конце отчета

Код:

break on report
compute sum label 'Итого' of sum(bytes) on report

в первой строке указываем вывод в конце отчета, во второй - операция (надо сказать, что поддерживается еще среднее значение, количество строк, минимум, максимум и др.), метку выводимого и на каком столбце идет подсчет. Можно считать не только итоговые значения, но и по изменениям какого-то столбца, указав его в команде break, но к нашему отчету я не знаю, как это прикрутить.

Код:

SQL> r
  1* select segment_type,sum(bytes) from dba_segments group by segment_type order by 1

SEGMENT_TYPE					       SUM(BYTES)
------------------------------------------------------ ----------
CLUSTER 						 62849024
INDEX						       6,3436E+10
INDEX PARTITION 				       1050214400
INDEX SUBPARTITION				       9312403456
LOB PARTITION						    65536
LOBINDEX						  9043968
LOBSEGMENT						174325760
NESTED TABLE						   262144
ROLLBACK						   393216
TABLE						       5,8477E+10
TABLE PARTITION 				       1843396608
TABLE SUBPARTITION				       1,1764E+10
TYPE2 UNDO					       1,4876E+10
						       ----------
Итого                                                  1,6101E+11

SQL Plus - весьма мощное средство, о котором можно писать и писать (я надеюсь продолжить в следующий отрывок свободного времени), комбинируя его возможности разными способами. Ввиду его “родного” происхождения, он избавлен от множества глюков, которые у меня бывали в PL/SQL developer, например.
Кстати, часто требуется шаблончик для вывода текста в файл...

Код:

SET LINESIZE 1200
SET PAGESIZE 0
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SET PAUSE OFF
SET TRIMSPOOL ON

█ 10.09.2011 10:53

На бегу добавлю. Есть возможность вычислять время выполнения запроса, для этого выставляется timing, т.е.

Код:

set timing on

есть возможность трассировать запросы

Код:

set autotrace traceonly explain

есть возможность задавать свои настройки, которые будут подхватываться при старте, это файлы login.sql и glogin.sql, которые лежат в $ORACLE_HOME\SQLPLUS\ADMIN

█ 17.10.2011 12:35

А возможен ли вывод только самих данных, т.е. без шапки (наименования полей и строки разделителя ----------) или хотябы без строки разделителя?

█ 17.10.2011 12:37

Ответ на этот вопрос есть в первом сообщении:

Цитата:

set pagesize 1000

Установи размер страницы больше количества выводимых строк и не будет тебе строк с разделителями...

█ 17.10.2011 12:49

Цитата:

Zhalex ➤ А возможен ли вывод только самих данных, т.е. без шапки

set pagesize 0 или set heading off?

█ 17.10.2011 23:18

Цитата:

OlegON ➤ set pagesize 0 или set heading off?

то, что надо!
(2-ой вариант в отличие от 1-го дает перед выборкой с данными пустую строку (т.е. символ перевод строки)

█ 16.07.2012 11:34

почему ширина колонок может не меняться? например запрос:

Цитата:

1 select c.article, c.name from smcard c where c.article=000100
2*

ARTICLE NAME
-------------------------------------------------- -------------------------------------------------
SQL> col c.article
COLUMN c.article ON
FORMAT A10

█ 16.07.2012 12:02

Не c.article, а просто ARTICLE

█ 14.08.2018 16:12

У многих возникает вопрос, как получить результаты запроса в файл с таким красивым форматированием.
Ответ простой.
В самом начале, до того, как вы дадите запрос на вывод нужных вам данных, необходимо ввести команду

SQL код:

 
spool имя_файла_с_путем

после этого выполнить запрос и выполнить команду

SQL код:

 
spool off

все, что было между этими двумя spool попадет в указанный вами файл.