Transcript Database Cursor

Database Cursor

Definition
Cursor is a control structure for the
successive traversal of records in a result
set. Cursor is a record pointer in a
database.
CURSOR
DATABASE
Database Cursor

Use For
A cursor is used for processing individual
rows returned by the database system for a
query. And use for handle a result set
Working with Cursor
Declare
 Open
 Fetch
 Close
 Free

Declare

This instruction associates a database cursor
with an SQL statement in the current
connection.
Cursor Type
Forward only cursors (non-scrollable )
 Scrollable cursors
 Hold cursors

Forward only cursor
Sequential cursor
 Forward only cursor can fetch only the next
row in sequence from the result set.
 On each execution of the FETCH statement,
the database server returns the contents of
the current row and locates the next row in
the result set.

Forward only cursor

Syntax
DECLARE cursor_name CURSOR FOR SELECT ... FROM ...

Example
DECLARE cursor1 CURSOR FOR SELECT * FROM customer_Table
Scrollable cursor


Scrollable cursor can fetch rows of the result
set in any sequence.
You can fetch the first, last, or any
intermediate rows of the result set as well as
fetch rows repeatedly without having to close
and reopen the cursor.
Scrollable cursor

Syntax
DECLARE cursor_name SCROLL CURSOR FOR SELECT ... FROM ...

Example
DECLARE cursor1 SCROLL CURSOR FOR SELECT * FROM customer
Hold cursor



Ordinarily, all cursors close at the end of a
transaction (COMMIT or ROLLBACK).
A hold cursor does not close. It remains open
after a transaction ends.
A hold cursor can be either a sequential
cursor or a scrollable cursor.
Hold cursor

Syntax
DECLARE cursor_name CURSOR WITH HOLD FOR SELECT ... FROM ...

Example
DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM customer
Open

Executes the SQL statement associated with
a database cursor declared in the same
connection.
Open Syntax
OPEN cursor_name
Fetch

Moves a cursor to a new row in the
corresponding result set and retrieves the row
values into fetch buffers.
Fetch Syntax

Forward only cursor
FETCH cursor_name INTO variable [,variable2…]

Scrollable cursor
FETCH [ Direction ] cursor_name INTO variable [,variable2…]
Direction for Scrollable cursor

NEXT
-> retrieves the next row in the result set

PREVIOUS,PRIOR
-> retrieves the previous row in the result set

CURRENT
-> retrieves the current row in the result set

FIRST
-> retrieves the first row in the result set.

LAST
-> retrieves the last row in the result set.

ABSOLUTE position
-> retrieves the row at position in the result set

RELATIVE offset
-> moves offset rows in the result set and returns the row at the
current position
Close

Closes a database cursor and frees resources
allocated on the database server for the
result set.
Close Syntax
CLOSE cursor_name
Free

This instruction releases resources allocated
to the database cursor with the DECLARE
instruction.
Free Syntax
FREE cursor_name
Cursor Attributes

CURSOR_NAME%ROWCOUNT
-> Return the number of row fetched so far

CURSOR_NAME%FOUND
-> Return TRUE if the last FETCH returned a row
-> Return FALSE if the last FETCH did not return

CURSOR_NAME%NOTFOUND
-> Return FALSE if the last FETCH returned a row
-> Return TRUE if the last FETCH did not return

CURSOR_NAME%ISOPEN
-> Return TRUE if the cursor is open
-> Return FALSE if the cursor is close
Position update/delete

Cursors can not only be used to fetch
data from the DBMS into an application
but also to identify a row in a table to
be updated or deleted
Position update/delete

Syntax
UPDATE table_name
SET ...
WHERE CURRENT OF cursor_name
DELETE table_name
SET ...
WHERE CURRENT OF cursor_name
The end…

Thank you
Member
Puris
 Anawat
 Manthana
 Thunyaluk

Chalermpug
Boochabuppajarn
Atipremanon
Kumnurdchati
5088055
5088009
5088016
5088129