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;