2009-07-09

Cursor and for loop in PL/SQL

In PL/SQL, we can use method below to query data from database:
select [column] into [variable]
from [table];


However method above can only retrieve one single row of data. To retrieve data more than 1 row, we can use cursor instead. First we need to declare the cursor at the declaration section:
cursor [cursor name] is [select statement];

For example:
cursor c is select id, name
from emp;

Then at the operation section, we can use a special for loop to retrieve the data:
for [row variable] in [cursor name] loop
do something;
end loop;

For example:
for r in c loop
insert into temp values (r.id, r.name);
end loop;

0 comments: