Re: CASE strucrure in PL/SQL

From: W.Breitling <member28455_at_dbforums.com>
Date: Thu, 05 Jun 2003 21:18:11 +0000
Message-ID: <2964825.1054847891@dbforums.com>

You are absolutely right. I missed that, maybe not so, subtle point. You'd have to code around it using sql to make the assignment

e.g. in Oracle 9i:

ora92.scott> DECLARE
  2 v_department_id number;
  3 BEGIN
  4 FOR i IN 1..7 LOOP
  5 select case when i=1 then 10

  6   else case when i=2 then 20
  7   else case when i=3 then 30
  8   else case when i=4 then 40
  9   else case when i=5 then 50
 10   else case when i=6 then 60
 11   else case when i=7 then 70

 12 end end end end end end end
 13 into v_department_id
 14 from dual;
 15 dbms_output.put_line(i||' - '||v_department_id);  16 end loop;
 17 end;
 18 /

1 - 10
2 - 20
3 - 30
4 - 40
5 - 50
6 - 60
7 - 70

PL/SQL procedure successfully completed.

In Oracle 8i you have to hide the case expressions in a dynamic sql:

ora81.scott> DECLARE
  2 v_department_id number;
  3 BEGIN
  4 FOR i IN 1..7 LOOP
  5 execute immediate 'select case when :b1=1 then 10 '||

  6   'else case when :b1=2 then 20 '||
  7   'else case when :b1=3 then 30 '||
  8   'else case when :b1=4 then 40 '||
  9   'else case when :b1=5 then 50 '||
 10   'else case when :b1=6 then 60 '||
 11   'else case when :b1=7 then 70 '||
 12   'end end end end end end end '||
 13   'from dual' into v_department_id using i,i,i,i,i,i,i;

 14 dbms_output.put_line(i||' - '||v_department_id);  15 end loop;
 16 end;
 17 /

1 - 10
2 - 20
3 - 30
4 - 40
5 - 50
6 - 60
7 - 70

PL/SQL procedure successfully completed.

The nested case statement can get a bit cumbersome though. There are easier ways of doing it if there are many more.

Originally posted by Jaap W . Van Dijk
> With assignment statement I mean a statement like
>
> v_department_id :=
> CASE i
> WHEN 1 THEN 10
> WHEN 2 THEN 20
> WHEN 3 THEN 50
> WHEN 4 THEN 60
> WHEN 5 THEN 80
> WHEN 6 THEN 90
> WHEN 7 THEN 110
> END ;
>
> so a statement with an assignment operator (:=). This is the statement
> the OP is using and which is incorrect, also in 9i.
>
> The example that W. Breitner uses is an SQL-statement (although being
> an UPDATE it assigns a (possible new) value to a database column of
> course).
>
> Jaap.
>

--
Posted via http://dbforums.com

Received on Thu Jun 05 2003 - 16:18:11 CDT