Cursors

Cursors:
======
Cursor is a pointer which points to the memory location where the processed information is stored.PL/SQL uses cursor to retrieve the processed information stored in the memory for manipulation - one row at a time.

Types of Cursors:
============
Simple Cursors
==========

Implicit Cursors
==========

Implicit Cursors are those which implicitly opens,fetches and closes.All DML statements and SELECT statements are implicit cursors. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. its transparent to your application code - no cursor declaration is required. SQL statements with implicit cursors are used for COMMIT, ROLLBACK, INSERT, UPDATE, DELETE and SELECT (single row and multi-row) queries.

Implicit Cursor Attributes
================
sql%found
sql%notfound
sql%isopen

Explicit Cursors
==========
Explicit cursors are those which has to be explicitly opened, fetched and closed.If we use cursor for loops then it implicitly opens,fetches and closes. Requires a PL/SQL Program cursor declaration. Used for multi-row SELECT queries.

Explicit Cursor Attributes
================
%isopen
%isfound
%isnotfound

Ref Cursors
========
Ref cursors are those, in which, select statements are dynamically associated at run time.

Week Cursors
=========
Syntax:
DECLARE
TYPE C1 AS REF CURSOR
TYPE1 C1;
BEGIN
IF A THEN
OPEN TYPE1 FOR SELECT * FROM EMPLOYEE;
IF B THEN
OPEN TYPE1 FOR SELECT * FROM DEPARTMENT;
END;

Strong Cursors
==========
Syntax:
DECLARE
TYPE C1 AS REF CURSOR RETURN EMPLOYEE%ROWTYPE --strong Cursors
TYPE1 C1;
BEGIN
IF A THEN
OPEN TYPE1 FOR SELECT * FROM EMPLOYEE;
IF B THEN
OPEN TYPE1 FOR SELECT * FROM DEPARTMENT;
END;

Parametrised Cursors
==============
In parametrised cursors we can define parameters to the cursor at design time. And while opening the cursor we have to pass the cursors parameters.

Note:

===

There are no explicit cursors for INSERT, UPDATE and DELETE statements. however there is always an implicit cursor for any INSERT, UPDATE or DELETE.

-------------------------------------------------------------------------------------------------