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

On 22 Feb, 21:12, 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

This is a cursor I have just been using in my code to assess whether a particular schemas triggers are all enabled.

    CURSOR cur_disabled(pOwner all_triggers.owner%TYPE) IS

      SELECT trigger_name
        FROM all_triggers
       WHERE UPPER(owner) = UPPER(pOwner)
         AND UPPER(status) = 'DISABLED';

Basically, the pOwner variable passed into the cursor is used in the WHERE clause.
So when I open the cursor, I do so passing the value I want to use in the WHERE clause...

 open cur_disabled('MY OWNER');

You can of course specify multiple parameters, like such...

CURSOR cur_disabled(pOwner all_triggers.owner%TYPE, pStatus all_triggers.status%TYPE) IS

      SELECT trigger_name
        FROM all_triggers
       WHERE UPPER(owner) = UPPER(pOwner)
         AND UPPER(status) = UPPER(pStatus);

This would be called like this...

  open cur_disabled('MY OWNER', 'DISABLED');

HTH Iain Received on Fri Feb 23 2007 - 03:56:19 CST