Cursors,parameter cursors

Cursors: A cursor is like a pointer in c. It holds the address location instead of item.

The cursor is of 2 types

a) implicit cursor
b) explicit cursor

• Implicit cursors:

Declared and managed by pl/sql for all dml and pl/sql select statements.

• Explicit cursors:

For queries return more than one row,explicit cursors are declared and managed by the programmer and manipulated through specific statements in the block's executable actions.

Explicit cursor attributes:

Attribute	Type	Description
%ISOPEN	Boolean	Evaluates to true if the cursor is open
%NOTFOUND	Boolean	Evaluates to true if the recent fetch does not return a row
%FOUND	Boolean	Evaluates to true if the recent fetch returns a row
%ROWCOUNT	Number	Evaluates to the total number of rows

ex-

Begin

open emp_cursor;

loop

fetch emp_cursor into empno,ename;

EXIT WHEN emp_cursor%ROWCOUNT > 10 OR  emp_cursor%NOTFOUND;

end loop;

close emp_cursor;

end;

PARAMETER CURSORS: 

We can pass values inside the cursor by using a parameterized cursors.

ex-

set serveroutput on

declare

dept_id number;

lname varchar2(15);

 cursor emp_cursor ( deptno NUMBER ) IS

select employee_id,last_name

from employees

where department_id=deptno;

BEGIN

open emp_cursor(10);

...

close emp_cursor;

open emp_cursor(20);

...

close emp_cursor;

...

end;