Последовательности

Во многих прикладных задачах от первичных ключей требуется лишь уникальность, а конкретное значение ключа не имеет значения. Таким ключом является, например, номер читательского билета, автобусного маршрута и т.д. Многие СУБД предлагают специальные механизмы для автоматического присваивания значений таким полям. Так, в СУБД MS Access имеется специальный тип «счетчик».

В Oracle для этих целей используется специальный объект – последовательность (SEQUENCE), которая дает возможность задать ключ из своего интервала и со своими характеристиками. Последовательность создается оператором

CREATE SEQUENCE [имясхемы.]имяпоследовательности

[INCREMENT BY шаг] [START WITH начальноезначение] [CYCLE]

[MINVALUE значение]

[MAXVALUE значение];

В самом простом случае последовательность генерирует целые числа от 1 до 10²⁷ с шагом 1.

Для работы с последовательностью можно вызывать два метода:

CURRVAL – возвращает текущее значение последовательности;

NEXTVAL – изменяет значение последовательности в зависимости от правил ее формирования и возвращает новое значение.

Н-р: Пусть код нового работника должен заполняться автоматически, начиная с 1001.

CREATE SEQUENCE sq_emp START WITH 1001 MAXVALUE 9999;

CREATE TABLE EMP (

EMPNO NUMBER(4) PRIMARY KEY DEFAULT sq_emp.nextval,

…);

Теперь при выполнении оператора INSERT нам нет нужды явно задавать значение поля EMPNO.

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

Модификация и удаление таблиц

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

Добавление столбцов. Пусть нам надо добавить в таблицу EMP столбцы «пол» и «семейное положение» с ограничениями, описанными выше. Это достигается командой

ALTER TABLE emp

ADD (sex CHAR(1) -- пол

CHECK(sex IN('m', 'f')),

marriage CHAR(1) -- семейное положение

DEFAULT 's' CHECK(marriage IN('s', 'm', 'd', 'w')));

Обратите внимание, что попытка записать фразу NOT NULL для столбца sex приведет к ошибке! Действительно, таблица должна удовлетворять вновь накладываемым ограничениям. В частности, для задания фразы NOT NULL мы должны вначале очистить таблицу.

Изменение характеристик столбца. Расширим столбец EMPNO до 50 символов:

ALTER TABLE emp

MODIFY (empno VARCHAR(50)));

Перед уменьшением размера столбца (и перед любой модификацией, при которой могут быть потеряны данные) содержимое столбца должно быть очищено. Кроме того, мы не можем изменять ограничения CHECK, связанные со столбцом.

Удаление столбцов. Удалим столбец sex из таблицы EMP:

ALTER TABLE emp

DROP (sex);

Если нам необходимо удалить столбец, являющийся внешним ключом для другой таблицы (или, по крайней мере, входящий в состав внешнего ключа), мы должны указать фразу CASCADE CONSTRAINTS для того, чтобы уничтожить «зависшие» ограничения внешнего ключа.

Добавление и удаление ограничений. Добавим ограничение на правильность даты рождения.

ALTER TABLE emp

ADD CONSTRAINT cs_birthdate

CHECK(birthdate > to_date('01.01.1900', 'dd.mm.yyyy')) NOVALIDATE;

Фраза NOVALIDATE отменяет проверку имеющихся строк таблицы на соответствие добавляемому ограничению.

Мы можем уничтожать либо именованные ограничения, либо те из них, которые можно однозначно определить:

ALTER TABLE emp

DROP PRIMARY KEY CASCADE;

Здесь фраза CASCADE, как и ранее, уничтожает ограничения внешнего ключа в других таблицах.

Уничтожение таблиц. Формат оператора SQL для этой операции следующий:

DROP TABLE [имясхемы.]имятаблицы

[CASCADE CONSTRAINTS];

Как и ранее, фраза CASCADE CONSTRAINTS уничтожает ограничения внешнего ключа в других таблицах.

12.Управление транзакциями. Представления.

Для обеспечения целостности данных логически связанные последовательности запросов, неделимые с точки зрения воздействия на базу данных, называются транзакциями. Таким образом, транзакция либо должна быть полностью завершена, либо база данных должна быть возвращена в состояние, предшествующее началу транзакции. Следует сразу сказать, что транзакция, содержащая только один оператор SQL, может быть восстановлена автоматически, так что нет необходимости применять специальные механизмы для отката такой транзакции.

В языке SQL имеются две команды для управления транзакциями.

COMMIT-завершает транзакцию, сохраняя в базе данных всю информацию и очищая сегмент отката (и все контрольные точки, речь о которых пойдет далее). Команда

ROLLBACK-выполняет откат транзакции, используя информацию из сегмента отката. Однако некоторые операторы (например, DROP TABLE) являются необратимыми и не могут быть восстановлены!

Пользователь может выполнить частичный откат транзакции. Для этой цели он может создать последовательность контрольных точек. Каждая контрольная точка имеет свое имя и задается оператором: SAVEPOINT имяконтрольнойточки

Для того, чтобы выполнить частичный откат, необходимо записать команду

ROLLBACK TO [SAVEPOINT] имяконтрольнойточки

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