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:

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;

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.

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.

10-5.

The block could fail for any of the following reasons:

If you want to stick with an implicit query (SELECT INTO), change the code to this:

DECLARE
  v_name employee.last_name%TYPE;
BEGIN
  SELECT last_name
    INTO v_name
    FROM employee;
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
     DBMS_OUTPUT.PUT_LINE (
        'Quick, hire one person so my cursor will work!'),

  WHEN TOO_MANY_ROWS
  THEN
     DBMS_OUTPUT.PUT_LINE (
        'Quick, fire somebody so my cursor will work!'),
END;

Note that you do not have to include a handler for VALUE_ERROR. Instead, you can change the way you declare the variable, so that if and when the column size changes, this program adapts automatically to that new structure.

10-6.

Choices (b), (e), (f), and (i) are all valid cursor attributes. They return the following information:

For explicit cursors, they are specified as cursor name%attribute ; for example, CURS_GET_EMPS%FOUND. For implicit cursors, they are specified as SQL%attribute ; for example, SQL%NOTFOUND.

10-7.

The usages are:

10-8.

You are executing the CLOSE after the RETURN statement, so in fact that CLOSE statement is never run. It would therefore seem that your cursor would remain open and you would run out of open cursors in your session. That is not, however, the case. Since your cursor is declared locally, it is automatically closed when the function returns its value. You can leave the CLOSE statement out entirely, and the function will work.

10-9.

The loyal employee cannot be identified by this code alone. It might not even be the same one each time unless the same index is always used. The point is that the first employee returned by the query always receives the 10% raise regardless of which employee_id is passed to the function. The cursor parameter is ignored within the query that simply equates the employee_id column to the employee_id column that is true for all rows.

In an effort to appease the other angry employees (and disappoint a few ecstatic ones) the cursor should be changed to avoid the naming conflict between the parameter and the column.

Here is one possibility:

CURSOR curs_get_curr_sal (cp_employee_id employee.employee_id%TYPE )
IS
  SELECT salary
    FROM employee
   WHERE employee_id = cp_employee_id
  FOR UPDATE OF salary;

This clearly differentiates the cursor parameter from the table column.

10-10.

The output is as follows:

%FOUND-TRUE
%NOTFOUND-FALSE
%ISOPEN-FALSE
%ROWCOUNT-1

These are the four attributes of implicit cursors.

10-11.

The cursor attributes %FOUND and %NOTFOUND come in handy here. If a row is returned, the %FOUND is TRUE, and %NOTFOUND is FALSE. Conversely, %FOUND is FALSE and %NOTFOUND is TRUE when no rows are returned. Either one can be used in this case.

DECLARE
  CURSOR curs_failure IS
     SELECT null
       FROM employee
      WHERE 1 = 2;
  v_dummy VARCHAR2(1);
BEGIN
  OPEN curs_failure;
  FETCH curs_failure INTO v_dummy;

  IF curs_failure%NOTFOUND
  THEN
     DBMS_OUTPUT.put_line (
        'It is official. 1 ' ||
        'does not equal 2!' );
  ELSE
     DBMS_OUTPUT.put_line (
        'It is still official. ' ||
        '1 does not equal 2!' );
  END IF;

  CLOSE curs_failure;
END;

10-12.

The %ROWCOUNT cursor attribute is incremented every time a row is returned by the cursor. It can be used to process a select number of rows from the query:

CREATE OR REPLACE FUNCTION three_highest RETURN NUMBER
IS
  -- Cursor to get the salaries in descending order
  CURSOR curs_get_salary IS
     SELECT NVL (salary, 0) salary
       FROM employee
      ORDER BY salary desc;
  v_salary_rec curs_get_salary%ROWTYPE;

  v_ret_val NUMBER := 0;

