In Chapter 18, "The Basics of PL/SQL," I presented some of the essentials of PL/SQL programming. As you've seen, PL/SQL supports all the datatypes that are available in SQL. However, PL/SQL also provides the following additional datatypes that aren't available for use in ordinary SQL statements:
In this chapter, I explain the use of PL/SQL variables that are based on these additional datatypes.
One of the additional datatypes that PL/SQL supports is BOOLEAN. The following code shows how you declare a BOOLEAN variable:
SQL> declare 2 Payment_Is_Late boolean; 3 3 begin 4 Payment_Is_Late := TRUE; 5 end; 6 / PL/SQL procedure successfully completed.
You can initialize a BOOLEAN variable to either TRUE or FALSE.
SQL> set serveroutput on
SQL>
SQL> declare
2 Payment_Is_Late boolean := TRUE;
3
3 begin
4
4 dbms_output.enable;
5
5 if Payment_Is_Late then
6 dbms_output.put_line('The payment is late!');
7 end if;
8
8 end;
9 /
The payment is late!
PL/SQL procedure successfully completed.
Until you assign a value to it, a BOOLEAN variable has the null value. In the following example, the BOOLEAN expression Day_of_Month > 5 is assigned to the BOOLEAN variable Payment_Is_Late:
SQL> set serveroutput on
SQL>
SQL> declare
2 Payment_Is_Late boolean;
3 Day_of_Month integer;
4
4 begin
5
5 dbms_output.enable;
6
6 select to_number(to_char(sysdate,'DD'))
7 into Day_of_Month
8 from dual;
9
9 Payment_Is_Late := Day_of_Month > 3;
10
10 if Payment_Is_Late then
11 dbms_output.put_line('The payment is late!');
12 end if;
13
13 end;
14 /
The payment is late!
PL/SQL procedure successfully completed.
The BINARY_INTEGER datatype stores signed integers in the range of [ms]2,147,483,647 to 2,147,483,647. PL/SQL also provides two other datatypes that are subtypes of BINARY_INTEGER.
You might want to declare variables that would never have a fractional part, such as a loop counter, with the NATURAL or POSITIVE datatype.
When you assign a real number to a variable that has been declared as BINARY_INTEGER, NATURAL, or POSITIVE, the number is truncated.
SQL> declare
2 Counter natural;
3
3 begin
4
4 dbms_output.enable;
5
5 Counter := 103.2;
6
6 dbms_output.put_line('Counter: ' || to_char(Counter,'999.999'));
7
7 end;
8 /
Counter: 103.000
PL/SQL offers two notations for referencing Oracle table and column datatypes.
These two datatypes help integrate PL/SQL code with the table and column definitions that exist in the Oracle data dictionary.
To define a variable as having the same datatype as a column, use the %TYPE designation with the following syntax:
variable-name table-name.column-name%TYPE;
where variable-name is the PL/SQL variable being declared and table-name.column-name specifies the column whose datatype should be used for variable-name.
The beauty of using %TYPE is that it generally reduces the amount of work needed to maintain PL/SQL code. As an example, suppose that you've defined the Patient table as follows:
SQL> desc Patient Name Null? Type ------------------------------- -------- ---- PATIENT_ID NOT NULL VARCHAR2(6) BODY_TEMP_DEG_F NUMBER(4,1) FEVER_CLASS VARCHAR2(20) LAST_NAME VARCHAR2(20) FIRST_NAME VARCHAR2(10)
As part of your application, you've written a PL/SQL stored procedure that displays the names of patients whose body temperature exceeds a specified temperature.
SQL> create or replace procedure Display_Feverish_Patients
2 (Patient_Temp in number) is
3
3 Patient_LName Patient.Last_Name%TYPE;
4 Patient_FName Patient.First_Name%TYPE;
5
5 cursor Get_Patient_Names (Temp_Threshold number) is
6 select Last_Name, First_Name
7 from Patient
8 where
9 Body_Temp_Deg_F >= Temp_Threshold;
10
10 begin
11
11 dbms_output.enable;
12
12 open Get_Patient_Names (Patient_Temp);
13
13 loop
14
14 fetch Get_Patient_Names into Patient_LName, Patient_FName;
15 exit when Get_Patient_Names%NOTFOUND;
16
16 dbms_output.put_line ('Feverish patient: ' || Patient_FName || ' ' ||
17 Patient_LName);
18
18 end loop;
19
19 end;
20 /
Procedure created.
SQL> execute Display_Feverish_Patients (101.0);
Feverish patient: HERMAN SMOLTON
Feverish patient: RED FENSTER
PL/SQL procedure successfully completed.
The hospital admitting manager has just notified you that two brothers, Vatadreykopswarthamurthyohm and Vatadreykopswarthamurthy Bhakavandiraniansubrumaniam, have been admitted with fevers of unknown origin. Given the length of their first and last names, you'll need to expand the length of the two columns in the Patient table by using an ALTER TABLE statement.
SQL> alter table Patient modify 2 (Last_Name varchar2(30), 3 First_Name varchar2(30)); Table altered.
Because you used %TYPE to declare the variables of the stored procedure Display_Feverish_Patients, you don't need to make any changes to the stored procedure, which works with the new definition for the table.
SQL> execute Display_Feverish_Patients (104.0); Feverish patient: RED FENSTER Feverish patient: VATADREYKOPSWARTHAMURTHY BHAKAVANDIRANIANSUBRUMANIAM Feverish patient: VATADREYKOPSWARTHAMURTHYOHM BHAKAVANDIRANIANSUBRUMANIAM PL/SQL procedure successfully completed.
You use the %ROWTYPE designation to declare a variable--;a record, really--;whose structure is identical to the structure of a specified table. The syntax for %ROWTYPE is
variable-name table-name%ROWTYPE;
where variable-name is the PL/SQL variable being declared and table-name specifies the table to which variable-name will correspond.
For example, a record named Product_Rec is declared as Product%ROWTYPE. As a result, Product_Rec's fields have the same names and datatypes as the columns of the Product table.
SQL> declare
2
2 Product_Rec Product%ROWTYPE;
3
3 begin
4
4 dbms_output.enable;
5
5 select *
6 into Product_Rec
7 from Product
8 where
9 Product_ID = 'A2001';
10
10 dbms_output.put_line('Product ID: ' || Product_Rec.Product_ID);
11 dbms_output.put_line('Manufacturer ID: ' ||
12 Product_Rec.Manufacturer_ID);
13 dbms_output.put_line('Initial Retail Vaue: ' ||
14 to_char(Product_Rec.Initial_Retail_Value,'$999.99'));
15
15 end;
16 /
Product ID: A2001
Manufacturer ID: TES801
Initial Retail Value: $350.00
PL/SQL procedure successfully completed.
As you can see, the fields of a %ROWTYPE record are referenced by
variable-name.field-name
where variable-name is the name of the declared %ROWTYPE variable and field-name is the name of a column in the table specified in variable-name's declaration.
WarningAlthough you can reference a record declared using %ROWTYPE in a SELECT statement, you cannot reference the entire record with the INSERT statement. For instance, PL/SQL rejects the following INSERT statement:
SQL> declare
2 Patient_Rec Patient%rowtype;
3
3 begin
4
4 Patient_Rec.Patient_ID := 'HHH111';
5 Patient_Rec.Body_Temp_Deg_F := 102.7;
6
6 insert into Patient
7 (Patient_ID, Body_Temp_Deg_F)
8 values
9 Patient_Rec;
10
10 end;
11 /
Patient_Rec;
*
ERROR at line 9:
ORA-06550: line 9, column 1:
PLS-00103: Encountered the symbol "PATIENT_REC"
when expecting one of the follow
an aggregate
Resuming parse at line 9, column 12.
You can also assign one variable to another variable if they are both declared using the %ROWTYPE designation for the same table. The following example illustrates this concept by assigning New_Patient to ER_Patient.
SQL> declare
2
2 New_Patient Patient%ROWTYPE;
3 ER_Patient 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 /
ER_Patient.Body_Temp_Deg_F: 98.6
However, you cannot assign one %ROWTYPE variable to another %ROWTYPE variable if the two variables do not point to the same database table, even if the two tables are identical.
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
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.
NoteUnfortunately, 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, contains 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 offers. 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.
TipIf 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.
The syntax to declare a type for a PL/SQL table is
TYPE type-name IS TABLE OF table-name.column-name%TYPE INDEX BY BINARY_INTEGER;
where type-name is the name of the declared type and 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 declare variables based on that type. For example, in the following anonymous PL/SQL block, Customer_ID_Tab is declared as a table of the column Customer_ID in the Customer table. A cursor FOR LOOP selects each Customer_ID from the Customer table and assigns it to an element in Customer_ID_Tab.
SQL> declare
2 type Customer_ID_Type is table of Customer.Customer_ID%TYPE
3 index by binary_integer;
4
4 Customer_ID_Tab Customer_ID_Type;
5 i binary_integer := 0;
6 final_count binary_integer;
7
7 begin
8
8 dbms_output.enable;
9
9 for Cust_ID_Rec in (select Customer_ID from Customer) loop
10
10 i := i + 1;
11 Customer_ID_Tab(i) := Cust_ID_Rec.Customer_ID;
12
12 end loop;
13
13 final_count := i;
14
14 for i in 1..final_count loop
15
15 dbms_output.put_line('Customer_ID_Tab(' || to_char(i) ||
16 ') = ' || Customer_ID_Tab(i));
17
17 end loop;
18
18 end;
19 /
Customer_ID_Tab(1) = 1001
Customer_ID_Tab(2) = 1002
Customer_ID_Tab(3) = 1003
Customer_ID_Tab(4) = 1004
Customer_ID_Tab(5) = 1005
Customer_ID_Tab(6) = 1006
Customer_ID_Tab(7) = 1007
Customer_ID_Tab(8) = 1008
Customer_ID_Tab(9) = 1009
Customer_ID_Tab(10) = 6101
Customer_ID_Tab(11) = 6102
Customer_ID_Tab(12) = 6103
Customer_ID_Tab(13) = 6104
Customer_ID_Tab(14) = 6105
Customer_ID_Tab(15) = 2222
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. Here's an example of a procedure that returns a PL/SQL table containing Customer_IDs numbered higher than 6,000.
SQL> declare
2 type Customer_ID_Type is table of Customer.Customer_ID%TYPE
3 index by binary_integer;
4
4 Customer_ID_Tab Customer_ID_Type;
5 i binary_integer := 0;
6 Total_Number binary_integer;
7
7
7 procedure Get_Customer_IDs (Num_Rows out binary_integer,
8 Customer_ID_Table out Customer_ID_Type) is
9
9 i binary_integer := 0;
10
10 begin
11
11 for Cust_ID_Rec in (select Customer_ID from Customer
where Customer_ID > 6000) loop
12
12 i := i + 1;
13 Customer_ID_Table(i) := Cust_ID_Rec.Customer_ID;
14
14 end loop;
15
15 Num_Rows := i;
16
16 end Get_Customer_IDs;
17
17 -- Main block.
18
18 begin
19
19 dbms_output.enable;
20
20 Get_Customer_IDs (Total_Number, Customer_ID_Tab);
21
21
21 for i in 1..Total_Number loop
22
22 exit when Customer_ID_Tab(i) = NULL;
23
23 dbms_output.put_line('Customer_ID_Tab(' || to_char(i) ||
24 ') = ' || Customer_ID_Tab(i));
25
25 end loop;
26
26 end;
27 /
Customer_ID_Tab(1) = 6101
Customer_ID_Tab(2) = 6102
Customer_ID_Tab(3) = 6103
Customer_ID_Tab(4) = 6104
Customer_ID_Tab(5) = 6105
NotePL/SQL doesn't restrict the range of the PL/SQL table index; you could start at [ms]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 syntax for declaring a record type is
TYPE type-name IS RECORD (field-name field-datatype [NOT NULL] [initial-value], ... field-name field-datatype [NOT NULL] [initial-value]);
where 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.
field-datatype is the datatype of the field, which can be a specific PL/SQL datatype (such as NUMBER or BOOLEAN) or can reference a column's datatype using the %TYPE designation.
initial-value is an initial value that must be assigned to field-name if it is declared as NOT NULL.
TipOne advantage of the user-defined record is that you can declare fields for storing derived data in a record that isn't stored in the associated database table.
The following example declares a user-defined record type named Patient_Rec_Type that is composed of three fields: Patient_ID, Body_Temp, and Bed_Number. The first two fields exist in the Patient table; however, Body_Temp has a different name--;Body_Temp_Deg_F--;in the table. The third field, Bed_Number, doesn't exist in the Patient table.
SQL> declare
2
2 type Patient_Rec_Type is record
3 (Patient_ID Patient.Patient_ID%TYPE,
4 Body_Temp Patient.Body_Temp_Deg_F%TYPE,
5 Bed_Number varchar2(4));
6
6 Patient_Rec Patient_Rec_Type;
7
7 begin
8
8 dbms_output.enable;
9
9 Patient_Rec.Patient_ID := 'ZZ0123';
10 Patient_Rec.Body_Temp := 98.6;
11 Patient_Rec.Bed_Number := 'A123';
12
12 dbms_output.put_line('Patient ID: ' || Patient_Rec.Patient_ID);
13 dbms_output.put_line('Body_Temp: ' || to_char(Patient_Rec.Body_Temp));
14 dbms_output.put_line('Bed Number: ' || Patient_Rec.Bed_Number);
15
15 insert into Patient
16 (Patient_ID, Body_Temp_Deg_F)
17 values
18 (Patient_Rec.Patient_ID, Patient_Rec.Body_Temp);
19
19 end;
20 /
Patient ID: ZZ0123
Body_Temp: 98.6
Bed Number: A123
By default, all variables are initialized to NULL whenever you enter a procedure, function, or anonymous block. You can initialize a variable in the PL/SQL declare section in two ways:
variable-name data-type := initial-value;
or
variable-name data-type DEFAULT initial-value;
Here is an anonymous block that illustrates both methods of initializing a PL/SQL variable:
SQL> declare 2 2 i natural := 33; 3 my_string varchar2(30) default 'JACKSON'; 4 4 begin 5 5 dbms_output.enable; 6 6 end; 7 / PL/SQL procedure successfully completed.
In this chapter, you learn the following concepts: