PL/SQL supports three types of records

Records: PL/SQL Record is a combination of fields. Records are used to represent complete row in a table. ‘Record is a group of multiple piece of information, related to one another, called fields’.

PL/SQL supports three types of records:

Table Based Record represents each field in the table. Fields in the record have the same name and datatype as the columns in the table have. Table Based Record is declared with an anchored declaration-using %ROWTYPE.

Write the PL/SQL code to display the ename and sal of emp whose empno is input by user, using table-based record.

DECLARE

RS EMP%ROWTYPE;

BEGIN

SELECT * INTO RS FROM EMP WHERE EMPNO=&ENO;

DBMS_OUTPUT.PUT_LINE(RS.ENAME ||RS.SAL);

END;

Programmer Defined Record is declared and defined explicitly by the programmer as per requirement. Firstly a RECORD type is defined and then variable of that RECORD type is declared.

Write the PL/SQL code to display the ename and sal of emp whose empno is input by user, using programmer-based record.

DECLARE

TYPE RS_EMP IS RECORD

(

EMP_NAME EMP.ENAME%TYPE,

EMP_SAL EMP.SAL%TYPE

);

RS RS_EMP;

BEGIN

SELECT ENAME, SAL INTO RS FROM EMP WHERE EMPNO=&ENO;

DBMS_OUTPUT.PUT_LINE(RS.EMP_NAME);

DBMS_OUTPUT.PUT_LINE(RS.EMP_SAL);

END;

Cursor Based Record is composite variable that represents each field in a cursor.  The fields of record have same name and datatype as the fields of the cursor on which the record is based. It is declared using %ROWTYPE