Saturday, February 28, 2009

Generalizing Cursors with Parameters

I really don't want to write a separate cursor for each different 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:

DECLARE
/*
|| Cursor with parameter list consisting of a single
|| string parameter.
*/
CURSOR joke_cur (category_in VARCHAR2)
IS
SELECT name, category, last_used_date
FROM joke
WHERE category = UPPER (category_in);
joke_rec joke_cur%ROWTYPE;
BEGIN
/* Now when I open the cursor, I also pass the argument */
OPEN joke_cur (:joke.category);
FETCH joke_cur INTO joke_rec;

No comments:

search engine

Custom Search