Файл: Процедуры на добавление use db home library 293 02.docx
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 12.01.2024
Просмотров: 9
Скачиваний: 1
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
--процедуры на добавление
use DB_home_library_293_02
go
Create procedure addbook
(
@ISBN char(17),
@Author nvarchar(30),
@NB nvarchar(50),
@Genre varchar(20),
@Book_read bit,
@Cost money,
@Purchase_year numeric(4, 0),
@Edition varchar(10),
@Publication_year numeric(4, 0)
)
AS
BEGIN
iNSERT INTO Book (ISBN, Author, NB, Genre, Book_read, Pub_code)
values (@ISBN, @Author, @NB, @Genre, @Book_read, SUBSTRING (@ISBN, charindex('-', @ISBN) -3, 6 ) + SUBSTRING (@ISBN, charindex('-', @ISBN) +6, 2))
Insert into debtor (ISBN)
values (@ISBN)
Insert into Publication(Edition, ISBN, Publication_year)
values (@Edition, @ISBN, @Publication_year)
iNSERT INTO Purchase (ISBN, Cost, Purchase_year)
VALUES (@ISBN, @Cost, @Purchase_year)
END
go
exec addpubs '978-9-85-163779-5', 'Аст Харвест', 'customer_service@astharvest.com', 'http://www.harvest.minsk.by'
exec addpubs '978-5-17-175374-7', 'АСТ', 'astpub@aha.ru', 'http://www.ast.ru'
exec addpubs '978-5-20-358658-8', 'Дрофа', 'help@rosuchebnik.ru', 'https://rosuchebnik.ru'
exec addpubs '978-5-85-865644-8', 'Инком НВ', null, null
exec addpubs '978-5-86-918003-1', 'Мада', null, null
exec addpubs '978-5-28-200575-1', 'Экономика', 'info@economizdat.ru', 'https://economizdat.ru'
exec addpubs '978-5-69-900316-9', 'Эксмо', 'info@eksmo.ru', 'https://eksmo.ru'
exec addpubs '978-5-88-682003-5', 'Юнвес', null, null
exec addbook '978-9-85-163889-5', 'Омелько С.Х.', 'Как выжить,если ты Игорь', 'Медицина', 1, 200, 2001, '3-е издани', 1993
go
exec addbook '978-5-17-174024-4', 'Маккаммон Р.Х.', 'Жизнь мальчишки', 'Ужасы', 0, 100, 2006, '3-е издание', 2005
exec addbook '978-5-20-358658-8', 'Даль В.И.', 'Толковый словарь живого великорусского языка', 'Словарь', 0, 1238, 2000, '11-е издание', 1997
exec addbook '978-5-28-200575-1', 'Морозов А.Т.', 'Кулинария для всех', 'Кулинария', 1, 5, 1987, '1-е издание', 1986
exec addbook '978-5-69-900316-9', 'Карамзин Н.М.', 'История государства Российского', 'Историческая литература', 0, 1331, 2003, '8-е издание', 2002
exec addbook '978-5-85-865644-8', 'Солженицын А.Х.','Архипелаг Гулаг','Историческая литература',0,12,1992,'5-е издание',1991
exec addbook '978-5-86-918003-1', 'Бруннек Н.И.','Рачительному хозяину','Кулинария',1,60,1996,'1-е издание',1992
exec addbook '978-5-88-682003-5', 'Качалова К.Н.','Практическая грамматика английского языка','Словарь',1,200,1999,'2-е издание',1998
exec addbook '978-5-17-173186-0', 'Дениц К.Х.', 'Немецкие подводные лодки','Военная тематика',1,209,2013,'1-е издание', 2002
exec addbook '978-5-17-175374-7', 'Брукс М.Х.', 'Мировая война Z', 'Ужасы',1, 209, 2009, '4-е издание', 2008
exec addbook '978-5-17-177051-9', 'Стивен К.Х.', 'Стрелок', 'Ужасы', 1, 410,2016,'9-е издание',2015
exec addbook '978-5-17-178406-6', 'Алабугина Ю.В.', 'Орфографический словарь русского языка', 'Словарь', 1, 340,2017,'2-е издание',2016
--exec addbook '978-5-17-086148-4', 'Каторин Ю.Ф.', 'Всё о танках','Военная тематика', 1, 759,2020,'1-е издание',2018
exec adddebtor_full '978-5-17-176148-4', 'Каторин Ю.Ф.', 'Всё о танках','Военная тематика',1, 'Омелько И.О.', '7-911-643-78-54', 759,2020,'1-е издание',2018, 'АСТ', 'astpub@aha.ru', 'http://www.ast.ru'
--exec addbook '978-5-17-089793-3', 'Стивен К.Х.', 'Ветер сквозь замочную скважину', 'Ужасы', 1, 410,2016,'9-е издание',2015
EXEC adddebtor_full '978-5-17-179893-3', 'Гриц А.Б.', 'А как ', 'Ужасы', 1, 'Николай П.П', '7-966-455-45-46', 410,2016,'6-е издание',2015, 'АСТ', 'astpub@aha.ru', 'http://www.ast.ru'
exec addfulldata '978-5-17-209893-3','Я П.П','Что делать Артему в современном мире ',1,555,2001,'1-е издание',2015,'Драма','info@economizdat.ru','http://www.harvest.minsk.by',
go
select * from Book
SELECT * FROM debtor
SELECT * FROM Purchase
sELECT * FROM Publication
sELECT * FROM Pubs
exec addbook '978-5-85-865744-8', 'Омелько И.И', 'Как выжить,если ты Игорь', 'Медицина', 0, 1840, 2000, '6-е издание', 1993
go
EXEC updbook_full '978-5-69-907853-9', 'Каганов Б.С.', 'Лечебное питание', 'Медицина'
EXEC updpubl '978-5-69-907853-9', '1-е издание', 2015
exec adddebtor_short '978-5-69-917853-9','Ты П.Б.', '7-666-555-35-35', 'Эксмо', 'Питание',1,2014,654,2019
exec updbook '978-5-85-865644-9', 0,1
exec upddeb '978-5-85-865644-8','Курицын Н.А.', '8-911-539-49-34'
exec updisbn '978-5-85-865644-8', '978-5-85-865644-9'
Select * from Pubs
Select * from Book
Select * from debtor
Select * from Purchase
Create procedure addpubs
(
@ISBN char(17),
@Pub_name varchar(50),
@Pub_Email varchar(50),
@Pub_site varchar(50)
)
as
begin
insert into pubs(Pub_name, Pub_site, Pub_Email, Pub_code)
Values(@Pub_name, @Pub_site, @Pub_Email, SUBSTRING (@ISBN, charindex('-', @ISBN) -3, 6 ) + SUBSTRING (@ISBN, charindex('-', @ISBN) +6, 2))
end
Create procedure adddebtor_short
(
@ISBN char(17),
@Debtor_name nvarchar(30),
@Debtor_phone char(15),
@Pub_name varchar(50),
@NB nvarchar(50),
@Book_read bit,
@Publication_year numeric(4, 0),
@Cost money,
@Purchase_year numeric(4, 0)
)
as
begin
insert into Pubs(Pub_name, Pub_code)
VALUES (@Pub_name, SUBSTRING (@ISBN, charindex('-', @ISBN) -3, 6 ) + SUBSTRING (@ISBN, charindex('-', @ISBN) +6, 2))
insert into Book (ISBN, NB, Book_read, Pub_code, [Availability])
values (@ISBN, @NB, @Book_read, SUBSTRING (@ISBN, charindex('-', @ISBN) -3, 6 ) + SUBSTRING (@ISBN, charindex('-', @ISBN) +6, 2), 0)
Insert into debtor (ISBN, Debtor_name, Debtor_phone)
values (@ISBN, @Debtor_name, @Debtor_phone)
Insert into Publication(ISBN, Publication_year)
values (@ISBN, @Publication_year)
iNSERT INTO Purchase (ISBN, Cost, Purchase_year)
VALUES (@ISBN, @Cost, @Purchase_year)
end
go
create procedure addfulldata
(
@ISBN char(17),
@Author nvarchar(30),
@NB nvarchar(50),
@Genre varchar(20),
@Book_read bit,
@Cost money,
@Purchase_year numeric(4, 0),
@Edition varchar(10),
@Publication_year numeric(4, 0),
@Pub_name varchar(50),
@Pub_Email varchar(50),
@Pub_site varchar(50)
)
as
begin
insert into pubs(Pub_name, Pub_site, Pub_Email, Pub_code)
Values(@Pub_name, @Pub_site, @Pub_Email, SUBSTRING (@ISBN, charindex('-', @ISBN) -3, 6 ) + SUBSTRING (@ISBN, charindex('-', @ISBN) +6, 2))
iNSERT INTO Book (ISBN, Author, NB, Genre, Book_read, Availability, Pub_code)
values (@ISBN, @Author, @NB, @Genre, @Book_read, 0, SUBSTRING (@ISBN, charindex('-', @ISBN) -3, 6 ) + SUBSTRING (@ISBN, charindex('-', @ISBN) +6, 2))
Insert into debtor (ISBN)
values (@ISBN)
Insert into Publication(Edition, ISBN, Publication_year)
values (@Edition, @ISBN, @Publication_year)
iNSERT INTO Purchase (ISBN, Cost, Purchase_year)
VALUES (@ISBN, @Cost, @Purchase_year)
end
go
create procedure adddebtor_full
(
@ISBN char(17),
@Author nvarchar(30),
@NB nvarchar(50),
@Genre varchar(20),
@Book_read bit,
@Debtor_name nvarchar(30),
@Debtor_phone char(15),
@Cost money,
@Purchase_year numeric(4, 0),
@Edition varchar(10),
@Publication_year numeric(4, 0),
@Pub_name varchar(50),
@Pub_Email varchar(50),
@Pub_site varchar(50)
)
as
begin
insert into pubs(Pub_name, Pub_site, Pub_Email, Pub_code)
Values(@Pub_name, @Pub_site, @Pub_Email, SUBSTRING (@ISBN, charindex('-', @ISBN) -3, 6 ) + SUBSTRING (@ISBN, charindex('-', @ISBN) +6, 2))
iNSERT INTO Book (ISBN, Author, NB, Genre, Availability, Book_read, Pub_code)
values (@ISBN, @Author, @NB, @Genre, 0, @Book_read, SUBSTRING (@ISBN, charindex('-', @ISBN) -3, 6 ) + SUBSTRING (@ISBN, charindex('-', @ISBN) +6, 2))
Insert into debtor (ISBN, Debtor_name, Debtor_phone)
values (@ISBN, @Debtor_name, @Debtor_phone)
Insert into Publication(Edition, ISBN, Publication_year)
values (@Edition, @ISBN, @Publication_year)
iNSERT INTO Purchase (ISBN, Cost, Purchase_year)
VALUES (@ISBN, @Cost, @Purchase_year)
end
go
create procedure addpurchase
(
@ISBN char(17),
@Cost money,
@Purchase_year numeric(4, 0)
)
as
begin
iNSERT INTO Purchase (ISBN, Cost, Purchase_year)
VALUES (@ISBN, @Cost, @Purchase_year)
end
go
create procedure addpublication
(
@ISBN char(17),
@Edition varchar(10),
@Publication_year numeric(4, 0)
)
as
begin
iNSERT INTO Publication(ISBN, Edition, Publication_year)
VALUES (@ISBN, @Edition, @Publication_year)
end
go
--процедура на обновление
create procedure upddeb
(
@ISBN char(17),
@Debtor_name nvarchar(30),
@Debtor_phone char(15)
)
as
begin
IF (Select [Availability] from Book where ISBN = @ISBN) = 0
update debtor
SET Debtor_name = @Debtor_name, Debtor_phone = @Debtor_phone
wHERE ISBN = @ISBN
else
print 'Эта книгу никто не брал'
end
GO
Create procedure updbook
(
@ISBN char(17),
@Book_read bit,
@Availability bit
)
as
begin
update Book
Set Book_read = @Book_read, [Availability] = @Availability
where ISBN = @ISBN
end
go
create procedure updisbn
(
@ISBN_old char(17),
@ISBN_new char(17)
)
as
begin
Update Pubs
set Pub_code = SUBSTRING (@ISBN_new, charindex('-', @ISBN_new) -3, 6 ) + SUBSTRING (@ISBN_new, charindex('-', @ISBN_new) +6, 2)
where Pub_code = SUBSTRING (@ISBN_old, charindex('-', @ISBN_old) -3, 6 ) + SUBSTRING (@ISBN_old, charindex('-', @ISBN_old) +6, 2)
update Book
set ISBN = @ISBN_new
where ISBN = @ISBN_old
update debtor
set ISBN = @ISBN_new
WHERE ISBN = @ISBN_old
update Publication
set ISBN = @ISBN_new
WHERE ISBN = @ISBN_old
update Purchase
set ISBN = @ISBN_new
WHERE ISBN = @ISBN_old
end
go
CREATE PROCEDURE updbook_full
(
@ISBN char(17),
@Author nvarchar(30),
@NB nvarchar(50),
@Genre varchar(20)
)
as
begin
Update Book
set Author = @Author, NB = @NB, Genre = @Genre
where ISBN = @ISBN
end
go
CREATE PROCEDURE updpubl
(
@ISBN char(17),
@Edition varchar(10),
@Publication_year numeric(4, 0)
)
as
update Publication
set Edition = @Edition, Publication_year = @Publication_year
where ISBN = @ISBN
GO
CREATE PROCEDURE updpurc
(
@ISBN char(17),
@Cost money,
@Purchase_year numeric(4, 0)
)
as
update Purchase
set Cost = @Cost, Purchase_year = @Purchase_year
where ISBN = @ISBN
GO
CREATE procedure updpubs
(
@Pub_code char(8),
@Pub_name varchar(50),
@Pub_Email varchar(50),
@Pub_site varchar(50)
)
as
update Pubs
set Pub_name = @Pub_name, Pub_Email = @Pub_Email, Pub_site = @Pub_site
where Pub_code = @Pub_code
go
--Процедура на удаление
create procedure delpub
(
@Pub_code char(8)
)
as
delete Pubs
where Pub_code = @Pub_code
go
exec delpub '231-1-43'
go
create procedure delreadbook
(
@ISBN char(17)
)
as
delete Book
where ISBN = @ISBN AND Book_read = 1
go
create procedure delbook
(
@ISBN char(17)
)
as
delete Book
where ISBN = @ISBN
GO
create procedure delpublication
(
@ISBN char(17)
)
as
delete Publication
where ISBN = @ISBN
GO
CREATE PROCEDURE deldebtor
(
@ISBN char(17)
)
as
delete debtor
where ISBN = @ISBN
GO
create procedure delpurchase
(
@Cost money,
@Purchase_year numeric(4,0)
)
as
delete Purchase
where Cost = @Cost AND Purchase_year = @Purchase_year
go
exec delpurchase 1840, 2000
--Процедура на поиск
CREATE PROCEDURE searchgenre
(
@Genre varchar(20)
)
as
select * from Book
where Genre = @Genre
go
create procedure searchauthor
(
@Author NVARCHAR(30)
)
as
select * from Book
where Author = @Author
go
create procedure searchpubs
(
@ISBN char(17)
)
as
BEGIN
Select Book.ISBN, Book.Author, Book.NB, Book.Genre, Book.Pub_code, Pubs.Pub_name, Pubs.Pub_site, Pubs.Pub_Email from Book INNER JOIN Pubs
on Book.Pub_code = Pubs.Pub_code
where Book.ISBN = @ISBN
END
GO
create procedure searchdebtor
(
@ISBN char(17)
)
as
begin
Select Book.ISBN, Book.Author, Book.NB, Book.Genre, debtor.Debtor_name, debtor.Debtor_phone from debtor INNER JOIN Book
on debtor.ISBN = Book.ISBN
where Book.Availability = 0 and Book.ISBN = @ISBN
end
go
create procedure searchpublic
(
@Publication_year numeric(4, 0)
)
as
begin
select Book.ISBN, Book.Author, Book.NB, Book.Genre, Publication.Edition, Publication.Publication_year from Book inner join Publication
on Book.ISBN = Publication.ISBN
WHERE Publication_year = @Publication_year
end
go
CREATE Procedure searchpurch
(
@Purchase_year numeric(4,0)
)
as
begin
select Book.ISBN, Book.Author, Book.NB, Book.Genre, Purchase.Cost, Purchase.Purchase_year from Book inner join Purchase
on Book.ISBN = Purchase.ISBN
WHERE Purchase_year = @Purchase_year
end
go