Oracle Cursor examples

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

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

10 thoughts on “Oracle Cursor examples”

  1. 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

  2. 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;
    /

    1. 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’);

  3. 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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s