A PL/SQL Record is a group of related data items stored in individual fields, each with its own attribute name and data type. To access any field of a record, we use the dot (.) operator.
PL/SQL can handle the following type of records:
- Table Based Record.
- Cursor Based Record.
- User Define Records.
Table Based Record:
%ROWTYPE attributes enables a programmer to create table based and cursor based records.
We are using the EMP table to show the concept of Table Based Record.
DECLARE
emp_rec emp%rowtype;
BEGIN
SELECT * into emp_rec
FROM emp
WHERE empno=7839 ;
dbms_output.put_line('Employee ID: ' || emp_rec.empno);
dbms_output.put_line('Employee Name: ' || emp_rec.ename);
dbms_output.put_line('Employee Department: ' || emp_rec.deptno);
dbms_output.put_line('Employee Salary: ' || emp_rec.sal);
END;
Cursor Based Record:
For Cursor Based Record also we are using %ROWTYPE. To show the concept of Cursor Based Record we are using MEP table.
DECLARE
CURSOR emp_cur is
SELECT EMPNO, ENAME, SAL
FROM emp;
emp_rec emp_cur%rowtype;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur into emp_rec;
EXIT WHEN emp_cur%notfound;
DBMS_OUTPUT.put_line(emp_rec.empno || ' ' || emp_rec.ename);
END LOOP;
END;
User Define Record:
PL/SQL provides a user-defined record type that allows you to define the different record structures.
These records consist of different fields. Suppose you want to keep track of your employee details.
You might want to track the following attributes about each employee−
- Employee Name
- Employee ID
- Employee SAL
- Employee Department Number
Now we are creating the above record
DECLARE
TYPE employee_details_typ IS RECORD
(emp_name varchar2(50),
emp_id number,
emp_sal number,
emp_dent_no number
);
/*Now we are declaring record-name type_name;*/
EMP_record1 employee_details_typ ;
EMP_record2 employee_details_typ ;
.....
Example:
DECLARE
TYPE employee_details_typ IS RECORD
(emp_name varchar2(50),
emp_id number,
emp_sal number,
emp_dent_no number
);
emp_datils1 employee_details_typ;
emp_datils2 employee_details_typ;
BEGIN
-- employee1 specification
emp_datils1.emp_name := 'Rabindra';
emp_datils1.emp_id := 2315;
emp_datils1.emp_sal := 50000;
emp_datils1.emp_dent_no := 10;
-- employee1 specification
emp_datils2.emp_name := 'Samir';
emp_datils2.emp_id := 2316;
emp_datils2.emp_sal := 50000;
emp_datils2.emp_dent_no := 20;
END;