MySql: Create a stored procedure or process which checks out a book from the boo
ID: 3920876 • Letter: M
Question
MySql: Create a stored procedure or process which checks out a book from the book table:
- Makes sure the copy isn't already checked out
- Makes sure the user doesn't have fines on their account
Customer table:
Fine table:
Book table:
Checkout table:
FINES_idFINES idCUSTOMER 123456789012345 Johnnv Rock1949-10-20 iohnr @mail.com 151595254105418 298765432123456 Waters 1999-04-23 lolawaters@mail.com 56 Red St. Baltimore MD 414392845733378 Joe 488806912544440 512651122522845 RachelSac FName LName DOB Email Address 22 Yellow Ct. Hvattsville MDULL NULL NULL 74446382 Julia inoot2929@mail.com 2 Purole Rd. Baltimore MD 35 Pink St. Eldersbura MD 726 Green Rd. Eldersbura MD Noot1989-03-10 1980-04-25 iomev14@mail.com Mever Mason1994-11-12 Alexis alexmavvv09@mail.com10 Red Ave. Baltimore MD NULL 1959-05-14 rsac837@mail.com 98371920Explanation / Answer
CREATE DATABASE dbdemo;
CREATE TABLE books(
id int auto_increment primary key,
title varchar(255) not null
)ENGINE=InnoDB;
Next, populate data for the books table by using the following stored procedure:
DELIMITER $$
CREATE PROCEDURE load_book_data(IN num int(4))
BEGIN
DECLARE counter int(4) default 0;
DECLARE book_title varchar(255) default '';
WHILE counter < num DO
SET title = concat('Book title #',counter);
SET counter = counter + 1;
INSERT INTO books
Values(book_title);
END WHILE;
END$$
DELIMITER ;
Then, load 10,000 rows into the books table. It will take a while.
CALL load_book_data(10000);
After that, check the data in the books table:
SELECT *
FROM books;
Finally, use the TRUNCATE TABLE statement to see how fast it performs in comparison with the DELETE statement.
TRUNCATE TABLE books;
Another program for My Sql
delimiter $$
create procedure BorrowBook(in theBookID int, in theUserID int)
begin
declare lim int;
declare num int;
declare loanNumber int;
declare copyNumber int;
set lim = (select Readers.Limit from Readers where Readers.id = theUserID);
set num = (select Count(*) from Loans where Loans.UserID = theUserID);
set loanNumber = (select Count(*) from Loans) + 1;
set copyNumber = (select NumberAvailable from Book where Book.id = theBookID);
if(copyNumber > 0)
then
if(num<lim)
then
insert into Loans values(loanNumber, theBookID, theUserID, curDate(), 0);
commit;
update Book set Book.NumberAvailable = Book.NumberAvailable - 1 where Book.id = theBookID;
select 'Succesful Update';
else
rollback;
select 'Borrow limit reached';
end if;
else
rollback;
select 'No copies available';
end if;
end $$
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.