Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 9.1 Exercise Answers

Answer: In this example, you select a single row for the ZIPCODE table into vr_zip record. Next, you display each element of the record on the screen. Notice that in order to reference each attribute of the record, dot notation is used. When run, the example produces the following output:

City: Santurce
State: PR
Zip: 00914
PL/SQL procedure successfully completed.

Answer: Record vr_student_name

has structure similar to a row returned by the SELECT statement defined in the cursor. It contains two attributes, student's first and last names.

It is important to note that a cursor-based record can be declared only after its corresponding cursor has been declared; otherwise, a compilation error will occur.

Answer: The following lines should be added to the lines in a).

BEGIN
OPEN c_student;

Answer: The following lines should be added:

LOOP
DBMS_OUTPUT.PUT_LINE(vr_student.name);
FETCH c_student INTO vr_student;

Answer: The following lines should be added:

CLOSE c_student;

The code is not complete since there is not a proper way to exit the loop.

Answer: In this example, you declare a cursor that returns five student names. Next, you declare a cursor-based record. In the body of the program you process explicit cursors via the cursor loop. In the body of the loop, you assign each record returned by the cursor to the cursor-based record, vr_student_name

. Next, you display its contents on the screen. When run, the example produces the following output:

Student name: George Eakheit

Student name: Leonard Millstein

Student name: Austin V. Cadet

Student name: Tamara Zapulla

Student name: Jenny Goldsmith

PL/SQL procedure successfully completed.

Answer: The DBMS_OUTPUT.PUT_LINE has been moved outside the loop. First the loop will process the five student records. The values for each record will be placed in the record vr_student_name, but each time the loop iterates it will replace the value in the record with a new value. When the five iterations of the loop are finished, it will exit because of the EXIT WHEN condition, leaving the vr_student_name record with the last value that was in the cursor. This is the only value that will be displayed via the DBMS_OUTPUT.PUT_LINE, which comes after the loop is closed.

Answer: In this example, you declare a record called vr_instructor

. This record is based on the type you defined previously. In the body of the PL/SQL block, you initialize this record with the help of the SELECT INTO statement, and display its value on the screen. It is important to note that the columns of the SELECT INTO statement are listed in the same order the attributes are defined in instructor_info

type. So there is no need to use dot notation for this record initialization. When run, this example produces the following output:

Instructor, Tom Wojick, teaches 9 section(s)
PL/SQL procedure successfully completed.

Answer: The PL/SQL block ch09_3a would display the following output:

Bayonne has a zipcode of 07002

PL/SQL procedure successfully completed.

The declaration section declares a variable, v_city,

anchored to the datatype of the city item in the zipcode table. The SELECT statement causes an implicit cursor to be opened, fetched, and then closed. The IF clause makes use of the attribute %ROWCOUNT to determine if the implicit cursor has a rowcount of 1 or not. If it does have a row count of 1, then the first DBMS_OUTPUT

line will be displayed. You should notice that this example does not handle a situation where the rowcount is greater than 1. Since the zipcode table's primary key is the zipcode, this could happen.

Answer: The PL/SQL block would display the following:

DECLARE

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 4

A select statement in a PL/SQL block that does not return any rows will raise a no data found exception. Since there was no exception handler, the preceding error would be displayed.

Answer: You may have expected the second and third condition of the IF statement to capture the instance of a %ROWCOUNT equal to 0. Now that you understand that a SELECT statement that returns no rows will raise a NO_DATA_FOUND exception, it would be a good idea to handle this by adding a <%WHEN NO_DATA_FOUND> exception to the existing block. You can add a %ROWCOUNT in the exception, either to display the rowcount in a DBMS_OUTPUT or to put an IF statement to display various possibilities.

