Thomas Thomas - 4 months ago 13
SQL Question

Toad: how to loop a resultset of select

I'm using Toad and there is a table named

MyTable
, it has a column named
INFO
:

INFO

abcd

efgh

ijkl


What I need is to take the elements of
INFO
one by one and do tasks. So I think I need something like below:


foreach (select INFO from MyTable)
print
end


I tried to Google and it seems that I should use CURSOR. So I tried like this:


DEF msg varchar2(15);

cursor cr is
select info from mytable;

begin
OPEN cr;
loop
FETCH cr into msg;
exit when cr%NOTFOUND;
-- do job
end loop;
CLOSE cr;
end;


But I got an error:



cursor cr is

Error at line 3

ORA-00900: invalid SQL statement

Script Terminated on line 3.

Answer

Clearly you want to execute a PL/SQL block, but DEF is not a part of PL/SQL. Try to execute following block:

declare
msg varchar2(15);
cursor cr is
    select info from mytable;
begin
  OPEN cr;
  loop
    FETCH cr into msg;
    exit when cr%NOTFOUND;
    -- do job
  end loop;
  CLOSE cr;
end;

You can also do the same using cursor for loop statement

begin
  for rec in (
    select info from mytable
  ) loop
      -- do job (you can reference info by using rec.info )
  end loop;
end;