Module 07: Using Explicit Cursors

Using Explicit Cursors

01. What are Explicit cursors?

Declared and managed by the Programmer

02. Write down the explicit cursor functions.

Explicit cursor functions:

a. Can perform row-by-row processing beyond the first row returned by a query.

b. Keep track of the row that is currently being processed.

c. Enable the programmer to manually control explicit cursors in the PL/SQL block.

03. Write down the lifecycle for Controlling Explicit Cursors.

04. Write down about Cursor FOR Loops.

FOR Loops:

a. The cursor FOR loop is a shortcut to process explicit cursors.

b. Implicit open, fetch, exit, and close occur.

c. The record is implicitly declared.

05. Write down the guidelines for using Cursor FORLoops.

Guidelines:

a. Do not declare the record that controls the loop; it is declared implicitly.

b. Test the cursor attributes during the loop if required.

c. Supply the parameters for a cursor, if required, in parentheses following the cursor name in the FOR statement.

06. Write down the Cursor Attributes for Explicit Cursors.

Use explicit cursor attributes to obtain status information about a cursor.

07. What is Cursors with Parameters?

Parameters:

a. Pass parameter values to a cursor when the cursor is opened and the query is executed.

b. Open an explicit cursor several times with a different active set each time.

08. Write down the use of FORUPDATE Clause.

FORUPDATE Clause:

a. Use explicit locking to deny access to other sessions for the duration of a transaction.

b. Lock the rows before the update or delete.

09. Write down the use of WHERECURRENTOF Clause.

WHERECURRENTOF Clause:

a. Use cursors to update or delete the current row.

b. Include the FOR UPDATE clause in the cursor query to first lock the rows.

c. Use the WHERE CURRENT OF clause to reference the current row from an explicit cursor.