PL/SQL Records

Records in PL/SQL programs are very similar in concept and structure to the rows of a database table. A record is a composite data structure, which means that it is composed of more than one element or component, each with its own value. The record as a whole does not have value of its own; instead, each individual component or field has a value. The record gives you a way to store and access these values as a group.

If you are not familiar with using records in your programs, you might initially find them complicated. When used properly, however, records will greatly simplify your life as a programmer. You will often need to transfer data from the database into PL/SQL structures and then use the procedural language to further massage, change, or display that data. When you use a cursor to read information from the database, for example, you can pass that table's record directly into a single PL/SQL record. When you do this you preserve the relationship between all the attributes from the table.

 

 

What are records?

Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc.  Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row.

Declaring a record:

To declare a record, you must first define a composite datatype; then declare a record for that type.

The General Syntax to define a composite datatype is: 

TYPE record_type_name IS RECORD (first_col_name column_datatype, second_col_name column_datatype, ...);

• record_type_name – it is the name of the composite type you want to define.
• first_col_name, second_col_name, etc.,- it is the names the fields/columns within the record.
• column_datatype defines the scalar datatype of the fields.

There are different ways you can declare the datatype of the fields. 1) You can declare the field in the same way as you declare the fieds while creating the table.
2) If a field is based on a column from database table, you can define the field_type as follows: 

col_name table_name.column_name%type;

By declaring the field datatype in the above method, the datatype of the column is dynamically applied to the field.  This method is useful when you are altering the column specification of the table, because you do not need to change the code again. 


NOTE: You can use also %type to declare variables and constants.

The General Syntax to declare a record of a uer-defined datatype is: 

 

record_name record_type_name;

The following code shows how to declare a record called employee_rec based on a user-defined type.

DECLARE TYPE employee_type IS RECORD (employee_id number(5),  employee_first_name varchar2(25),  employee_last_name employee.last_name%type,  employee_dept employee.dept%type);  employee_salary employee.salary%type; employee_rec employee_type;

If all the fields of a record are based on the columns of a table, we can declare the record as follows: 

record_name table_name%ROWTYPE;

For example, the above declaration of employee_rec can as follows: 

DECLARE  employee_rec employee%ROWTYPE;

The advantages of declaring the record as a ROWTYPE are:

1)  You do not need to explicitly declare variables for all the columns in a table.
2) If you alter the column specification in the database table, you do not need to update the code.
The disadvantage of declaring the record as a ROWTYPE is:
1) When u create a record as a ROWTYPE, fields will be created for all the columns in the table and memory will be used to create the datatype for all the fields. So use ROWTYPE only when you are using all the columns of the table in the program.


NOTE: When you are creating a record, you are just creating a datatype, similar to creating a variable. You need to assign values to the record to use them.

The following table consolidates the different ways in which you can define and declare a pl/sql record.

Syntax	Usage
TYPE record_type_name IS RECORD (column_name1 datatype, column_name2 datatype, ...);	Define a composite datatype, where each field is scalar.
col_name table_name.column_name%type;	Dynamically define the datatype of a column based on a database column.
record_name record_type_name;	Declare a record based on a user-defined type.
record_name table_name%ROWTYPE;	Dynamically declare a record based on an entire row of a table. Each column in the table corresponds to a field in the record.

Passing Values To and From a Record

When you assign values to a record, you actually assign values to the fields within it.
The General Syntax to assign a value to a column within a record direclty is:

record_name.col_name := value;

If you used %ROWTYPE to declare a record, you can assign values as shown:

record_name.column_name := value;

We can assign values to records using SELECT Statements as shown:

SELECT col1, col2 INTO record_name.col_name1, record_name.col_name2 FROM table_name [WHERE clause];

If %ROWTYPE is used to declare a record then you can directly assign values to the whole record instead of each columns separately. In this case, you must SELECT all the columns from the table into the record as shown: 

SELECT * INTO record_name FROM table_name [WHERE clause];

Lets see how we can get values from a record. 

The General Syntax to retrieve a value from a specific field into another variable is:

var_name := record_name.col_name;

The following table consolidates the different ways you can assign values to and from a record: 

Syntax	Usage
record_name.col_name := value;	To directly assign a value to a specific column of a record.
record_name.column_name := value;	To directly assign a value to a specific column of a record, if the record is declared using %ROWTYPE.
SELECT col1, col2 INTO record_name.col_name1, record_name.col_name2 FROM table_name [WHERE clause];	To assign values to each field of a record from the database table.
SELECT * INTO record_name FROM table_name [WHERE clause];	To assign a value to all fields in the record from a database table.
variable_name := record_name.col_name;	To get a value from a record column and assigning it to a variable.

Guidelines for Using Records

Use of PL/SQL records can have a dramatic impact on your programs, both in initial development and in ongoing maintenance. To ensure that I personally get the most out of record structures, I have set the following guidelines for my development:

• Create corresponding cursors and records. Whenever I create a cursor in my programs, I also create a corresponding record (except in the case of cursor FOR loops). I always FETCH into a record, rather than into individual variables. In those few instances when it might involve a little extra work over simply fetching into a single variable, I marvel at the elegance of this approach and compliment myself on my commitment to principle.
• Create table-based records. Whenever I need to store table-based data within my programs, I create a new (or use a predefined) table-based record to store that data. I keep my variable use to a minimum and dynamically link my program data structures to my RDBMS data structures with the %ROWTYPE attribute.
• Pass records as parameters. Whenever appropriate, I pass records rather than individual variables as parameters in my procedural interfaces. This way, my procedure calls are less likely to change over time, making my code more stable. There is a downside to this technique, however: if a record is passed as an OUT or IN OUT parameter, its field values are saved by the PL/SQL program in case of the need for a rollback. This can use up memory and consume unnecessary CPU cycles.

Comparing Two Records

While it is possible to stay at the record level in certain situations, you can't avoid direct references to fields in many other cases. If you want to compare records, for example, you must always do so through comparison of the records' individual fields.

Suppose you want to know if the old company information is the same as the new company information, both being stored in records of the same structure. The following test for equality will not compile:

IF old_company_rec = new_company_rec /-- Illegal syntax! THEN    ... END IF;

even though the structures of the two records are absolutely identical and based on the same record type (in this case, a table record type).

PL/SQL will not automatically compare each individual field in the old company record to the corresponding field in the new company record. Instead, you will have to perform that detailed check yourself, as in:

IF old_company_rec.name = new_company_rec.name AND  

old_company_rec.incorp_date = new_company_rec.incorp_date AND 

old_company_rec.address1 = new_company_rec.address1 AND THEN   

.. the two records are identical ... END IF;

Of course, you do not simply examine the value of a particular field when you work with records and their fields. Instead, you will assign values to the record and its fields, from either scalar variables or other records. You can reference a record's field on both sides of the assignment operator. In the following example I change the contents of a record, even though that record was just filled from a cursor:

DECLARE    CURSOR company_cur IS ...;    

company_rec company_cur%ROWTYPE; BEGIN    OPEN company_cur;   

FETCH company_cur INTO company_rec;    company_rec.name := 'New Name'; 

END;