PLSQL RECORD IN ORACLE

A record is a composite datatype, which means that it can hold more than one piece of information, as compared to a scalar datatype, such as a number or string. It’s rare, in fact, that the data with which you are working is just a single value, so records and other composite datatypes are likely to figure prominently in your PL/SQL programs.

Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc.  Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row.


What are records?
Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc.  Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row.


Declaring a record:
To declare a record, you must first define a composite datatype; then declare a record for that type.



The General Syntax to define a composite datatype is:

TYPE record_type_name IS RECORD
(first_col_name column_datatype,
second_col_name column_datatype, ...);
record_type_name – it is the name of the composite type you want to define.
first_col_name, second_col_name, etc.,- it is the names the fields/columns within the record.
column_datatype defines the scalar datatype of the fields.



SET SERVEROUT ON
DECLARE
  -- CREATE RECORD DATA TYPE
  TYPE EMPRECTYPE IS RECORD(LAST_NAME EMPLOYEES.LAST_NAME%TYPE,
                            SALARY    EMPLOYEES.SALARY%TYPE);
  -- DECLARE VARIABLE OF THAT RECORD DATA TYPE
  EMPREC EMPRECTYPE;
BEGIN
  SELECT LAST_NAME,SALARY
  INTO   EMPREC
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID=&EMPNO;

  DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);
END;