 |
|
EnterpriseDB: Records
Oracle Tips by
Burleson
|
Like a
database table, an SPL record is composed of at least one column but
is usually composed of multiple columns. If you are familiar with
PL/SQL, you should be familiar with SPL records as they are exactly
the same concept as the PL/SQL version. If you are more familiar with
C-like languages, a record is much like a STRUCT and is used for the
same purposes.
The ROWTYPE
keyword creates a variable with a record type of the associated
table. In the example above, the variable v_emp_record was associated
with the structure of the emp table. That means that all of the
columns available in the table are also available in the variable.
We will learn
about using SQL in SPL later but a very good use, one you will use
frequently, is selecting a record from a table directly into a record
variable. I'll show an example of that below.
Another way to
declare a variable that is a user defined record type is to use the
TYPE..RECORD keywords. When you use the TYPE..RECORD keywords, you
are not declaring a record, you are declaring a data type for use in
your program. That means that once you have the record data type
declared, you must define a variable of that type. You do not need to
do this extra step with the %ROWTYPE because the data type was already
created (as a table).
Declaring a
RECORD type is very similar to declaring a table. The format of the
RECORD declaration is:
TYPE <type
name> IS RECORD (
<column name> <data type> [, <more columns>]
);
Once you've
declared the RECORD type, you need to declare a variable using that
type:
DECLARE
TYPE
r_user_record IS RECORD (
emp_name emp.ename%TYPE,
emp_dept emp.deptno%TYPE,
emp_length_in_service INTERVAL ); -- Step 1
v_user_record r_user_record; -- Step 2
v_emp_record emp%ROWTYPE; -- Step 3
BEGIN
-- Step 4
SELECT *
INTO v_emp_record
FROM emp
LIMIT 1;
-- Step 5
DBMS_OUTPUT.PUT_LINE( 'ename: ' ||
v_emp_record.ename ||
', deptno: ' || v_emp_record.deptno ||
', emp_length_in_service: ' || sysdate - v_emp_record.hiredate
);
-- Step 6
SELECT ename, deptno, sysdate -
hiredate
INTO v_user_record
FROM emp
LIMIT 1;
-- Step 7
DBMS_OUTPUT.PUT_LINE( 'emp_name: ' || v_user_record.emp_name ||
', emp_dept: ' || v_user_record.emp_dept ||
', emp_length_in_service: ' ||
v_user_record.emp_length_in_service );
-- Step 8
SELECT ename, deptno
INTO v_user_record.emp_name, v_user_record.emp_dept
FROM emp
LIMIT 1;
-- Step 9
DBMS_OUTPUT.PUT_LINE( 'emp_name: '
|| v_user_record.emp_name ||
', emp_dept: ' || v_user_record.emp_dept );
END;
INFO: ename: SMITH, deptno: 20, emp_length_in_service: @ 9465 days 7
hours 57
mins 54 secs
INFO: emp_name: SMITH, emp_dept: 20, emp_length_in_service: @ 9465
days 7 hours
57 mins 54 secs
INFO: emp_name: SMITH, emp_dept: 20
At step 1, I
created the RECORD type, r_user_record. The record has three columns
(or elements), emp_name, emp_dept and emp_length_in_service). At this
point, r_user_record is just like VARCHAR2 or NUMBER. The only
difference is that it is not a SCALAR type, it is a composite type
made up of multiple elements.
Step 2 creates
the variable using my new r_user_record data type.
Step 3 creates
a record variable using the %ROWTYPE notation. Note that I am not
declaring a type here; I am declaring a variable that will be used
directly. This record variable has the same structure as the table
that it is referencing, emp.
In Step 4, I
am selecting from the emp table directly into my %ROWTYPE variable.
Notice that in the first query, I selected * (which returns all
columns).
Step 5
displays the output to the terminal via the DBMS_OUTPUT package.
DBMS_OUTPUT is a package that allows you to print text and variables
to the output device. It is syntactically compatible with the Oracle
DBMS_OUTPUT package.
I calculate
the emp_length_in_service by subtracting the employee's hiredate from
sysdate (which is the current date and time).
In Step 6, I
selected the same number of columns from the emp table as I had in my
user defined record type and was able to select directly into the
record variable. I calculated the emp_length_in_service in the query
rather than after the fact. Where you choose to do your calculations
is a matter of choice.
Step 7
displays the output of our new record.
If you are not
selecting the same number (and type) of columns into a user defined
record, you should specifically assign each selected column to a
column in the record (as I did in Step 8). You must use the syntax
of:
<record
variable>.<column name>
In addition to
selecting into a record variable, you can assign values manually:
DECLARE
TYPE r_user_record IS RECORD (
emp_name emp.ename%TYPE,
emp_dept emp.deptno%TYPE,
emp_length_in_service INTERVAL );
v_user_record r_user_record;
v_emp_record emp%ROWTYPE;
BEGIN
v_emp_record.ename :=
'Lewis';
v_user_record.emp_name := 'Lewis';
END;
The syntax for
a manual assignment is the same as named assignment and is a record
variable followed by the assignment operator and a value:
<record
variable>.<column name> := <value>;
You can also
assign a record to a record of the same structure directly or you can
assign elements of one record to another record:
DECLARE
TYPE r_user_record IS RECORD (
emp_name emp.ename%TYPE,
emp_dept emp.deptno%TYPE,
emp_length_in_service INTERVAL );
v_user_record_1 r_user_record;
v_user_record_2 r_user_record;
v_emp_record emp%ROWTYPE;
BEGIN
v_emp_record.ename := 'Lewis';
v_emp_record.deptno := 1;
v_user_record_1.emp_name := v_emp_record.ename;
v_user_record_1.emp_dept :=
v_emp_record.deptno;
v_user_record_2 := v_user_record_1;
DBMS_OUTPUT.PUT_LINE( 'emp_name: ' || v_user_record_2.emp_name ||
', emp_dept: ' || v_user_record_2.emp_dept );
END;
INFO: emp_name: Lewis, emp_dept: 1
Records really
get powerful when you combine them with collections.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
