Re: PL-SQL: Cursor definition with additional parameter ? reason/meaning ?

From: <rolf.unger_at_ctilabs.de>
Date: 20 Mar 2007 03:32:12 -0700
Message-ID: <1174386732.363448.295650@p15g2000hsd.googlegroups.com>

On Feb 22, 10:12 pm, Wern..._at_mail.org (Werner Mueller) wrote:
> I learned that a cursor definition in PL-SQL follows the schema:
>
> Curosr mycurosr is ...
>
> Now I found a couple of sample where the cursor definition is like
>
> Cursor mycursor (somevar VARCHAR2) is ....
>
> What does that parameter/variable defintion mean ?
> Sometimes even more 2 or 3 additional vars are defiend
>
> How can I use this variable.
> May I get an example on how to apply such a parameter.
>
> Werner

CURSOR mycursor(somevar VARCHAR2) IS ....

is a clearer way to say that your cursor has a variable part.

Mostly you have something like:

DECLARE
 v_filter NUMBER(3);
 CURSOR my_cursor IS SELECT col_a, substr(col_b,1,4) FROM my_table

                      WHERE col_a = v_filter;

 v_num NUMBER(3);
 v_char VARCHAR2(20);
BEGIN
  v_filter := 7;
  OPEN my_cursor;
  FETCH my_cursor INTO v_num, v_char;
  CLOSE my_cursor;

• do something with v_num and v_char

  v_filter := 8;
  OPEN my_cursor;
  FETCH my_cursor INTO v_num, v_char;
  CLOSE my_cursor;

• again do something with v_num, v_char

END; Now if you look at this code after 3 or 4 weeks you will say to yourself: "What in the world is the reason for setting v_filter to 7 and 8 before opening the cursor?"
You will go to the declaration of the cursor and will figure out, but if the part PL/SQL block is lengthly you might have to scroll a lot.

To use parameters in the cursor declaration makes this a lot clearer:

DECLARE
 CURSOR my_cursor(p_filter NUMBER)

        IS SELECT col_a, substr(col_b,1,4) FROM my_table
            WHERE col_a = p_filter;

 v_num NUMBER(3);
 v_char VARCHAR2(20);
BEGIN   OPEN my_cursor(7);
  FETCH my_cursor INTO v_num, v_char;
  CLOSE my_cursor;

END; Most likely you will not use a literal 7, but a variable that might be a parameter for your procedure or that depends on some other statements that you execute in your PL/SQL block before opening the cursor.

I guess similar information should also be in the Oracle Documentation,
(PL/SQL User Guide: Interaction Between PL/SQL and Oracle) but it might be irritating in the beginning, because the mention parameters for cursors in a kind of side note and continue with cursor variables (which sounds similar, but is quite different).

HTH, Rolf. Received on Tue Mar 20 2007 - 05:32:12 CDT