Tag: Cursor example

Oracle Cursor examples

~ sqlandplsql ~ 10 Comments

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.

Advertisements