PLSQL RECORD IN ORACLE

PLSQL RECORD  IN ORACLE

PLSQL RECORD 

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;