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.
