cursor oracle 10g

What is corsors in oracle
-----------------------------
A cursors is a pointer used to fetch rows from a result set. One can think of a cursor as a data structure that describes the results returned from a SQL SELECT statement. One of the variables in this structure is a pointer to the next record to be fetched from the query results.

Two types of classification - I and II
--------------------------------------

I. Implicit and Explict

a. Implicit : More fast and less coding effort.

Will never raise INVALID_CURSOR error

Cannot be opened outside the statement

Raises NO_DATA_FOUND and TOO_MANY_ROWS exceptions (eg: select )

Implicit cursor returning more than one record? FOR LOOP cursors.

b. Explicit : 2 network round trips. Store data first then retrieve data. More programmatic control.

Programmer could open; fetch data, close, check attributes etc.

II Static and Dynamic

a. Static : Normal cursor (implicit or explicit)

b. Dynamic : Ref cursor: Cursor created only when it is opened.

Could be declared once and defined many times in different procedures.

Ref Cursors can have Record/s as return types. So could be used for returning

data to other languages like Java, C++ etc

II.b) Ref cursor - Two types

Strong : Ref cursor with a specified return type.

Weak : No return type specified.

3. Cursor attributes

%FOUND - records fetched successfully

%NOTFOUND - no records fetched

%ROWCOUNT - Number of records fetched

%ISOPEN - returns TRUE if cursor is open

Could be used for implicit and explicit cursors.

Eg:

Implicit:
---------

select * from emp -- also for delete operations

If SQL%FOUND then

v_count:= SQL%ROWCOUNT

end if;



Explicit:
--------------

open c1; -- cursor c1 is select

fetch <>

exit when c1%NOTFOUND



Eg: Explicit cursor.

Declare

Cursor cur1 is

select ename,empno,sal from emp
where empno between 9000 and 1000
and sal<50000
and deptno=50
begin
open cur1;
fetch cur1 into v_ename,v_empno,v_sal;
exit when cur1%notfound;
---
close cur1;
end;



Key words :

cursor oracle 10g
ref cursor in oracle
parameterized cursor in oracle
dynamic cursor in oracle
create cursor in oracle
cursor in oracle stored procedure
fetch cursor in oracle
cursor sql oracle