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: An example of an implicit cursor. Any INSERT, UPDATE, or DELETE is always an implicit cursor (you never open an UPDATE’s cursor explicitly). An example of an explicit cursor. An example of an implicit cursor. A SELECT INTO is an implicit query to retrieve (you hope) a single row. A DDL (Data Definition Language) command executed in SQL*Plus. It is not a cursor inside a PL/SQL program. 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 ...