Документация Oracle на русском языке





Сайт посвящен разработке информационных систем с использованием технологий Oracle. На сайте можно найти полезную литературу и документацию на русском языке по программированию и администрированию Oracle.Программирование баз данных на Oracle, техническая документация, литература, статьи и публикации.

Главная :: Карта


Oracle Database или Oracle RDBMS — объектно-реляционная система управления базами данных компании Oracle.



 

DeepEdit!

Программирование баз данных на Oracle, техническая документация, литература, статьи и публикации

  • Увеличить размер шрифта
  • Размер шрифта по умолчанию
  • Уменьшить размер шрифта

Первая программа на PL/SQL

Использование пакетной передачи команд
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
 


Ремонт квартир и черновая отделка помещений в Омске в рассрочку.
jAntivirus