Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

PL/SQL Cursors
Oracle Tips by Burleson
 

A cursor is a reference to a private SQL area inside Oracle. Figure 2.7 illustrates the functionality of a cursor.

There are two types of cursors: implicit and explicit. PL/SQL also provides a CURSOR FOR loop that makes the process of looping through the cursor data simpler.

Explicit Cursors

An explicit cursor is defined in the DECLARE section of a PL/SQL block, as shown in Listing 2.12.

Listing 2.12 The declaration of an explicit cursor.

DECLARE
   CURSOR Students_cur
   IS
   SELECT last_name, first_name, middle_name, gpa
   FROM   STUDENTS
   WHERE  gpa > 3.0;

The use of explicit cursors allows PL/SQL programs to handle sets of rows, rather than being forced to query a single row for each operation. Each row that meets the criteria defined by the cursorís WHERE clause can be processed individually.

Several PL/SQL statements are used to deal with explicit cursors:

  • OPENóParses a cursor and prepares the result set for retrieval. If the cursor is already open, an error will occur.

  • CLOSEóDisables the cursor. If the specified cursor hasnít been opened, an error will result.

  • FETCHóRetrieves the next row of the result set into variables for processing inside the PL/SQL block. If the specified cursor hasnít been opened, an error will occur.

A cursor is not an array; once a row has been bypassed using the FETCH statement, it is impossible to go backwards inside the cursor. The only way to get back to a row that has been bypassed is to close and reopen the cursor.

The CURSOR FOR Loop

The CURSOR FOR loop allows you to handle cursors without using the OPEN, FETCH, and CLOSE statements. Listing 2.13 shows a CURSOR FOR loop.

Listing 2.13 A CURSOR FOR loop.

DECLARE
   CURSOR Students_cur
   IS
   SELECT last_name, first_name, middle_name, gpa
   FROM   STUDENTS
   WHERE  gpa > 3.0;
BEGIN
   FOR Students_rec IN Students_cur LOOP
       <statements>
   END LOOP;
END;

This type of loop automatically opens the cursor, loops through each row in the result set, and closes the cursor when the last row has been processed. In this example, the loop index variable Students_rec is implicitly declared by the cursor for loop as a %ROWTYPE variable based on the structure of the cursor (%ROWTYPE will be discussed later in this chapter).

Cursors can be used with any type of loop available in PL/SQL; the use of CURSOR FOR loops is not required.

Implicit Cursors

An implicit cursor is created by Oracle for all SQL statements that manipulate data (even if the query returns only a single row). If a query has a chance of returning multiple rows, itís a good idea to use an explicit cursor to retrieve the rows instead of using a single SELECT statement.

 

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter