Хранимые процедуры и функции, определенные пользователем.
Хранимая процедура (StoredProcedure) – это именованный набор команд языка Transact-SQL, хранящийся на сервере в качестве самостоятельного объекта БД
- SP хранится на сервере (пакет – на клиенте)
- Для вызова используется имя (у пакета нет имени)
- SP могут вызвать друг друга (пакеты не могут)
- Изменения вносятся в одном месте, на сервере (в пакеты – на всех клиентах)
- Реализуется модульность
- Уменьшается объем передаваемой информации по сети
СистемныеХП – входят в состав SQLServer, реализуют все действия администрирования сервера, начинаются с sp_, хранятся в БД master. Контекст выполнения любой
ПользовательскиеХП (User-DefinedSP) – размещаются в пользовательских БД и выполняются в контексте только одной БД
ВременныеХП – существуют только в рамках одного соединения с сервером (временно хранятся в tempDB)
Создание
CREATEPROC[EDURE] <имяпроцедуры>
[{@<параметр><тип>} [= <значение по умолчанию>]
[OUTPUT] ] [,…n]
AS <SQL оператор> [,…n]
a) <имя процедуры> - начинается с «sp_» для системных ХП, «#» - для локальных временных ХП, «##» - для глобальных временных ХП.
b) Параметры и локальные переменные начинаются с @. Параметры разделяются запятыми, количество параметров до 1024.
c) OUTPUT помечает выходной параметр (он же является и входным)
d) <значение по умолчанию> присваивается входному параметру, если при вызове процедуры ему не присваивается значение
e) Возвращаемое значение процедуры (как для функции) задается в теле процедуры оператором RETURN
|
|
|
Триггеры.
Триггер – процедура, связанная с таблицей или представлением, которая автоматически выполняется при выполнении операции вставки, изменения или удаления строки этой таблицы или представления
Назначение триггеров:
- Обеспечение целостности данных (например, не стандартное каскадное удаление)
- Сокращение затрат на программирование приложений (общие действия – в триггер)
- Автоматическое ведение журнала изменений базы данных
- Автоматическое предупреждение об изменении данных в БД
Классификация по типу действия:
- INSERTTRIGGER – запускаются при выполнении команды INSERT
- UPDATETRIGGER – запускаются при выполнении команды UPDATE
- DELETETRIGGER – запускаются при выполнении команды DELETE
Классификация по типу поведения:
- AFTER – триггер выполняется после успешного выполнения команды
- INSTEADOF – триггер вызывается вместо выполнения команды. Для представлений можно использовать только триггер INSTEADOF.
Создание триггера:
CREATETRIGGER<имя триггера>
ON<имя таблицы> | <имя представления>
{FOR | AFTER | INSTEAD OF}
{ [DELETE] [,] [INSERT] [,] [UPDATE]}
AS
<SQL оператор> […n]
FOR и AFTER – синонимы.
AFTER триггер выполняется в транзакции
|
|
|
Сравнение AFTER и INSTEADOF триггеров:AFTER триггер выполняется после того, как действие команды было завершено. Поэтому, если необходимо отменить действие команды, то в AFTER триггере надо использовать конструкцию ROLLBACKTRANSACTION. В этой же ситуации в INSTEADOF триггере не надо отменять действие, т.к. оно не выполняется (т.е. не надо использовать ROLLBACKTRANSACTION). Но для фиксации операции сам триггер должен выполнить соответствующую операцию (INSERT, DELETE, UPDATE).
CREATE TRIGGER ExemplarsUpdateTrigger
ON Exemplars
AFTER UPDATE, INSERT
AS
BEGIN
IF (EXISTS (select e.reader_id
FROM Exemplars e WHERE e.reader_id IS NOT NULL
GROUP BY e.reader_id
HAVING count(*) > 2))
BEGIN
ROLLBACK TRANSACTION
PRINT ‘Попыткавзятьболее 2 книг'
END
END
-- Взять все свободные экземпляры
UPDATE Exemplars SET reader_id = 1 WHERE reader_id IS NULL
Таблицы inserted и deleted
- Команда INSERT: inserted содержит все вставляемые строки, deleted – пустая
- Команда DELETE: inserted – пустая, deleted содержит удаленные строки
- Команда UPDATE: inserted содержит новые значения строк, deleted – старые (заменяемые) значения
CREATE TRIGGER ExemplarsDeleteTrigger
ON Exemplars
AFTER DELETE
AS
BEGIN
IF EXISTS(SELECT * FROM deleted WHERE reader_id IS NOT NULL)
BEGIN
ROLLBACKTRANSACTION
PRINT ‘Запрещено удалять не возвращенные книги'
|
|
|
END
END
UPDATE(<имя столбца>) – логическая функция для проверки изменения значения столбца (только для команд INSERT и UPDATE)
COLUMNS_UPDATED () - функция возвращает результат типа VARBINARY, каждому разряду соответствует номер столбца (0 – не изменен, 1 – изменен)
CREATE TRIGGER reminder ON Person.Address AFTER UPDATE
AS
IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) )
BEGIN
RAISERROR (50009, 16, 10)
END;
Предупреждение: объекты, на которые ссылаются триггеры, могут быть удалены или переименованы без ограничения. В результате таких изменений триггер будет работать некорректно.
Представления.
Представление (view) – это псевдотаблица, содержимое которой определяется SELECT-запросом,пользователь воспринимает как некоторое виртуальное отношение.
Представление – средство реализации внешних моделей БД
Содержимое представления формируется в момент обращения
В запросах таблицы и представления трактуются одинаково, поэтому их имена не должны совпадать
Использование представлений:
Для группировки столбцов разных таблиц в виде одного объекта
Для ограничения доступа пользователей к определенным строкам таблиц
Для ограничения доступа пользователей к определенным столбцам таблиц
|
|
|
Для предоставления результатов преобразования данных
Синтаксис
CREATE VIEW <имя представления>[(<столбец> [,…n])]AS <оператор SELECT>
Виды представлений
Горизонтальное представление - этот вид представления широко применяется для уменьшения объема реальных таблиц в обработке и ограничения доступа пользователей к закрытой для них информации.
Пример
(столбцы представления определяются
по умолчанию)
CREATE VIEW SalesDepartment
AS
SELECT * FROM Employee
WHERE department = ‘отделпродаж’
Использование
SELECT name, salary FROM SalesDepartment
Вертикальное представление - этотвидпредставленияпрактическисоответствуетвыполнениюоперациипроектированиянекоторогоотношениянарядстолбцов. Ониспользуетсявосновномдляскрытияинформации,котораянедолжнабытьдоступнавконкретнойвнешнеймодели.
Пример
CREATE VIEW Tabel
AS
SELECT t_num, name, position, department
FROM Employee
Использование
SELECT name, department FROM Tabel
WHEREposition = ‘начальник отдела’
Сгруппированные представления - этипредставлениясодержатзапросы, которыеимеютгруппировку. Сгруппированныепредставлениявсегдадолжнысодержатьсписокстолбцов. Онимогутиспользоватьагрегированныефункциивкачестверезультирующихстолбцов, авдальнейшемэтопредставлениеможетиспользоватьсякаквиртуальнаятаблица, например, вдругихзапросах.
Пример
CREATE VIEW Debtors
(isbn, title, reader_id, last_name, home_phone, work_phone, date_back)
AS
SELECT p.isbn, title, r.reader_id, last_name, home_phone, work_phone, date_out+30
FROM Publications p JOIN Exemplars e ON p.isbn = e.isbn
JOIN Readers r ON e.Reader_id = r.Reader_id
WHERE e.date_out + 30 <GetDate()
Использование
SELECT DISTINCT last_name FROM Debtors WHERE home_phone IS NULL
Модификация данных
Ограничения:
- В запросе не должно быть DISTINCT
- Изменяться могут поля только одной таблицы
- Каждое имя в списке параметров представления должно быть простой ссылкой на столбец таблицы
-В предложении WHERE не должно быть вложенных запросов
- Не должна использоваться группировка
-Ограничения можно обойти, используя триггеры типа INSTEAD OF к представлению
Управление представлениями
Изменение представления:
ALTER VIEW <как в CREATE VIEW>
Дата добавления: 2018-08-06; просмотров: 326; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!