Oracle. Форматирование и буферизация выходных данных

     Вероятно, вы уже заметили, что SELECT очень мало заботится о красоте отображаемых записей. Он не выравнивает количество десятичных знаков в числах, обрезает заголовки столбцов по своему усмотрению и настойчиво показывает все содержимое большого текстового столбца в одной строке, независимо от того, насколько широким будет этот столбец на экране. Всего лишь несколько простых команд могут радикально улучшить внешний вид выходных данных SELECT.

     Секрет заключен в команде COLUMN. Она не взаимодействует с базой данных Oracle. Ее единственная задача — повлиять на способ отображения информации. По этой причине команды COLUMN не требуется завершать точкой с запятой, как стандартные команды SQL.

 

Форматирование чисел

     Обычно с числами требуется осуществлять три действия:
• Выравнивать количество десятичных знаков
• Помещать разделитель между сотнями, тысячами и т.д.
• Добавлять знак денежной единицы

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

 

Выравнивание количества десятичных знаков

     Команда COLUMN, выравнивающая количество десятичных знаков, имеет следующий синтаксис:

COLUMN имя_столбца FORMAT код_формата;

     Вместо аргумента имя_столбца подставляется имя того столбца, который вы хотите отформатировать. Обратите внимание: здесь не упоминается, какой таблице принадлежит столбец! Команда COLUMN воздействует на все столбцы с указанным именем, независимо от того, в какой таблице они находятся. Впрочем, если два столбца имеют одинаковые имена, они с большой вероятностью содержат похожие данные, и форматирование, примененное к одному из них, обычно имеет смысл и для другого.

     Аргумент код_формата заменяется на представление числа. Это представление содержит по одной цифре «9 » для каждого десятичного знака, который требуется для ваших чисел, а также символ «.» для обозначения десятичного разделителя. (Если в вашей стране целая и дробная части разделяются другим символом, используйте вместо точки букву «D». В этом случае будет использован десятичный разделитель из национальной конфигурации базы данных.)

COLUMN product_price FORMAT 9999.99
SELECT * FROM table1;

 

Добавления разделителя групп разрядов

     Разделитель групп разрядов (group separator) — это символ, разделяющий сотни, тысячи и т.д. в пределах числа. Иногда в  столбце вашей таблицы будут содержатся значения, которые будут смотреться лучше при наличии запятой, отделяющей сотни от тысяч. Этого результата можно достичь при помощи того же синтаксиса COLUMN, который использовался для выравнивания количества десятичных знаков; нужно лишь изменить код формата. Это представление содержит по одной цифре «9″ для каждого десятичного знака, который требуется для ваших чисел, а также символ «,» для разделителя групп разрядов.

COLUMN quantity_on_hand FORMAT 99,999
SELECT * FROM table1;

 

Добавления знака денежной единицы

     Легко догадаться, что речь идет об очередной разновидности кода формата. Попробуйте ввести следующий код, чтобы поместить знак доллара ($) перед каждым значением PRODUCT_PRICE:

COLUMN product_price FORMAT $99.99
SELECT * FROM table1;

 

Таблица кодов числовых форматов

Элемент	Пример	Описание
$	$9999	Помещает знак доллара перед значением
, (запятая)	9,999	Помещает запятую в указанной позиции
. (точка)	99.99	Помещает десятичную точку в указанной позиции
MI	9999MI	Отображает знак минуса (-) после любого отрицательного числа
S	S9999	Помещает в указанной позиции знак плюса (+) для положительных чисел и знак минуса (-) для отрицательных
PR	9999PR	Окружает отрицательные числа угловыми скобками <999>
D	99D99	Отображает в указанной позиции десятичный разделитель, принятый в вашей стране
G	9G999	Отображает в указанной позиции разделитель групп разрядов, принятый в вашей стране
C	C999	Отображает в указанной позиции знак денежной единицы ISO
L	L999	Отображает в указанной позиции знак местной денежной единицы
RN или m	RN	Отображает числа римскими цифрами верхнего или нижнего регистра (только для целых чисел от 1 до 3999)
0	0999	Отображает один и более начальных нулей
0	9990	Отображает пустые значения в виде нулей

 

