Re: pl/sql conditional cursor?
From: Peter Kallweit <p_kallweit_at_arcor.de>
Date: Thu, 04 Jun 2009 12:17:51 +0200
Message-ID: <4a279f4f$0$31874$9b4e6d93_at_newsspool3.arcor-online.net>
steph wrote:
>>> hi,
>>> Oracle 10g. In a procedure I have some code like
>>> declare
>>> cursor c1 is
>>> select x,y
>>> from tab1,tab2
>>> where <condition1>
>>> and <condition2>
>>> and <condition3>;
>>> begin
>>> for r1 in c1 loop
>>> <some code>
>>> end loop;
>>> end;
>>> Now I want to turn condition 3 on/off depending on one of the
>>> procedure's parameters - without having to define a second cursor
>>> (because this would mean duplicating some amount of code). So what I
>>> don't want to do is this:
Date: Thu, 04 Jun 2009 12:17:51 +0200
Message-ID: <4a279f4f$0$31874$9b4e6d93_at_newsspool3.arcor-online.net>
steph wrote:
> On 4 Jun., 11:05, Peter Kallweit <p_kallw..._at_arcor.de> wrote: >> steph wrote:
>>> hi,
>>> Oracle 10g. In a procedure I have some code like
>>> declare
>>> cursor c1 is
>>> select x,y
>>> from tab1,tab2
>>> where <condition1>
>>> and <condition2>
>>> and <condition3>;
>>> begin
>>> for r1 in c1 loop
>>> <some code>
>>> end loop;
>>> end;
>>> Now I want to turn condition 3 on/off depending on one of the
>>> procedure's parameters - without having to define a second cursor
>>> (because this would mean duplicating some amount of code). So what I
>>> don't want to do is this:
>> Hi Stephan, >> >> how about >> >> cursor c1 is >> select x,y >> from tab1,tab2 >> where <condition1> >> and <condition2> >> and (<parametersaysoff> or <condition3>); >> >> hth >> Peter > > I'm not sure if this wouldn't possibly degrade performance of my > query? At least the execution plan is surely different then when > having 2 distinct cursors? >
The execution plans will be different - sure.
Regarding the performance, I'd think it depends on the kind and
complexity of the conditions.
The only way to find out, if the performance remains acceptable, is to
give it a try ;-)
Received on Thu Jun 04 2009 - 05:17:51 CDT