Добавление записей с услугами
Сначала добавим две записи в таблицу услуг exsvc, а затем рассчитаем стоимость услуг.
1. desc exsvc
2.
insert into exsvc
( doc_nd
, doc_dd
, service_n
, tax)
values
( 137
, '12-sep-2003'
, 'услуги доставки'
, 18);
3. save ins_exsvc1
4.
insert into exsvc
( doc_nd
, doc_dd
, service_n
, tax)
values
( 137
, '12-sep-2003'
, 'услуги упаковки'
, 18)
5. save ins_exsvc2
6. col service_n for a16
7. select * from exsvc;
В таблице exsvc две записи. Стоимость услуг не заполнена.
Расчёт стоимости услуг упаковки
1. Выполним следующую команду. Она рассчитывает стоимость упаковки для позиции "Банки стеклянные".
select good_n
, quant *1 *1.00 as cost
from expos
where good_n = 'Банки стеклянные';
Команда выбрала только одну из четырех имеющихся записей. В колонке cost выведено рассчитанное значение. Оно отличается от того, которое хранится в записи в колонке с таким же именем cost.
Вспоминаем алгоритм расчёта стоимости упаковки. Значения коэффициентов в формулу подставили из справочной таблицы.
Особенности команды.
В списке выводимых значений добавили вычисляемую колонку. Задали для неё имя "cost". Для этого использовали ключевое слово "as".
С помощью конструкции WHERE ограничили выборку. Читаем только записи, для которых выполняется условие: поле good_n имеет значение "Банки стеклянные". При сравнении учитывается регистр букв.
Условие - это логическое выражение. Оно может быть "вычислено" и результат вычисления может быть либо "истина", либо "ложь", либо "NULL". Если условие истинно, значит, оно выполняется. Если ложно, то не выполняется. Если NULL, то ничего нельзя сказать.
При составлении условия можно использовать: имена колонок; константы: числа, строки, даты; арифметические выражения. Как правило условие имеет две сравниваемые части. В качестве операторов сравнения используют знаки: <, >, =, !=, <=,>=.
2. Расчёт стоимости упаковки для второй позиции.
select good_n
, quant *1 * 1.7 as cost
from expos
where good_n like 'Горшки%';
Особенность команды в использование оператора LIKE. Он используется для проверки: подходит ли символьная строка под заданный шаблон. Символ "%" используют в шаблоне для обозначения незначимой части строки.
В команде SELECT проверяются значения колонки good_n. Любая запись, которая начинается с подстроки "Горшки", попадает в результирующий набор.
3. Для третьей позиции "Грунт" расчёт услуг упаковки не требуется, этого товара нет в справочной таблице. Выполняем расчёт стоимости упаковки медицинских пробок.
select good_n
, ROUND(quant / 10, 0) * 1.5 as cost
from expos
where good_n like '%робк%'
Команда использует оператор LIKE для поиска записей, у которых в названии товара содержится подстрока "робк". Неважно где она встретиться в начале или в конце строки. Обратите внимание на двойное использование символа "%".
Арифметическое выражение использует функцию ORACLE округления ROUND. В качестве первого аргумента передаётся результат деления – значение, которое требуется округлить. Второй аргумент задаёт количество знаков после десятичной точки, до которых будет округлено значение. В этой команде округление будет выполнено до единиц.
4. Можно на калькуляторе сложить все полученные значения и записать в таблицу услуг. Но…
лучше напишем такую команду.
select good_n
, DECODE( good_n
, 'Банки стеклянные', quant
, 'Горшки цветочные', quant * 1.7
, 'Пробка медицинская', round( quant / 10) * 1.5)
as cost
from expos;
Конструкции WHERE нет, поэтому будут выведены все записи. Для каждой будет выведено два значения: название товара и рассчитанная стоимость услуг упаковки.
Арифметическое выражение использует функцию DECODE. Она работает так. Берётся первый аргумент, в данном случае значение из колонки good_n, и сравнивается с символьными константами. При совпадении значений вычисляется выражение, которое записано после константы.
Команда выводит четыре строки. В колонке cost видим ранее рассчитанные значения. Для товара "Грунт для цветов" значение не выведено. В функции DECODE нет правила для товара с таким названием, поэтому оно равно NULL.
5. Рассчитываем сумму услуг упаковки для всех позиций счёта. Отредактируем команду следующим образом.
edit
select SUM( DECODE( good_n
, 'Банки стеклянные', quant
, 'Горшки цветочные', quant * 1.7
, 'Пробка медицинская', round( quant / 10) * 1.5))
from expos;
Выполним команду.
Получили одну запись с одной колонкой. На экран выведено значение стоимости услуг упаковки для всех позиций счёта. Сравним его с контрольным значением в форме счёта из учебного примера.
Особенности команды. Удалили колонку с названием товара и добавили функцию SUM. Эта функция выполняет накопительное суммирование, т.е. складывает значения одной колонки в группе записей. Поэтому введённая команда обрабатывает все записи таблицы, но на экран выводит только одну строку.
6. Напишем команду, которая запишет рассчитанное значение в таблицу с услугами.
edit
update exsvc
set cost =
(
select SUM(DECODE( good_n
, 'Банки стеклянные', quant
, 'Горшки цветочные', quant * 1.7
, 'Пробка медицинская', round( quant / 10) * 1.5))
from expos
)
where service_n like '%упаковки%';
Выполним команду. Изменена одна запись.
Команда UPDATE используется для внесения изменений в записи таблицы exsvc, у которых значение в колонке service_n содержит слово "упаковки". В нашем случае это одна запись.
Изменяется значение в колонке cost. Оно будет рассчитано с помощью подзапроса – это команда SELECT, заключенная в круглые скобки. Обращаю внимание, что подзапрос должен возвращать одну строку с одной колонкой. Не больше и не меньше.
7. Запишем команду в файл.
save upd_svc1
8. Смотрим записи в таблице exsvc.
select * from exsvc;
Для одной записи рассчитана стоимость услуг.
Расчёт услуг доставки
1. Приступаем к расчёту стоимости доставки. Находим общий вес товара по счёту. Вводим команду.
select sum( decode( good_n
, 'Банки стеклянные', quant *0.1
, 'Горшки цветочные', quant * 1.2
, 'Пробка медицинская', round( quant / 10) * 0.85
, 'Грунт для цветов', quant)
) as weight
from expos;
Выполним. Вес товара по счёту 167.35 кг.
Этот запрос аналогичен предыдущему. Коэффициенты берём из справочной таблицы. Хотя грунт для цветов отсутствует в ней, мы будем учитывать его вес, т.е. количество задано в колонке quant.
2. В зависимости от веса должны определить стоимость доставки.
edit
select case
when 0 <= v.weight and v.weight < 100 then 1.8
when 100 <= v.weight and v.weight < 200 then 4.7
when 200 <= v.weight then 6
else 0
end
as cost
from (
select sum( decode( good_n
, 'Банки стеклянные', quant * 0.1
, 'Горшки цветочные', quant * 1.2
, 'Пробка медицинская', round( quant / 10) * 0.85
, 'Грунт для цветов', quant)
) as weight
from expos
) v;
Выполним команду. Стоимость доставки равна 4.70 руб.
Команда SELECT, которая рассчитывает вес товара по счёту, обрамлена круглыми скобками и написана в конструкции FROM. Команда SELECT, написанная в конструкции FROM, называется view, по-русски - представление. Сразу после закрывающей скобки указывается имя, по которому можно обращаться к колонкам представления.
Уже знаем, что команда SELECT умеет читать записи из таблицы, но в нашем случае команда SELECT выполняет чтение записей из результирующего набора, который подготовлен другой командой SELECT, написанной в конструкции FROM.
Представлению присвоено имя из одного символа “v”. Оно возвращает одну запись с одной колонкой “weight”.
В зависимости от веса товаров определяем стоимость доставки. Для расчёта используем конструкцию CASE. Как и функция DECODE, она позволяет выполнить проверку нескольких условий. Каждое условие начинается с ключевого слова WHEN. Если оно выполняется, то берётся значение, которое записано после ключевого слова THEN для этого условия. Если не выполняется ни одно условие, то берётся значение после ELSE.
Отличие в условиях. В DECODE выполняется сравнение какого-либо значения с константами, а в CASE можно использовать условия такие же, как в конструкции WHERE. Причём мы используем сложные условия: два простых правила сравнения объединены операцией and, т.е. условие будет истинным при соблюдении обоих правил.
Для обращения к значению в представлении используем запись “v.weight”, т.е. перед названием колонки ставим имя представления и отделяем точкой.
3. Запишем стоимость доставки в таблицу.
edit
update exsvc
set cost =
(
select case
when 0 <= v.weight and v.weight < 100 then 1.8
when 100 <= v.weight and v.weight < 200 then 4.7
when 200 <= v.weight then 6
else 0
end as cost
from (
select sum( DECODE( good_n
, 'Банки стеклянные', quant * 0.1
, 'Горшки цветочные', quant * 1.2
, 'Пробка медицинская', ROUND( quant / 10) * 0.85
, 'Грунт для цветов', quant)
) as weight
from expos
) v
)
where service_n like '%доставки%';
Выполняем. Одна строка изменена.
4. Записываем в файл.
save upd_svc2
5. Проверяем.
select * from exsvc;
6. Осталось рассчитать стоимость налога и общую стоимость услуг. Вводим команды.
edit
update exsvc set sum_tax = cost * tax / 100
/
update exsvc set sum_svc = cost + sum_tax
/
7. Записываем в файл.
save upd_svc3
8. Выполняем.
@upd_svc3
9. Проверяем.
select * from exsvc;
Команды UPDATE не содержат конструкции WHERE и они пересчитали суммы сразу по двум записям. Сравним результат с данным в форме учебного примера. Должны совпадать.
Расчёт общих сумм по счёту
Переходим к расчёту общих сумм по документу. В процессе добавления записи в таблицу exdoc, колонки doc_cost, doc_tax, doc_total заполнили значениями из формы счёта. В команду INSERT написали константы. Теперь рассчитаем эти значения, используя данные из таблицы с позициями товаров и таблицы с услугами.
1. Рассчитываем итоги по товарам. Вводим и выполняем команду.
update exdoc
set (total_cost, total_tax, total_doc)=
(
select sum( cost)
, sum( sum_tax)
, sum( sum_pos)
from expos
);
Изменена одна запись.
В этой команде подзапрос изменяет сразу несколько колонок. Они перечислены через запятую и заключены в круглые скобки. Подзапрос тоже содержит три колонки и для каждой используется функция накопительного суммирования SUM.
2. Сохраним команду в файл.
save upd_exdoc1
3. Проверим.
select * from exdoc;
Суммы рассчитаны. Сравним с итогами по товарам в форме счёта из учебного примера.
4. По аналогии пишем команду для расчёта общей стоимости услуг по счёту. Вводим и выполняем команду.
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
);
Особенность команды в том, что в подзапросе используются значения, которые уже хранятся в таблице exdoc. Это те итоговые суммы за товар, которые рассчитали предыдущей командой UPDATE. Подзапрос рассчитает накопительные суммы по записям в таблице exsvc, добавит их к существующим суммам в таблице exdoc, результат запишет обратно таблицу exdoc.
5. Запишем команду в файл.
save upd_exdoc2
6. Проверим.
select * from exdoc;
Суммы общих итогов совпадают с данными формы из учебного примера.
Скрипт очистки таблиц
Итак. Мы подготовили набор команд для ввода тестового примера. Напишем скрипт для очистки таблиц учебного примера, а затем скрипт для ввода тестового набора данных.
1. Готовим скрипт для удаления данных из таблиц. Открываем текстовый редактор и вводим команды.
edit
delete from exsvc
/
delete from expos
/
delete from exdoc
/
2. Выполняем "/".
3. Записываем в файл.
save del_ex
Скрипт для очистки таблиц готов.
Скрипт загрузки тестового набора данных
4. Готовим скрипт для добавления данных тестового примера. В текстовом редакторе вводим команды SQL*Plus. В процессе работы мы каждую команду записывали в файл. Пусть скрипт последовательно выполняет сохраненные команды.
edit
@ins_exdoc
@ins_expos1
@ins_expos2
@ins_expos3
@ins_expos4
@upd_expos
@ins_exsvc1
@ins_exsvc2
@upd_svc1
@upd_svc2
@upd_svc3
@upd_exdoc1
@upd_exdoc2
5. Сохраним скрипт в файле.
save ins_ex
6. Выполним.
@ins_ex
Скрипт для ввода тестового примера готов.
Скрипт просмотра счетов
7. Готовим скрипт для просмотра таблиц. Перечислим все команды форматирования SQL*Plus. Прочитаем данные из трех таблиц. Обращаю внимание. В команде SELECT используем конструкцию ORDER BY. Она позволяет задать порядок вывода записей.
edit
set linesize 120
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
select * from exdoc order by doc_nd, doc_dd;
select * from expos order by doc_nd, doc_dd, pos_no;
select * from exsvc order by doc_nd, doc_dd, service_n;
8. Запишем скрипт в файл.
save show_ex
Проверим работу скриптов. Удаляем, добавляем, смотрим.
9. @del_ex
10. @ins_ex
11. @show_ex
Теперь знаем
Язык управления данными DML:
INSERT
запись констант
арифметические выражения
значение NULL
DELETE
UPDATE
UPADTE нескольких колонок подзапросом SELECT
SELECT * FROM
SELECT <список колонок> FROM
SELECT FROM (SELECT …)
алиас колонки as
алиас представления
WHERE уcловия с LIKE
ORDER BY
стандартные функции ROUND, DECODE
выражение CASE
функция накопительного суммирование SUM
Команды SQL*Plus:
save с опцией replace
set linesize
column с опицией format
@ - start сокращенно
get
| < Предыдущая | Следующая > |
|---|
