I do not want to write a separate cursor for each category, that is definitely not a data-driven approach to programming.
Instead, I would much rather be able to change the joke cursor so that it can accept different categories and return the appropriate rows. The best (though not the only) way to do this is with a cursor parameter
PROCEDURE explain_joke (main_category_in IN joke_category.category_id%TYPE)
|| Cursor with parameter list consisting of a single
|| string parameter.
CURSOR joke_cur (category_in IN VARCHAR2)
SELECT name, category, last_used_date
WHERE category = UPPER (category_in);
/* Now when I open the cursor, I also pass the argument */
OPEN joke_cur (main_category_in);
FETCH joke_cur INTO joke_rec;
I added a parameter list after the cursor name and before the IS keyword. I took out the hardcoded "HUSBAND" and replaced it with
"UPPER (category_in)" so that I could enter "HUSBAND”, “husband”, or "HuSbAnD" and the cursor would still work.
Now when I open the cursor, I specify the value I want to pass as the category by including that value (which can be a literal, a constant, or an expression) inside parentheses. At the moment the cursor is opened, the SELECT statement is parsed and bound using the specified value for category_in. The result set is identified, and the cursor is ready for fetching.