October 2005
Intermediate to advanced
454 pages
14h 44m
English
As I mentioned earlier in this chapter, you can further promote cursor reuse in PL/SQL by parameterizing your cursors. Here’s my simple order_date procedure with a parameterized cursor:
DECLARE
CURSOR curs_get_od ( cp_on NUMBER ) IS
SELECT order_date
FROM orders
WHERE order_number = cp_on;
v_date DATE;
BEGIN
OPEN curs_get_od(100);
FETCH curs_get_od INTO v_date;
CLOSE curs_get_od;
END;If later on in the program I wanted to get order 200, 300, and 500, I could simply reopen the cursor. Doing so promotes cursor reuse within the PL/SQL program itself as well as in the shared pool.