Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: DECODE statement: is this a reasonable way to do things?

Re: DECODE statement: is this a reasonable way to do things?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 4 Jul 2001 07:49:46 -0700
Message-ID: <9hvaea02k6k@drn.newsguy.com>

In article <5my07.4546$A51.1120877_at_monolith.news.easynet.net>, "Benetnasch" says...
>
>It's O7, I'm afraid, so I think I'm right in saying ref cursors
>don't exist... do they?
>

sure they do, they are a 7.2 and up feature (although the ability to fetch from them in PLSQL was added in 7.3, in 7.2 they were exclusively for returning result sets from stored procedures):

scott_at_ORA734.WORLD> variable x refcursor
scott_at_ORA734.WORLD> variable n number
scott_at_ORA734.WORLD> set echo on
scott_at_ORA734.WORLD> @a
scott_at_ORA734.WORLD> begin

  2 if ( 1 = :n ) then open :x for select 'Yes' from dual;   3 else open :x for select 'No' from dual;   4 end if;
  5 end;
  6 /

PL/SQL procedure successfully completed.

scott_at_ORA734.WORLD> print x

'N

--
No

scott_at_ORA734.WORLD> exec :n := 1;

PL/SQL procedure successfully completed.

scott_at_ORA734.WORLD> @a
scott_at_ORA734.WORLD> begin
  2  if ( 1 = :n ) then open :x for select 'Yes' from dual;
  3  else open :x for select 'No' from dual;
  4  end if;
  5  end;
  6  /

PL/SQL procedure successfully completed.

scott_at_ORA734.WORLD> print x

'YE
---
Yes

scott_at_ORA734.WORLD> 



>--
>Benetnasch
>Remove ".spam.begone"
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:9hstlv02g4s_at_drn.newsguy.com...
>> In article <Xml07.4424$A51.1022535_at_monolith.news.easynet.net>,
"Benetnasch"
>> says...
>> >
>> >Just wondered whether this is a reasonable way of doing things:
>> >
>> >I have a PL/SQL procedure which has, as an argument, a flag.
>> >If this flag is a 'Y', then I want only records with a value of 17
>> >in a specific column to be selected. If the flag isn't "Y", then
>> >all records can be selected.
>> >
>> ><column name> is a number, so I don't really want to TO_CHAR
>> >it, as it has an index on it.
>> >
>> >My code is thus:
>> >
>> > AND <column name> BETWEEN DECODE(p_newflag
>> > , 'Y', 17
>> > , 0)
>> > AND DECODE(p_newflag
>> > , 'Y', 17
>> > , 100)
>> >
>> >(the column is only NUMBER(2), so 100 should be big enough)
>> >
>>
>> well, if the flag is Y I would want to use an index.
>>
>> if the flag ISN'T Y -- I do not want to use an index (retrieving too many
rows
>> from the table to use an index efficiently)
>>
>> I want two queries. I would use a ref cursor.
>>
>> is
>> type rc is ref cursor;
>> l_cursor rc;
>> ...
>> begin
>> if ( p_newflag = 'Y' )
>> then
>> open l_cursor for select * from t where CNAME = 17;
>> else
>> open l_cursor for select * from t;
>> end if;
>>
>> loop
>> fetch l_cursor into ...;
>> exit when l_cursor%notfound;
>> ...
>> end loop;
>>
>> close l_cursor;
>> end;
>>
>>
>>
>> >This is the way I've thought of doing it, but I'd be grateful if anyone
can
>> >suggest a better way of doing it.
>> >
>> >Thanks for any assistance,
>> >
>> >--
>> >Benetnasch
>> >Remove ".spam.begone"
>> >
>> >
>>
>> --
>> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
>> Expert one on one Oracle, programming techniques and solutions for Oracle.
>> http://www.amazon.com/exec/obidos/ASIN/1861004826/
>> Opinions are mine and do not necessarily reflect those of Oracle Corp
>>
>
>
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Wed Jul 04 2001 - 09:49:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US