Обязательные для заполнения колонки
1. Очистим таблицу счетов.
delete from exdoc;
2. Вспомним структуру таблицы.
desc exdoc
3. Добавим одну запись в таблицу exdoc.
insert into exdoc( doc_nd) values( null);
Одна запись добавлена.
Особенности команды. В списке колонок указали только одну doc_nd, хотя команда desc показала, что в таблице 7 колонок. Для обозначения пустого значения использовали ключевое слово NULL.
Итак, команда добавляет одну запись и присваивает пустое значение одной колонке.
4. Смотрим содержимое таблицы.
select * from exdoc;
На экран выведена шапка таблицы, записей не видно. На самом деле в таблице точно есть одна запись, но все поля имеют значение NULL и поэтому её на экране не видно.
Предыдущая команда INSERT добавила запись, но в ней было задано значение только для одной колонки и в процессе добавления пропущенным колонкам было присвоено тоже значение NULL. Таким образом, команда добавила запись, у которой все колонки имеют значение NULL.
5. Проверим наличие записей другим запросом.
select count(*) from exdoc;
Выведена запись, которая содержит число 1.
Выведенное число – количество записей в таблице exdoc. Команда использует функцию COUNT. Эта функция работает аналогично функции накопительного суммирования SUM. Функция SUM складывает значения, функция COUNT подсчитывает количество значений. Выражение “COUNT(*)” подсчитывает количество записей в таблице.
6. Удалим запись "невидимку".
delete from exdoc;
7. Изменим описание таблицы. Пусть колонка doc_nd будет обязательна для заполнения.
alter table exdoc modify (doc_nd not null);
Получили рапорт: таблица изменена.
Команда ALTER TABLE изменила описание колонки doc_nd в таблице exdoc. Колонка с этих пор не может принимать значение NULL.
8. Запомним команду.
save alt_exdoc1
Пусть поля "Дата счёта" и "Покупатель" обязательны для заполнения. Введём команды, сохраним их в файле.
9. alter table exdoc modify (doc_dd not null);
10. save alt_exdoc2
11. alter table exdoc modify (customer_n not null);
12. save alt_exdoc3
13. Посмотрим ещё раз структуру таблицы exdoc.
desc exdoc
Результат команды desc содержит подсказку – может в колонке содержаться пустое значение или нет. Смотрите столбец “Null?”. Напротив колонок, которые обязательны для заполнения, написано “NOT NULL”.
14. Ну, а теперь можно добавить запись "невидимку"?
insert into exdoc( doc_nd) values( null);
Нет. Получили сообщение об ошибке. Обратите внимание. В сообщении указано, какую колонку надо заполнить.
Дублирование записей
Воспроизведем ситуацию с дублированием записей.
Пусть в таблице счетов будет одна запись из тестового набора. Почистим таблицу; добавим запись; настроем вывод; проверим, что запись есть.
1. delete from exdoc;
2. @ins_exdoc
3. set linesize 512
4. col customer_n for a18
5. col address for a32
6. select * from exdoc;
7. Введём такую команду.
insert into exdoc select * from exdoc;
Получили сообщение: 1 запись добавлена.
Внимательно рассмотрим команду. Это команда добавления записей в таблицу exdoc, но вместо списка колонок и списка значений написан подзапрос.
Команда SELECT будет выбирать записи, команда INSERT будет их добавлять в указанную таблицу. Обращаю внимание. Добавление записей начнется после того, как будет завершена обработка подзапроса.
В этой команде чтение и добавление производится с одной и той же таблицей, т.е. создается копия того, что есть в таблице.
Для команды INSERT опущен список колонок. Это означает, что значения колонок должны быть указаны в том порядке, который задан при создании таблицы. В команде SELECT тоже опущен список колонок. Уже знаем, что символ ”*” вернёт колонки в том порядке, который был указан при создании таблицы.
8. Посмотрим на результат работы команды "INSERT по SELECT’у".
select * from exdoc;
Выведено две записи. Запись, которая была до выполнения команды INSERT, сдублирована.
Итак, команда INSERT может добавлять сразу несколько записей. Для этого используется механизм подзапросов, конструкция "INSERT по SELECT’у"
9. Уже было сказано, что трудно написать "простой SELECT" для доступа к записи, имеющей дубликат. Введём команду.
select distinct * from exdoc;
Выведена только одна запись.
Опция DISTINCT указывает, что нужно выбирать только уникальные записи по указанному списку колонок, т.е. объединять дубликаты. В команде вместо перечисления колонок использовали символ “*”.
10. Попробуем удалить, например, "вторую" запись.
delete from exdoc where doc_nd = 137;
Сообщение: удалено 2 записи.
Как бы не переписывали, запрос всё время будет удаляться обе записи.
Создание первичного ключа
1. Для предотвращения создания записей-дубликатов добавим к описанию таблицы правило уникальности. Добавим первичный ключ.
alter table exdoc
add constraint pk_exdoc
primary key(doc_nd, doc_dd);
Таблица изменена.
Рассмотрим команду.
Команда требует указать имя таблицы, это exdoc.
Затем, указываем, что будем добавлять в описание таблицы – ключевое слово add. Вспомним, что до этого использовали ALTER TABLE для изменения описания - modify.
Сообщаем, что добавляем правило-ограничение, ключевое слово constraint. После него пишем имя правила pk_exdoc.
Определяем тип правила – первичный ключ primary key. В круглых скобках перечисляем колонки. В этой команде создается составной первичный ключ.
2. Запомним команду.
save alt_exdoc4
3. Добавим запись из тестового набора.
@ins_exdoc
4. Попробуем воспроизвести ситуацию с дублированием.
insert into exdoc select * from exdoc;
Получили ошибку. Указано имя правила-ограничения.
Пересоздание таблицы с позициями счетов
Добавим правила для обязательного заполнения колонок в таблицу с позициями expos. Для добавления правил будем использовать не ALTER TABLE, а команду CREATE TABLE. Но прежде удалим таблицу.
1. Смотрим структуру таблицы
desc expos
Все колонки могут принимать значение NULL.
2. Удалим таблицу.
drop table expos;
3. Загрузим в буфер ввода текст команды CRETE TABLE для таблицы expos.
get cre_expos
4. Перейдем в текстовый редактор и изменим команду следующим образом.
edit
create table expos
( doc_nd number ( 6) not null
, doc_dd date not null
, pos_no number ( 5) not null
, good_n varchar2( 60) not null
, ed varchar2( 6) not null
, quant number ( 9, 2) not null
, price number ( 9, 2) not null
, cost number ( 12, 2)
, tax number ( 12, 2) not null
, sum_tax number ( 12, 2)
, sum_pos number ( 12, 2)
)
Для колонок pos_no, good_n, ed, price, quant, tax после размерности введём ключевое слово “not null”.
5. Выполним "/".
6. Сохраним команду в файле с другим именем.
save cre_expos2
7. Посмотрим на структуру.
desc expos
Видим, что некоторые колонки теперь не могут принимать значение NULL.
Добавим проверку значений
1. Добавим правило проверки значений в колонке pos_no. Номер позиции должен начинаться с единицы. Скажем проще, должен быть больше нуля.
alter table expos
add constraint ck_expos_pos_no
check( pos_no > 0);
Правило проверки значений имеет имя ck_expos_pos_no. Тип правила – check, т.е. проверка значения. Диапазон задан в круглых скобках - это простое логическое выражение.
2. Сохраним команду.
save alt_expos1
3. Добавляем правило для проверки значений в колонке price и quant.
alter table expos
add constraint ck_expos_quant
check( quant > 0);
4. save alt_expos2
5.
alter table expos
add constraint ck_expos_price
check( price > 0);
6. save alt_expos3
7. Добавляем правило для ставки налога.
alter table expos tax
add constraint ck_expos_tax
check( tax =10 OR tax = 18);
Обращаю внимание на составное условие: две простых проверки объединены логическим оператором OR. Должна выполниться либо левая, либо правая часть условия.
8. save alt_expos4
9. Добавим правило ссылочной целостности. Пара значений из колонок doc_nd и doc_dd должна присутствовать в таблице exdoc.
Вводим команду:
alter table expos
add constraint fk_expos_exdoc
foreign key(doc_nd, doc_dd)
references exdoc( doc_nd, doc_dd);
В этой команде добавляем правило ссылочной целостности – foreign key. В круглых скобках перечисляем колонки, значения которых необходимо проверять. После ключевого слова references указываем имя таблицы и список колонок, в которой должны присутствовать проверяемые значения.
10. Запомним команду.
save alt_expos5
11. Добавим первичный ключ в таблицу expos.
alter table expos
add constraint pk_expos
primary key(doc_nd, doc_dd, pos_no);
Уникальность задаётся комбинацией из трех значений.
Обращаю внимание:
комбинация из пары значений, в колонках doc_nd и doc_dd, обязательно должна быть в таблице exdoc;
значение в колонке pos_no должно быть целым числом больше 0.
Вспоминаем правила ссылочной целостности, проверку значений и смотрим на тип колонки pos_no.
12. Сохраним команду.
save alt_expos6
Пора проверить работу ограничений. Почистим таблицы и добавим тестовые записи.
13. delete from exdoc;
14. delete from expos;
15. @ins_exdoc
16. @ins_expos1
Добавление записей прошло успешно, т.е. она удовлетворяет всем правилам.
17. Умышленно вносим некорректные данные. Изменяем значение колонки doc_nd на 12. Счёт с номером 12 в таблице exdoc отсутствует.
edit ins_expos1
Внесите правки самостоятельно.
18. Выполните файл ins_expos1. Получили ошибку? Какое правило нарушено?
19. Ещё раз откройте файл ins_expos1 в режиме редактирования, верните правильный номер счёта 137. Измените процент налоговой ставки на 20.
20. Выполните файл ins_expos1. Понятен текст ошибки? Что нарушено?
21. Восстановите правильные значения в файле ins_expos1.
| < Предыдущая | Следующая > |
|---|
