Файл: Процедуры на добавление use db home library 293 02.docx
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 12.01.2024
Просмотров: 163
Скачиваний: 1
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)
)
asbegininsert 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)endgocreate procedure addpurchase(@ISBN char(17),@Cost money,@Purchase_year numeric(4, 0))asbeginiNSERT INTO Purchase (ISBN, Cost, Purchase_year)VALUES (@ISBN, @Cost, @Purchase_year)endgocreate procedure addpublication(@ISBN char(17),@Edition varchar(10),@Publication_year numeric(4, 0))asbeginiNSERT INTO Publication(ISBN, Edition, Publication_year)VALUES (@ISBN, @Edition, @Publication_year)endgo--процедура на обновлениеcreate procedure upddeb(@ISBN char(17),@Debtor_name nvarchar(30),@Debtor_phone char(15))asbeginIF (Select [Availability] from Book where ISBN = @ISBN) = 0update debtorSET Debtor_name = @Debtor_name, Debtor_phone = @Debtor_phonewHERE ISBN = @ISBNelseprint 'Эта книгу никто не брал'endGOCreate procedure updbook(@ISBN char(17),@Book_read bit,@Availability bit)asbeginupdate BookSet Book_read = @Book_read, [Availability] = @Availabilitywhere ISBN = @ISBNendgocreate procedure updisbn(@ISBN_old char(17),@ISBN_new char(17))asbeginUpdate Pubsset 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 Bookset ISBN = @ISBN_newwhere ISBN = @ISBN_oldupdate debtorset ISBN = @ISBN_newWHERE ISBN = @ISBN_oldupdate Publicationset ISBN = @ISBN_newWHERE ISBN = @ISBN_oldupdate Purchaseset ISBN = @ISBN_newWHERE ISBN = @ISBN_old endgoCREATE PROCEDURE updbook_full(@ISBN char(17),@Author nvarchar(30), @NB nvarchar(50),@Genre varchar(20))asbeginUpdate Bookset Author = @Author, NB = @NB, Genre = @Genrewhere ISBN = @ISBNendgoCREATE PROCEDURE updpubl(@ISBN char(17),@Edition varchar(10),@Publication_year numeric(4, 0))asupdate Publicationset Edition = @Edition, Publication_year = @Publication_yearwhere ISBN = @ISBNGOCREATE PROCEDURE updpurc(@ISBN char(17),@Cost money,@Purchase_year numeric(4, 0))asupdate Purchaseset Cost = @Cost, Purchase_year = @Purchase_yearwhere ISBN = @ISBNGOCREATE procedure updpubs(@Pub_code char(8),@Pub_name varchar(50),@Pub_Email varchar(50),@Pub_site varchar(50))asupdate Pubsset Pub_name = @Pub_name, Pub_Email = @Pub_Email, Pub_site = @Pub_sitewhere Pub_code = @Pub_codego--Процедура на удалениеcreate procedure delpub(@Pub_code char(8))asdelete Pubswhere Pub_code = @Pub_codegoexec delpub '231-1-43'gocreate procedure delreadbook(@ISBN char(17))asdelete Bookwhere ISBN = @ISBN AND Book_read = 1gocreate procedure delbook
(@ISBN char(17))asdelete Bookwhere ISBN = @ISBNGOcreate procedure delpublication(@ISBN char(17))asdelete Publicationwhere ISBN = @ISBNGOCREATE PROCEDURE deldebtor(@ISBN char(17))asdelete debtorwhere ISBN = @ISBNGOcreate procedure delpurchase(@Cost money,@Purchase_year numeric(4,0))asdelete Purchasewhere Cost = @Cost AND Purchase_year = @Purchase_yeargoexec delpurchase 1840, 2000--Процедура на поискCREATE PROCEDURE searchgenre(@Genre varchar(20))asselect * from Bookwhere Genre = @Genregocreate procedure searchauthor(@Author NVARCHAR(30))asselect * from Bookwhere Author = @Authorgocreate procedure searchpubs(@ISBN char(17))asBEGINSelect Book.ISBN, Book.Author, Book.NB, Book.Genre, Book.Pub_code, Pubs.Pub_name, Pubs.Pub_site, Pubs.Pub_Email from Book INNER JOIN Pubson Book.Pub_code = Pubs.Pub_codewhere Book.ISBN = @ISBNENDGOcreate procedure searchdebtor(@ISBN char(17))asbeginSelect Book.ISBN, Book.Author, Book.NB, Book.Genre, debtor.Debtor_name, debtor.Debtor_phone from debtor INNER JOIN Bookon debtor.ISBN = Book.ISBNwhere Book.Availability = 0 and Book.ISBN = @ISBNendgocreate procedure searchpublic(@Publication_year numeric(4, 0))asbeginselect Book.ISBN, Book.Author, Book.NB, Book.Genre, Publication.Edition, Publication.Publication_year from Book inner join Publicationon Book.ISBN = Publication.ISBNWHERE Publication_year = @Publication_yearendgoCREATE Procedure searchpurch(@Purchase_year numeric(4,0))asbeginselect Book.ISBN, Book.Author, Book.NB, Book.Genre, Purchase.Cost, Purchase.Purchase_year from Book inner join Purchaseon Book.ISBN = Purchase.ISBNWHERE Purchase_year = @Purchase_yearendgo