Oracle Magazine - May/June 2012

Code Listing 1: Insert of a single row with each column specified

DECLARE
l_employee_id omag_employees.employee_id%TYPE
:= 2000;

BEGIN
INSERT
INTO omag_employees (employee_id,
last_name,
salary)
VALUES (
l_employee_id,
l_last_name,
l_salary);

area_code PLS_INTEGER,
exchange PLS_INTEGER,
phn_number PLS_INTEGER,
TYPE contact_rt IS RECORD
(
day_phone# phone_rt,
eve_phone# phone_rt,
l_sales_rep contact_rt;

cell_phone# phone_rt );

declare your own, user-defined record types by using the T YPE. . . RECORD statement.

User-defined record types come in handy when you find yourself declaring “sets” of individual variables, such as

DECLARE
l_name1 VARCHAR2 (100);
l_total_sales1 NUMBER;
l_deliver_pref1 VARCHAR2 ( 10);
--
l_name2 VARCHAR2 (100);
l_total_sales2 NUMBER;
l_deliver_pref2 VARCHAR2 ( 10);

Instead, why not create your own record type and then declare two records:

DECLARE
TYPE customer_info_rt IS RECORD
(
name VARCHAR2 (100),
total_sales NUMBER,
deliver_pref VARCHAR2 ( 10)

l_customer1 customer_info_rt; l_customer2 customer_info_rt;

(Note that when I declare types, I use a root “t” suffix and then add the “type of type.” Here I added “_rt” for record type.)

With this approach, you do more than
avoid writing repetitive statements. You also
document that those three pieces of infor-
mation are all related to a customer. And
once you’ve “moved up” to using a record,
you can pass that record as an argument
or perform record-level operations, further
reducing the volume of code needed to
implement your requirements.

DECLARE
TYPE user_preferences_rt IS RECORD
(
show_full_name BOOLEAN,
l_user user_preferences_rt;

autologin BOOLEAN );

Records are, themselves, PL/SQL-specific datatypes, so another nice feature of user-defined record types is that you can define a record type as a field in another record type. In the declaration section below, I have created one record type that holds the different numeric elements that make up a telephone number. I then create another record to hold the various telephone numbers for a salesperson:

DECLARE TYPE phone_rt IS RECORD (

COMPOSI TE DATAT YPES TO THE RESCUE!

PL/SQL’s support for records, one of several composite datatypes, enables you to write code that is simple, clean, and easy to maintain. Rather than work with long lists of variables or parameters, you can work with a record that contains all that information. User-defined records offer the flexibility to construct your own composite datatype, reflecting program-specific requirements that may not be represented by a relational table.

In the next article in this PL/SQL 101 series, I will explore another key composite datatype, the collection. Collections, PL/SQL’s implementation of arraylike structures, are used in some of the most important performance-related PL/SQL features, including FORALL and BULK COLLEC T. 

Steven Feuerstein
(steven.feuerstein@
quest.com) is Quest
Software’s PL/SQL
evangelist. He has
published 10 books on
Oracle PL/SQL (O’Reilly Media) and is an Oracle
ACE Director. More information is available at
stevenfeuerstein.com.