Re: Decode equivalent within pl/sql?

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Mon, 13 Nov 2006 16:33:36 +0100
Message-ID: <45589050.8050101@arcor.de>

Jeremy schrieb:
> In article <455888e9$0$30316$9b4e6d93_at_newsspool1.arcor-online.net>,
> Maxim Demenko says...

>> Jeremy schrieb:
>>> 9iR2
>>>
>>> As I understand it you can only use DECODE as part of a SQL statement
>>>
>>> e.g. select decode(var,
>>>                    'A','LetterA',
>>>                    'B','LetterB',
>>>                    'Lettersomethingelse')
>>>       from dual;
>>>
>>> In a pl/sql statement, you cannot code e.g.
>>>
>>>   l_string := decode(.....
>>>
>>> What do you use to achieve a similar result - or do you set the value 
>>> into a variable beforehand with an 
>>>  if 
>>>  elsif 
>>>  end if   
>>>
>>> structure?
>>>
>>> Thanks
>>>
>> Decode was introduced to make IF ELSE logic available in SQL, later came 
>> CASE which can do it as well, in PL SQL the IF ELSE logic was always 
>> available. In your example i would probably use IF ELSE ( while no data 
>> from tables are involved in your logic). But, to round up - in 9iR2 the 
>> appropriate use of decode in plsql is possible.
>>
>> scott_at_CMS42DEV> SELECT * FROM V$VERSION;
>>
>> BANNER
>> ----------------------------------------------------------------
>> Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
>> PL/SQL Release 9.2.0.6.0 - Production
>> CORE    9.2.0.6.0       Production
>> TNS for Linux: Version 9.2.0.6.0 - Production
>> NLSRTL Version 9.2.0.6.0 - Production
>>
>> scott_at_CMS42DEV> DECLARE
>>    2  l_one NUMBER;
>>    3  l_two NUMBER;
>>    4  BEGIN
>>    5  SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END INTO l_one FROM dual;
>>    6  dbms_output.put_line(l_one);
>>    7  SELECT decode(2,2,2) INTO l_two FROM dual;
>>    8  dbms_output.put_line(l_two);
>>    9  END;
>>   10  /
>> 1
>> 2
>>

>
> Thanks I may not have made it clear - I know I can use DECODE in a
> select stamenet within PL/SQL but I was looking for a nicer way to code
> the following (example) without having to define a variable into which
> to put the value I want before calling the proc.
>
> e.g
>
> begin
> -- stuff
> utility_proc(p1 => 'ADD',
> p2 => decode(thing,'val1','res1','val2','res2','dflt'));
> --
> end;
>
> Now I know I cannot use the decode there and it seems that I have to
> look at the value of "thing" using either
> "if then else"
> or a
> "select decode(....) into var from dual;"
>
>
>

Well, for that purpose you can also use CASE direct within procedure call, like

scott_at_CMS42DEV> exec dbms_output.put_line(a=>case when 1=1 then 'Hello' end);
Hello

PL/SQL procedure successfully completed.

However, that will not probably shorten the code compared to IF ELSE... Best regards

Maxim Received on Mon Nov 13 2006 - 09:33:36 CST