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
Everything Changes
2 weeks ago