PLSQL For Loop Cursor

For Loop Cursor

For Loop Cursor:- This is the best cursor ever. It is most frequently used cursor. For this cursor no need to follow cursor steps means no need to declare,open fetch and close for this cursor

declare
cursor c1 is select empno,ename,job  from emp
where deptno=&dno;
begin
for i in c1 loop
dbms_output.put_line(i.ename||','||i.empno||','||i.job);
end loop;
end;
/


More Examples for For Loop Cursor:-

begin
for i in (select empno,ename,job  from emp where deptno=&dno) loop
dbms_output.put_line(i.ename||','||i.empno||','||i.job);
end loop;
end;
/
drop table emp_cur;
create table emp_cur as select * from emp where 1=2;
select * from emp_cur;

begin
for i in (select * from emp) loop
insert into emp_cur values (i.empno,i.ename,i.job,i.mgr,i.hiredate,i.sal,i.comm,i.deptno);
end loop;
end;
/

begin
for i in (select * from emp) loop
insert into emp_cur values (i.empno,i.ename,i.job,i.mgr,i.hiredate,i.sal,i.comm,i.deptno);
end loop;
commit;
end;
/


begin
for i in (select * from emp where deptno=&dno) loop
delete from emp_cur where empno=i.empno;
commit;
end loop;
end;
/

begin
for i in (select * from emp) loop
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
commit;
end loop;
end;
/

begin
for i in (select * from emp_cur) loop
if i.deptno=10 then
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
end if;
commit;
end loop;
end;
/

begin
for i in (select * from emp_cur) loop
if i.deptno=10 then
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
elsif i.deptno=20 then
update emp_cur set sal=nvl(i.sal,0)+5000 where empno=i.empno;
end if;
commit;
end loop;
end;
/
begin
execute immediate 'create table xy (a number)';
end;
/
select * from emp_cur ;


declare
cursor c1 is select empno,ename,job  from emp
where deptno=&dno;
begin
for i in c1 loop
dbms_output.put_line(i.ename||','||i.empno||','||i.job);
end loop;
end;
/


begin
for i in (select empno,ename,job  from emp where deptno=&dno) loop
dbms_output.put_line(i.ename||','||i.empno||','||i.job);
end loop;
end;
/

create table emp_cur as select * from emp where 1=2;
select * from emp_cur;

begin
for i in (select * from emp) loop
insert into emp_cur values (i.empno,i.ename,i.job,i.mgr,i.hiredate,i.sal,i.comm,i.deptno);
end loop;
end;
/

begin
for i in (select * from emp) loop
insert into emp_cur values (i.empno,i.ename,i.job,i.mgr,i.hiredate,i.sal,i.comm,i.deptno);
end loop;
commit;
end;
/


begin
for i in (select * from emp) loop
delete from emp_cur where empno=i.empno;
commit;
end loop;
end;
/

begin
for i in (select * from emp) loop
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
commit;
end loop;
end;
/

begin
for i in (select * from emp_cur) loop
if i.deptno=10 then
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
end if;
commit;
end loop;
end;
/

begin
for i in (select * from emp_cur) loop
if i.deptno=10 then
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
elsif i.deptno=20 then
update emp_cur set sal=nvl(i.sal,0)+5000 where empno=i.empno;
end if;
commit;
end loop;
end;
/


declare
cursor c1 is select sal  from emp;
vsal emp.sal%type;
begin
open c1;
loop
fetch c1 into vsal;
exit when c1%rowcount=5;
dbms_output.put_line(vsal);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/