DeepEdit!

Пересоздадим таблицу с услугами exsvc. В команду CREATE TABLE добавим ограничения: обязательные колонки; проверки на допустимые значения; первичный ключ; правило ссылочной целостности.

1. drop table exsvc; 

2. Откроем в редакторе файл cre_exsvc, исправим текст команды и запишем под новым именем cre_exsvc2.sql

edit cre_exsvc

create table exsvc

( doc_nd    number  (  6)    not null

, doc_dd    date             not null

, service_n varchar2( 60)    check( service_n in ( 'услуги доставки'

                                                 , 'услуги упаковки'))

, cost      number  ( 12, 2)

, tax       number  (  5, 2) check( tax in (10,18))

, sum_tax   number  ( 12, 2)

, sum_svc   number  ( 12, 2)

, constraint pk_exsvc

     primary key(doc_nd, doc_dd, service_n)

, constraint fk_exsvc_exdoc

     foreign key(doc_nd, doc_dd)

     references exdoc (doc_nd, doc_dd)

)

Для колонки service_n добавили проверку по списку значений. Использовали оператор IN с перечислением строковых констант.

Правила для первичного ключа и ссылочной целостности записали после списка колонок.

3. Создадим таблицу.

@cre_exsvc2

4. Добавим запись.

@ins_exsvc1

5. Введём команду.

 select *

   from exsvc

  where doc_nd    = 137

    and doc_dd    = ‘12-sep-2003’

    and service_n = ‘услуги упаковки’;

При "длинном" первичном ключе команда SQL для поиска нужной записи становиться тоже "длинной". Удобно?

6. Создадим суррогатный первичный ключ. Удалим существующий ключ.

alter table exsvc drop constraint pk_exsvc;

Таблица изменена.

Команда ALTER TABLE позволяет удалять правила-ограничения. При удалении нужно указывать имя правила, то, которое задали при создании.

7. Запомним команду.

save alt_exsvc1

8. Добавим колонку "код записи".

alter table exsvc add exsvc_c number(9) not null primary key;

Таблица изменена.

Используем команду ALTER TABLE для добавления колонок в существующую таблицу. Для колонки можно указать правила целостности. 

Особенности. 

Добавить колонку обязательную для заполнения можно только в пустую таблицу. 

Для составного первичного ключа нужно писать свою команду ALTER TABLE. 

В этой команде не указано имя для первичного ключа. Если в команде ALTER  TABLE опущена конструкция constraint, то имя будет сгенерировано базой данных. 

9. Добавленная колонка будет в описании таблицы последней. 

desc exsvc

10. Запомним команду.

save alt_exsvc2

11. Естественный первичный ключ желательно оставить, создадим альтернативный ключ.

alter table exsvc

  add constraint ak_exsvc

  unique (doc_nd, doc_dd, service_n);

Добавили правило – проверка уникальности значений unique. Уникальная комбинация задаётся тремя колонками.

12. save alt_exsvc3

1. Для генерации значений первичного ключа используется специальный механизм - последовательность. Вводим команду.

create sequence seq_exsvc start with 1;

Последовательность, SEQUNCE – счётчик значений. Используется для генерации набора значений. 

Создается командой CREATE SEQUENCE. В команде обязательно указать имя, например, seq_exsvc. 

С помощью различных опций можно настроить порядок формирования значений. В нашей команде задано - генерировать последовательность целых чисел, начиная с единицы, шаг по умолчанию равен 1. Последовательность вернёт набор чисел: 1, 2, 3, 4, 5, … 

2. Сохраним команду в файл.

save cre_seq

3. Введём команду.

select seq_exsvc.NEXTVAL from dual;

Команда вернула одну строку и какое-то значение.

Будем разбираться.

Это обычный SELECT. Чтение данных выполняется из "доселе неизвестной" таблицы dual. 

dual – это таблица, в которой одна колонка и одна запись. В ней хранится одно и тоже значение. Его изменить нельзя. В базе данных только одна таблица с именем dual, но она доступна для всех, всегда под рукой. Если пишем SELECT к этой таблице, то он всегда возвращает одну и только одну запись.

Последовательность тоже можно представить как таблицу с одной строкой и одной колонкой. В базе данных может быть много последовательностей. У всех последовательностей колонка имеет одно и тоже имя - NEXTVAL. Эту колонку называют псевдоколонкой. Значение не хранится на диске, оно вычисляется при каждом обращении. В случае с последовательностью – псевдоколонка NEXTVAL будет вычислять и возвращать следующее значений из набора чисел.

Вспомните view, конструкцию FROM-SELECT. Для ссылки на значение использовали запись “v.weight”, имя таблицы и через точку имя колонки. Для чтения значения из последовательности используем такую же запись seq_exsvc.NEXTVAL

Получается, что команда SELECT читает записи из таблицы dual. Возвращает одну запись, но не использует значение из этой записи, а вычисляет выражение - извлечь следующие значение из последовательности.

Последовательность каждый раз будет возвращать новое значение.

4. Выполните запрос ещё раз. Введите "/".

5. И ещё раз "/".

Значение каждый раз новое.

6. Смотрите, что в таблице dual.

select * from dual;

7. Посмотрим последнее выбранное значение в последовательности.

select seq_exsvc.CURRVAL from dual;

Действительно, это то значение.

Псевдоколонка CURRVAL возвращает последнее значение из последовательности.

8. Повторите команду. Значение не изменилось. Это всё тоже последнее значение. 

9. Исправим команды для добавления записей с услугами. Добавим колонку и обращение к последовательности.

edit ins_exsvc1

insert into exsvc

( exsvc_c

, doc_nd

, doc_dd

, service_n

, tax

)

values

( seq_exsvc.NEXTVAL

, 137

, '12-sep-2003'

, 'услуги доставки'

, 18)

/

Обратите внимание. Список колонок в команде может отличаться от того, который выводится командой desc. 

10. Отредактируем файл с услугами упаковки.

edit ins_exsvc2

insert into exsvc

( exsvc_c

, doc_nd

, doc_dd

, service_n

, tax

)

values

( seq_exsvc.NEXTVAL

, 137

, '12-sep-2003'

, 'услуги упаковки'

, 18)

/

Воспользуемся скриптами тестового набора: почистим таблицы; добавим тестовые данные; посмотрим результат. 

11. @del_ex 

12. @ins_ex 

13. @show_ex 

Тестовый набор прошел проверку? Удовлетворяет он ограничениям?

Подготовить скрипт ins_ex. Объединить все команды для ввода тестового набора в один большой файл. Проверить работу скрипта.

Язык описания данных DDL:

значение NULL, колонка NOT NULL

ALTER TABLE modify

ALTER TABLE add column

ALTER TABLE add constraint check

ALTER TABLE add constraint primary key

ALTER TABLE add constraint foreign key

ALTER TABLE add constraint unique

ALTER TABLE drop constraint

CREATE TABLE с NOT NULL и constraint

CREATE SEQUENCE

Язык манипулирования данными DML:

INSERT по SELECT

SELECT DISTINCT

псевдоколонки NEXTVAL, CURRVAL

полезная таблица dual