Using Cursor

Purpose:

sometimes there is a need to step through the rows forward or backward and one or more time. By using cursor, applications can scroll or browse up and down through the data as needed.

SQL retrieval operations work with sets of rows knows as result sets. The rows returned are all the rows that match a SQL statement - zero or more of them. Using the simple SELECT statements, there is no way to get the first row, the next row, or the previous 10 rows. That's why Cursor is used.

Shortage:

Cursors and web-based applications cursors are rather useless when it comes to web-based applications. Cursors are designed to persist for the duration of a session between a client application and a server, but this client/server model does not fir in the web application world because the application server is the database client, ot the end user. As such, most Web application developers avoid the use of cursors and re-create the functionality themselves if needed.

Create a cursor:

DECLAIR CustomerCursor CURSOR

FOR

SELECT * FROM Customers

WHERE customerEmail IS NULL

Using a Cursor:

DECLARE TYPE CustomerCursor IS REF CURSOR RETURN Customers%RWOTYPE;

DECLARE CustomerRecord Customers%ROWTYPE

BEGIN

   OPEN CustomerCursor;

   FETCH CustomerCUrsor INTO CustomerRecord;

  CLOSE CustomerCursor;

END;