Использование пакетной передачи команд
1. Подготовим команды для пакетной передачи. Создадим PL/SQL блок. Откроем файл ins_ex в текстовом редакторе. В начале напишем ключевое слово BEGIN, в конце END. Удалим символ “/”. После каждой команды поставим “;”. Сохраним файл под другим именем ins_pls.sql
Должно получиться так:
begin
-- первая команда скрипта
insert into exdoc
( doc_nd
, doc_dd
, customer_n
, address
, total_cost
, total_tax
, total_doc)
values
( 137
, ’12-sep-2003’
, 'Морейнис А. Н.'
, 'ул. Рабочая канавка д.12, кв.37'
, 5391.60
, 959.52
, 6351.12);
-- здесь должны быть команды скрипта
-- последняя команда
update exdoc
set(total_cost, total_tax, total_doc) =
(
select total_cost + sum( cost)
, total_tax + sum( sum_tax)
, total_doc + sum( sum_svc)
from exsvc
);
end;
/
Обращаю внимание. После ключевого слова END стоит ‘;’.
В тексте можно использовать комментарии. Начинается комментарий с пары минусов '--'. Текст до конца строки будет считаться комментарием.
2. Выполним скрипт.
@ins_ex_pls
Если скрипт будет выполнен с ошибками, то воспользуйтесь командами SQL*Plus:
showerror – выводит список ошибок с номерами строк.
list – выводит листинг с номером строки.
Исправьте ошибки и повторно выполните. Нужно добиться безошибочного выполнения.
Если выполнился без ошибок, то будет выведено только одно сообщение. Напомню, при выполнении скрипта ins_ex выводилось сообщение для каждой команды.
3. Закроемт ранзакцию.
commit;
4. Посмотрим результат.
@show_ex
Видим, что данные добавлены корректно.
Переменные в PL/SQL
1. Напомню, именованная ячейка памяти – кирпич в фундаменте программирования. В PL/SQL блоке можно использовать переменные.
Добавляем переменные. Теперь номер и дата счёта будут задаваться через переменные.
Открываем файл в текстовом редакторе.
edit ins_ex_pls
Вносим следующие изменения:
Добавляем секцию declare.
Объявляем переменные ll_doc_nd, ld_doc_dd.
Присваиваем значения переменным, используем оператор ":=".
Во всех командах заменяем константы "137" на ll_doc_nd, ’12-sep-2003’ на ld_doc_dd.
Должно получиться так:
declare
ll_doc_nd number(6); -- номер счёта
ld_doc_dd date; -- дата счёта
begin
-- задаём номер и дату счёта
ll_doc_nd := 137;
ld_doc_dd := '12-sep-2003';
-- первая команда скрипта
-- пример команды insert c переменными
insert into exdoc
( doc_nd
, doc_dd
, customer_n
, address
, total_cost
, total_tax
, total_doc)
values
( ll_doc_nd
, ld_doc_dd
, 'Морейнис А. Н.'
, 'ул. Рабочая канавка д.12, кв.37'
, 5391.60
, 959.52
, 6351.12);
-- здесь должны быть команды скрипта
… и так далее …
Обращаю внимание.
Секцию с объявлением переменных приписал перед словом BEGIN. Все переменные должны быть объявлены в секции DECLARE. В объявлении указывается имя переменной и тип данных, которая она будет хранить.
Переменным можно присваивать значения. Для этого используется оператор присваивания ‘:=’.
В командах DML можно использовать переменные, объявленные в этом блоке.
Придерживайтесь хорошего стиля программирования. Используйте правила именования переменных.
Пусть основной имени переменной будет название соответствующей колонки в таблице. Это избавит вас от выдумывания абстрактных и малозначащих имен.
Вопрос: "А если переменной не соответствует никакая колонка в таблице?"
Ответ: "И часто такое бывает при работе с базой данных?"
Используйте префиксы для переменных. Например, префикс из двух букв. Пусть первая подскажет нам видимость переменной. Буква ‘l’ – от слова локальная, т.е. объявлена в секции DECLARE этого блока.
Вторая буква подскажет тип данных:
l – целое, number;
d – дата, время, date;
n – число со знаками после запятой, number( 9, 2);
s – строка, varchar.
6. Почистим таблицы.
@del_ex
7. Выполним скрипт.
@ins_ex_pls
Если есть ошибки, то исправляем и повторно выполняем. Не забываем команду ROLLBACK и повторную очистку таблиц.
8. @show_ex
Данные правильно загружены.
9. commit;
10. Добавим ещё один счёт с номером 375.
Открываем файл на редактирование
edit ins_ex_pls
Изменяем значение переменной ll_doc_nd на 375.
ll_doc_nd := '375';
11. Выполняем.
@ins_ex_pls
12. Закрываем транзакцию.
commit;
13. Смотрим.
@show_ex
Записей стало больше. Видим два счёта. Уж очень они похожи друга на друга. Обратите внимание на колонку exsvc.exsvc_c – счётчик работает.
Создание отчётов в SQL*Plus
1. Неудобно читать. Отформатируем вывод. Будем использовать команды SQL*Plus для создания отчётов.
edit show_ex
Текст файла:
set linesize 512
col doc_nd for 9999
col address for a36
col customer_n for a16
col pos_no for 99
col good_n for a18
col price for 990.00
col service_n for a16
break on doc_nd page 1
compute sum of cost on doc_nd
compute sum of sum_tax on doc_nd
compute sum of sum_pos on doc_nd
compute sum of sum_svc on doc_nd
select * from exdoc order by doc_nd;
select * from expos order by doc_nd, pos_no;
select * from exsvc order by doc_nd, service_n;
clear break
Команда break – указывает, что нужно делать, если в процессе вывода записей на экран значение в колонке изменилось. В данном примере при изменении значения в колонке doc_nd нужно вставить разделитель страниц, т.е. будет выведена шапка с названием колонок.
Команда compute – создает вычисляемое поле для колонки. Расчёт выполняется в момент изменения значения в колонке, для которой задано правило break. Например, первая команда compute будет суммировать значения в колонке cost, пока не изменится значение в колонке doc_nd.
Обращаю внимание. Колонка doc_nd есть во всех таблицах, правило break будет действовать для каждой из команд SELECT. Колонки cost и sum_tax есть в двух таблицах. Команда для этих колонок compute будет срабатывать для двух последних запросов.
Команда clear break – удаляет все правила break.
2. Смотрим на отформатированную выдачу.
@show_ex
Обратите внимание.
Записи таблицы с заговками счетов выведены по отдельности, каждая со своей "шапкой".
Записи с товарами и услугами сгруппированы по счетам, выведены с шапкой, имеют промежуточные итоги. Номер счёта выводится только для первой строки в группе. Для остальных – подавляется.
Работа с несколькими счетами
Увеличим суммы по документу, создадим третий счёт 391.
1. Откроем скрипт edit ins_ex_pls.sql и найдем какую-нибудь команду UPDATE. Эти команды пересчитывают все записи, которые есть в таблице. Давайте в этом убедимся.
Пусть номер счёта будет 391, увеличим стоимость услуг доставки в 10 раз.
Команда будет такой:
update exsvc
set cost = (select 10 * sum( decode( good_n
,'Банки стеклянные'
, quant
, 'Горшки цветочные'
, quant * 1.7
, 'Пробка медицинская'
, round( quant / 10) * 1.5))
from expos
);
2. Выполним его.
@ins_ex_pls
3. Закроем транзакцию.
commit;
4. Смотрим.
@show_ex
Выдача уже не помещается на экран. Видим, что стоимость услуг упаковки пересчитана для всех счетов.
Обратите внимание на то, каким способом увеличена стоимость упаковки. Результат подзапроса используется в арифметическом выражении.
5. Изменим скрипт таким образом, чтобы изменения затрагивали только один счёт.
Открываем на редактирование файл ins_ex_pls.sql
Во всех командах UPDATE делаем приписку: конструкцию WHERE с проверкой номера и даты счёта. Примерно так:
update expos
set cost =price * quant
where doc_nd =ll_doc_nd
and doc_dd =ld_doc_dd;
Другими словами, ограничиваем изменения только записями, у которых реквизиты счёта совпадают со значением переменных.
Если конструкция WHERE уже есть в команде, то проверку реквизитов счёта включим в условие проверки. Используем логический оператор AND. Для подзапросов тоже добавим WHERE.
Привожу для образца команду пересчёта стоимости услуг. Заодно удалим умножение цены на 10.
update exsvc
set cost =
(
select sum( decode( good_n
, 'Банки стеклянные'
, quant
, 'Горшки цветочные'
, quant * 1.7
, 'Пробка медицинская'
, round( quant / 10) * 1.5))
from expos
where doc_nd = ln_doc_nd
and doc_dd = ld_doc_dd
)
where service_n like '%упаковки%'
and doc_nd = ll_doc_nd
and doc_dd = ld_doc_dd;
Изменим номер счёта на 392. Сохраним правки в файле.
6. Выполним скрипт.
@ins_ex_pls
7. commit;
8. Смотрим.
@show_ex
Итак. В базе уже 4 счёта. Для последнего стоимость услуг пересчитана в 10 раз меньше чем у предыдущих. Другими словами, изменения затронули только один последний счёт. Это видно и на общих суммах по документу. Для просмотра прокрутите экран "назад".
9. Изменим номер счёта на 137. И перепишем команду расчёта общих итогов следующим образом.
update exdoc
set(total_cost, total_tax, total_doc) =
(
select total_cost + sum( cost)
, total_tax + sum( sum_tax)
, total_doc + sum( sum_svc)
from exsvc
where exsvc.doc_nd = exdoc.doc_nd
and exsvc.doc_dd = exdoc.doc_dd
)
where doc_nd = ll_doc_nd
and doc_dd = ld_doc_dd;
В команде арифметические выражения используют значения той записи, которая будет изменена. Значения этой записи можно использовать и в конструкции WHERE. В нашем случае будем использовать реквизиты счёта из главной таблицы exdoc для поиска записей в подчиненной таблице exsvc. Для каждой найденной записи из exdoc будет выполнен свой запрос к таблице exsvc. Такие подзапросы называют итерационными.
Поскольку имена колонок в главной и подчиненной таблицах совпадают нужно перед названием колонки использовать имя таблицы. Это уже делали при использовании представления и последовательности.
Изменим все команды с подзапросами.
Обращаю внимание. Команду с использованием представления следует оставить нетронутой. Вспомните, как работает конструкция FROM-SELECT. Первым будет выполнен SELECT во FROM. Во время его выполнения ничего не известно о "внешнем" запросе, поэтому и ссылаться не на что.
Проверяем работу скрипта.
9. @del_ex
10. @ins_ex_pls
11. commit;
12. @show_ex
Проверяем тестовый набор.
Если есть ошибки, то исправляем их. Помним о команде ROLLBACK. Добейтесь правильной работы скрипта.
Теперь знаем
Язык манипулирования данными DML:
COMMIT
ROLLBACK
WHERE с несколькими условиями, объединены AND
Язык PL/SQL:
структура программы – блок BEGIN-END
переменные
секция DECLARE - объявление переменных
оператор присваивания :=
Программа SQL*Plus:
show error
break
compute
clear break
| < Предыдущая | Следующая > |
|---|
