Re: easy way for list processing with pl/sql?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 20 Jul 1999 21:51:16 GMT
Message-ID: <3796e6de.114690446@inet16.us.oracle.com>

On Tue, 20 Jul 1999 23:53:26 +0200, bmlam <bmlam_at_online.de> wrote:

>I know I can do the following:
>
>declare
> type int_tab is table of integer index by binary_integer;
> v_int_list int_tab;
> v_cnt integer;
> v_empno integer;
> v_index binary_integer;
>begin
> v_int_list(1):= 998; -- this
> v_int_list(2):= 996; -- is a damned tedious way
> v_int_list(3):= 994; -- to initialize a list
> for v_index in 1 to v_int_list.count loop
> v_empno:= v_int_list(v_index);
> select count(*) into v_cnt from emp where emp_no = v_empno;
> -- do something with v_cnt
> end loop;
>end;
>
>
>Suppose the list v_int_list has some dozen elements, then the above way
>of doing it will look really stupid.

Well first of all, if you are initializing the pl/sql table with hard coded values, then you could put then in a table and init the pl/sql table like this

  for c in ( select id, rownum r from init_table ) loop     v_int_list( c.r ) := c.id;
  end loop;

I don't think this looks stupid, do you?

Or if you want/need to hardcode the values then you might want to consider this more fashionable way to assign values using a user defined type. This will work in 8 and up.

SQL> create or replace type int_tab as table of number;   2 /
Type created.

SQL> declare
  2 v_int_list int_tab := int_tab( 998, 996, 994 ); -- single line init   3 begin
  4 for i in 1 .. v_int_list.count loop   5 dbms_output.put_line( v_int_list(i) );   6 end loop;
  7 end;
  8 /

998
996
994

PL/SQL procedure successfully completed.

Or of course, you could really be stylish and select from the table type in your in clause of the select like this ...

SQL> declare
  2 v_int_list int_tab := int_tab( 7369, 7499, 7521, 1234 );   3 begin
  4 for c in ( select *

  5                 from emp
  6                where empno in
  7                    ( select a.column_value
  8                        from the( select cast( v_int_list as int_tab )
  9                                    from dual ) a ) )
 10    loop
 11      dbms_output.put_line( c.empno || ' - ' || c.ename );

 12 end loop;
 13 end;
 14 /

7369 - SMITH
7499 - ALLEN
7521 - WARD

PL/SQL procedure successfully completed.

hope this helps.

chris.

>What I would like to have is some thing much more simple, like :
>
> for v_empno in (998, 996, 994) loop
> select count(*) into v_cnt from emp where emp_no = v_empno;
> -- do something with v_cnt
> end loop;
>
>But of course this does not work. The point is: is there a better pl/sql
>solution than the one first mentioned?

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jul 20 1999 - 16:51:16 CDT