User-defined records

Oracle PL/SQL User-defined records are a powerful tool that allows developers to create their own data types. The PL/SQL records can be used to store data in a structured format, or to create custom data types that can be used in pl/sql programs.

Oracle PL/SQL User-defined records can be created using the TYPE statement, and can be used in pl/sql programs by using the TYPE keyword. The syntax for the PL/SQL User-defined records is as follows:

Syntax

DECLARE
TYPE record_table IS RECORD (
	column1  datatype,
	column2  datatype
);
my_records record_table;
BEGIN
SELECT column1, column2
INTO my_records 
FROM table_name;
END;

Example

Phones table

User-defined records

DECLARE
TYPE rec_phones IS RECORD (
	id  NUMBER(9),
	name VARCHAR2(100),
	price NUMBER
);
my_phones rec_phones;
BEGIN
SELECT PHONE_ID, PHONE_NAME, PRICE 
INTO my_phones 
FROM phones WHERE price=900;
DBMS_OUTPUT.PUT_LINE
('Id: '||my_phones.id||' Name: '||my_phones.name||' Price: '||my_phones.price);
END;
/

Output