Oracle PL/SQL Language Pocket Reference

Cursors in PL/SQL

Every SQL statement executed by the RDBMS has a private SQL area that contains information about the SQL statement and the set of data returned. In PL/SQL, a cursor is a name assigned to a specific private SQL area for a specific SQL statement. There can be either static cursors, whose SQL statement is determined at compile time, or dynamic cursors, whose SQL statement is determined at runtime. Static cursors are covered in greater detail in this section. Dynamic cursors in PL/SQL are implemented via the built-in package DBMS_SQL. See the book Oracle Built-in Packages and the corresponding Oracle PL/SQL Built-ins Pocket Reference , both from O’Reilly & Associates, for full coverage on DBMS_SQL and the other built-in packages.

Explicit Cursors

Explicit cursors are SELECT statements that are DECLAREd explicitly in the declaration section of the current block or in a package specification. Use OPEN, FETCH, and CLOSE in the execution or exception sections of your programs.

Declaring explicit cursors

To use an explicit cursor, you must first declare it in the declaration section of a block or package. There are three types of explicit cursor declarations:

• A cursor without parameters, such as:

      CURSOR company_cur 
         IS
         SELECT company_id FROM company;

• A cursor that accepts arguments through a parameter list:

      CURSOR company_cur (id_in IN NUMBER) IS
      SELECT name FROM company
      WHERE  company_id = id_in;

• A cursor header that contains a RETURN clause in place of the SELECT statement: ...