Skip to Main Content
Oracle PL/SQL Language Pocket Reference, 4th Edition
book

Oracle PL/SQL Language Pocket Reference, 4th Edition

by Steven Feuerstein, Bill Pribyl, Chip Dawes
October 2007
Intermediate to advanced content levelIntermediate to advanced
178 pages
2h 50m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Language Pocket Reference, 4th Edition

Cursors in PL/SQL

Every SQL statement executed by the Oracle database 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 used only for DML statements (SELECT, INSERT, UPDATE, DELETE, MERGE, or SELECT FOR UPDATE). These static cursors may be explicitly declared and named or may appear inline as implicit cursors.

Dynamic cursors are used for any type of valid SQL statement including DDL (CREATE, TRUNCATE, ALTER) and DCL (GRANT, REVOKE). Dynamic cursors are implemented with the EXECUTE IMMEDIATE statement.

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; for example:

    CURSOR company_cur
       IS
       SELECT company_id FROM company;
  • A cursor that accepts arguments through a parameter list; for example:

    CURSOR company_cur (id_in IN NUMBER) IS SELECT name FROM company WHERE company_id ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Language Pocket Reference, 5th Edition

Oracle PL/SQL Language Pocket Reference, 5th Edition

Steven Feuerstein, Bill Pribyl, Chip Dawes

Publisher Resources

ISBN: 9780596514044Errata Page