| Lab Objectives After this Lab, you will be able to: A record structure is somewhat similar to a row of a database table. Each data item is stored in a field with its own name and datatype. For example, suppose you have various data about a company, such as name, address, and number of employees. A record containing a field for each of these items allows you to treat a company as a logical unit, thus making it easier to organize and represent company's information. Table-Based and Cursor-Based Records The %ROWTYPE attribute enables you to create table-based and cursor-based records. It is similar to the %TYPE attribute that is used to define scalar variables. Consider the following example of a table-based record.  FOR EXAMPLE DECLARE course_rec course%ROWTYPE; BEGIN SELECT * INTO course_rec FROM course WHERE course_no = 25; DBMS_OUTPUT.PUT_LINE ('Course No: '|| course_rec.course_no); DBMS_OUTPUT.PUT_LINE ('Course Description: '|| course_rec.description); DBMS_OUTPUT.PUT_LINE ('Prerequisite: '|| course_rec.prerequisite); END; The course_rec record has the same structure as a row from the COURSE table. As a result, there is no need to reference individual record fields when the SELECT INTO statement populates the course_rec record. However, note that a record does not have a value of its own; rather, each individual field holds a value. Therefore, to display record information on the screen, individual fields are referenced using the dot notation, as shown in the DBMS_OUTPUT.PUT_LINE statements. When run, this example produces the following output: Course No: 25 Course Description: Intro to Programming Prerequisite: 140 PL/SQL procedure successfully completed.  | As mentioned previously, a record does not have a value of its own. For this reason, you cannot test records for nullity, equality, or inequality. In other words, the statements |
IF course_rec IS NULL THEN … IF course_rec1 = course_rec2 THEN … are illegal and will cause syntax errors.
Next, consider an example of a cursor-based record. FOR EXAMPLE DECLARE CURSOR student_cur IS SELECT first_name, last_name, registration_date FROM student WHERE rownum <= 4; student_rec student_cur%ROWTYPE; BEGIN OPEN student_cur; LOOP FETCH student_cur INTO student_rec; EXIT WHEN student_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('Name: '|| student_rec.first_name||' '|| student_rec.last_name); DBMS_OUTPUT.PUT_LINE ('Registration Date: '|| student_rec.registration_date); END LOOP; END; The student_rec record has the same structure as the rows returned by the STUDENT_CUR cursor. As a result, similar to the previous example, there is no need to reference individual fields when data is fetched from the cursor to the record. When run, this example produces the following output: Name: Fred Crocitto Registration Date: 22-JAN-99 Name: J. Landry Registration Date: 22-JAN-99 Name: Laetia Enison Registration Date: 22-JAN-99 Name: Angel Moskowitz Registration Date: 22-JAN-99 PL/SQL procedure successfully completed. Note that because a cursor-based record is defined based on the rows returned by a select statement of a cursor, its declaration must be proceeded by a cursor declaration. In other words, a cursor-based record is dependent on a particular cursor and cannot be declared prior to its cursor. Consider a modified version of the previous example. The cursor-based record variable is declared before the cursor, and as a result, when run, this example causes a syntax error. FOR EXAMPLE DECLARE student_rec student_cur%ROWTYPE; CURSOR student_cur IS SELECT first_name, last_name, registration_date FROM student WHERE rownum <= 4; BEGIN OPEN student_cur; LOOP FETCH student_cur INTO student_rec; EXIT WHEN student_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('Name: '|| student_rec.first_name||' '|| student_rec.last_name); DBMS_OUTPUT.PUT_LINE ('Registration Date: '|| student_rec.registration_date); END LOOP; END; student_rec student_cur%ROWTYPE; * ERROR at line 2: ORA-06550: line 2, column 16: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 2, column 16: PL/SQL: Item ignored ORA-06550: line 12, column 30: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 12, column 7: PL/SQL: SQL Statement ignored ORA-06550: line 16, column 10: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 15, column 7: PL/SQL: Statement ignored ORA-06550: line 17, column 52: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 17, column 7: PL/SQL: Statement ignored User-Defined Records So far, you have seen how to create records based on a table or a cursor. However, you may need to create a record that is not based on any table or any one cursor. For such situations, PL/SQL provides a user-defined record type that allows you to have complete control over the record structure. The general syntax for creating a user-defined record is as follows (the reserved words and phrases surrounded by brackets are optional): TYPE type_name IS RECORD (field_name1 datatype1 [NOT NULL] [ := DEFAULT EXPRESSION], field_name2 datatype2 [NOT NULL] [ := DEFAULT EXPRESSION], … field_nameN datatypeN [NOT NULL] [ := DEFAULT EXPRESSION]); record_name TYPE_NAME; First, a record structure is defined using the TYPE statement, where TYPE_NAME is the name of the record type that is used in the second step to declare the actual record. Enclosed in the parentheses are declarations of each record field with its name and datatype. You may also specify a NOT NULL constraint and/or assign a default value. Second, the actual record is declared based on the type specified in the previous step. Consider the following example. FOR EXAMPLE DECLARE TYPE time_rec_type IS RECORD (curr_date DATE, curr_day VARCHAR2(12), curr_time VARCHAR2(8) := '00:00:00'); time_rec TIME_REC_TYPE; BEGIN SELECT sysdate INTO time_rec.curr_date FROM dual; time_rec.curr_day := TO_CHAR(time_rec.curr_date, 'DAY'); time_rec.curr_time := TO_CHAR(time_rec.curr_date, 'HH24:MI:SS'); DBMS_OUTPUT.PUT_LINE ('Date: '||time_rec.curr_date); DBMS_OUTPUT.PUT_LINE ('Day: '||time_rec.curr_day); DBMS_OUTPUT.PUT_LINE ('Time: '||time_rec.curr_time); END; In this example, the time_rec_type is a user-defined record type that contains three fields. Notice that the last field, curr_time, has been initialized to a particular value. The time_rec is a user-defined record based on the time_rec_type. Notice that, different from the previous examples, each record field is assigned a value individually. When run, the script produces the following output: Date: 30-MAR-02 Day: SATURDAY Time: 18:12:59 PL/SQL procedure successfully completed. As mentioned earlier, when declaring a record type you may specify a NOT NULL constraint for individual fields. It is important to note that such fields must be initialized. Consider an example that causes a syntax error because a record field has not been initialized after a NOT NULL constraint has been defined on it. FOR EXAMPLE DECLARE TYPE sample_type IS RECORD (field1 NUMBER(3), field2 VARCHAR2(3) NOT NULL); sample_rec sample_type; BEGIN sample_rec.field1 := 10; sample_rec.field2 := 'ABC'; DBMS_OUTPUT.PUT_LINE ('sample_rec.field1 = '|| sample_rec.field1); DBMS_OUTPUT.PUT_LINE ('sample_rec.field2 = '|| sample_rec.field2); END; field2 VARCHAR2(3) NOT NULL); * ERROR at line 4: ORA-06550: line 4, column 8: PLS-00218: a variable declared NOT NULL must have an initialization assignment Next, consider the correct version of the preceding example and its output. FOR EXAMPLE DECLARE TYPE sample_type IS RECORD (field1 NUMBER(3), field2 VARCHAR2(3) NOT NULL := 'ABC'); -- initialize a NOT NULL field sample_rec sample_type; BEGIN sample_rec.field1 := 10; DBMS_OUTPUT.PUT_LINE ('sample_rec.field1 = '|| sample_rec.field1); DBMS_OUTPUT.PUT_LINE ('sample_rec.field2 = '|| sample_rec.field2); END; sample_rec.field1 = 10 sample_rec.field2 = ABC PL/SQL procedure successfully completed. Record Compatibility You have seen that a record is defined by its name, structure, and type. However, it is important to realize that two records may have the same structure yet be of a different type. As a result, there are certain restrictions that apply to the operations between different record types. Consider the following example. FOR EXAMPLE DECLARE TYPE name_type1 IS RECORD (first_name VARCHAR2(15), last_name VARCHAR2(30)); TYPE name_type2 IS RECORD (first_name VARCHAR2(15), last_name VARCHAR2(30)); name_rec1 name_type1; name_rec2 name_type2; BEGIN name_rec1.first_name := 'John'; name_rec1.last_name := 'Smith'; name_rec2 := name_rec1; -- illegal assignment END; In this example, both records have the same structure; however, each record is of a different type. As a result, these records are not compatible with each other on the record level. In other words, an aggregate assignment statement will cause an error as follows: name_rec2 := name_rec1; -- illegal assignment * ERROR at line 15: ORA-06550: line 15, column 17: PLS-00382: expression is of wrong type ORA-06550: line 15, column 4: PL/SQL: Statement ignored In order to assign name_rec1 to name_rec2, you can assign each field of name_rec1 to the corresponding field of name_rec2, or you can declare name_rec2 so that it has the same datatype as name_rec1, as follows: FOR EXAMPLE DECLARE TYPE name_type1 IS RECORD (first_name VARCHAR2(15), last_name VARCHAR2(30)); name_rec1 name_type1; name_rec2 name_type1; BEGIN name_rec1.first_name := 'John'; name_rec1.last_name := 'Smith'; name_rec2 := name_rec1; -- no longer illegal assignment END; It is important to note that the assignment restriction just mentioned applies to the user-defined records. In other words, you can assign a table-based or a cursor-based record to a user-defined record as long as they have the same structure. Consider the following example. FOR EXAMPLE DECLARE CURSOR course_cur IS SELECT * FROM course WHERE rownum <= 4; TYPE course_type IS RECORD (course_no NUMBER(38), description VARCHAR2(50), cost NUMBER(9,2), prerequisite NUMBER(8), created_by VARCHAR2(30), created_date DATE, modified_by VARCHAR2(30), modified_date DATE); course_rec1 course%ROWTYPE; -- table-based record course_rec2 course_cur%ROWTYPE; -- cursor-based record course_rec3 course_type; -- user-defined record BEGIN -- Populate table-based record SELECT * INTO course_rec1 FROM course WHERE course_no = 10; -- Populate cursor-based record OPEN course_cur; LOOP FETCH course_cur INTO course_rec2; EXIT WHEN course_cur%NOTFOUND; END LOOP; course_rec1 := course_rec2; course_rec3 := course_rec2; END; In this example, each record is a different type; however, they are compatible with each other because all records have the same structure. As a result, this example does not cause any syntax errors. Lab 19.1 Exercises 19.1.1 Use Table-Based and Cursor-Based Records In this exercise, you will learn more about table-based and cursor-based records. Create the following PL/SQL script: -- ch19_1a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE zip_rec zipcode%ROWTYPE; BEGIN SELECT * INTO zip_rec FROM zipcode WHERE rownum < 2; END; Answer the following questions: | a) | Explain the script ch19_1a.sql. | | b) | Modify the script so that zip_rec data is displayed on the screen. | | c) | Modify the script created in the previous exercise (ch19_1b.sql) so that zip_rec is defined as a cursor-based record. | | d) | 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. |
19.1.2 Use User-Defined Records In this exercise, you will learn more about user-defined records. Create the following PL/SQL script: -- ch19_2a.sql, version 1.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; BEGIN FOR zip_rec IN zip_cur LOOP zip_info_rec.zip_code := zip_rec.zip; zip_info_rec.students := zip_rec.students; END LOOP; END; Answer the following questions: | a) | Explain the script ch19_2a.sql. | | 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. | | c) | 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. |
Lab 19.1 Exercise Answers 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. | | c) | 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. | | d) | 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. | | c) | 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; |
Lab 19.1 Self-Review Questions In order to test your progress, you should be able to answer the following questions. Answers appear in Appendix A, Section 19.1. | 1) | The %ROWTYPE attribute allows you to specify _____ table-based records only. _____ cursor-based records only. _____ table-based and cursor-based records.
| | 2) | When creating a user-defined record, you must _____ initialize all of its fields. _____ initialize at least one of its fields. _____ initialize a field only if there is a NOT NULL constraint defined in it.
| | 3) | An aggregate assignment statement will cause an error if table-based and cursor-based records have the same structure. _____ True _____ False
| | 4) | An aggregate assignment statement will cause an error if two user-defined records have the same structure yet different types. _____ True _____ False
| | 5) | An aggregate assignment statement will cause an error if table-based and user-defined records have the same structure. _____ True _____ False
|
|