Answer: The example illustrates a cursor fetch loop, in which multiple rows of data are returned from the query. The cursor is declared in the declaration section of the block (16) just like other identifiers. In the executable section of the block (715), a cursor is opened using the OPEN (8) statement. Because the cursor returns multiple rows, a loop is used to assign returned data to the variables with a FETCH statement (10). Because the loop statement has no other means of termination, there must be an exit condition specified. In this case, one of the attributes for the cursor is %NOTFOUND (12). The cursor is then closed to free the memory allocation (14). Additionally, if the exception handler is called, there is a check to see if the cursor is open (18) and if it is closed (20).

Answer: Your modification should look like this:

-- ch09_5a.sql
SET SERVEROUTPUT ON
DECLARE
v_sid student.student_id%TYPE;
CURSOR c_student IS
SELECT student_id
FROM student
WHERE student_id < 110;
BEGIN
OPEN c_student;
LOOP
FETCH c_student INTO v_sid;
IF c_student%FOUND THEN
DBMS_OUTPUT.PUT_LINE
('Just FETCHED row '
||TO_CHAR(c_student%ROWCOUNT)||
' Student ID: '||v_sid);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE c_student;
EXCEPTION
WHEN OTHERS
THEN
IF c_student%ISOPEN
THEN
CLOSE c_student;
END IF;
END;

There has been a modification to the loop structure. Instead of having an exit condition, an IF statement is being used. The IF statement is making use of the cursor attribute %FOUND. This attribute returns true when a row has been "found" in the cursor and false when it has not. The next attribute %ROWCOUNT returns a number, which is the current row number of the cursor.

Answer: One method of doing this would be as follows:

-- ch09_6a.sql
SET SERVEROUTPUT ON
DECLARE
CURSOR c_student_enroll IS
SELECT s.student_id, first_name, last_name,
COUNT(*) enroll,
(CASE
WHEN count(*) = 1 Then ' class.'
WHEN count(*) is null then
' no classes.'
ELSE ' classes.'
END) class
FROM student s, enrollment e
WHERE s.student_id = e.student_id
AND s.student_id <110
GROUP BY s.student_id, first_name, last_name;
r_student_enroll c_student_enroll%ROWTYPE;
BEGIN
OPEN c_student_enroll;
LOOP
FETCH c_student_enroll INTO r_student_enroll;
EXIT WHEN c_student_enroll%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Student INFO: ID '||
r_student_enroll.student_id||' is '||
r_student_enroll.first_name|| ' ' ||
r_student_enroll.last_name||
' is enrolled in '||r_student_enroll.enroll||
r_student_enroll.class);
END LOOP;
CLOSE c_student_enroll;
EXCEPTION
WHEN OTHERS
THEN
IF c_student_enroll %ISOPEN
THEN
CLOSE c_student_enroll;
END IF;
END;

Remember that the CASE syntax was introduced in Oracle 9i. This means that the previous statement will not run in Oracle 8 or 8i. You can change the CASE statement to a DECODE statement as follows: DECODE( count(*), 1, ' class. ', null, ' no classes.', 'classes') class

In the declarative section, a cursor c_student_enroll

is defined as well as a record, which is the type of a row of the cursor. The cursor loop structure makes use of an exit condition with the %NOTFOUND cursor attribute. When there are no more rows, the %NOTFOUND will be false and will cause the loop to exit. While the cursor is open and loop is processing, it will fetch a row of the cursor in a record one at a time. The DBMS output will cause each row to be displayed to the screen. Finally, the cursor is closed, and an exception clause will also close the cursor if any error is raised.

Assorted Tips on Cursors

Cursor SELECT LIST

Match the Select list with PL/SQL variables or PL/SQL record components.

The number of variables must be equal to the number of columns or expressions in the Select list. The number of the components of a record must match the columns or expressions in the Select list.

Cursor Scope

The scope of a cursor declared in the main block (or an enclosing block) extends to the sub-blocks.

Expressions in a Cursor SELECT List

PL/SQL variables, expressions, and even functions can be included in the Cursor Select list.

Column Aliases in Cursors

An alternative name you provide to a column or expression in the Select list.

In an Explicit cursor column, aliases are required for calculated columns when

• You FETCH into a record declared with %ROWTYPE declaration against that cursor

• You want to reference the calculated column in the program