Record data type

In Pl Sql exists a lot of data types : varchar2, number, float, double, boolean, etc, but it may
happen that you want to use a structure that contains several types of data, we call it Record type.
It’s a simply a new data type. Record type is formed by a group of one or more columns with own
name and data type.
To create Record type have to use the keyword TYPE so to instruct the compiler about this new
data type.
There are two ways to create it , the first is at “database level” which can be stored and used all
over database like any other data type (example 1), the second is at “program level” which is used
only in the program (example 2).
If Record type is created at “database level” it can be declared for the table column, so a single
column can hold a complex data.
To store data into this structure is possible to accept value for each single column of this record
(example 2) or use it like a single record of many columns (example 3).
Here below some example to create Record type:
EXAMPLE 1:
create type emp_rec is record
(
id number,
name varchar2(100),
position varchar2(200),
salary number
);
/
Explanation :
This code create a record type at database level.
The emp_rec is the name of object that has 4 columns id, name, position and salary
emp_rec object is visible in all over database similar to other datatypes (varchar2, number, and so
on) after declared a variable of this type.
EXAMPLE 2:
declare
type emp_rec is record
(
id number,
name varchar2(100),
position varchar2(200),
salary number
);
my_emp emp_rec;
begin
my_emp.id=45;
my_emp.name=’Steve Austin’;
my_emp.position=’Astronaut’;
my_emp.salary=’200000′;
end;
Explanation :
This code create a record type at program level.
The emp_rec is the name of object that has 4 columns id, name, position and salary.
emp_rec object is visible only in this program.
You can be accessed to data of this data type using the variable declared as emp_rec_type
(my_emp in our example) dot(.) followed by column name.
i.e. . i.e. my_emp.salary
EXAMPLE 3:
declare
type emp_rec is record
(
id number,
name varchar2(100),
position varchar2(200),
salary number
);
my_emp emp_rec;
begin
insert into emp (id_emp, name_emp, pos_emp, sal_emp) values (42,’Steve Austin’, ‘Pilot’, 20000);
select id_emp, name_emp, pos_emp, sal_emp
into my_emp
from emp
where id_emp = 42;
dbms_output(‘Value for id => ‘||my_emp.id);
dbms_output(‘Value for name => ‘||my_emp.name);
dbms_output(‘Value for position => ‘||my_emp.position);
dbms_output(‘Value for salary => ‘||my_emp.salary);
end;
Explanation :
This code create a record type at program level.
The emp_rec is the name of object that has 4 columns id, name, position and salary.
The program use the emp table that has the same columns of our data types (emp_rec).
The program populate the emp table and after that populate my_emp as a row level data using a
select query.
Finally it print the values presents into my_emp variable.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *