Материал: !Лабораторный практикум ТБД (задание)

Вспомним, что Метод NextVal выдает следующее значение в последовательности, а метод CurrVal выдает текущее значение в последовательности.

Вставим строку в таблицу CUSTOMER

INSERT INTO CUSTOMER CustomerID, Name, Area_Code, Phorie_Nuniber) VALUES (CustID.NextVal, 'Mary Jones', '350', '555-1234'):

Этот оператор создаст в таблице CUSTOMER строку, где столбцу CustomerID будет присвоено следующее значение в последовательности CustID. Выполнив этот оператор можно считать только что созданную строку с помощью метода CurrVaL:

SELECТ * FROM CUSTOMER WHERE CustomerID = CustID.CurrVal;

Здесь метод CustID.CurrVal возвращает текущее значение в последовательности, то есть только что использованное значение.

!!! Использование последовательностей не гарантирует корректности значений суррогатных ключей (могут быть пропущенные или повторяющиеся значения).

Создайте с помощью SQL Plus следующие последовательности:.

Create Sequence CustID Increment by 1 start with 1000; Create Sequence ArtistID Increment by 1 start with 1; Create Sequence WorkID Increment by 1 start with 500; Create Sequence TransID Increment by 1 start with 100;

Ввод данных

Запустите файл ACIns.sql, предварительно набрав и ПРОВЕРИВ текст:

INSERT INTO ARTIST (ArtlstID, Name, Nationality) Values (ArtistID.NextVal, 'Tobey', 'US');

INSERT INTO ARTIST (ArtistID, Name, Nationality) Values (ArtistID.NextVal, Miro, 'Spanish');

