1) DELIMITER $$
CREATE PROCEDURE readerIsLoginElLib(IN wr varchar(30), IN wt varchar(255))
BEGIN
SELECT * FROM reader INNER JOIN user
ON reader.fk_user = user.pk_user
WHERE user.login = wr AND user.pass = wt;
END;

2) DELIMITER $$
CREATE PROCEDURE readerIsntLoginElLib()
BEGIN
SELECT * FROM reader
reader LEFT JOIN user
ON reader.fk_user = user.pk_user
WHERE user.pk_user is null
ORDER BY reader.lastname, reader.firstname, reader.middlename, reader.dateOfEntry;
END;

3) DELIMITER $$
CREATE PROCEDURE amountIsntLoginElLib(OUT wt smallint)
BEGIN
SELECT COUNT(*) INTO wt FROM reader
reader LEFT JOIN user
ON reader.fk_user = user.pk_user
WHERE user.pk_user is null
ORDER BY reader.lastname, reader.firstname, reader.middlename, reader.dateOfEntry;
END;

4)DELIMITER $$
CREATE PROCEDURE isLoginElLib(IN f varchar(45), IN i varchar(45),
                              IN o varchar(45), IN email varchar(255), OUT flag boolean)
BEGIN
DECLARE nr int;
SET flag = false;
SELECT MAX(reader.pk_reader) INTO nr FROM reader
WHERE(reader.lastname=f) AND (reader.firstname=i) AND (reader.middlename=o) AND (reader.email=email);
IF(nr IS NOT null)
THEN SET flag=true;
END IF;
END;

5)DELIMITER $$
CREATE PROCEDURE regElLib(IN loginP varchar(30), IN passP varchar(255))
BEGIN
INSERT INTO user (user.login, user.pass, user.role)
VALUES (loginP, PASSWORD(passP), 'Libreader');
END;

6)DELIMITER $$
CREATE PROCEDURE regLib(IN pk_readerP smallint, IN lastnameP varchar(45),
IN firstnameP varchar(45), IN middlenameP varchar(45), IN number_telP char(11),
IN cityP varchar(45), IN adressP varchar(45), IN genterP enum('М','Ж'), IN dateOfBirchP date,
IN dateOfEntry timestamp, IN emailP varchar(255))
BEGIN
INSERT INTO reader (reader.lastname, reader.firstname, reader.middlename, reader.number_tel,
reader.city, reader.adress, reader.genter, reader.dateOfBirch, reader.dateOfEntry, reader.email)
VALUES (pk_readerP, lastnameP, firstnameP, middlenameP,
number_telP, cityP, adressP, genterP, dateOfBirchP, dateOfEntry, emailP);
end;

7)DELIMITER $$
CREATE PROCEDURE issueAll(IN dateOfIssueP timestamp, IN dateActualReturnP datetime, IN pk_readerP smallint)
BEGIN
SELECT issued.dateOfIssue, reader.pk_reader, concat(reader.lastname, reader.firstname, reader.middlename),
 issued.fk_book, COUNT(issued.fk_book) AS Количество, issued_fine.fineOf  FROM (issued INNER JOIN reader
ON issued.fk_reader=reader.pk_reader) INNER JOIN
issued_fine ON issued.pk_issued=issued_fine.fk_issued
WHERE issued.dateOfIssue = dateOfIssueP AND issued.dateActualReturn = dateActualReturnP AND reader.pk_reader = pk_readerP
ORDER BY issued.dateOfIssue AND reader.lastname AND reader.firstname AND reader.middlename;
END;

8)DELIMITER $$
CREATE PROCEDURE issueIsntReturn(IN dateOfIssueP timestamp, IN dateActualReturnP datetime, IN pk_readerP smallint)
BEGIN
SELECT issued.dateOfIssue, reader.pk_reader, concat(reader.lastname, reader.firstname, reader.middlename), issued.fk_book, COUNT(issued.fk_book) AS Количество, issued_fine.fineOf  FROM (issued INNER JOIN reader
ON issued.fk_reader=reader.pk_reader) INNER JOIN
issued_fine ON issued.pk_issued=issued_fine.fk_issued
WHERE issued.dateOfIssue = dateOfIssueP AND (issued.dateActualReturn IS null OR issued.dateActualReturn = ' ') AND reader.pk_reader = pk_readerP
ORDER BY issued.dateOfIssue AND reader.lastname AND reader.firstname AND reader.middlename;
END;

9)

10)DELIMITER $$
CREATE PROCEDURE enouthBook(IN ni smallint, IN col tinyint, OUT flag boolean)
BEGIN
DECLARE nr int;
SET flag = false;
SELECT MAX(book.numberOfCopies) INTO nr FROM issued
WHERE(issued.fk_book=ni) AND (col = book.numberOfCopies);
IF(nr IS NOT null)
THEN SET flag=true;
END IF;
END;

11)DELIMITER $$
CREATE PROCEDURE insIssue(IN ww tinyint, IN rw smallint, IN wr smallint, IN col tinyint)
BEGIN
INSERT INTO issued(issued.numberOfIssue, issued.fk_reader, issued.fk_periodForBack, issued.fk_book)
VALUES (ww, rw, wr, col);
END;

12)DELIMITER $$
CREATE PROCEDURE updDateReturn(IN ww smallint)
BEGIN
UPDATE issued SET issued.dateActualReturn = NOW()
WHERE issued.pk_issued = ww;
END;

13)DELIMITER $$
CREATE PROCEDURE delBook(IN ww smallint)
BEGIN
DELETE FROM issued
WHERE issued.fk_book = ww;
END;