June 9, 2015

  By Steve Callan 

I would venture to say that most DBAs (and developers) who use PL/SQL are pretty comfortable with understanding and using the declare, begin and exception sections of a block, anonymous or otherwise.  Sometimes it's hard to expand your PL/SQL skills because of a lack of good examples, and as a DBA, your programming opportunities may be limited.  Additionally, seeing lines upon lines of syntax definitions (although important in the long run) can be somewhat of a turn-off, and incomplete examples or code snippets often times leave you hanging (as in many calculus books, "proof of the following theorem is left to the curious reader."). 

If you peruse enough books, pieces of documentation and web sites, chances are you're going to come across an example that finally sinks in and makes you wonder why you weren't doing that (use of the feature) before.  So with that in mind, let's take a look at moving beyond the mundane SELECT X, Y, Z into v_X, v_Y, v_Z from SOME_TABLE construct (where you also have to declare the v_ variables too).

To move away from the mundane, we need to look at using a composite data type.  Our framework is the employees table in the HR sample schema provided by Oracle.  Let's say you need to work with the employee_id, first_name, last_name, hire_date and salary fields.  We want to declare local or user-defined variables we can select data into later on.

create procedure update_emp (empid in number) is 
v_empid number;
v_fname varchar2(20);
v_lname varchar2(30);
v_hire date;
v_sal number;
begin
--start processing records
end;
/

There are a number of things wrong or potentially wrong with the code.  Aside from no exception handling, my user-defined variables are kind of hit and miss.  It's fairly obvious the name fields are of the VARCHAR2 datatype, but how big? The last name field is actually VARCHAR2(25), not 30, and the number fields make no mention of the precision as found in the EMPLOYEES table.

With respect to sophistication, one step above this example would be to use the %TYPE attribute (anchor the variable to the field's datatype in the table).  The variable declaration would then look like this:

create procedure update_emp (empid in number) is 
v_empid employees.employee_id%type;
v_fname employees.first_name%type;
v_lname employees.last_name%type;
v_hire employees.hire_date%type;
v_sal employees.salary%type;
begin 
--start processing records 
end;
/

If this code were to be part of a named block (something within a function, procedure or package), you would dodge most bullets related to changing the datatype of a field.  In other words, you wouldn't have to hunt down repeated occurrences of VARCHAR2(25) for the last name if you had coded VARCHAR2(25) in multiple locations.

In the "start processing records" area, we are now faced with the tedious coding of populating local variables (only the BEGIN section is shown).

begin
select employee_id, first_name,
last_name, hire_date, salary
into
v_empid, v_fname, v_lname, v_hire, v_sal
from employees 
where employee_id = empid;
--do whatever
end;
/

There is an easier, faster and more efficient way of declaring and using the variables.  We can use a PL/SQL record.  A PL/SQL record stores values of different datatypes, but only one occurrence at a time - which is the same thing we're doing by selecting values into local variables one record at a time.

There are two steps in setting up a PL/SQL record (and these steps are the same when using collections).  One way to look at the steps is to think of them as declare (or define) and instantiate.

create procedure update_emp (empid in number) is
--declare
TYPE emp_record_type IS RECORD
(v_empid employees.employee_id%type;
v_fname employees.first_name%type;
v_lname employees.last_name%type;
v_hire employees.hire_date%type;
v_sal employees.salary%type;
);
--instantiate
emp_record emp_record_type;
begin
...

Now the question is about how to select values into the PL/SQL record.  It's easy.  All you have to do is select the matching columns into the emp_record.

begin
select employee_id, first_name, last_name,
hire_date, salary
into emp_record
from employees 
where employee_id = empid;
--do whatever
end;

If you need to reference a field, it is done by using "dot" notation.  If I wanted to set salary to 110% of the current value (a 10% raise), then the syntax would be:

emp_record.salary := emp_record.salary * 1.1;

The notation is "record_name.field_name" to describe it more formally.

With PL/SQL records, you have the option of defining your own fields (as in selecting a subset of the columns in a table, which is why a TYPE is declared first) or to make things even easier, grab all fields/columns in one fell swoop.  It's not uncommon to select all columns from a table, and a PL/SQL record makes this extremely easy to do so.  In the example below, the "pl" procedure takes an input string and calls DBMS_OUTPUT.PUT_LINE(the input string).

create procedure update_emp (empid in number) is
emp_record employees%rowtype;
begin
select *
into emp_record
from employees 
where employee_id = empid;
pl('last name is '||emp_record.last_name);
end;
/

Let's suppose you wanted to take the current values and insert them into an archive table (whose structure matches the base table).  All you need to do is this:

insert into other_table values emp_record;

After some values have been updated/modified, and wanting to update the source table with the new values, one approach would be to use a simple update statement and set the column values to whatever based on a where clause. 

But with PL/SQL records, why repeat all those "set column_A = whatever, column_B=whatever" and so on? You can update a row using the SET ROW feature within PL/SQL records.

create or replace procedure update_emp (empid in number) is
emp_record employees%ROWTYPE;
begin
--get the data
select * into emp_record 
from employees
where employee_id = empid;
--do some assignments
emp_record.hire_date := sysdate;
emp_record.salary := emp_record.salary*1.1;
--go back and update the table
UPDATE employees 
SET ROW = emp_record 
WHERE empno=empid; 
end;
/ 

That's pretty hard to beat in terms of having to write less code to do some fairly routine tasks.

There are other things you can do with PL/SQL records.  When declaring a TYPE, you can define as many fields as you want, and further, assign default values and NOT NULL constraints.  Fields with NOT NULL must be initialized, and a field cannot be a REF CURSOR.  If you want, you can also nest records, so that a field in one PL/SQL record is another PL/SQL record in of itself.

A distinction between PL/SQL records and collections is that a record contains related data consisting of different data types (like a row in a table).  A collection contains data of the same datatype, but the data is unrelated (i.e., collect all last names, which are the same datatype, but obviously unrelated).

Peformance-wise, if you're selecting the majority of columns from a table, then selecting all of them via %ROWTYPE may not add any significant overhead, and in turn, makes the select into a record step much simpler.  If using the SET ROW for an update, and only one or a few columns are affected, all columns will be processed (you won't be able to tell that "King" was updated to "King").

In Closing

Using PL/SQL records can be fairly easy and they can add a lot more robustness to your code (especially when using anchored datatypes).  They can be used in anonymous and named blocks, and with some simple naming conventions, their identifiers will be clear (e.g., a variable you declare is prefixed with v underscore, and a record you declare has "rec" or record in its name, and the same follows with types). 

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.