Lab 19.1 Exercise Answers | Oracle PL[s]SQL by Example

This section gives you some suggested answers to the questions in Lab 19.1, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

19.1.1 Answers

a)	Explain the script ch19_1a.sql.
A1:	Answer: The declaration portion of the script contains a declaration of the table-based record, `zip_rec` , that has the same structure as a row from the ZIPCODE table. The executable portion of the script populates the `zip_rec` record via the SELECT INTO statement with a row from the ZIPCODE table. Notice that a restriction applied to the ROWNUM enforces the SELECT INTO statement always returns a random single row. As mentioned earlier, there is no need to reference individual record fields when the SELECT INTO statement populates the `zip_rec` record because `zip_rec` has a structure identical to a row of the ZIPCODE table.
b)	Modify the script so that `zip_rec` data is displayed on the screen.
A2:	Answer: Your script should look similar to the following script. Changes are shown in bold letters . -- ch19_1b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE zip_rec zipcode%ROWTYPE; BEGIN SELECT * INTO zip_rec FROM zipcode WHERE rownum < 2; DBMS_OUTPUT.PUT_LINE ('Zip: ' zip_rec.zip); DBMS_OUTPUT.PUT_LINE ('City: ' zip_rec.city); DBMS_OUTPUT.PUT_LINE ('State: ' zip_rec.state); DBMS_OUTPUT.PUT_LINE ('Created By: ' zip_rec.created_by); DBMS_OUTPUT.PUT_LINE ('Created Date: ' zip_rec.created_date); DBMS_OUTPUT.PUT_LINE ('Modified By: ' zip_rec.modified_by); DBMS_OUTPUT.PUT_LINE ('Modified Date: ' zip_rec.modified_date); END;

When run, both versions produce the same output:

  Zip: 00914   City: Santurce   State: PR   Created By: AMORRISO   Created Date: 03-AUG-99   Modified By: ARISCHER   Modified Date: 24-NOV-99   PL/SQL procedure successfully completed.

Modify the script created in the previous exercise (ch19_1b.sql) so that zip_rec is defined as a cursor-based record.

A3:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch19_1c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE  CURSOR zip_cur IS   SELECT *   FROM zipcode   WHERE rownum < 4;   zip_rec zip_cur%ROWTYPE;  BEGIN  OPEN zip_cur;   LOOP   FETCH zip_cur INTO zip_rec;   EXIT WHEN zip_cur%NOTFOUND;  DBMS_OUTPUT.PUT_LINE ('Zip: ' zip_rec.zip); DBMS_OUTPUT.PUT_LINE ('City: ' zip_rec.city); DBMS_OUTPUT.PUT_LINE ('State: ' zip_rec.state); DBMS_OUTPUT.PUT_LINE ('Created By: ' zip_rec.created_by); DBMS_OUTPUT.PUT_LINE ('Created Date: ' zip_rec.created_date); DBMS_OUTPUT.PUT_LINE ('Modified By: ' zip_rec.modified_by); DBMS_OUTPUT.PUT_LINE ('Modified Date: ' zip_rec.modified_date);  END LOOP;  END;

The declaration portion of the script contains a definition of the ZIP_CUR cursor that returns four records from the ZIPCODE table. In this case, the number of records returned by the cursor has been chosen for one reason only, so that the cursor loop iterates more than once. Next, it contains the definition of the cursor-based record, zip_rec .

The executable portion of the script populates the zip_rec record and displays its data on the screen via the simple cursor loop.

This version of the script produces the following output:

  Zip: 00914   City: Santurce   State: PR   Created By: AMORRISO   Created Date: 03-AUG-99   Modified By: ARISCHER   Modified Date: 24-NOV-99   Zip: 01247   City: North Adams   State: MA   Created By: AMORRISO   Created Date: 03-AUG-99   Modified By: ARISCHER   Modified Date: 24-NOV-99   Zip: 02124   City: Dorchester   State: MA   Created By: AMORRISO   Created Date: 03-AUG-99   Modified By: ARISCHER   Modified Date: 24-NOV-99   PL/SQL procedure successfully completed.

Modify the script created in the previous exercise (ch19_1c.sql). Change the structure of the zip_rec record so that it contains total number of students in a given city, state, and zipcode. Do not include audit columns such as CREATED_BY and CREATED_DATE in the record structure.

A4:

