лаба11БД
.docМинистерство образования РФ
РГРТУ
Кафедра ЭВМ
Отчет
о лабораторной работе №11
«Разработка хранимых процедур»
Выполнили:
Ст.гр. 048 (бр. 6)
Перунова Е.
Шумилова А.
Проверил:
Громов А.Ю.
Рязань, 2012г.
1. Изучение оператора CREATE PROCEDURE для создания хранимых процедур.
1.1. Разработать хранимую процедуру, которая по заданному шифру книги возвращает автора и название книги.
use gr048_br6_library
go
CREATE PROC P1 (@shifr int)
AS
SELECT Автор, Название
FROM Книги
WHERE Шифр=@shifr
GO
EXEC P1 5
1.2. Разработать хранимую процедуру, которая осуществляет вставку записей в таблицу Книги. Хранимая процедура должна обеспечить защиту данных от дублирования согласно следующему правилу: могут существовать несколько книг с одинаковыми названиями, но у них должны быть разные авторы.
CREATE PROC P2 (@shifr int, @avtor char(15), @nazvanie char(20), @kol int)
AS
IF NOT EXISTS (SELECT *
FROM Книги
WHERE Название=@nazvanie AND Автор=@avtor)
INSERT INTO Книги
VALUES (@shifr, @avtor, @nazvanie, @kol)
ELSE PRINT 'ошибка'
GO
EXEC P2 11,'Есенин','Стихи',5
SELECT *
FROM Книги
1.3. Разработать хранимую процедуру, которая по заданному номеру абонента, шифру книги выводит информацию о выдаче книги на руки с указанием даты выдачи, срока возврата и штрафа за утерю в валюте, заданной пользователем.
CREATE PROC P3 (@Abonent int, @Shifr int,@val char(3))
AS
DECLARE @val_koef int
IF (@val='eur') set @val_koef=40
ELSE IF (@val='$') set @val_koef=30
ELSE IF (@val='руб')set @val_koef=1
IF EXISTS (SELECT *
FROM ВыдачаКниг
WHERE @Abonent=Абонент) AND EXISTS (SELECT *
FROM Книги
WHERE @Shifr=Шифр)
BEGIN
DECLARE @Abonemt char(50)
DECLARE @Kniga char(50)
DECLARE @Avtor char(50)
DECLARE @data_vyd char(50)
DECLARE @data_vozvr char(50)
DECLARE @shtraf_in_val char(10)
SELECT @Abonemt = Фамилия
FROM Абоненты
WHERE @Abonent=ЧитБилет
SELECT @Kniga=Название
FROM Книги
WHERE @Shifr=Шифр
SELECT @Avtor =Автор
FROM Книги
WHERE @Shifr=Шифр
SELECT @data_vyd =Convert(char(40),ВыдачаКниг.ДатаВыдачи)
FROM ВыдачаКниг
WHERE @Abonent=Абонент
SELECT @data_vozvr = Convert(char(40),ВыдачаКниг.СрокВозврата)
FROM ВыдачаКниг
WHERE @Abonent=Абонент
SELECT @shtraf_in_val =Штраф/@val_koef
FROM ВыдачаКниг
WHERE @Abonent=Абонент
PRINT 'Абонент:'+ @Abonemt
PRINT 'Книга: ' + @Kniga
PRINT 'Автор:' + @Avtor
PRINT 'Дата выдачи: ' + @data_vyd
PRINT 'Дата возврата: ' + @data_vozvr
PRINT 'Штраф за утерю книги в валюте'+ @shtraf_in_val
END
ELSE PRINT 'Ошибка! Требуемая информация отсутствует в базе данных'
GO
EXEC P3 @Abonent =1, @Shifr =1, @val='eur'
1.4. Разработать хранимую процедуру, которая уменьшает размер штрафа для заданной пользователем книги согласно значениям, указанным в следующей таблице:
|
Количество экземпляров |
Уменьшение штрафа |
|
1 |
10% |
|
1..10 |
20% |
|
>10 |
50% |
CREATE PROC P4 (@Shifr int)
AS
DECLARE @Kol int, @discount real
SELECT @Kol=КолЭкз
FROM Книги
WHERE Шифр=@Shifr
SET @discount = CASE
WHEN @Kol=1 THEN 0.9
WHEN @Kol Between 1 and 10 THEN 0.8
WHEN @Kol>10 THEN 0.5
END
UPDATE ВыдачаКниг
SET Штраф=Штраф*@discount
WHERE Книга=@Shifr
GO
EXEC P4 1
SELECT *
FROM ВыдачаКниг