BEGIN
  /*
    || Use the cursor to fetch rows and increment the
    || salary total. Stop after processing the third row.
  */
  OPEN curs_get_salary;
  LOOP
    FETCH curs_get_salary INTO v_salary_rec;
    EXIT WHEN curs_get_salary%NOTFOUND; -- Fewer than 3 rows?
    v_ret_val := v_ret_val + v_salary_rec.salary;
    EXIT WHEN curs_get_salary%ROWCOUNT = 3;
  END LOOP;

  CLOSE curs_get_salary;

  RETURN(v_ret_val);

END three_highest;

10-13.

You should always take the approach shown in (b): do not declare and fetch into individual variables. Instead, declare a record based on the cursor and fetch into the record. You can then reference whichever fields you need.

10-14.

You see the following output:

32
MURRAY

This program does not raise a NO_DATA_FOUND error, even though it is fetching well beyond the end of the result set of this cursor. An explicit FETCH past the last row simply has no effect; the contents of the record (rec in this case) remains unchanged.

10-15.

You can remove the CLOSE statement, and the code executes in exactly the same way—and the cursor closes. If you declare a cursor in a local block, its scope and visibility are restricted to this block (or procedure or function). When the block finishes executing, the memory associated with the cursor and any other local data structures is released.

10-16.

Since you are fetching and processing each and every row identified by the cursor, you can use a cursor FOR loop and delete much of the code you previously wrote:

DECLARE
   CURSOR brought_to_you_by_unions
   IS
      SELECT weekends, forty_hour_weeks, overtime_pay
        FROM business_practices;
BEGIN
   FOR social_benefits IN brought_to_you_by_unions
   LOOP
      calculate_impact (social_benefits);
   END LOOP;
END;

10-17.

The example shown in the problem most definitely does not do the trick. It, instead, raises the following error:

ORA-01001: invalid cursor

When you reference the %ROWCOUNT attribute, the cursor has already been opened, fetched from, and closed. That is the trade-off with a cursor FOR loop: it makes it easy to write the body of your loop, but outside of your loop, you are in the dark about what just happened. To obtain the count, you have to create a local variable and keep count yourself. Here is one implementation:

DECLARE
   CURSOR brought_to_you_by_unions
   IS
      SELECT weekends, forty_hour_weeks, overtime_pay
        FROM business_practices;
   v_count PLS_INTEGER;
BEGIN
   FOR social_benefits IN brought_to_you_by_unions
   LOOP
      calculate_impact (social_benefits);
      v_count := brought_to_you_by_unions%ROWCOUNT;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE (
      'Calculated impact of ' || NVL (v_count, 0) -- in case of NOT FOUND
      || ' benefits brought about by unions.'),
END;

10-18.

The call to DBMS_OUTPUT.PUT_LINE shows the following:

FALSE - 32

for each row fetched (a total of 32 lines). How can this be? Here’s a somewhat strange situation. You execute an implicit cursor (UPDATE), and then after that you open and process another cursor—the SELECT embedded in the cursor FOR loop.

Even though you have not declared a cursor by name for this SELECT, it is still not an implicit cursor; it is simply unnamed. Any reference, therefore, to the SQL% cursor attributes reflects the results of the UPDATE statement, not the query.

10-19.

This block displays a single line of output:

TRUE

You are now using a declared, explicit cursor, so you can reference that cursor with these attributes. This block displays output only when you have fetched the first row, and the cursor is open (which is always the case inside the cursor FOR loop).

10-20.

When you use the FOR UPDATE clause in a query, you can then specify:

WHERE CURRENT OF
cursor

as the WHERE clause of DML statements inside the FOR loop. The runtime engine automagically updates/inserts/deletes the current row in the cursor’s result set. You can also add a WHERE clause to minimize data queried back to the PL/SQL block and then rejected with the IF statement. Here is the rewrite:

DECLARE
   CURSOR upd_all_cur
      IS SELECT * FROM employee
          WHERE commission IS NOT NULL
         FOR UPDATE;
BEGIN
   FOR rec IN upd_all_cur
   LOOP
      UPDATE employee
         SET commission = commission * 2
       WHERE CURRENT OF upd_all_cur;
   END LOOP;
END;

