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 .

graphics/error_icon.gif

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; 
graphics/trick_icon.gif

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




Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net