Главная страница | Контакты | Случайная страница Автомобили Астрономия Биология География Дом и сад Другие языки Другое Информатика История Культура Литература Логика Математика Медицина Металлургия Механика Образование Охрана труда Педагогика Политика Право Психология Религия Риторика Социология Спорт Строительство Технология Туризм Физика Философия Финансы Химия Черчение Экология Экономика Электроника

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ.

ВСТАВКА и УДАЛЕНИЕ ЗАПИСЕЙ

Цель лабораторной работы: изучить операторы SQL для модификации данных (вставки, удаления и обновления строк), научиться создавать хранимые процедуры и триггеры.

Планируемое время выполнения: 1 пара (2 академических часа).

Порядок выполнения работы

1. Изучить теоретическую часть.
2. В каждую таблицу из лабораторной работы №1 «вручную» добавить по 2-3 и более строки произвольных данных.
3. Анализируя задание на разработку БД из ЛР№1, выписать все пользовательские операции с базой данных, связанные с добавлением, удалением или изменением данных. Для каждой операции разработать алгоритм модификации таблиц.
4. Если при проектировании БД в ЛР№1 были введены отложенные ограничения целостности, реализовать алгоритм проверки этих ограничений, используя средства выбранной СУБД (написать триггеры или дополнить алгоритмы из п.3 проверками целостности).
5. Создать хранимые процедуры, реализующие пользовательские действия из п.3. Каждое действие оформляется в виде хранимой процедуры (если выбранная СУБД вообще поддерживает хранимые процедуры. В противном случае запишите последовательность SQL-операторов).
6. Протестировать работу хранимых процедур, добавив новые данные в базу.
7. Принять решение об уровне изолированности транзакций.
8. Оформить отчет.

Содержание отчета

1. Задание на разработку базы данных.
2. Таблица со списком пользовательских действий (образец на стр. ХХ)
3. Текст SQL-запросов: на создание хранимых процедур, триггеров, изменения уровня изолированности транзакций, вызов хранимых процедур. Если выбранная СУБД хранимые процедуры не поддерживает, то список SQL-операторов для реализации каждого пользовательского действия.

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

Изменять содержимое реляционных таблиц позволяют SQL-операторы:

• INSERT – добавление новых строк;
• DELETE – удаление строк из таблицы;
• UPDATE – изменение ячеек существующих строк.

Добавление строк

Оператор INSERT существует в двух модификациях: 1) однострочная инструкция – добавляет одну строку в таблицу; 2) многострочная инструкция – добавляет сразу несколько строк.

Синтаксис однострочной инструкции INSERT:

INSERT INTO Имя_таблицы (Имя_столбца_1, Имя_столбца_2, и т.д.)

VALUES(Значение_столбца_1, Значение_столбца_2, и т.д.)

В качестве значений столбцов могут указываться: константы, слова NULL или DEFAULT (в этом случае берутся значения по умолчаниюиз ограничений целостности столбца). Если какой-то из столбцов не указать, в эту ячейку запишется значение по умолчанию или NULL.

Пример вставки в таблицу Сотрудники(КодСотрудника, ФИО, ГодРождения):

INSERT INTO “Сотрудники”(“ КодСотрудника ”, “ФИО”, “ГодРождения”)

VALUES (517, “Каменев Виктор Павлович”, 1981);

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

INSERT INTO “Сотрудники”

VALUES (517, “Каменев Виктор Павлович”, 1981);

Как показывает практика, структура таблиц нередко меняется: в них появляются новые столбцы и удаляются не нужные, может измениться порядок следования столбцов. Если в запросе INSERT столбцы не указывать, то при реструктуризации таблицы этот запрос приведет к ошибке.

Многострочная инструкция INSERT добавляет в таблицу несколько строк (одну и более). Значения для новых строк явно не задаются, а являются результатом выборки SELECT.

INSERT INTO Имя_таблицы (Имя_столбца_1, Имя_столбца_2, и т.д.)

