Лабораторная работа №6 Создание хранимых процедур
На практике часто требуется повторять последовательность одинаковых запросов. Хранимые процедуры позволяют объединить последовательность таких запросов и сохранить их на сервере. После этого клиентам достаточно послать один запрос на выполнение хранимой процедуры.
Хранимые процедуры обладают следующими преимуществами.
Повторное использование кода– после создания хранимой процедуры ее можно вызывать из любых приложений и SQL-запросов.
Сокращение сетевого трафика– вместо нескольких запросов экономнее послать серверу запрос на выполнение хранимой процедуры и сразу получить ответ.
Безопасность– действия не приведут к нарушению целостности данных, т.к. для выполнения хранимой процедуры пользователь должен иметь привилегию.
Простота доступа– хранимые процедуры позволяютинкапсулировать сложный код и оформить его в виде простого вызова.
Выполнение бизнес-логики– хранимые процедуры позволяют перенести код сохранения целостности БД из прикладной программы на сервер БД. Бизнес-логика в виде хранимых процедур не зависит от языка разработки приложения.
MS
Расчет суммы услуги без комиссионных оформлен в виде хранимой процедуры с использованием табличной переменной.
USE[Notarius]
GO
/*Сумма услуги без комиссионных*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Sdelka1]
@N_klienta int
AS
DECLARE @mytable TABLE(sdelka1 int, sdelka2 int)
INSERT @mytable
SELECT SUM([SummaSdelki]-[Komissionie])
AS Sdelka, Sdelka.[N sdelki]
FROM [dbo].[Sdelka]
WHERE Sdelka.[N sdelki]=@N_klienta
GROUP BY [N sdelki]
SELECT SUM(sdelka1), SUM(sdelka2)FROM @mytable
GO
Вторая процедура:
USE [Notarius]
GO
--ФИО Клиента
CREATE PROCEDURE FIOkl
@Name nvarchar(max)
AS
SELECT * FROM [dbo].Klient WHERE FIO=@Name
GO
ORACLE
Простая процедура, которая выводит ФИО клиента и его номер телефона. Входным параметром является ФИО клиента:
CREATE procedure FIOkl(nam IN VARCHAR2)
IS
BEGIN
SELECT * FROM klient WHERE fio=nam
ENDFIOkl/
Вызвать процедуру, указав фио клиента:
SET SERVEROUTPUT ON
DECLARE
BEGIN FIOkl('Иванов Пётр Михайлович');
END/
Следующий скрипт представляет собой курсор, после выполнения которого на экране появляется список всех клиентов:
SET SERVEROUTPUT ON
DECLARE
CURSOR get_kl IS
SELECT * FROM klient;
klient get_kl%ROWTYPE;
BEGIN
OPEN get_kl;
LOOP
EXIT WHEN get_kl%NOTFOUND;
DBMS_OUTPUT.enable;
FETCH get_kl INTO klient;
DBMS_OUTPUT.put_line('N_klienta:'||TO_CHAR(klient.n_klienta)||''||klient.fio||''||klient.telefon||''||TO_CHAR(klient.adres));
END LOOP;
CLOSE get_kl;
AND;
Лабораторная работа №7 Создание триггеров
Триггер – эта та же хранимая процедура, но привязанная к событию изменения содержимого конкретной таблицы.
Возможны три события, связанных с изменением содержимого таблицы, к которым можно привязать триггер:
insert– вставка новых данных в таблицу;
delete– удаление данных из таблицы;
Update– обновление данных в таблице.
MS
Создадим триггер, который выполняется проверку при вставке новых данных в таблицу Клиент: если № Клиента задаётся равным 0, то вставка не выполняется, выводится сообщение: «Нельзя ввести такое число» и происходит откат назад:
CREATE TRIGGER proverka ON Klient FOR INSERT
AS
IF EXISTS (SELECT * FROM Klient WHERE [N klienta]='0')
BEGIN
PRINT'Нельзя ввести такое число'
ROLLBACK TRANSACTION
END
GO
USE [Notarius]
GO
Второй триггер не даёт вставить в таблицу Сделка Дату, которая ещё не наступила:
CREATE TRIGGER DATE ON Sdelka FOR INSERT
AS
IF (SELECT Data FROM inserted) > GETDATE()
BEGIN PRINT 'Вы ввели неверную дату!'
ROLLBACK TRANSACTION;
END
GO
ORACLE
Данный триггер не позволит добавить в таблицу Услуга услугу дешевле 500:
CREATE TRIGGER stUsl
BEFORE INSERT ON Usluga
FOR each row
BEGIN
if(new.stoimost_uslugi<500)
then
raise_application_error(-20000, 'Слишком маленькая стоимость!');
end if;
END; /
Данный триггер не позволяет заносить сделки, которые произошло позже 30 декабря 2012 года:
CREATE TRIGGER tr2
BEFORE INSERT ON Sdelka
FOR each row
BEGIN
if(new.data_sdelki>30-DEC-12)
then
raise_application_error(-20000, 'Неверная дата');
end if;
END;