学习存储过程（IN/OUT参数）：求助调试查询作者书籍的存储过程

Hey there! Let's walk through your stored procedure and iron out the kinks together.

First, let's break down the issues in your current code:

• Naming inconsistency: Your comment references get_all_books_by_author, but you're creating a procedure named func. It's better to align these for clarity and maintainability.
• Single-value limitation: Using SELECT book.title INTO books will only capture the first book title if the author has multiple works. If there are multiple matches, this will throw an error because a single variable can't hold multiple rows of data.
• Minor readability tweak: Your WHERE clause has the parameter before the column (authName = author.authorFirst); swapping these to author.authorFirst = authName follows more standard SQL formatting.

Here's the revised version of your procedure that fixes these issues:

# get_all_books_by_author
DROP PROCEDURE IF EXISTS get_all_books_by_author;
DELIMITER ^^
CREATE PROCEDURE get_all_books_by_author(IN authName VARCHAR(20), OUT books VARCHAR(255))
BEGIN
    -- Use GROUP_CONCAT to combine all book titles into a single comma-separated string
    SELECT GROUP_CONCAT(book.title SEPARATOR ', ') INTO books
    FROM book
    INNER JOIN wrote ON wrote.bookcode = book.bookcode
    INNER JOIN author ON author.authornum = wrote.authornum
    WHERE author.authorFirst = authName;
    
    -- Optional: If you want an empty string instead of NULL when no books are found
    -- SELECT COALESCE(GROUP_CONCAT(book.title SEPARATOR ', '), '') INTO books
    -- FROM book
    -- ... rest of the query ...
END ^^
DELIMITER ;

-- Test the procedure
CALL get_all_books_by_author('Toni', @books);
SELECT @books;

Key changes explained:

1. Unified naming: The procedure now matches the comment name get_all_books_by_author.
2. Handling multiple books: GROUP_CONCAT aggregates all matching book titles into a single string, separated by commas (you can change the separator by modifying the SEPARATOR value).
3. Optional NULL handling: The commented-out line uses COALESCE to return an empty string instead of NULL if the author has no books in the database.

A quick note: If you expect very long lists of book titles, you might need to increase the length of the books OUT parameter (e.g., VARCHAR(1000) instead of 255) to avoid truncation.

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