INSERT INTO ARTIST (ArtistID, Name, Nationality) Values (ArtistID.NextVal, ‘Frings', 'US');

INSERT INTO ARTIST (ArtistID, Name, Nationality) Values (ArtistID.NextVal, 'Foster', 'English'):

INSERT INTO ARTIST (ArtistID, Name, Nationality) Values (ArtistID.NextVal, 'van Vronkin', 'US'):

INSERT INTO CUSTOMER (CustomerID, Name, Area__Code, Phone Number) Values

26

(CustID.NextVal, 'Jeffrey Janes', ‘206’, 555-1234');

INSERT INTO CUSTOMER (CustomerID, Name, Area__Code, Phone Number) Values (CustID.NextVal, 'David Smith', '206', ‘555-443');

INSERT INTO CUSTOMER (CustomerID, Name, Area__Code, Phone Number) Values (CustID.NextVal, 'Tiffany Twilight', '360', ‘555-1040');

Отобразите на экране столбцы ArtistID, Name, Nationality из таблицы ARTIST; CustomerID, Name, AreaCode, PhoneNumber из таблицы CUSTOMER.

Создание связей

В Oracle связи создаются путем введения ограничений целостности по внешнему ключу. Например, следующие sql-операторы определяют связь между

таблицами CUSTOMER и CUSTOMER_ARTIST_INT и между таблицами ARTIST и CUSTOMER_ARTIST_INT:

ALTER TABLE CUSTOMER_ARTIST_INT ADD CONSTRAINT ArtlstIntFK FOREIGN KEY(ArtistID) REFERENCES ARTIST ON DELETE CASCADE; ALTER TABLE CUSTOMER_ARTIST_INT ADD CONSTRAINT CustonerIntFK FOREIGN KEY(CustomerID) REFERENCES CUSTOMER ON DELETE CASCADE;

Ограничениям даны имена ArtistIntFK и CustomerIntFK. Эти имена не играют особой роли для Oracle и могут выбираться разработчиком. Обратите внимание, что для родительской таблицы указан только столбец, являющийся внешним ключом. Oracle предполагает, что внешний ключ будет связан с первичным ключом родительской таблицы, поэтому указывать столбец первичного ключа нет необходимости. Фраза ON DELETE CASCADE указывает на то, что при удалении строк из родительской таблицы соответствующие строки дочерних таблиц должны быть также удалены. Слово cascade (каскад) используется здесь потому, что удаление идет каскадом от родительской таблицы к дочерней.

Введите эти операторы в редактор SQL Plus и заполните несколько строк таблицы пересечения. Теперь, если вы удалите данные о покупателе или

27

художнике, соответствующие строки в таблице пересечения будут также удалены.

Создайте таблицы WORK и TRANSACTION, Обратите внимание, что в определениях ограничений по внешнему ключу отсутствует фраза ON DELETE CASCADE. Так, ограничение ArtistFK сделает невозможным удаление тех строк в таблице ARTIST, которые имеют дочерние строки и таблице WORK. Ограничения WorkFK и CustomerFK функционируют сходным образом.

ALTER TABLE WORK ADD CONSTRAINT ArtistFK

FOREIGN KEY (ArtistID) REFERENCES ARTIST:

ALTER TABLE TRANSACTION ADD CONSTRAINT WorkFK FOREIGN KEY (WorkID) REFERENCES WORK;

ALTER TABLE TRANSACTION ADD CONSTRAINT CustomerFK FOREIGN KEY (CustomerID) REFERENCES CUSTOMER;

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

ALTER TABLE MYTABLE DROP CONSTRAINT MyConstraint

удалит ограничение MyConstraint из таблицы MyTable.

28

Создание индексов

Создайте индекс по столбцу Name таблицы CUSTOMER: CREATE INDEX CustNameIdx ON CUSTOMER(Name);

Индексу дано имя CustNameIdx. Имя не играет роли для Oracle. Чтобы создать уникальный индекс, перед ключевым словом INDEX используют ключевое слово UNIQUE. Например, чтобы гарантировать, что ни одно произведение не:6удет записано дважды в таблицу WORK, можно создать уникальный индекс по столбцам (Title, Copy, ArtistID):

СREATE UNIQUE INDEX WorkUniqueIndex ON WORK (Title, Copy, ArtistID);

Изменение структуры таблиц, контрольные ограничения

После создания таблицы ее структуру можно изменять с помощью оператора ALTER TABLE. Будьте, однако, осторожны с этим оператором, поскольку при его использовании возможна потеря данных,

Добавление или удаление столбца:

ALTER TABLE MYTABLE ADD C1 NUMBER(4);

ALTER TABLE MYTABLE DROP COLUMN C1;

Ограничения на модификацию столбцов таблиц

Чтобы добавить непустой (NOT NULL) столбец, сначала создают его в таблице как пустой, заполняют все его строки данными, а затем объявляют непустым (NOT NULL) с помощью предложения MODIFY.

Модифицируем таблицу ARTIST. Мы установили для столбцов BirthDate (дата рождения) и DeceasedDate (дата смерти) тип данных Date. Допустим, что пользователям базы данных не нужно, чтобы и этих столбцах хранилась полная дата, а нужен только год рождения или смерти художника. Предположим также, что из представленных в галерее художников нет ни одного, кто бы родился или умер ранее 1400 года или позже 2100 года.

Пока эти столбцы имеют пустые значения, что позволяет нам менять тип данных, не удаляя сами столбцы.:

ALTER TABLE ARTIST MODIFY BirthDate Number(4); ALTER TABLE ARTIST MODIFY DeceasedDate Number(4);

Следующие два оператора устанавливают пределы значений столбцов BirthDate

и DeceasedDate:

29

ALTER TABLE ARTIST ADD CONSTRAINT BDLimit CHECK (BirthDate BETWEEN 1400 AND 2100):

ALTER TABLE ARTIST ADD CONSTRAINT DDLimit CHECK (DeceasedDate BETWEEN 1400 AND 2100).

Выполним команды обновления:

UPDATE ARTIST SET BirthDate = 1870 WHERE Name = 'Miro': UPDATE ARTIST SET BirthDate = 1270 WHERE Name = 'Tobey':

Первое обновление пройдет успешно, а второе нарушит ограничение и поэтому не будет выполнено. Попробуйте запустить эти операторы и посмотрите, каковы будут результаты.

Представления

Важное ограничение SQL-представлений состоит в том, что они могут содержать не более одного многозначного пути.

Определим представление, соединяющее три таблицы, с наложенным условием на столбцы AcquisitionPrice и CustomerID. Этопредставления соединения–

CustomerID IS NULL;

Вообще говоря, представления, основанные на одной таблице, допускают обновление данных. Если это почему-либо нежелательно, вы можете создать представление только для чтения, добавив выражение WITH READ ONLY в конец определения представления. Так, выражение

CREATE VIEW V1 AS SELECT * FROM ARTIST WITH READ ONLY;

создаст представление, доступное только для чтения.

Иногда для обновления данных и представлении можно использовать SQLоператор UPDATE, но это возможно только в особых обстоятельствах и только

втом случае, если изменение затрагивает только одну таблицу.

Вобщем же случае оператор UPDATE не может использоваться для обновления данных о представлении. Для этих целей потребуется написать

30