PL/SQL RECORD

In Oracle PL/SQL, the RECORD data type is used to define a composite data type that consists of one or more fields. A RECORD data type is similar to a row in a database table, but it is not associated with any particular table. Instead, it is used to group related data items together into a single data structure.

Syntax

To define a RECORD data type, you use the following syntax:

TYPE record_type IS RECORD (
   field1 datatype1,
   field2 datatype2,
   ...
);

Here, record_type is the name of the data type, and field1, field2, etc. are the names of the fields in the record. Each field is defined with a datatype, such as VARCHAR2 or NUMBER.

Example

Once you have defined a RECORD data type, you can declare variables of that type and assign values to its fields. For example:

DECLARE
   TYPE person_type IS RECORD (
      first_name VARCHAR2(50),
      last_name VARCHAR2(50),
      age NUMBER
   );

   p person_type;
BEGIN
   p.first_name := 'John';
   p.last_name := 'Doe';
   p.age := 30;
END;

In this example, we have defined a person_type RECORD data type with three fields: first_name, last_name, and age. We then declare a variable p of type person_type and assign values to its fields using dot notation.

You can also use RECORD data types as parameters to procedures and functions. For example:

CREATE OR REPLACE PROCEDURE print_person(p_person person_type) IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Name: ' || p_person.first_name || ' ' || p_person.last_name);
   DBMS_OUTPUT.PUT_LINE('Age: ' || p_person.age);
END;

This procedure takes a person_type RECORD as a parameter and prints out the person’s name and age.

In summary, the RECORD data type in Oracle PL/SQL is a useful way to group related data items together into a single data structure. It can be declared and assigned values like any other variable, and can be used as a parameter to procedures and functions.