We will explain different types of Cursors with simple examples. Previous post we have explained detailed about different types of cursors.
In case you want to read the previous post – Oracle Cursor – Basics
All examples are tested in Oracle 11g Release 2 version.
I used the classic DEPT and EMP tables to illustrate the examples
SQL> desc dept;
Name Type
—————————————– ——– ————–
DEPTNO NOT NULL NUMBER(38)
DEPTNAME VARCHAR2(20)
LOCATION VARCHAR2(30)
DEPTNO | DEPTNAME | Location |
1 | Sales | Dubai |
2 | Marketing | London |
3 | IT | Paris |
4 | Logistics | Tokyo |
5 | Finance | Singapore |
SQL> desc emp;
Name Type
—————————————– ——– ————–
EMPNO NOT NULL NUMBER(38)
EMPNAME VARCHAR2(20)
SALARY NUMBER
DEPTNO NUMBER(38)
EMPNO | EMPNAME | SALARY | DEPTNO |
10 | Bill | 12000 | 5 |
11 | Solomon | 10000 | 5 |
12 | Susan | 10000 | 5 |
13 | Wendy | 9000 | 1 |
14 | Benjamin | 7500 | 1 |
15 | Tom | 7600 | 1 |
16 | Henry | 8500 | 2 |
17 | Robert | 9500 | 2 |
18 | Paul | 7700 | 2 |
1. Single record and single column
declare
v_ename varchar2(50);
Cursor Cur_ename is select empname from emp where empno = 10;
begin
Open Cur_ename;
Fetch Cur_ename into v_ename;
dbms_output.put_line(‘Employee Name –>’|| v_ename);
Close Cur_ename;
end;
/
Output would be
Employee Name –>Bill
In general case cursors are intended for multi-record queries. Just given above example for illustration purpose only.
2. Multiple record and single column
declare
v_ename varchar2(20);
Cursor Cur_ename is select empname from emp;
begin
Open Cur_ename;
loop
Fetch Cur_ename into v_ename;
exit when Cur_ename%notfound;
dbms_output.put_line(‘Employee Name –>’|| v_ename);
end loop;
Close Cur_ename;
end;
/
Output would be
Employee Name –>Bill
Employee Name –>Solomon
Employee Name –>Susan
Employee Name –>Wendy
Employee Name –>Benjamin
Employee Name –>Tom
Employee Name –>Henry
Employee Name –>Robert
Employee Name –>Paul
This is a classic example. Here query returns multiple records having one column. Variable v_ename used to store the returned column
3. Multiple record and multiple column – Same table
declare
v_emprec emp%rowtype;
Cursor Cur_ename is select * from emp;
begin
Open Cur_ename;
loop
Fetch Cur_ename into v_emprec;
exit when Cur_ename%notfound;
dbms_output.put_line(‘Name –>’|| v_emprec.empname || ‘———-Salary –>’ || v_emprec.salary );
end loop;
Close Cur_ename;
end;
Output would be
Name –>Bill———-Salary –>12000
Name –>Solomon———-Salary –>10000
Name –>Susan———-Salary –>10000
Name –>Wendy———-Salary –>9000
Name –>Benjamin———-Salary –>7500
Name –>Tom———-Salary –>7600
Name –>Henry———-Salary –>8500
Name –>Robert———-Salary –>9500
Name –>Paul———-Salary –>7700
Here we are fetching all columns from EMP table and storing in cursor variable v_emprec. It is a row-type variable which used to hold all column values from EMP table.
4. Multiple record and multiple column from different tables
Below example we are trying to fetch department names and employee names.
declare
Cursor Cur_ename is select empname,deptname from emp e,dept d
where d.deptno = e.deptno;
v_rec Cur_ename%rowtype;
begin
Open Cur_ename;
loop
Fetch Cur_ename into v_rec;
exit when Cur_ename%notfound;
dbms_output.put_line(‘Name –>’|| v_rec.empname || ‘———-deptname –>’ || v_rec.deptname );
end loop;
Close Cur_ename;
end;
Here we can use different style of variables
1)
v_empname emp.empname%type;
v_deptname dept.deptname%type;
2)
v_rec Cur_ename%rowtype;
I used 2) because in future if I want to add one more column in SQL no need to add another variable since v_rec automatically adjusts the column fetches.
5. Nested Cursor example
declare
v_deptno emp.deptno%type;
Cursor Cur_dept is select * from dept;
Cursor Cur_emp is select * from emp e where e.deptno = v_deptno;
v_deptrec dept%rowtype;
v_emprec emp%rowtype;
begin
Open Cur_dept;
loop
Fetch Cur_dept into v_deptrec;
exit when Cur_dept%notfound;
v_deptno := v_deptrec.deptno;
dbms_output.put_line(‘Outerloop’);
— Inner loop start here
Open Cur_emp;
loop
fetch Cur_emp into v_emprec;
exit when Cur_emp%notfound;
dbms_output.put_line(‘Deptname –> ‘|| v_deptrec.deptname || ‘——Empname — > ‘|| v_emprec.empname);
end loop;
close Cur_emp;
— End of Inner Loop
end loop;
Close Cur_dept;
end;
Output would be
Outerloop
Deptname –> Sales——Empname –> Wendy
Deptname –> Sales——Empname –> Benjamin
Deptname –> Sales——Empname –> Tom
Outerloop
Deptname –> Marketing——Empname –> Henry
Deptname –> Marketing——Empname –> Robert
Deptname –> Marketing——Empname –> Paul
Outerloop
Outerloop
Outerloop
Deptname –> Finance——Empname –> Bill
Deptname –> Finance——Empname –> Solomon
Deptname –> Finance——Empname –> Susan
Here two different cursors, one for finding all departments and other for finding employees associated with it. Outerloop executed 5 times and inner loop executed 5 times. ( Note:- Only 3 departments has employees, so total 6 employees shown in the output )
When writing nested cursor make sure
1. Properly format the code
2. Properly apply the comments in each section
3. Try to provide log statements
—
The above examples covered all basic cursors and still there are many complex cursor examples need to explain and will do in another post.
Did you find above post useful ? If yes please comment.
This is sooooo good
Thanks
i need a print of how many rows are fetched when given sql statements
declare
Cursor Cur_ename is select empname,deptname from emp e,dept d
where d.deptno = e.deptno;
v_rec Cur_ename%rowtype;
begin
Open Cur_ename;
loop
Fetch Cur_ename into v_rec;
exit when Cur_ename%notfound;
dbms_output.put_line(‘Name –>’|| v_rec.empname || ‘———-deptname –>’ || v_rec.deptname );
end loop;
Close Cur_ename;
end;
i need cout of fetching records
Thanks
dbms_output.put_line(‘Total fetched data->’ || Cur_ename%rowcount );
Thanks yaar.. awesome way of explaining.. keep posting plz
How to count query returned row in below program??
declare
lv_first_name employees.first_name%type;
lv_department_name departments.department_name%type;
Cursor Cur_ename is select e.first_name,d.department_name into lv_first_name,lv_department_name
from employees e,departments d
where e.department_id=d.department_id;
begin
Open Cur_ename;
loop
Fetch Cur_ename into lv_first_name,lv_department_name;
dbms_output.put_line(‘Employee name’ || ‘ ‘||lv_first_name ||’ ‘||’Department in’ ||’ ‘|| lv_department_name);
exit when Cur_ename%notfound;
end loop;
Close Cur_ename;
end;
/
Thanks
you need do one thing here…by using sql%rowcount we can count the how many rows are returned by the query..
like that below..
dbms_output.put_line(sql%rowcount||’Rows are count by the returned query’);
Very nice
sql%rowcount does not work with cursors.
you must count the records/rows caught by cursors.
still,following is a modification of nested cursor example code where total records are counted and displayed (what you asked for) :
declare
v_deptno scott.emp.deptno%type;
c number(10):=0;
Cursor Cur_dept is select * from scott.dept;
Cursor Cur_emp is select * from scott.emp e where e.deptno = v_deptno;
v_deptrec scott.dept%rowtype;
v_emprec scott.emp%rowtype;
begin
Open Cur_dept;
loop
Fetch Cur_dept into v_deptrec;
exit when Cur_dept%notfound;
v_deptno := v_deptrec.deptno;
dbms_output.put_line(‘Outerloop’||’ ‘||’Dept_No–>’|| v_deptno);
–Inner loop start here
Open Cur_emp;
loop
fetch Cur_emp into v_emprec;
exit when Cur_emp%notfound;
dbms_output.put_line(‘Deptname –> ‘|| v_deptrec.dname || ‘——Empname — > ‘|| v_emprec.ename);
c:=c+1;
end loop;
close Cur_emp;
–End of Inner Loop
end loop;
dbms_output.put_line(‘Total Records processed’||’ ‘||c);
Close Cur_dept;
end;