Cursors in PL/SQL

1. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur1 IS
v_ename emp.ename%type;
v_sal EMP.SAL%type;
cursor mycsr is select ename,sal from emp;
 BEGIN
  open mycsr;
  fetch mycsr into v_ename,v_sal;
  dbms_output.put_line(v_ename||', '||v_sal);
  close mycsr;
     EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur1;
/

2. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.cur_in_proc(a emp.deptno%type) IS
 cursor mycsr is select * from emp where deptno=a;
 b emp%rowtype;
 BEGIN
 open mycsr;
 loop
 fetch mycsr into b;
 if mycsr%FOUND then
 dbms_output.put_line(b.empno||', '||b.deptno||', '||b.ename);
 else exit;
 end if;
 end loop;
 close mycsr;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END cur_in_proc;
/

3. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur2 IS
 v_ename EMP.ENAME%type;
 v_sal EMP.SAL%type;
 cursor mycsr is select ename,sal from emp;
 BEGIN
  open mycsr;
  loop
  fetch mycsr into v_ename, v_sal;
  dbms_output.put_line(v_ename||''||v_sal);
  end loop;
  close mycsr;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur2;
/

4.Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur3 IS
cursor mycsr is select * from emp;
v_mycsr_data mycsr%rowtype;
begin
open mycsr;
loop
fetch mycsr into v_mycsr_data;
dbms_output.put_line('Record Number is '||mycsr%ROWCOUNT||' '||v_mycsr_data.ename);
exit when mycsr%NOTFOUND;
end loop;
close mycsr;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur3;
/

5. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur4 IS
cursor mycsr is select * from emp;
v_mycsr mycsr%ROWTYPE;
begin
open mycsr;
dbms_output.put_line('The details of an employee in our organization as follows');
dbms_output.put_line('----------------------------------------');
 dbms_output.put_line('Name  '||'     Designation  '||'         Basic Salaray'||'            Annual Salary');
 dbms_output.put_line('---------------------------------------');
 loop
 fetch mycsr into v_mycsr;
 exit when mycsr%NOTFOUND;
 dbms_output.put_line(v_mycsr.ename||'      '||v_mycsr.job||'                     '||v_mycsr.sal||'                   '||v_mycsr.sal*12);
 end loop;
 close mycsr;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur4;
/

6. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur5 IS
mycsr_cnt number ;
cursor mycsr is select * from emp;
v_mycsr_data mycsr%ROWTYPE;
begin
open mycsr;
loop
fetch mycsr into  v_mycsr_data;
exit when mycsr%NOTFOUND;
mycsr_cnt:= mycsr%ROWCOUNT;
if v_mycsr_data.job='SALESMAN' then
dbms_output.put_line(v_mycsr_data.ename||'     '||v_mycsr_data.job||'    Eligible for commission.');
else
dbms_output.put_line('Not Eligible for commission.');
end if;
end loop;
close mycsr;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur5;
/

7. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cursor_use2 IS
cursor deptcsr is select deptno,dname from dept order by deptno;
cursor empcsr(v_deptno emp.deptno%type) is select ename,job,hiredate,sal from emp where deptno=v_deptno;
v_curdeptno DEPT.DEPTNO%type;
v_curdname DEPT.DNAME%type;
v_ename EMP.ENAME%type;
v_job EMP.JOB%type;
v_mgr EMP.MGR%type;
v_hiredate EMP.HIREDATE%type;
v_sal EMP.SAL%type;
begin
open deptcsr;
loop
fetch deptcsr into v_curdeptno,v_curdname;
exit when deptcsr%NOTFOUND;
dbms_output.put_line('Department number '||v_curdeptno||' department name '||v_curdname);
if empcsr%ISOPEN then
close empcsr;
end if;
open empcsr(v_curdeptno);
loop
fetch empcsr into v_ename,v_job,v_hiredate,v_sal;
if empcsr%FOUND then
dbms_output.put_line(v_ename||' '||v_job||' '||v_hiredate||' '||v_sal);
else
dbms_output.put_line('End of Employees in department '||v_curdeptno);
end if;
exit when empcsr%NOTFOUND;
end loop;
if empcsr%ISOPEN then
close empcsr;
end if;
end loop;
if empcsr%ISOPEN then
close empcsr;
end if;
close deptcsr;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cursor_use2;
/

8. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur_for1 IS
cursor mycsr is select * from emp;
v_mycsr mycsr%ROWTYPE;
begin
for v_mycsr in mycsr
loop
dbms_output.put_line('=========='||v_mycsr.ename);
end loop;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur_for1;
/

9. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur_for2 IS
cursor mycsr is select * from emp;
begin
for cur_index in mycsr
loop
dbms_output.put_line('Record number is '||mycsr%ROWCOUNT||' '||cur_index.ename);
end loop;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur_for2;
/

10. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur_for3 IS
cursor mycsr is select * from emp order by sal desc;
begin
for v_cur_ind in mycsr
loop
dbms_output.put_line('Records number is '||RPAD(mycsr%ROWCOUNT,10)||' '||RPAD(v_cur_ind.ename,10)||' with salary is '||RPAD(v_cur_ind.sal,10));
exit when mycsr%ROWCOUNT=5;
end loop;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur_for3;
/

11. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur_for_subqry1 IS
v_dname dept.dname%type;
v_loc dept.loc%type;
begin
for emp_ind in (select dname, deptno from dept order by deptno)
loop
select dname, loc into v_dname, v_loc from dept where deptno=emp_ind.deptno;
dbms_output.put_line(emp_ind.deptno||'  '||emp_ind.dname||'  '||v_dname||'  '||v_loc);
end loop;
   EXCEPTION
   when TOO_MANY_ROWS then
   dbms_output.put_line('Retrieving too many rows');
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur_for_subqry1;
/

12. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur_for_subqry2 IS
begin
for v_ind in (select * from dept)
loop
dbms_output.put_line('Department '||v_ind.dname||' and number '||v_ind.deptno||' located at '||v_ind.loc);
end loop;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur_for_subqry2;
/

13. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur_updt_of_and_curent_of IS
cursor mycsr is select sal from emp where deptno=30 for UPDATE OF sal NOWAIT;
begin
for mycsr_ind in mycsr
loop
 update emp set sal=sal+(mycsr_ind.sal*1.10) where CURRENT OF mycsr;
end loop;
commit;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur_updt_of_and_curent_of;
/

14. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur_while1 IS
cursor mycsr is select * from emp;
v_mycsr mycsr%ROWTYPE;
begin
open mycsr;
fetch mycsr into v_mycsr;
while mycsr%FOUND
loop
dbms_output.put_line('=============='||v_mycsr.ename);
fetch mycsr into v_mycsr;
end loop;
close mycsr;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur_while1;
/

15. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.proc_cur_while2 IS
cursor mycsr is select * from emp;
v_mycsr mycsr%rowtype;
begin
open mycsr;
fetch mycsr into v_mycsr;
while mycsr%FOUND
loop
dbms_output.put_line('Record number is '||mycsr%ROWCOUNT||' '||v_mycsr.ename);
fetch mycsr into v_mycsr;
end loop;
close mycsr;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_cur_while2;
/

16. Cursor in Procedure

CREATE OR REPLACE PROCEDURE SCOTT.Real_cursor_use1 IS
v_number number(2):=3;
v_ename emp.ename%type;
v_sal EMP.SAL%type;
cursor mycsr is select ename,sal from emp;
begin
 delete bigheads;
 commit;
 open mycsr;
 fetch mycsr into v_ename,v_sal;
 while mycsr%ROWCOUNT<=v_number and mycsr%FOUND
 loop
 insert into bigheads(ename, salary) values(v_ename, v_sal);
 fetch mycsr into v_ename,v_sal;
 end loop;
 close mycsr;
 commit;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END Real_cursor_use1;
/