Август/Сентябрь 2003
Сделано в России
Павел Лузанов
Владимир Бегун
Understanding SQL*Plus. Использование переменных
Моей сестре Кате
Владимир Бегун
Лене и Лизе
Павел Лузанов
Источник: персональный сайт П.Лузанова
(http://www.geocities.com/luzanovp/),
18 Марта 2003г.,
http://www.geocities.com/luzanovp/sp_vars.html
Переменные являются фундаментальной составляющей любого средства
программирования. SQL*Plus не исключение и в этой статье
мы рассмотрим использование переменных в этом продукте.
Системные переменные
Практически любой GUI продукт имеет в своем главном меню пункт
с названием "Параметры" (иногда "Опции", "Свойства"). Бывает, что
изучение нового продукта (или новой версии старого) начинается
именно с этого пункта меню. Ведь именно здесь можно посмотреть
какие установки можно задать, как можно настроить рабочую среду.
SQL*Plus использует для настройки среды системные переменные.
Изменяя их значения, мы можем изменить поведение продукта в зависимости
от поставленных задач и требований.
Установка
Для установки значений системным переменным используется команда
SET:
scott@orcl> SET LINESIZE 120
scott@orcl> SET TIMING ON
scott@orcl> SET PAUSE ON
scott@orcl> SET PAUSE More...
scott@orcl> SET TERMOUT OFF PAGESIZE 0 FEEDBACK OFF VERIFY OFF
Из этого примера видно, что существуют системные переменные,
окончательное значение которых определяется несколькими переданными
параметрами (PAUSE, SERVEROUTPUT, AUTOTRACE и т.д.). А последняя строка
показывает, что одной командой SET можно присвоить
значение сразу нескольким переменным.
Просмотр
Логично предположить, что если есть способ установить значение
системной переменной, то и должен быть способ посмотреть установленное
значение. Это делается командой SHOW:
scott@orcl> SHOW LINESIZE
linesize 120
scott@orcl> SHOW TIMING PAUSE TERMOUT PAGESIZE FEEDBACK VERIFY
timing ON
PAUSE is ON and set to "More..."
termout OFF
pagesize 0
feedback OFF
verify OFF
Опять же отметим, что одной командой SHOW можно
посмотреть значения сразу нескольких переменных.
А команда SHOW ALL покажет значения
всех системных переменных. Более того, если внимательно сравнить
результат работы этой команды с возможностями команды SET,
то можно заметить, что SHOW позволяет получить больше,
чем можно установить при помощи SET.
К таким дополнительным опциям команды SHOW относятся:
USER SGA ERRORS PARAMETERS и некоторые другие.
Сохранение/восстановление среды
Разработка скриптов, как правило, начинается с установки
необходимой рабочей среды, т.е. с установки значений системным переменным.
Например, в скриптах выгружающих данные из таблиц БД в
текстовые файлы, можно часто встретить следующее:
SET TERMOUT OFF ECHO OFF PAGESIZE 0 FEEDBACK OFF VERIFY OFF PAUSE OFF
Правила хорошего тона диктуют, что по окончании работы скрипта нужно
вернуть значения измененных системных переменных в начальное состояние.
Пути решения достаточно очевидны. Перед изменением переменных,
нужно запомнить их начальные значения, а в конце работы восстановить
эти запомненные значения.
И вот тут может возникнуть проблема. Точнее проблема возникает
в том случае, если вы захотите запомнить значения отдельных переменных,
например тех, которые будут изменены скриптом (что вообще-то вполне логично).
Дело в том, что в SQL*Plus нет прямого способа запомнить значение переменной,
а в последствии его восстановить.
Но SQL*Plus предлагает другой способ. Мы можем запомнить
значения сразу всех системных переменных, затем изменить отдельные из них,
а в последствии восстановить запомненные значения.
Для того чтобы запомнить значения всех системных переменных
нужно воспользоваться командой STORE:
scott@orcl> STORE SET env.sql
Created file env.sql
Значения переменных сохраняются в файле, который мы указываем в качестве
параметра команды STORE. В нашем случае это env.sql.
Вот небольшой фрагмент этого файла:
set appinfo ON
set appinfo "SQL*Plus"
set arraysize 15
set autocommit OFF
set autoprint OFF
...
Как видим, файл состоит из набора команд SET,
выполнив которые можно установить текущие значения системных
переменных.
Следовательно, если теперь изменить значения отдельных переменных,
то в последствии для восстановления первоначальной среды, достаточно
просто выполнить файл env.sql:
scott@orcl> SET ARRAYSIZE 10
scott@orcl> START env.sql
scott@orcl> SHOW ARRAYSIZE
arraysize 15
Пользовательские переменные
SQL*Plus предлагает к использованию два вида пользовательских переменных:
переменные подстановки (substitution variables) и связанные
переменные (bind variables). Рассмотрим их по порядку.
Переменные подстановки
Если попробовать продолжить классификацию переменных дальше,
то переменные подстановки можно разделить на постоянные и временные.
Постоянные подстановочные переменные
Для создания переменных подстановки используется команда
DEFINE (также можно использовать ACCEPT,
но это тема для отдельного разговора):
scott@orcl> DEFINE tab = DEPT
Выполнив эту команду, мы определили переменную tab и
присвоили ей значение DEPT. Теперь, можно
использовать только что созданную переменную:
scott@orcl> SELECT * FROM &tab;
old 1: SELECT * FROM &tab
new 1: SELECT * FROM DEPT
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
scott@orcl>
Давайте посмотрим на то, что получилось.
Во-первых, перед именем переменной,
мы поставили символ &. Это сделано для того, чтобы SQL*Plus
мог понять, что tab это не имя таблицы, а имя
переменной, значение которой нужно использовать в этом месте
команды SELECT.
Во-вторых, сразу после команды SELECT и до вывода
результатов запроса идут две строки, начинающиеся с old
и new. Первая из них (old) показывает,
как выглядела исходная строка запроса, какой её получил SQL*Plus.
Вторая (new) - это то, как стала выглядеть эта строка
после подстановки значения переменной tab.
Именно в таком виде строка будет отправлена на сервер Oracle для
исполнения.
Вывод этих строк можно подавить, отключив системную переменную
VERIFY, командой SET VERIFY OFF.
Важно понять, что переменная tab в нашем примере
может быть использована не только для хранения имени таблицы.
Она может содержать любое значение, после подстановки которого,
получится допустимая команда SELECT:
scott@orcl> DEFINE tab = 'DEPT ORDER BY deptno DESC'
scott@orcl> SELECT * FROM &tab;
old 1: SELECT * FROM &tab
new 1: SELECT * FROM DEPT ORDER BY deptno DESC
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
scott@orcl>
Теперь становится ясным истинный смысл названия
переменная подстановки. Перед выполнением
команды, SQL*Plus подставляет значение такой переменной
в то место команды, где эта переменная встретилась.
Также, из этого примера видно, что для того, чтобы изменить значение
переменной подстановки нужно снова выполнить команду DEFINE.
Более того, для того чтобы проверить значение переменной подстановки,
мы опять используем DEFINE varname, но уже без знака =
scott@orcl> DEFINE tab
DEFINE TAB = "DEPT ORDER BY deptno DESC" (CHAR)
Всякий раз, выполняя команды, включающие &tab,
SQL*Plus будет использовать значение этой переменной, и это
будет происходить до тех пор, пока мы не выйдем из SQL*Plus
или не удалим эту переменную командой UNDEFINE:
scott@orcl> UNDEFINE tab
scott@orcl> DEFINE tab
SP2-0135: symbol tab is UNDEFINED
Мы убедились, что переменная tab больше не существует.
Временные переменные подстановки
Но что будет, если выполнить команду, включающую
переменную подстановки, заранее неопределенную командой
DEFINE ?
scott@orcl> SELECT * FROM &tab;
Enter value for tab:
Оказывается в этом случае SQL*Plus попросит ввести значение для переменной
tab. И именно введенное значение будет использовано в
окончательном варианте команды SELECT:
scott@orcl> SELECT * FROM &tab;
Enter value for tab: dual
old 1: SELECT * FROM &tab
new 1: SELECT * FROM dual
D
-
X
Чему же теперь, после выполнения запроса, равно значение tab?
scott@orcl> DEFINE tab
SP2-0135: symbol tab is UNDEFINED
Переменная tab не существует!
Это означает, что она была определена только на время выполнения
команды SELECT.
Временные переменные подстановки удобно использовать в тех
случаях, когда нужно выполнить подряд несколько почти одинаковых
команд, заменив изменяющиеся части переменными подстановки:
scott@orcl> INSERT INTO emp (empno, deptno) VALUES (&empno, &deptno);
Enter value for empno: 1
Enter value for deptno: 10
old 1: INSERT INTO emp (empno, deptno) VALUES (&empno, &deptno)
new 1: INSERT INTO emp (empno, deptno) VALUES (1, 10)
1 row created.
scott@orcl> /
Enter value for empno: 2
Enter value for deptno: 30
old 1: INSERT INTO emp (empno, deptno) VALUES (&empno, &deptno)
new 1: INSERT INTO emp (empno, deptno) VALUES (2, 30)
1 row created.
Использование &&
В некоторых случаях нам может понадобиться использовать
переменную подстановки несколько раз в одной и той же
команде:
scott@orcl> SELECT &group_column, COUNT(*) FROM emp GROUP BY &group_column;
Enter value for group_column: deptno
Enter value for group_column: deptno
old 1: SELECT &group_column, COUNT(*) FROM emp GROUP BY &group_column
new 1: SELECT deptno, COUNT(*) FROM emp GROUP BY deptno
DEPTNO COUNT(*)
---------- ----------
10 3
20 5
30 6
Как видим, SQL*Plus повторно запрашивает ввести значение
переменной group_column, не смотря на то,
что мы его только что вводили.
Для избежания повторного ввода, можно использовать два
символа амперсанда перед именем переменной:
scott@orcl> SELECT &&group_column, COUNT(*) FROM emp GROUP BY &group_column;
Enter value for group_column: job
old 1: SELECT &&group_column, COUNT(*) FROM emp GROUP BY &group_column
new 1: SELECT job, COUNT(*) FROM emp GROUP BY job
JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
Теперь значение переменной group_column запрашивается
лишь один раз. Это происходит потому, что при использовании &&
SQL*Plus сначала запрашивает значение несуществующей переменной у
пользователя, аналогично &, а затем создаёт эту переменную, так,
как это делается командой DEFINE. Встретив второй
экземпляр переменной group_column, повторный ввод значения
уже не требуется, а используется значение только что созданной переменной.
Мы можем убедиться в том, что переменная group_column
теперь определена и последующие запросы с её использованием,
не будут требовать ввода значения:
scott@orcl> DEFINE group_column
DEFINE GROUP_COLUMN = "job" (CHAR)
scott@orcl> SELECT &group_column, COUNT(*) FROM emp GROUP BY &group_column;
old 1: SELECT &group_column, COUNT(*) FROM emp GROUP BY &group_column
new 1: SELECT job, COUNT(*) FROM emp GROUP BY job
JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
Предопределенные переменные
В предыдущих разделах мы увидели, как при помощи команды
DEFINE создать новую переменную подстановки,
изменить значение существующей переменной, вывести значение
переменной на экран. При помощи этой же команды можно вывести на экран
значения всех существующих переменных подстановки. Для этого нужно
выполнить DEFINE без параметров. Давайте сделаем это сразу
после запуска SQL*Plus:
c:\>sqlplus scott/tiger
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Mar 3 23:42:41 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
scott@ora92> DEFINE
DEFINE _CONNECT_IDENTIFIER = "ora92" (CHAR)
DEFINE _SQLPLUS_RELEASE = "902000100" (CHAR)
DEFINE _EDITOR = "c:\vim\vim60\gvim.exe" (CHAR)
DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production" (CHAR)
DEFINE _O_RELEASE = "902000100" (CHAR)
scott@ora92>
Мы видим, что несколько переменных уже существуют, хотя мы их
не создавали (за исключением _EDITOR). Это
переменные, которые были автоматически созданы SQL*Plus при
старте. Вот их полный перечень (для версии 9.2):
-
_CONNECT_IDENTIFIER (Появилась в 9.2)
- TNS алиас базы данных, с которой произведено соединение или
SID/service_name (усечённое) если при запуске sqlplus использовалось:
c:\> sqlplus "u/p@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=myport))(CONNECT_DATA=(SID=mysid)))"
-
_SQLPLUS_RELEASE
- Версия SQL*Plus
-
_O_VERSION (Доступна после соединения с БД)
- Описание версии сервера Oracle и имеющихся опций
-
_O_RELEASE (Доступна после соединения с БД)
- Версия сервера Oracle
-
_EDITOR
- Редактор используемый командой
EDIT.
Эта переменная не создается при запуске SQL*Plus хотя и
является предопределенной. Она может вообще отсутствовать,
в этом случае SQL*Plus будет использовать платформно зависимые
алгоритмы для определения редактора (в Windows будет использован
системный редактор, в *nix проверяется окружение пользователя:
переменные $EDITOR, затем $VISUAL
и если они не установлены, то используется ed).
-
_RC
- Также как и
_EDITOR, эта переменная не
создается при старте. Она создается после выполнения
команды HOST и содержит код завершения команды
операционной системы:
scott@orcl> DEFINE _RC
SP2-0135: symbol _rc is UNDEFINED
scott@orcl> HOST del afiedt.buf
scott@orcl> DEFINE _RC
DEFINE _RC = "0" (CHAR)
scott@orcl> HOST ping hostnotexists
hostnotexists: неизвестный узел.
scott@orcl> DEFINE _RC
DEFINE _RC = "1" (CHAR)
Эти переменные предоставляют дополнительную информацию о текущей
SQL*Plus-сессии и являются самыми обычными переменными подстановки.
Ничего не мешает нам изменить их значения или даже удалить,
вопрос только в том - зачем?
Настройка
SQL*Plus имеет несколько системных переменных, которые влияют на
использование переменных подстановки:
SET DEFINE {&|c|ON|OFF}
- Определяет символ подстановки.
Также позволяет отключить сканирование команды на наличие
переменных подстановки.
Поклонники perl могут использовать
SET DEFINE $
SET ESCAPE {\|c|ON|OFF}
- Определяет символ, который ставится перед символом подстановки.
В таком случае, SQL*Plus будет считать символ подстановки
как обычный символ.
SET VERIFY {ON|OFF}
- Определяет, нужно ли после выполнения подстановки выводить на экран
значения старой и новой строки.
SET CONCAT {.|c|ON|OFF}
- Определяет символ, который разделяет имя переменной подстановки
от последующего текста, если этот текст должен начинаться сразу
после значения переменной.
Если вы не переопределили эту переменную и сразу после значения
переменной должна следовать точка (.),
то используйте два символа точки подряд:
scott@orcl> DEFINE filename=myfile
scott@orcl> SPOOL &filename..lst
В результате последующий вывод будет направлен в
файл myfile.lst
Связанные переменные
Помимо переменных подстановки, SQL*Plus предлагает ещё один
тип переменных - связанные переменные (хост-переменные).
Их главное предназначение - это использование в анонимных PL/SQL блоках.
В отличие от переменных подстановки, которые лишь формируют
окончательный вид выполняемой команды (PL/SQL блока в данном случае),
связанные переменные используются как обычные переменные, т.е.
внутри PL/SQL блока связанной переменной можно присвоить значение,
сравнить это значение со значением других переменных и, что очень важно,
использовать значение этой переменной в другом PL/SQL блоке:
scott@orcl> VARIABLE num_var NUMBER
scott@orcl> DECLARE
2 dummy NUMBER := 10;
3 BEGIN
4 :num_var := dummy + 5;
5 IF :num_var > 10
6 THEN
7 DBMS_OUTPUT.PUT_LINE ('num_var > 10');
8 END IF;
9 END;
10 /
num_var > 10
PL/SQL procedure successfully completed.
scott@orcl> BEGIN
2 :num_var := :num_var + 10;
3 DBMS_OUTPUT.PUT_LINE ('num_var: ' || TO_CHAR(:num_var));
4 END;
5 /
num_var: 25
PL/SQL procedure successfully completed.
scott@orcl>
Как видим, связанные переменные создаются командой VARIABLE,
а при использовании перед именем переменной ставится двоеточие.
При создании переменной мы определяем тип данных, в данной случае, была
объявлена числовая переменная. Помимо числовых можно объявлять переменные
типов: CHAR, VARCHAR2, CLOB, REFCURSOR и некоторых других.
Если вы забыли, какого типа та или иная связанная переменная, то
нам опять же поможет команда VARIABLE:
scott@orcl> VARIABLE num_var
variable num_var
datatype NUMBER
А чтобы получить полный список объявленных связанных переменных,
то нужно выполнить эту команду без параметров:
scott@orcl> VARIABLE
variable n
datatype NUMBER
variable vc30
datatype VARCHAR2(30)
variable vc255
datatype VARCHAR2(255)
variable rc
datatype REFCURSOR
variable num_var
datatype NUMBER
Команда VARIABLE позволяет получить тип данных
переменной, но не её значение.
Получить значение связанной переменной можно разными способами.
Один из них мы видели в вышеприведенном примере, это использование пакета
DBMS_OUTPUT внутри PL/SQL блока.
Помимо этого можно воспользоваться командой SQL*Plus -
PRINT:
scott@orcl> PRINT num_var vc30
NUM_VAR
----------
25
VC30
--------------------------------
scott@orcl>
Т.е. PRINT может выводить на экран значения сразу
нескольких переменных, ну а без параметров, как и можно предположить,
она выведет значения всех переменных.
Здесь же нужно упомянуть системную переменную AUTOPRINT,
установив которую в значение ON, мы добъемся того, что после
каждого выполненного PL/SQL блока, SQL*Plus будет автоматически выполнять
команду PRINT для всех связанных переменных, использованных
в этом блоке:
scott@orcl> SET AUTOPRINT ON
scott@orcl> BEGIN :vc30 := 'Hello, World!'; END;
2 /
PL/SQL procedure successfully completed.
VC30
--------------------------------
Hello, World!
Ну и напоследок мы оставили использование связанных переменных
в командах SQL:
scott@orcl> EXECUTE :n := 10;
PL/SQL procedure successfully completed.
scott@orcl> SELECT empno, ename, deptno FROM emp WHERE deptno = :n;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7782 CLARK 10
7839 KING 10
7934 MILLER 10
Ничего необычного, всё отлично работает.
Но настораживает один факт. В документации по SQL*Plus версии 9,
в разделах посвященных связанным переменным, вы не найдете ни одного
упоминания о SQL, ни одного примера использования связанных переменных
в командах SQL. Речь идет только о PL/SQL.
Мы не знаем, с чем это связано, но знаем, что в старых версиях
продукта (в 3-ей, которая поставлялась с Oracle7) в определённых
ситуациях связанные переменные в командах SQL работали некорректно.
Передача данных между переменными разных типов и базой данных
Переменные ничего бы не стоили, если бы им нельзя было присваивать
значения. Ну а поскольку мы имеем дело с СУБД Oracle, то переменные бы
ничего не стоили, если бы им нельзя было присваивать значения из базы
данных.
Поэтому в этом разделе мы рассмотрим способы, позволяющие пользовательским
переменным обоих типов получать значения из базы данных, а также
обмениваться значениями между собой.
Со связанными переменными всё достаточно просто. Мы присваиваем им
значения внутри анонимного PL/SQL блока как обычным PL/SQL переменным:
scott@orcl> VARIABLE version VARCHAR2(30)
scott@orcl> VARIABLE compatibility VARCHAR2(30)
scott@orcl> BEGIN dbms_utility.db_version (:version, :compatibility); END;
2 /
PL/SQL procedure successfully completed.
scott@orcl> PRINT version compatibility
VERSION
--------------------------------
8.1.7.1.1
COMPATIBILITY
--------------------------------
8.1.0
С переменными подстановки немного сложнее. Им нельзя присвоить значение
из базы данных при помощи анонимного PL/SQL блока. Но это можно сделать
комбинацией из команды COLUMN... NEW_VALUE... и последующей
SELECT:
scott@orcl> DEFINE rowcount = ''
scott@orcl> COLUMN total_rows NEW_VALUE rowcount
scott@orcl> SELECT COUNT(*) AS total_rows FROM emp;
TOTAL_ROWS
----------
14
scott@orcl> DEFINE rowcount
DEFINE ROWCOUNT = 14 (NUMBER)
В результате выполнения запроса, значение столбца total_rows
присваивается переменной rowcount.
Если запрос возвратит несколько строк, то переменной rowcount
будет присвоено значение этого столбца для последней возвращенной строки:
scott@orcl> DEFINE rownum = 0
scott@orcl> COLUMN rownum NEW_VALUE rownum
scott@orcl> SELECT rownum, dname FROM dept;
ROWNUM DNAME
---------- --------------
1 ACCOUNTING
2 RESEARCH
3 SALES
4 OPERATIONS
scott@orcl> DEFINE rownum
DEFINE ROWNUM = 4 (NUMBER)
Этот приём уже настолько вошел в практику, что и не сразу
вспомнишь, что опция NEW_VALUE (вместе с OLD_VALUE)
команды COLUMN изначально предназначалась для удобства
при создании мастер-детальных отчетов.
Теперь, что касается обмена значениями между переменными разных типов.
Опять же, присвоить связанной переменной значение переменной подстановки
просто:
scott@orcl> DEFINE subst_var = 'Hello, World!';
scott@orcl> VARIABLE bind_var VARCHAR2(30)
scott@orcl> BEGIN :bind_var := '&subst_var'; END;
2 /
old 1: BEGIN :bind_var := '&subst_var'; END;
new 1: BEGIN :bind_var := 'Hello, World!'; END;
PL/SQL procedure successfully completed.
scott@orcl> PRINT bind_var
BIND_VAR
--------------------------------
Hello, World!
Нужно лишь следить за наличием одинарных кавычек в переменной
подстановки. Их наличие может привести к неправильному синтаксису
результирующего PL/SQL блока.
Ну а для присвоения переменной подстановки значения связанной
переменной, используем тот же трюк с COLUMN... NEW_VALUE...:
scott@orcl> DEFINE version = ''
scott@orcl> COLUMN version NEW_VALUE version NOPRINT
scott@orcl> SELECT :version AS version FROM DUAL;
scott@orcl> DEFINE version
DEFINE VERSION = "8.1.7.1.1" (CHAR)
Примеры использования
Выгрузка табличных данных в текстовый файл
Часто можно услышать вопрос о том, какую утилиту/программу использовать
для выгрузки данных из Oracle. Весьма достойную альтернативу написанию
собственных "unloader-ов" на C, Java и даже Delphi представляет собой
написание скрипта для SQL*Plus.
Нам же этот пример интересен тем, что в нём активно используются
системные переменные SQL*Plus.
Оговоримся сразу, что речь не идет об универсальном скрипте для
выгрузки данных из любой таблицы. Создание универсальной программы
для выгрузки - весьма нетривиальная задача. Мало того, что нужно
определиться с форматом вывода дат и чисел, нужно еще как-то
обрабатывать такие типы данных как LOB-ы, LONG, RAW, XMLType,
объектные типы и т.п.
Речь идет о примере скрипта, который может быть использован для
выгрузки данных из таблицы emp. Данные выгружаются
в формате, где отдельные столбцы разделяются друг от друга
разделителем, в данном случае символом табуляции. Выходной файл
имеет расширение xls, для того чтобы его можно было
открыть в Excel.
REM Отключим всё, что может помешать процессу выгрузки данных
REM или повлияет на выходной формат
SET TERMOUT OFF ECHO OFF PAGESIZE 0 FEEDBACK OFF VERIFY OFF PAUSE OFF
REM Установим длину строки достаточную для вывода одной записи
SET LINESIZE 80
SET TRIMSPOOL ON
REM Установим символ табуляции в качестве разделителя столбцов
COLUMN tabchar NEW_VALUE tabchar NOPRINT
SELECT CHR(9) AS tabchar FROM DUAL;
SET COLSEP '&tabchar'
REM Для примера возьмём лишь четыре столбца
COLUMN empno FORMAT 9999
COLUMN ename FORMAT a10
COLUMN hiredate FORMAT a10
COLUMN sal FORMAT 99999.99
REM Сохраним вывод в файле с расширением xls, для того чтобы
REM его можно было открыть в Excel
SPOOL emp.xls
SELECT empno,
ename,
TO_CHAR(hiredate, 'DD.MM.YYYY') AS hiredate,
sal
FROM
emp
ORDER BY
empno;
SPOOL OFF
EXIT
Если сохранить этот скрипт с именем emp.sql, то
следующие команды позволят увидеть данные о сотрудниках
в электронной таблице:
c:\>sqlplus -s scott/tiger @emp.sql
c:\>start emp.xls
Макроподстановки для часто используемых команд
Понятие макроподстановок существует во многих средствах разработки.
В SQL*Plus для реализации макроподстановок можно использовать
переменные подстановки.
Например, при работе с анонимными PL/SQL блоками в SQL*Plus
часто приходится пользоваться процедурой DBMS_OUTPUT.PUT_LINE
для вывода информации на экран. Каждый раз набирать эту последовательность
символов - крайне утомительное занятие. Так почему бы ни поместить
в файл login.sql следующую команду:
DEFINE p = DBMS_OUTPUT.PUT_LINE
Теперь мы можем заметно продлить жизнь клавиатуре:
scott@orcl> begin &p('Hello, World!'); end;
2 /
Hello, World!
PL/SQL procedure successfully completed.
Другой пример. Попробуйте догадаться, что скрывается за переменной
DBA_INVALIDS :
system@orcl> select &dba_invalids;
no rows selected
Ответ:
system@orcl> DEFINE dba_invalids = "owner, object_name, object_type -
> FROM dba_objects -
> WHERE status = 'INVALID' -
> ORDER BY owner, object_name"
Проверка существования переменной и установка значения по умолчанию
Скрипт nvl.sql проверяет существование переменной
подстановки и если таковой еще нет, то задаёт ей значение по умолчанию.
Если же такая переменная существует, то её значение остаётся
неизменным. Как и следует из названия, действие скрипта похоже
на действие функции NVL (а если быть ещё точнее,
на действия встроенной процедуры DEFAULT_VALUE
из Oracle Forms).
Например, если вы хотите установить в качестве редактора - vi,
но только в том случае если редактор ещё не определен, то выполните
следующее:
SQL> DEFINE nvl_name = '_EDITOR'
SQL> DEFINE nvl_value = 'vi'
SQL> @nvl
С одной стороны может показаться, что было бы более логично
передать значения _EDITOR и vi в
скрипт nvl.sql при помощи параметров:
SQL> @nvl _editor vi
но дело в том, что параметры скриптов сами являются
переменными подстановки, и им точно также можно задавать
значения по умолчанию скриптом nvl.sql! Таким образом,
для того чтобы воспользоваться данным скриптом для проверки
переменных 1 и 2, нужен другой способ передачи данных.
Но параметры, скрипты - это тема для отдельного разговора,
к которой мы обязательно вернёмся. Что же касается файла
nvl.sql, то вот он:
REM nvl.sql
REM
REM DESCRIPTION
REM Emulates NVL() functionality for sqlplus substitution variables.
REM Before running nvl.sql you need to define two variables:
REM nvl_name - a name of checked variable
REM nvl_value - a value which will be assigned to the variable
REM if that is not defined yet
REM USAGE
REM The following commands assign vi to _editor variable
REM if _editor is not defined.
REM
REM DEFINE nvl_name = '_EDITOR'
REM DEFINE nvl_value = 'vi'
REM @nvl
REM
REM RESTRICTIONS
REM NVL_VALUE variable must not contain single quote character - '
REM Use two single quote characters instead - ''
REM
REM Copyright(c) 2002,2003 Pavel Luzanov, Vladimir Begun
REM All Rights Reserved.
REM No warranty, the code is distributed "as is". This header
REM must not be removed in case of using the code or the algorithm.
SET TERMOUT OFF
REM *nix command for deleting files
REM DEFINE del = 'rm -f'
REM Windows command for deleting files
DEFINE del = 'del'
REM
REM Define temp file name
REM
COLUMN hidden_column NEW_VALUE tmpfile NOPRINT
SELECT dbms_random.string('U', 8)||'.sql' AS hidden_column FROM dual;
REM
REM Save the output from DEFINE command into a file and then
REM load the file back to SQL buffer
REM
SPOOL &tmpfile
DEFINE &nvl_name
SPOOL OFF
GET &tmpfile NOLIST
REM
REM Here we suppose that nvl_name is NOT a multilined variable, therefore
REM all lines except first one are removed
REM
DEL 2 LAST
REM
REM If variable exists then do nothing
REM
CHANGE /DEFINE .../BEGIN null; END;/
REM
REM Else assign default value
REM
COLUMN hidden_column NEW_VALUE &nvl_name NOPRINT
CHANGE /SP2-.../SELECT '&nvl_value' AS hidden_column FROM dual/
REM
REM Run accumulated SQL buffer
REM
/
REM
REM Cleanup
REM
COLUMN hidden_column CLEAR
HOST &del &tmpfile
UNDEFINE nvl_name
UNDEFINE nvl_value
UNDEFINE tmpfile
UNDEFINE del
CLEAR BUFFER
Матричные отчеты
Этот пример показывает использование связанной переменной типа
REFCURSOR для получения матричных отчётов.
Нижеприведенный скрипт позволяет получить информацию о заработной
плате сотрудников в разрезах отделов и должностей.
VARIABLE rc REFCURSOR
DECLARE
sql_v VARCHAR2(32767);
BEGIN
sql_v := 'SELECT job';
FOR rec IN (SELECT deptno, dname FROM dept)
LOOP
sql_v := sql_v ||',SUM(DECODE(deptno,'''||rec.deptno||''',sal,0)) AS "'||
rec.dname||'"';
END LOOP;
sql_v := sql_v || ' FROM emp GROUP BY job';
OPEN :rc FOR sql_v;
END;
/
PRINT rc
JOB ACCOUNTING RESEARCH SALES OPERATIONS
--------- ---------- ---------- ----------- ----------
ANALYST 0 6000 0 0
CLERK 1300 1900 950 0
MANAGER 2450 2975 2850 0
PRESIDENT 5000 0 0 0
SALESMAN 0 0 5600 0
18 Марта 2003г.
Владимир Бегун. Примечание.
The statements and opinions expressed here are my own
and do not necessarily represent those of Oracle Corporation.
Copyright(c) 2002, 2003 Pavel Luzanov, Vladimir Begun
All Rights Reserved
You may use library or code examples in your non-commerical use subject
to citing the original authors in your documentation and to understanding
that NO WARRANTY is implied. For commerical use or purpose, you must
contact the authors of any library or code example for permission.
An example of appropriate credit is:
"This product uses code for /whatever/ written by
Pavel Luzanov and Vladimir Begun, which is being
used by their permission."
| 