COLUMN имя_столбца FORMAT Amn WORD_WRAP

     Вероятно, вы уже поняли, что вместо аргумента имя_столбца нужно подставить имя столбца с текстом, подлежащим переносу. Другим аргументом является mn, позволяющий указать, какую ширину (в символах) должен иметь столбец. Буква «А» перед числом означает «алфавитно-цифровой» («alphanumeric»).

 

Форматирования заголовков столбцов

     Все рассмотренные выше способы позволяют улучшать внешний вид отображаемых записей, тогда как заголовки столбцов над этими записями остаются в беспорядке. Помимо того, что имена столбцов состоят только из заглавных букв, в некоторых случаях они превышают ширину самих столбцов и поэтому обрезаются. Об устранении этих проблем позаботится специальная разновидность команды COLUMN. Вот ее синтаксис:

COLUMN имя_столбца HEADING текст_заголовка JUSTIFY LEFT;

или

COLUMN имя_столбца HEADING текст_заголовка JUSTIFY CENTER;

или

COLUMN имя_столбца HEADING текст_заголовка JUSTIFY RIGHT;

     Кроме управления содержимым заголовков, вы получаете возможность использовать смесь символов верхнего и нижнего регистров и разбивать заголовки на несколько строк. Вертикальная черта ( | ) в аргументе текст_заголовка обозначает перевод строки.

     Для отключения форматирования, заданного командой COLUMN, используется следующий синтаксис:

COLUMN имя_столбца OFF;

 

Буферизация выходных данных на диске

     Буферизация (spooling) — это процесс записи информации в дисковый файл. Если выходные данные умещаются на одном экране, можно просто выделить мышью нужный фрагмент, скопировать его в буфер обмена, а затем вставить в любую нужную программу. Возможно, после вставки потребуется установить для этих данных моноширинный шрифт, чтобы сохранить выравнивание строк.

     Команда SPOOL имеет следующий синтаксис:

SPOOL имя_буферного_файла;

     При желании можете включить в имя буферного файла расширение (например, .sql или .ргп). Если расширение не указано, имя автоматически дополняется расширением .lst. Кроме того, имя буферного файла может содержать путь (path), т.е. имя дискового накопителя и каталога, где должен быть сохранен файл. Если путь не указан, буферный файл сохраняется в подкаталоге BIN базового каталога Oracle. Чтобы увидеть, как выполняется буферизация, введите перечисленные ниже команды. Обратите внимание, что после команд SPOOL не нужно ставить точку с запятой, поскольку они являются внутренними командами и не влияют на работу сервера Oracle.

SPOOL c:\test1.prn
SELECT * FROM table1;
SELECT * FROM table2;
SPOOL OFF

 

Файлы сценариев SQL

     К этому моменту вы изучили порядочное количество различных SQL-команд, и в ходе экспериментов с ними набрали множество строк кода. Для бизнес-сред характерно наличие операций, выполняющихся одинаковым (или почти одинаковым) образом по многу раз. Повторный набор одних и тех же команд может быстро утомить. Вместо этого вы можете сохранять часто используемые команды в дисковом файле. Такой подход имеет три основных преимущества: экономит время, устраняя необходимость повторного набора команд; позволяет завершать процедуру в более короткие сроки, поскольку команды считываются с диска намного быстрее, чем вы их набираете; гарантирует, что при каждом выполнении команды будут иметь один и тот же выверенный синтаксис.

 

Создания файла сценария

     Файл сценария (script file) представляет собой обычный текстовый файл. Вы можете создать его в любом текстовом редакторе или процессоре. При использовании текстового процессора не забудьте сохранить файл командой Save As в формате «text only», чтобы он не содержал кодов форматирования.)

 

