Oracle Cursor basics

Cursor

Cursor is one of the fundamental feature of Oracle. Oracle cursor is a memory handle to the SQL area. Cursors are associated with SQL and variables. Oracle holds all relevant information about SQL and variables in the context area.

Example : – Cursor Cur_Emp is Select * from EMP where deptno =10;

Oracle Cursor Classification
Oracle Cursor Classification

Mainly there are two type of cursors. Implicit Cursors and Explicit Cursors

Implicit Cursor

Implicit Cursor is defined and controlled by Oracle Internally. It is easy use.

Example :-

declare
v_empname varchar2(50);
begin
select empname into v_empname from emp where empno = 10;
end;

select query used in above PL/SQL block is an implicit cursor

Explicit Cursor

Explicit Cursors are defined and controlled programatically. There are two types of explicit cursors, Static Cursors and Ref Cursors.

In this post we will discuss more abour Static Cursors

Static Cursors :- Defining and associating before run time. SQL is constructed and associated prior to execution or run time.

Example :-

declare
v_empname varchar2(50);
Cursor Cur_ename is select empname from emp where empno = 10;
begin
Open Cur_ename;
Fetch Cur_ename into v_empname;
dbms_output.put_line(‘Employee Name –>’|| v_empname);
Close Cur_ename;
end;

A cursor has mainly 4 stages. Declaration, Opening, Fetching and Closing.

Declaring Cursor

Generally Cursors all declared at declare section of the PL/SQL block or sub program and it must be associated with an SQL.

declare
Cursor Cur_ename is select empname  from emp where empno = 10;
begin

………….

Here Cur_ename is the name of the cursor and “select ename into v_ename from emp where empno = 10″is the SQL associated with it.

Opening Cursor

After declaration cursor need to open. OPEN keyword use to open a cursor

OPEN Cur_ename;

Fetching Cursor

Fetching cursor into variables. Note that number of columns in the SQL and number of variables must match and also variables must be compatible.

Fetch Cur_ename into v_ename;

In above case SQL has only one column which will return a varchar2. So we have declared a variable v_ename as varchar2.

Closing Cursor

CLOSE command will close the opened cursor.

Close Cur_ename;

Explicit Cursor Attributes

Explicit cursor has 4 attributes. %FOUND, %NOTFOUND, %ISOPEN and %ROWCOUNT.

%FOUND – Using to check the cursor is success or not. Returns true or false. If cursor returns any row then %FOUND will be true. Only used after opening the cursor.

%NOTFOUND – Using to check the cursor is not a success or not. Returns true or false. If cursor returns no rows then %NOTFOUND will be true. Only used after opening the cursor.

%ISOPEN – This is self-explanatory. Used to check whether cursor is open or not. Returns true or false.

%ROWCOUNT – To find the number of rows the cursor returned. Return as an integer.

( Note that SQL cursor has one more attribute %BULK_ROWCOUNT which using the bulk collect functionality. We will discuss this in another section)

Examples:-

1.  Cursor example with single record

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 the above example Cur_ename is associated with SQL which will return only one record. So only one fetch is required here.

2.  Cursor example with multiple record

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

In general

OPEN cursor
Fetch first record and print the variables
Fetch second record and print the variable
Fetch third record and print the variable
….
Fetch last record and print the variable
CLOSE cursor

Also read –

Cursor Examples
Ref Cursors 
Triggers
Bind Variables

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.

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