Oracle PL/SQL for DBAs

Cursor Parameters

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.