Using Explicit Cursors  «Prev  Next»

Lesson 6Open a cursor and fetch the Records
ObjectiveDescribe how to Open a Cursor and fetch the Records

Open Cursor and fetch Records in PL/SQL

After defining a cursor, you must open it before fetching the records.
The records can be fetched into a record or a variable list. The record or variable list can be used for further processing of the data.

Open a cursor

The following MouseOver explains the syntax for opening a cursor and the variables in the syntax. It also shows an example of using the syntax to open a cursor. In this example, no arguments are specified.

  1. Name of the cursor
  2. A series of arguments. The arguments specified are optional.

Opening Cursor-with Arguments
When opening a cursor, the arguments specified are optional. When you open a cursor, PL/SQL executes the query defined within the cursor and identifies the result set pertaining to the SELECT statement, that is, the records from all the tables that meet the criteria defined within the WHERE clause.
The OPEN statement does not actually retrieve the records. This action is done by the FETCH statement.

Fetching records

The following MouseOver explains the syntax for fetching records and the variables in the syntax. It also shows an example of using the syntax for fetching records. In this example, the records are fetched into a variable list (pet_name1, pet_count1).
  1. Name of the cursor.
  2. This is a PL/SQL data structure into which the next record of the active set of records is copied.
  3. This is a PL/SQL data structure into which the next record of the active set of records is copied.

Fetching Records
The number of columns within the cursor definition should match the columns within the FETCH statement. In a cursor block without a loop, the number of variables defined should be equal to the number of records fetched within the cursor. In the next lesson, you will learn how to close a cursor.