Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Programming: A Developer's Workbook

Chapter 10. Cursors

Beginner

Q:

10-1.

An implicit cursor is a SQL statement whose associated cursor is implicitly (performed automatically by Oracle) opened, executed, and closed. The code snippets are:

  1. An example of an implicit cursor. Any INSERT, UPDATE, or DELETE is always an implicit cursor (you never open an UPDATE’s cursor explicitly).

  2. An example of an explicit cursor.

  3. An example of an implicit cursor. A SELECT INTO is an implicit query to retrieve (you hope) a single row.

  4. A DDL (Data Definition Language) command executed in SQL*Plus. It is not a cursor inside a PL/SQL program.

  5. An example of an implicit cursor.

Q:

10-2.

An explicit cursor is a SELECT statement that is declared explicitly in a declaration section (of an anonymous block, procedure, function, or package) with the CURSOR statement, as in the following:

DECLARE
   CURSOR my_cur
   IS
      SELECT * FROM employee;

Q:

10-3.

If the implicit cursor is a SELECT INTO, the TOO_MANY_ROWS exception is raised when the cursor returns more than one row; NO_DATA_FOUND is raised if the query does not return any rows. If the cursor is an INSERT, it may also raise DUP_VAL_ON_INDEX. Finally, if the cursor contains expressions, it may also raise exceptions such as ZERO_DIVIDE and INVALID_NUMBER.

Q:

10-4.

Even though this query returns at most one row, the SQL engine fetches or attempts to fetch twice: once to retrieve the row and then again to see if the TOO_MANY_ROWS exception should be raised.

Q:

10-5.

The block could fail for any of the following reasons:

  • There ...

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 Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page