PL/SQL Records

Objects of type RECORD are called PL/SQL records
PL/SQL records have uniquely named fields, which can belong to different datatypes
Define a RECORD type

TYPE IS RECORD

(fieldname1
:
fieldnameN ;

(%TYPE and %ROWTYPE can be used to specify ]
Example 1 of Record Type –

Declare

TYPE empdetails IS RECORD
(eno Emp.Empno%type,
name Emp.Ename%type,
s Emp.Sal%type);

VE empdetails;

Begin
Select empno,ename,sal Into  VE
from Emp
where ename = ‘SMITH’;
dbms_output.put_line(VE.eno || ‘ – ‘ || VE.name || ‘-‘ ||VE.s);
End;

Example 2 of Record Type –

Declare
TYPE si IS RECORD
(p number,
n number,
r number := 4.5);

/* r variable of si type has been given value. */
VSI  si;
x number;
Begin
VSI.p  :=  5000;
VSI.n  :=  6;
x := (VSI.p * VSI.n * VSI.r) / 100;
dbms_output.put_line(x);
End;

Using a record type in another record type.

Declare
type Address_details is record
(sector char(3),
colony varchar2(50),
bldg_name varchar2(25),
pincode number(7));
type personal_details is record
(name varchar2(60),
Addr  Address_Details,
age number);

V personal_details;
Begin
V.name := ‘John’;
V.Addr.sector := ‘S1’;
V.Addr.colony := ‘Model’;
V.Addr.bldg_name := ‘Hill View’;
V.Addr.pincode := 6775;
dbms_output.put_line(‘The building name is ‘ || V.Addr.bldg_name);
dbms_output.put_line(‘The pincode is  ‘ ||V.Addr.pincode);
End;

  ————————————-

%Rowtype with Record

declare

type t1 is record
(ed emp%rowtype,
dd dept%rowtype);
VT1 t1;
Begin
select * into VT1.ed
from emp
where ename = ‘KING’;

 select * into VT1.dd
from dept
where dname = ‘ACCOUNTING’;
dbms_output.put_line(VT1.ed.ename);
dbms_output.put_line(VT1.dd.dname);
end;

Posted in UncategorizedTagged