Oracle DBA FAQ - Working with Cursors in PL/SQL

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5

A collection of 19 FAQs on working with database objects in PL/SQL. Clear answers are provided with tutorial exercises on defining, opening, and closing cursors, looping through cursors, defining and using cursor variables. Topics included in this FAQ are:

What Is a Cursor?
How Many Types of Cursors Supported in PL/SQL?
What Is the Implicit Cursor?
How To Use Attributes of the Implicit Cursor?
How To Loop through Data Rows in the Implicit Curosr?
How To Define an Explicit Cursor?
How To Open and Close an Explicit Cursor?
How To Retrieve Data from an Explicit Cursor?
How To Retrieve Data from an Cursor to a RECORD?
How To Use FETCH Statement in a Loop?
How To Use an Explicit Cursor without OPEN Statements?
Can Multiple Cursors Being Opened at the Same Time?
How To Pass a Parameter to a Cursor?
What Is a Cursor Variable?
How To Define a Cursor Variable?
How To Open a Cursor Variable?
How To Loop through a Cursor Variable?
How To Pass a Cursor Variable to a Procedure?
Why Cursor Variables Are Easier to Use than Cursors?

Sample scripts used in this FAQ collection assumes that you are connected to the server with the HR user account on the default database instance XE. See other FAQ collections on how to connect to the server. It is also assumed that you are using SQL*Plus to practice the tutorial exercises.

What Is a Cursor?

A cursor looks like a variable, but it is not a variable. A cursor looks like a procedure, but it is not a procedure. A cursor is a cursor. It is a logical representation of a resource connects to a set of data rows related to a DML statement. A cursor is consists of:

A DML statement with or without parameters.
A status to indicate whether it is connected or not.
A pointer to indicate the current row in the resource set.
A number of attributes about the cursor, like FOUND, NOTFOUND and ROWCOUNT.

How Many Types of Cursors Supported in PL/SQL?

PL/SQL supports two types of cursors:

The implicit cursor - A single default cursor that automatically connects to the last DML statement executed.
Explicit cursors - User defined cursors with specific DML statements and execution statuses.

What Is the Implicit Cursor?

There is only one implicitly cursor in a session. The implicit cursor is the cursor automatically defined by PL/SQL for you. Whenever a SQL statement is executed, this cursor will be assigned to represent the execution of this statement. This implicit cursor is called SQL. It has many attributes representing some good information about the execution like:

SQL%FOUND - True, if the SQL statement has changed any rows.
SQL%NOTFOUND - True, if the SQL statement has not changed any rows.
SQL%ROWCOUNT - The number of rows affected by the SQL statement.
%ISOPEN - True, if there is a SQL statement being associated to the cursor.

How To Use Attributes of the Implicit Cursor?

Right after executing a DML statement, you retrieve any attribute of the implicit cursor by using SQL%attribute_name, as shown in the following tutorial exercise:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL);
Table created.

DECLARE
  id NUMBER;
  first_name CHAR(10);
BEGIN
  id := 29;
  first_name := 'Bob';
  INSERT INTO student VALUES(id, first_name, 'Henry');
  first_name := 'Joe';
  INSERT INTO student VALUES(id+1, first_name, 'Bush');
  DBMS_OUTPUT.PUT_LINE('# of rows inserted: '
    || SQL%ROWCOUNT);
 
  first_name := 'Fyi';
  UPDATE student SET first_name = first_name WHERE id = 29;
  IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('# of rows updated: '
        || SQL%ROWCOUNT);
  END IF;

  UPDATE student SET first_name = first_name
    WHERE id = id+1;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No records updated.');
  END IF;
    
  DELETE FROM student WHERE id = id;                    
  DBMS_OUTPUT.PUT_LINE('# of rows deleted: '
    || SQL%ROWCOUNT);
END; 
/
# of rows inserted: 1
# of rows updated: 1
No records updated.
# of rows deleted: 2

(Continued on next part...)

Part: 1 2 3 4 5

More...