Please, explain me how to use cursor for loop in oracle.
If I use next code, all is fine.
for rec in (select id, name from students) loop
-- do anything
end loop;
But if I define variable for this sql statement, it doesn't work.
v_sql := 'select id, name from students';
for rec in v_sql loop
-- do anything
end loop;
Error: PLS-00103
To address issues associated with the second approach in your question you need to use
cursor variable and explicit way of opening a cursor and fetching data. It is not
allowed to use cursor variables in the FOR loop:
declare
l_sql varchar2(123); -- variable that contains a query
l_c sys_refcursor; -- cursor variable(weak cursor).
l_res your_table%rowtype; -- variable containing fetching data
begin
l_sql := 'select * from your_table';
-- Open the cursor and fetching data explicitly
-- in the LOOP.
open l_c for l_sql;
loop
fetch l_c into l_res;
exit when l_c%notfound; -- Exit the loop if there is nothing to fetch.
-- process fetched data
end loop;
close l_c; -- close the cursor
end;
try this :
cursor v_sql is
select id, name from students;
for rec in v_sql
loop
-- do anything
end loop;
then no need to open, fetch or close the cursor.
OPEN c FOR string is probably the way to go.You're not executing that sql string anywhere. Simply do this
v_sql := 'select id, name from students';
open cur for v_sql;
for rec in cur loop
-- do anything
end loop;
Or you can do this
cursor cur is select id, name from students;
open cur;
for rec in cur loop
-- do anything
end loop;
Or you can do this
for rec in (select id, name from students) loop
-- do anything
end loop
You have to use Refcursor if you are making the query at runtime. Actually refcursors are pointers to the query they wont take up any space for the rows fetched. Normal Cursors will not work for it.
declare
v_sql varchar2(200);
rec sys_refcursor;
BEGIN
v_sql := 'select id, name from students';
open rec for v_sql
loop
fetch
exit when....
-- do anything
end loop;
