Chapter 16 Cursors and Exceptions

1
Chapter 16Cursors and Exceptions
2
Chapter Objectives

• Determine when an explicit cursor is required
• Declare, open, and close an explicit cursor
• Fetch data from an explicit cursor
• Identify attributes associated with a cursor

3
Chapter Objectives

• Use a cursor FOR loop to retrieve data from a
  cursor
• Declare a cursor in the subquery of a cursor FOR
  loop
• Evaluate BOOLEAN conditions combined with logical
  operators
• Identify the purpose of the exception-handling
  section of a PL/SQL block

4
Chapter Objectives

• Trap predefined exceptions in a PL/SQL block
• Trap user-defined exceptions in a PL/SQL block

5
Cursor

• An area in memory that holds the values currently
  being processed
• Must be explicitly declared when retrieving more
  than one row

6
Opening an Explicit Cursor

• When opened, the memory is allocated, the SELECT
  statement is executed, and necessary data is
  loaded into the cursor

7
Closing the Cursor
8
Fetching Data from the Cursor

• Data is retrieved from an explicit cursor using
  the FETCH command
• Assigns the values to previously declared
  variables

9
Cursor Attributes
10
Cursor Example
Declaring a cursor
Opening a cursor
Fetching data
Closing a cursor
11
Cursor FOR Loop

• Automatically opens and closes a cursor, and
  fetchs data from a cursor

12
Example Cursor FOR Loop
13
ROWTYPE Attribute

• Specifies that a record will have the same
  structure as the row being retrieved

14
Logic Table

• Determines how a statement will be evaluated
  when conditions are joined with logical operators

15
Exception Handling

• Exception signal that an error has occurred
• Does not include syntax errors
• Common examples NO_DATA_FOUND, TOO_MANY_ROWS,
  ZERO_DIVIDE

16
Exception-Handling Section Syntax
17
Exception Example
18
User-Defined Exception Handling

• Name of exception must be declared with datatype
  EXCEPTION
• Declared exception must be associated with Oracle
  server error number using PRAGMA EXCEPTION_INIT
  statement
• Must be included in the blocks
  exception-handling section

19
Syntax for the PRAGMA EXCEPTION_INIT Statement
20
Example of Trapping a User-Defined Exception