Lab 19.1 Records

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 19.  Records


Lab Objectives

After this Lab, you will be able to:

  • Use Table-Based and Cursor-Based Records

  • Use User-Defined Records

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.

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

graphics/intfig04.gif

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.

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

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

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

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

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

graphics/intfig03.gif 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:

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

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

graphics/intfig07.gif

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

  1. _____ table-based records only.

  2. _____ cursor-based records only.

  3. _____ table-based and cursor-based records.

2)

When creating a user-defined record, you must

  1. _____ initialize all of its fields.

  2. _____ initialize at least one of its fields.

  3. _____ 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.

  1. _____ True

  2. _____ False

4)

An aggregate assignment statement will cause an error if two user-defined records have the same structure yet different types.

  1. _____ True

  2. _____ False

5)

An aggregate assignment statement will cause an error if table-based and user-defined records have the same structure.

  1. _____ True

  2. _____ False


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

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