Re: How to use array concept in plsql

On Wed, 29 Jul 1998 18:23:06 GMT, maadhavan_at_my-dejanews.com wrote:

>Hi I have written a stored procedure which retrieves just one row from the
>database when i run it. If i had to retrieve more than one row at the time
>please suggest whether i should use PLsql tables data type to achieve that or
>is there any other way. If anybody can give me sample syntax it will be nice
>of them. Advance thanks maadhavan

I can suggest two ways.

1. use a cursor loop

for c1 in ( select blah, blah2 from foobar where column1 = 'HELLO' ) loop

• do stuff like dbms_output.put_line( c1.blah ); dbms_output.put_line( c1.blah2 ); end loop;

Everytime through the loop, c1 represents another record. This works great as long as you can do all your processing for a single row within the loop. If you need to save all the values and process them later then...

2. Use PL/SQL tables

• define a PL/SQL table type

  type myTableType is table of varchar2(2000) index by binary_integer;

• define variables of that type

  myTable myTableType;
  myTable2 myTableType;

• populate the table

i := 1;
for c1 in ( select blah, blah2 from foobar where column1 = 'HELLO' ) loop

   mytable(i) := c1.blah;
   mytable2(i) := c1.blah2;
    i := i + 1;
end loop;

Now you have two PL/SQL tables with all your data.

Hope this helps.

chris.

>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Wed Jul 29 1998 - 15:31:27 CDT