Create array in PL/SQL to store value form database-CodePudding

I want to create array that store more than one value form one column in table. I use code below

declare
type arr is table of list.price%type index by pls_integer;
prices arr;

begin
for i in 1 .. 10 loop
select price into prices form list
where doctore_id = :list.doctore_id;
end loop;
end;

that the list is name of table and name of the data block and price is name of column

but I get the error: Error 487 at line 2, column 23 invalid reference to variable 'list'

I use database oracle and form 10g
I check the spilling of table name and column and make sure that I connect to database

CodePudding user response：

You could try:

declare
type arr is table of list.price%type index by pls_integer;
prices arr;

begin
  for i in 1 .. 10 loop
   select price into prices(i) from list
   where doctore_id = :list.doctore_id;
  end loop;
end;

It will work only if select returns only one row. Unless you change record in block, you will obtain ten copies of price.

CodePudding user response：

declare
    type arr is table of list.price%type index by pls_integer;
    prices arr;
begin
    select price bulk collect into prices form list where doctore_id =:list.doctore_id and rownum< 11;
end;