Запуск сценария

     Запустить сценарий очень легко. Нужно лишь поставить перед именем файла знак «at» (@). Введите в строке приглашения SQL> следующую команду:

@с:\test1.sql

 

Использование переменных в файлах сценариев

     Временами возникает потребность в сценарии, который мог бы выполнять разные действия в зависимости от ситуации. Этого можно достичь за счет использования переменных (variables). Переменная заменяет часть команды, позволяя вводить эту часть при выполнении сценария и тем самым влиять на его работу. (Противоположностью переменным является информация, явно указанная в файле сценария. Информация такого типа называется жестко закодированной (hard-coded), поскольку ее нельзя изменять во время выполнения сценария.) Переменные можно встраивать в сценарии SQL двумя способами: использовать переменные подстановки или команду ACCEPT.

 

Переменные подстановки

     Использование переменной подстановки (substitution variable) — это самый простой способ встроить переменную в сценарий. Чтобы увидеть, как это делается, создайте файл сценария с именем test2.sql и поместите в него следующие команды:

SET VERIFY OFF
SELECT product_name, quantity, purchase_date FROM table1 WHERE quantity>=&minimum_quantity_sold;
SET VERIFY ON

      Сохраните и запустите этот сценарий. Вы увидите приглашение на ввод значения minimum_quantity_sold («Enter value for mmimum_quantity_sold»). Введенное число будет помещено в конструкцию WHERE.

     Команды SET VERIFY OFF и SETVERIFY ON помогают улучшить восприятие сценария. Без них будет показано старое и новое значение переменной подстановки перед выполнением команды SELECT, что приведет в замешательство любого другого пользователя, запускающего сценарий, а в конце концов начнет раздражать и самого создателя сценария. Переменные подстановки можно с тем же успехом использовать для текста и дат. При этом вы не должны забывать, что в SQL текст и даты требуется заключать в одиночные кавычки.

Команда ACCEPT

     Как вы могли заметить, приглашение, которое SQL выдает пользователям, встречая переменную подстановки, выглядит не слишком привлекательно. Альтернативой является команда ACCEPT, которая позволяет определять приглашение произвольного вида. Эта команда имеет следующий синтаксис:

accept имя_переменной prompt текст приглашения

     Чтобы увидеть, как она работает, создайте сценарий с именем test2.sql и поместите в него следующие команды:

SET VERIFY OFF
SET ECHO OFF
ACCEPT v_earliest_date
PROMPT «Earliest date you would like to see? (dd-mmm-yy):»
ACCEPT v_latest_date
PROMPT «Thank you. Latest date you would like to see? (dd-mmm-yy):»
SELECT product_name, quantity, purchase_date FROM «table1″
WHERE purchase_date BETWEEN ‘&v_earliest_date’ AND ‘&v_latest_date’
ORDER BY product_name, quantity;
SET VERIFY ON
SET ECHO ON

     В этом сценарии задействована пара новых команд: SET ECHO OFF и SET ECHO ON. Команды SET ECHO включают и выключают отображение команд сценария на экране. В данном случае они гарантируют, что пользователь увидит только приглашения из команд ACCEPT, но не сами эти команды.

 

Итоги

     Облегчить чтение выбранных данных помогает команда COLUMN, позволяющая форматировать числа, даты и текст, а также определять и выравнивать заголовки столбцов. Чтобы сохранить в дисковом файле все, что выводилось на экран , можно воспользоваться командой SPOOL. Одним из самых мощных способов повышения эффективности работы с SQL является хранение часто используемых групп команд в текстовых файлах с расширением .sql, называемых файлами сценариев. Для запуска этих сценариев достаточно ввести в приглашении SQL символ @, указав после него имя файла, содержащего нужные команды. Чтобы сделать сценарии более универсальными, в них можно включать переменные подстановки и команды ACCEPT, позволяющие вводить критерии (или любую другую информацию) в ходе выполнения сценария.