PL/SQL Records

PL/SQL RECORDS

In this article we discuss on below points :

. Declare Record
. Assigning values to a record
. Advantage using records
. Inserting a row using record
. Updating a row using record

Before starting assume we have a table xx_emp

CREATE TABLE XX_EMP (
 eno NUMBER,
 enmae VARCHAR2(15),
 dept VARCHAR2(15));

BEGIN
INSERT INTO XX_EMP VALUES (101,'Nag','EEE');
INSERT INTO XX_EMP VALUES (101,'Adi','CSE');
INSERT INTO XX_EMP VALUES (101,'Om','Mech');
COMMIT;
END;
/

Declaration of a record :

We can declare a record in 2 ways
1. Using TYPE keyword
2. Using %ROWTYPE

We can declare a record in a PL/SQL Declare section.

DECLARE
--declare record using TYPE
 TYPE AddrRecTyp IS RECORD (
  DoorNo VARCHAR2(15),
  street VARCHAR2(15),
  city   VARCHAR2(15),
  state  VARCHAR2(15),
  country VARCHAR2(15),
  pincode number(6) );
--create a variable of record type
vAddrRec1 AddrRecTyp;
--create a rec variable using %ROWTYPE
vempRec xx_emp%ROWTYPE;
BEGIN
--Assigning Values to record
vAddrRec1.DoorNo:='4-59-240/2';
vAddrRec1.street:='baystreet';
vAddrRec1.city:='vizag';
vAddrRec1.state:='Andhra';
vAddrRec1.country:='India';
vAddrRec1.pincode:=531162;
dbms_output.put_line(rpad('DoorNo',15)||rpad('street',15)||
       rpad('city',15)||rpad('state',15)||
       rpad('country',15)||rpad('pincode',15));
dbms_output.put_line(rpad('--',13,'-')||rpad('--',13,'-')||
       rpad('--',13,'-')||rpad('--',13,'-')||
       rpad('--',13,'-')||rpad('--',13,'-'));
dbms_output.put_line(rpad(vAddrRec1.DoorNo,15)||rpad(vAddrRec1.street,15)||
       rpad(vAddrRec1.city,15)||rpad(vAddrRec1.state,15)||
       rpad(vAddrRec1.country,15)||rpad(vAddrRec1.pincode,15));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error Occurred');
END;
/

--SELECT INTO record

DECLARE
x NUMBER;
y VARCHAR2(15);
Z VARCHAR2(15);
TYPE EmpRecTyp IS RECORD (
 no xx_emp.eno%type,
 name xx_emp.ename%type );
VempRec EmpRecTyp;
CURSOR empcur IS
SELECT eno,ename FROM XX_EMP
WHERE eno=101;
BEGIN
--so here we need 2 variables to hold
--the 2 values of emp rec
OPEN empcur;
FETCH empcur into x,y;
CLOSE empcur;
dbms_output.put_line('y : '||y);
--If we use record code becomes compact
--and need not declare separate variables
OPEN empcur;
FETCH empcur into VempRec;
CLOSE empcur;
dbms_output.put_line('Eno : '||VempRec.name);

END;
/

--Inserting a pl/sql record

DECLARE
vemprec xx_emp%ROWTYPE;
BEGIN
vemprec.eno:=104;
vemprec.ename:='Sri';
vemprec.dept:='ECE';
INSERT INTO xx_emp VALUES vemprec;
COMMIT;
END;
/

--Updating a row using record

DECLARE
vemprec xx_emp%ROWTYPE;
BEGIN
vemprec.eno:=104;
vemprec.ename:='Sri';
vemprec.dept:='Civil';
--While updating make sure you give
--all values in the record
UPDATE xx_emp SET ROW = vemprec where eno=104;
commit;
END;/

Thanks!!