Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL-SQL: Cursor definition with additional parameter ? reason/meaning ?
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_filter := 8;
OPEN my_cursor;
FETCH my_cursor INTO v_num, v_char;
CLOSE my_cursor;
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;
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
![]() |
![]() |