Lab 9.1 Exercise Answers
9.1.1 Answers
| a) | What will happen when the preceding example is run in a SQL*Plus session? | A1:
| 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.
|
| b) | Explain how the record type vr_student_name is being used in the following example. | A1:
| 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. |
|
9.1.2 Answers
| a) | Write the declarative section of a PL/SQL block that defines a cursor named c_student, based on the student table with the last_name and the first_name concatenated into one item called name and leaving out the created_by and modified_by columns. Then declare a record based on this cursor. | A1:
| Answer:
DECLARE
CURSOR c_student is
SELECT first_name||' '||Last_name name
FROM student;
vr_student c_student%ROWTYPE;
|
| b) | Add the necessary lines to the PL/SQL block that you just wrote to open the cursor. | A1:
| Answer: The following lines should be added to the lines in a).
BEGIN
OPEN c_student;
|
| c) | In Chapter 3 you learned how to construct a loop. For the PL/SQL block that you have been writing, add a loop. Inside the loop FETCH the cursor into the record. Include a DBMS_OUTPUT line inside the loop so that each time the loop iterates, all the information in the record is displayed in a SQL*Plus session. | A1:
| Answer: The following lines should be added:
LOOP
DBMS_OUTPUT.PUT_LINE(vr_student.name);
FETCH c_student INTO vr_student;
|
| d) | Continue with the code you have developed by adding a close statement to the cursor. Is your code complete now? | A1:
| 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. |
| e) | Explain what is occurring in this PL/SQL block. What will be the output from the preceding example? | A1:
| 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.
| | f) | Next, consider the same example with single modification. Notice that the DBMS_OUTPUT.PUT_LINE statement has been moved outside the loop (shown in bold letters). Execute this example, and try to explain why this version of the script produces different output. | A2:
| 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. |
| g) | Explain what is declared in the previous example. Describe what is happening to the record and explain how this results in the output. | A1:
| 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.
|
9.1.3 Answers
| a) | Now that you know cursor attributes, you can use one of these to exit the loop within the code you developed in the previous example. Are you able to make a fully executable block now? If not, explain why. | A1:
| Answer: You can make use of attribute %NOTFOUND to close the loop. It would also be a wise idea to add an exception clause to the end of the block to close the cursor if it is still open. If you add the following statements to the end of your block, it will be complete.
EXIT WHEN c_student%NOTFOUND;
END LOOP;
CLOSE c_student;
EXCEPTION
WHEN OTHERS
THEN
IF c_student%ISOPEN
THEN
CLOSE c_student;
END IF;
END;
|
| b) | What will happen if this code is run? Describe what is happening in each phase of the example. | A1:
| 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. | | c) | Rerun this block, changing 07002 to 99999. What do you think will happen? Explain. | A2:
| 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. | | d) | Now, try running this file. Did it run as you expected? Why or why not? What could be done to improve the way it handles a possible error condition? | A3:
| 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. |
9.1.4 Answers
| a) | Describe what is happening in each phase of example ch09_4a.sql. Use the line numbers to reference the example. | A1:
| 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 (1�6) just like other identifiers. In the executable section of the block (7�15), 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). | | b) | Modify the example to make use of the cursor attributes %FOUND and %ROWCOUNT. | A2:
| 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. | | c) | Fetch a cursor that has a data from the student table into a %ROWTYPE. Only select students with a student_id under 110. The columns are the STUDENT_ID, LAST_NAME, FIRST_NAME, and a count of the number of classes they are enrolled in (using the enrollment table). Fetch the cursor with a loop and then output all the columns. You will have to use an alias for the enrollment count. | A3:
| 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. |
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
|
|
No comments:
Post a Comment