SELECT далее_синтаксис_запроса

Например, для копирования из таблицы Заказы(Номер, Дата, КодСотрудника) всех заказов, сделанных в 2006 году, в таблицу Заказы2006(Номер, Дата, КодСотрудника), выполняется запрос:

INSERT INTO “Заказы2006”(“Номер”, “Дата”, “КодСотрудника”)

SELECT “Номер”, “Дата”, “КодСотрудника”

FROM “Заказы”

WHERE “Дата” BETWEEN ’01-01-2006’ AND ’31-12-2006’;

Стандартом SQL1 запрещается использовать в многострочной инструкции INSERT оператор сортировки ORDER BY, объединения строк UNION, и добавление строк в таблицу из самой себя.

Удаление строк

Инструкция DELETE удаляет из таблицы одну и более строк, удовлетворяющих условию.

DELETE FROM Имя_таблицы

WHERE условие_отбора_строк

Условие может отсутствовать – тогда удаляются все строки таблицы. Например,

DELETE FROM “Сотрудники”; // вычищает всю таблицу

Пример другой: удалить из таблицы Заказы строки, обслуживаемые сотрудниками 13 и 14.

DELETE FROM “Заказы”

WHERE “КодСотрудника”=13 OR “КодСотрудника”=14;

В условии отбора строк можно использовать запросы из других таблиц.

Например, имеются две связанные таблицы Заказы(Номер, Дата, КодСотрудника(FK)) и Сотрудники(КодСотрудника (PK), ФИО). Требуется удалить из таблицы Заказы строки, относящиеся к сотруднику “Петров Иван Иванович”.

DELETE FROM “Заказы”

WHERE “ КодСотрудника ” IN (SELECT “ КодСотрудника ”

FROM “ Сотрудники ”

WHERE “ Имя ”= ’Петров Иван Иванович’);

Изменение строк

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

UPDATE Имя_таблицы

SET Имя_столбца_1=выражение, Имя_столбца_2=выражение, и т.д.

WHERE условие_отбора_строк

Условие отбора можно не указывать – тогда изменению подвергнутся все строки таблицы.

Пример: в таблице Сотрудники(КодСотрудника, Имя) изменить имя “Бринёва Лариса Николаевна” на “Медведева Лариса Николаевна”:

UPDATE “Поставщики”

SET “Имя”=’Медведева Лариса Николаевна’

WHERE “Имя”=’Бринёва Лариса Николаевна’;

В выражении можно использовать операции + – * /, значения других столбцов текущей строки, имена переменных, вызывать функции. Пример: увеличить значение скидки на 0,05 у всех клиентов, имеющих сумму заказов свыше 3000.

UPDATE “Клиенты”

SET “Скидка”=”Скидка”+0.05

WHERE “СуммаЗаказов”>3000;

Стандарт SQL1 разрешает использовать в условии WHERE выборку из других таблиц. Например, у сотрудников, имеющих более 10 заказов, установить в таблице Сотрудники(КодСотрудника, ФИО, Статус) поле Статус =2.

UPDATE “Сотрудники”

SET “Статус”=2

WHERE EXISTS(SELECT COUNT(*)

FROM “Заказы”

WHERE “Заказы”.”КодСотрудника”=”Сотрудники”.”КодСотрудника”

HAVING COUNT(*)>10);

Запрос будет работать так: сервер по очереди перебирает все строки таблицы Сотрудники. Для каждой строки вычисляет подзапрос SELECT. Если подзапрос SELECT содержит хотя бы одну строку результата, EXISTS возвращает TRUE и Статус меняется; если подзапрос пустой, то EXISTS возвращает FALSE, и данная строка остается прежней. Оператор COUNT(*) подсчитывает количество строк (=количество заказов), у которых поле КодСотрудника равно коду сотрудника текущей строки. Условие HAVING – то же, что WHERE.