Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter