Records are the data structures, which can hold data items, data items of different kinds. These consists of different fields, similar to a row of database details.
They are three different kinds of records: -
- Table-based
The %ROWTYPE attribute enables the programmer for creating table-based and cursorbased records.
Example:
DECLARE
teacher_rec teachers%rowtype;
BEGIN
SELECT * into teacher_rec
FROM teachers
WHERE id = 2;
dbms_output.put_line('teachers ID: ' || teacherer_rec.id);
dbms_output.put_line('teachers Name: ' || teacher_rec.name);
dbms_output.put_line('teachers Address: ' || teacher_rec.address);
dbms_output.put_line('teachers Salary: ' || teacher_rec.salary);
END;
/
Output:
teacher ID: 2
teacher Name: sai
teacher Address: Hyderabad
teacher Salary: 2500
PL/SQL procedure successfully completed.
Cursor-based records
Example:
DECLARE
CURSOR teacher_cur is
SELECT id, name, address
FROM teachers;
teacher_rec teacher_cur%rowtype;
BEGIN
OPEN teacher_cur;
LOOP
FETCH teacher_cur into teacher_rec;
EXIT WHEN teacher_cur%notfound;
DBMS_OUTPUT.put_line(teacher_rec.id || ' ' || teacher_rec.name);
END LOOP;
END;
/
Output:
1 ranjitha
2 sai
3 shakthi
4 vignesh
5 gayathri
PL/SQL procedure successfully completed.
User-defined records
We have user-defined records in PL/SQL that allows us for defining the record structures. And these records, consists of different fields.
Syntax:
TYPE
type_name IS RECORD
( field_name1 datatype1 [NOT NULL] [:= DEFAULT EXPRESSION],
field_name2 datatype2 [NOT NULL] [:= DEFAULT EXPRESSION],
...
field_nameN datatypeN [NOT NULL] [:= DEFAULT EXPRESSION);
record-name type_name;