MySQL存储函数/存储过程开发：统计指定读者借阅书籍数量

Hey there! Let's figure out how to count the number of books a specific reader has borrowed using MySQL stored functions or procedures. First, a quick note: your original CREATE TABLE statement has a tiny syntax error (missing comma between idBook and idReader columns). Here's the corrected version to avoid issues:

CREATE TABLE Form (
    idForm INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    date_of_issue DATE NOT NULL,
    return_date DATE,
    idBook INT NOT NULL,
    idReader INT NOT NULL
);

Now, let's dive into the two solutions:

方案一：使用存储函数 #

Stored functions are perfect for scenarios where you need to return a single value directly. This one takes a reader ID as input and returns their total borrow count:

DELIMITER //
CREATE FUNCTION GetReaderBorrowCount(p_reader_id INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE total_borrows INT;
    
    -- Count all borrow records for the specified reader
    SELECT COUNT(*) INTO total_borrows
    FROM Form
    WHERE idReader = p_reader_id;
    
    RETURN total_borrows;
END //
DELIMITER ;

How to use it: #

Just call the function in a SELECT statement with your target reader ID:

SELECT GetReaderBorrowCount(4); -- Returns 2 for your sample data

方案二：使用存储过程 #

If you prefer using stored procedures (great for more complex logic or multiple outputs), here's an implementation with an output parameter to hold the count:

DELIMITER //
CREATE PROCEDURE GetReaderBorrowCountProc(
    IN p_reader_id INT,
    OUT p_total_borrows INT
)
BEGIN
    -- Store the count in the output parameter
    SELECT COUNT(*) INTO p_total_borrows
    FROM Form
    WHERE idReader = p_reader_id;
END //
DELIMITER ;

How to use it: #

You'll need to declare a variable to capture the output, then call the procedure:

SET @borrow_count = 0;
CALL GetReaderBorrowCountProc(4, @borrow_count);
SELECT @borrow_count; -- Returns 2 for your sample data

Optional: Filter by return status #

If you ever need to count only returned or unreturned books, you can tweak the WHERE clause:

• To count returned books: add AND return_date IS NOT NULL
• To count unreturned books: add AND return_date IS NULL

内容的提问来源于stack exchange，提问作者w7ve