You should also see an improvement in program performance with this change. My tests (see the wco_vs_pkey.sql file on the book’s web page) revealed these results:

SQL> @wco_vs_pkey 500
WHERE CURRENT OF Elapsed: 12.3 seconds.
BY PRIMARY KEY Elapsed: 14.52 seconds.

10-21.

The block locks each and every row in the employee table, whether or not an UPDATE is actually performed on a particular row. Specifying the column name in the FOR UPDATE clause has an impact only if there are multiple tables specified in the FROM clause. In that situation, only tables whose columns are specified in the FOR UPDATE OF clause will have rows locked.

10-22.

The first time you execute the block, all four cursors will be opened and no error occurs. The second time you run the block, you get this error:

ORA-06511: PL/SQL: cursor already open

when the block attempts to open door number one. Why does this happen? When the anonymous block terminates, doors number three and four are closed, while doors one and two remain open. Cursors door_number_three and door_number_four are declared within a specific PL/SQL block (a procedure and an anonymous block, respectively). When that block closes, the cursors also close. Doors numbered one and two are declared at the package level (one in the package specification and one in the body), so their scope is the entire session. These cursors will not close until you close them explicitly, or you exit the session.

10-23.

A database record (%ROWTYPE) or a programmer-defined record.

10-24.

The best solution is to move all this related code into a single package, as shown here:

CREATE OR REPLACE PACKAGE jobinfo
IS
   FUNCTION job_for (name_in IN VARCHAR2) RETURN VARCHAR2;
   PROCEDURE show_jobs;
END jobinfo;
/
CREATE OR REPLACE PACKAGE BODY jobinfo
IS
   CURSOR around_the_house_jobs_cur (
      name_in IN adolescent_workload.name%TYPE)
   IS
       SELECT job
         FROM adolescent_workload
        WHERE name = name_in;

   PROCEDURE show_jobs IS
   BEGIN
      FOR rec IN around_the_house_jobs_cur ('ELI')
      LOOP
         DBMS_OUTPUT.PUT_LINE (rec.job);
      END LOOP;
   END;

   FUNCTION job_for (name_in IN VARCHAR2) RETURN VARCHAR2
   IS
      Retval adolescent_workload.job%type;
   BEGIN
      OPEN around_the_house_jobs_cur (name_in);
      FETCH around_the_house_jobs_cur INTO retval;
      CLOSE around_the_house_jobs_cur;
      RETURN retval;
   END;
END jobinfo;
/

In the original programs, there were two different cursors that were logically equivalent, but physically different. This led to excessive parsing of SQL statements. In this version, we have just one cursor, and it has been parameterized, so no matter what name is passed to it, the same pre-parsed SQL statement cached in the SGA is used.

Notice that you need to add the CLOSE statement to the function (in bold). When the cursor is defined inside the function, it’s closed automatically when the function returns its data. Once you move the cursor to the package level, it stays open unless closed.

10-25.

Oracle8i offers a new feature called bulk collect that allows you to request that all rows in a query be fetched in a single pass to the database, depositing the result set directly into the specified nested tables. Here is a rewrite of the original program utilizing that technique:

CREATE OR REPLACE FUNCTION get_a_mess_o_emps
   (deptno_in IN dept.deptno%TYPE)
RETURN emplist_t
IS
   emplist emplist_t := emplist_t();
   TYPE numTab IS TABLE OF NUMBER;
   TYPE charTab IS TABLE OF VARCHAR2(12);
   TYPE dateTab IS TABLE OF DATE;
   enos numTab;
   names charTab;
   hdates dateTab;
BEGIN
   SELECT empno, ename, hiredate
      BULK COLLECT INTO enos, names, hdates
      FROM emp1
     WHERE deptno = deptno_in;
   emplist.EXTEND(enos.COUNT);
   FOR i IN enos.FIRST..enos.LAST
   LOOP
      emplist(i) := emp_t(enos(i), names(i), hdates(i));
   END LOOP;
   RETURN emplist;
END;
/