PL SQL Record Type :

In my previous article i have explained about the different datatypes of oracle with examples.In this article i will try to explain the PL SQL complex datatypes with examples.The complex datatypes are those which holds the data in to different record types or arrays.PL SQL Record Type are nothing but the composite datatype which holds the values of different scalar datatypes together.

PL SQL Record Type are types which holds the combination of different scalar datatypes together.

Record Type is the complex data type which allows the programmer to create a new data type with the desirable column structure.

About PL SQL Record Types :

There are so many situation where user needs to use the different types of columns together.So PL SQL gives us the solution to define our own datatype which contains different scalar datatypes which is also known as record types.Following are some bullet points user needs to remember :

• It groups one or more datatypes and creates new type with combination of those datatypes.
• The New record type has its own name and its columns are defined separately as simple datatypes.
• The Record type is nothing but the separate datatype which is stored as a new datatype in the database.
• User needs to use ‘Type’ keyword to instruct the compiler to create new datatype.
• The database level record type can also be declared for the table columns so that single column can hold the complex data.
• The data in these data type can be accessed by referring to their variable_name followed by period operator (.) followed by column_name i.e.                              ‘<record_type_variable_name>.<column_name>’

Syntax and examples of PL SQL Record Type :

Creation of record type is at program level.

Syntax :

Create type type_name is Record

(Column1 Datatype(size),

Column2 Datatype(size), …

…Column’N’ Datatype(size));

The Create type statement is used to create record type of specific datatypes.This is given as individual statement not inside the block.

Example of PL SQL Record Type as Database object :

If user wants to use the specific datatype of Student table which contains Student information like Roll_No,Name and subjects of student.

Create type Student_Type is Record

(Roll_No number,

Name Varchar2(30),

Subjects Varchar2(30));

Output :

Type Created

Here the datatype named Student_Type is created at database level. User can use that record type in any program.This record type is reusable to different PL SQL blocks.

The following syntax is used to define the record type.The Declaration of type is always at subprogram level :

TYPE Name_of_Record IS RECORD

(Column1 Datatype(size),

Column2 Datatype(size), …

…Column’N’ Datatype(size));

Name_of_Record :

The Name_of_Record is nothing but the name given to that specific record.This should be the name of name of composite type you want to define.

Column1..Column’N’ :

These are nothing but different names of fields user wants to use to create record types.

Datatypes :

The Datatypes are nothing but different scalar datatypes.

Example of Record Type at program level :

Declare                                             —Declaration of type variable

type Student_Type is Record

(Roll_No number,

Name Varchar2(30),

Subjects Varchar2(30));

Type_Stud Student_Type;      —Here We need to create the variable for new type declared

Begin

Type_Stud.Roll_No:=’1′;

Type_Stud.Name:=’Amit’;

Type_Stud.Subjects:=’Maths’;

dbms_output.put_line(‘Student Information:’||Type_Stud.Roll_No||’ ‘||Type_Stud.Name||’ ‘||Type_Stud.Subjects);

end;

Output :

Student Information : 1 Amit Maths

These are some examples of Record type in PL SQL.This means user can create record type in program as well as separately also.

Example of Record Type at subprogram level for row level access :

Declare                                             —Declaration of type variable

type Student_Type is Record

(Roll_No number,

Name Varchar2(30),

Subjects Varchar2(30));

Type_Stud Student_Type;      —Here We need to create the variable for new type declared

Begin

Insert into Student

values (‘1′,’Amit’,’Maths’);

Commit;

Select Roll_No, Name ,Subjects  into Student_Type from student where Roll_No=’1′;

dbms_output.put_line(‘Student Information:’||Type_Stud.Roll_No||’ ‘||Type_Stud.Name||’ ‘||Type_Stud.Subjects);

end;

Output :

Student Information : 1 Amit Maths

The record type can be accessed only in column level while redirecting its value to any output mode. The record types are used by pl sql to define combination of different scalar types. Hope this article is useful for everyone.Please comment in comment section if you like this article.