Answer: Your script should look similar to the following script. All changes are shown in bold letters.

 -- ch19_1d.sql, version 4.0  SET SERVEROUTPUT ON SIZE 40000  DECLARE  CURSOR zip_cur IS   SELECT city, state, z.zip, COUNT(*) students   FROM zipcode z, student s   WHERE z.zip = s.zip   GROUP BY city, state, z.zip;  zip_rec zip_cur%ROWTYPE; BEGIN OPEN zip_cur; LOOP FETCH zip_cur INTO zip_rec; EXIT WHEN zip_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('Zip: 'zip_rec.zip); DBMS_OUTPUT.PUT_LINE ('City: 'zip_rec.city); DBMS_OUTPUT.PUT_LINE ('State: 'zip_rec.state);  DBMS_OUTPUT.PUT_LINE ('Students: '   zip_rec.students);  END LOOP; END;

In this example, the cursor SELECT statement has been modified so that it returns total number of students for a given city, state, and zipcode. Notice that the ROWNUM restriction has been removed so that the total number of students is calculated correctly. As a result, the buffer size has been changed from 2000 to 40,000 so that the script does not cause a buffer overflow error.

Consider the partial output retuned by this example:

  Zip: 07401   City: Allendale   State: NJ   Students: 1   Zip: 11373   City: Amherst   State: NY   Students: 6   Zip: 48104   City: Ann Arbor   State: MI   Students: 1   Zip: 11102   City: Astoria   State: NY   Students: 1   Zip: 11105   City: Astoria   State: NY   Students: 2   Zip: 11510   City: Baldwin   State: NY   Students: 1   Zip: 11360   City: Bayside   State: NY   Students: 1   ...   PL/SQL procedure successfully completed.

Next, assume that just like in the previous version of the script (ch19_1c.sql), you would like to display only four records on the screen. This can be achieved as follows :

 -- ch19_1e.sql, version 5.0 SET SERVEROUTPUT ON DECLARE  CURSOR zip_cur IS   SELECT city, state, z.zip, COUNT(*) students   FROM zipcode z, student s   WHERE z.zip = s.zip   GROUP BY city, state, z.zip;  zip_rec zip_cur%ROWTYPE;  v_counter INTEGER := 0;  BEGIN OPEN zip_cur; LOOP FETCH zip_cur INTO zip_rec; EXIT WHEN zip_cur%NOTFOUND;  v_counter := v_counter + 1;   IF v_counter <= 4 THEN  DBMS_OUTPUT.PUT_LINE ('Zip: ' zip_rec.zip); DBMS_OUTPUT.PUT_LINE ('City: ' zip_rec.city); DBMS_OUTPUT.PUT_LINE ('State: ' zip_rec.state);  DBMS_OUTPUT.PUT_LINE ('Students: '   zip_rec.students);   END IF;  END LOOP; END;

The SELECT statement defined in the cursor is supported by multiple versions of Oracle. As mentioned previously, Oracle 9i also supports the new ANSI 1999 SQL standard, and the SELECT statement can be modified as follows according to this new standard:

  SELECT city, state, z.zip, COUNT(*) students   FROM zipcode z   JOIN student s   ON s.zip = z.zip   GROUP BY city, state, z.zip;

The preceding SELECT statement uses the ON syntax to specify the join condition between two tables. This type of join becomes especially useful when the columns participating in the join do not have the same name .

You will find detailed explanations and examples of the statements using new ANSI 1999 SQL standard in Appendix E and Oracle help.

19.1.2 Answers

a)	Explain the script ch19_2a.sql.
A1:	Answer: The declaration portion of the script contains ZIP_CUR cursor, which returns total number of students corresponding to a particular zipcode. Next, it contains the declaration of the user -defined record type, `zip_info_type` , which has two fields, and the actual user-defined record, `zip_info_rec` . The executable portion of the script populates the `zip_info_rec` record via the cursor FOR loop. As mentioned earlier, because `zip_info_rec` is a user-defined record, each record field is assigned a value individually.
b)	Modify the script so that `zip_info_rec` data is displayed on the screen only for the first five records returned by the ZIP_CUR cursor.
A2:	Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch19_2b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE CURSOR zip_cur IS SELECT zip, COUNT(*) students FROM student GROUP BY zip; TYPE zip_info_type IS RECORD (zip_code VARCHAR2(5), students INTEGER); zip_info_rec zip_info_type; v_counter INTEGER := 0; BEGIN FOR zip_rec IN zip_cur LOOP zip_info_rec.zip_code := zip_rec.zip; zip_info_rec.students := zip_rec.students; v_counter := v_counter + 1; IF v_counter <= 5 THEN DBMS_OUTPUT.PUT_LINE ('Zip Code: ' zip_info_rec.zip_code); DBMS_OUTPUT.PUT_LINE ('Students: ' zip_info_rec.students); DBMS_OUTPUT.PUT_LINE ('--------------------'); END IF; END LOOP; END;

In order to display information for the first five records returned by the ZIP_CUR cursor, a new variable, v_counter , is declared. For each iteration of the loop, the value of this variable is incremented by one. As long as the value of v_counter is less than or equal to five, the data of the zip_info_rec record is displayed on the screen.

When run, this script produces the following output:

  Zip Code: 01247   Students: 1   --------------------   Zip Code: 02124   Students: 1   --------------------   Zip Code: 02155   Students: 1   --------------------   Zip Code: 02189   Students: 1   --------------------   Zip Code: 02563   Students: 1   --------------------   PL/SQL procedure successfully completed.

Modify the script created in the previous exercise (ch19_2b.sql). Change the structure of the zip_info_rec record so that it also contains total number of instructors for a given zipcode. Populate this new record and display its data on the screen for the first five records returned by the ZIP_CUR cursor.

A3:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch19_2c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE CURSOR zip_cur IS  SELECT zip   FROM zipcode   WHERE ROWNUM <= 5;  TYPE zip_info_type IS RECORD (zip_code VARCHAR2(5), students INTEGER,  instructors INTEGER);  zip_info_rec zip_info_type; BEGIN FOR zip_rec IN zip_cur LOOP zip_info_rec.zip_code := zip_rec.zip;  SELECT COUNT(*)   INTO zip_info_rec.students   FROM student   WHERE zip = zip_info_rec.zip_code;   SELECT COUNT(*)   INTO zip_info_rec.instructors   FROM instructor   WHERE zip = zip_info_rec.zip_code;  DBMS_OUTPUT.PUT_LINE ('Zip Code: ' zip_info_rec.zip_code); DBMS_OUTPUT.PUT_LINE ('Students: ' zip_info_rec.students);  DBMS_OUTPUT.PUT_LINE ('Instructors: '   zip_info_rec.instructors);  DBMS_OUTPUT.PUT_LINE ('--------------------'); END LOOP; END;

Consider the changes applied to this version of the script. In the declaration portion of the script, the cursor SELECT statement has changed so that records are retrieved from the ZIPCODE table rather than the STUDENT table. This change allows you to see accurately the total number of students and instructors in a particular zipcode. In addition, because the cursor SELECT statement does not have group function, the ROWNUM restriction is listed in the WHERE clause so that only the first five records are returned. The structure of the user-defined record type, zip_info_type , has changed so that total number of instructors for a given zipcode is stored in the instructors field.

In the executable portion of the script, there are two SELECT INTO statements that populate zip_info_rec . students and zip_info_rec.instructors fields, respectively.

When run, this example produces the following output:

  Zip Code: 00914   Students: 0   Instructors: 0   --------------------   Zip Code: 01247   Students: 1   Instructors: 0   --------------------   Zip Code: 02124   Students: 1   Instructors: 0   --------------------   Zip Code: 02155   Students: 1   Instructors: 0   --------------------   Zip Code: 02189   Students: 1   Instructors: 0   --------------------   PL/SQL procedure successfully completed.

Consider another version of the same script. Here, instead of using two SELECT INTO statements to calculate the total number of students and instructors in a particular zip code, the cursor SELECT statement contains outer joins.

 -- ch19_2d.sql, version 4.0 SET SERVEROUTPUT ON DECLARE CURSOR zip_cur IS  SELECT z.zip, COUNT(student_id) students,   COUNT(instructor_id) instructors   FROM zipcode z, student s, instructor i   WHERE z.zip = s.zip (+)   AND z.zip = i.zip (+)   GROUP BY z.zip;  TYPE zip_info_type IS RECORD (zip_code VARCHAR2(5), students INTEGER, instructors INTEGER); zip_info_rec zip_info_type; v_counter INTEGER := 0; BEGIN FOR zip_rec IN zip_cur LOOP zip_info_rec.zip_code := zip_rec.zip; zip_info_rec.students := zip_rec.students; zip_info_rec.instructors := zip_rec.instructors; v_counter := v_counter + 1; IF v_counter <= 5 THEN DBMS_OUTPUT.PUT_LINE ('Zip Code: ' zip_info_rec.zip_code); DBMS_OUTPUT.PUT_LINE ('Students: ' zip_info_rec.students); DBMS_OUTPUT.PUT_LINE ('Instructors: ' zip_info_rec.instructors); DBMS_OUTPUT.PUT_LINE ('--------------------'); END IF; END LOOP; END;