Page 274
Listing 10.16. continued
13 to_char(ER_Patient.Body_Temp_Deg_F)); 14 14 end; 15 / ER_Patient.Body_Temp_Deg_F: 98.6
However, you cannot assign one %ROWTYPE variable to another %ROWTYPE variable if the two variables don't point to the same database table, even if the two tables are identical.
Listing 10.17. %ROWTYPE variables based on different tables cannot be assigned to one another.
SQL> create table Identical_Patient as 2 select * from Patient; Table created. SQL> set serveroutput on SQL> SQL> declare 2 2 New_Patient Patient%ROWTYPE; 3 ER_Patient Identical_Patient%ROWTYPE; 4 4 begin 5 5 dbms_output.enable; 6 6 select * 7 into New_Patient 8 from Patient 9 where 10 Patient_ID = `ZZ0123'; 11 11 ER_Patient := New_Patient; 12 12 dbms_output.put_line(`ER_Patient.Body_Temp_Deg_F: ` || 13 to_char(ER_Patient.Body_Temp_Deg_F)); 14 14 end; 15 / declare * ERROR at line 1: ORA-06550: line 11, column 15: PLS-00382: expression is of wrong type ORA-06550: line 11, column 1: PL/SQL: Statement ignored
Page 275
PL/SQL supports two additional composite datatypes: tables and records. Each of these objects is first declared as a datatype, and then the actual PL/SQL table or record is declared based upon the specified datatype.
You can think of a PL/SQL table as an array: it consists of a single field. Also, you don't declare an upper limit on the number of elements that a PL/SQL table can contain; its size is dynamic.
NEW TERM
A PL/SQL table is a collection of elements of the same type, ordered by an index number.
| NOTE |
It is unfortunate that Oracle chose to apply the label table to a structure that is more appropriately described as an array. A PL/SQL table, unlike a database table, is composed of a single column. As with an array, the values of a PL/SQL table are accessed by an index. Just remember that a PL/SQL table and a database table are two distinct objects with very specific characteristics and uses. |
A user-defined record offers more flexibility than the %ROWTYPE designation. You should consider using a user-defined record when both of the following conditions are true:
The following section delves into the use of PL/SQL tables.
| TIP |
If you declare a user-defined record type that is associated with a database table, use the %TYPE designation for each field that mirrors a column in the database table. It reduces the effort needed to maintain PL/SQL code in response to those inevitable database changes. |
A type for a PL/SQL table is declared using the following syntax:
TYPE type-name IS TABLE OF table-name.column-name%TYPE INDEX BY BINARY_INTEGER;
Page 276
The variables are defined as follows:
type-name is the name of the declared type.
table-name.column-name specifies the column whose datatype is the base type for type-name.
After you've declared a PL/SQL table type, you can then declare variables based on that type. For example, in Listing 10.18, which contains an anonymous PL/SQL block, Class_ID_Tab is declared as a table of the column Class_ID in the Class table. A cursor FOR LOOP selects each Class_ID from the Class table and assigns it to an element in Class_ID_Tab. The example shown in Listing 10.18 was performed using SQL Worksheet.
Listing 10.18. Using a PL/SQL table.
SQLWKS> set serveroutput on
Server Output ON
SQLWKS> declare
2>
3> type Class_ID_Type is table of Class.Class_ID%TYPE
4> index by binary_integer;
5>
6> Class_ID_Tab Class_ID_Type;
7> i binary_integer := 0;
8> final_count binary_integer;
9>
10> begin
11>
12> dbms_output.enable;
13>
14> for Class_ID_Rec in (select Class_ID from Class) loop
15>
16> i := i + 1;
17> Class_ID_Tab(i) := Class_ID_Rec.Class_ID;
18>
19> end loop;
20>
21> final_count := i;
22>
23> for i in 1..final_count loop
24>
25> dbms_output.put_line(`Class_ID_Tab(` || to_char(i) || `) = ` ||
26> Class_ID_Tab(i));
27> end loop;
28>
29> end;
30> /
Statement processed.
Class_ID_Tab(1) = 104200
Class_ID_Tab(2) = 104500
Class_ID_Tab(3) = 109100
Class_ID_Tab(4) = 120200
Page 277
Class_ID_Tab(5) = 110300 Class_ID_Tab(6) = 108300 Class_ID_Tab(7) = 108400 Class_ID_Tab(8) = 108600 Class_ID_Tab(9) = 103400 Class_ID_Tab(10) = 103600
You can pass a PL/SQL table as an argument to a procedure or function. Along with the PL/SQL table, you'll also want to pass a BINARY_INTEGER variable that indicates the number of elements in the PL/SQL table. Listing 10.19 illustrates an example of a procedure that returns a PL/SQL table containing Course_IDs for which the additional fees exceed $50.
Listing 10.19. Returning a PL/SQL table.
SQLWKS> declare
2>
3> type Course_ID_Type is table of Course.Course_ID%TYPE
4> index by binary_integer;
5> Course_ID_Tab Course_ID_Type;
6>
7> i binary_integer := 0;
8> Total_Number binary_integer;
9>
10> procedure Get_Course_IDs (Num_Rows out binary_integer,
11> Course_ID_Table out Course_ID_Type) is
12>
13> i binary_integer := 0;
14>
15> begin
16>
17> for Course_ID_Rec in (select Course_ID from Course
18> where Additional_Fees > 50) loop
19>
20> i := i + 1;
21> Course_ID_Table(i) := Course_ID_Rec.Course_ID;
22>
23> end loop;
24>
25> Num_Rows := i;
26>
27> end Get_Course_IDs;
28>
29> -- Main block.
30>
31> begin
32>
33> dbms_output.enable;
34>
35> Get_Course_IDs (Total_Number, Course_ID_Tab);
36>
37> for i in 1..Total_Number loop
38> exit when Course_ID_Tab(i) = NULL;
continues
Page 278
Listing 10.19. continued
39> dbms_output.put_line(`Course_ID_Tab(` || to_char(i) ||
40> `) = ` || Course_ID_Tab(i));
41> end loop;
42>
43> end;
44> /
Statement processed.
Course_ID_Tab(1) = 101
Course_ID_Tab(2) = 189
Course_ID_Tab(3) = 101
Course_ID_Tab(4) = 178
Course_ID_Tab(5) = 177
Course_ID_Tab(6) = 174
Course_ID_Tab(7) = 181
Course_ID_Tab(8) = 501
ANALYSIS
In Listing 10.19, the first line begins with the declaration section of an anonymous block. The type Course_ID_Type is declared in line 3. A procedure, Get_Course_IDs, is declared beginning on line 10. In line 13, the index for the PL/SQL table Course_ID_Table is initialized to 0. The execution section of the anonymous block begins on line 31. The procedure Get_Course_IDs is invoked on line 35 in the execution section of the anonymous block.
| NOTE |
PL/SQL doesn't restrict the range of the PL/SQL table index; you could start at _100, 0, 1, or any other number that is appropriate. |
The process for using a user-defined record is much like that of a PL/SQL table: you define a datatype for the record and then declare variables based on the new type.
The following is the syntax for declaring a record type:
TYPE type-name IS RECORD (field-name field-datatype [NOT NULL] [initial-value], ... field-name field-datatype [NOT NULL] [initial-value]);
The variables for declaring a record type are defined as follows:
type-name is the name of the declared record type.
field-name is the name of the field and subject to PL/SQL variable-name restrictions.