1. What is a RECORD in Oracle?
A RECORD is a PL/SQL composite data type that can store multiple fields of different data types in a single variable.
- Think of it as a row in a table stored in PL/SQL.
- Each field is called a component or attribute.
2. How do I declare a RECORD type?
You can declare a RECORD using the TYPE keyword, then create a variable of that type.
Example:
DECLARE
TYPE student_rec IS RECORD (
id NUMBER,
name VARCHAR2(50),
marks NUMBER
);
s student_rec;
BEGIN
s.id := 101;
s.name := 'Alice';
s.marks := 95;
DBMS_OUTPUT.PUT_LINE('Student: ' || s.name || ' - Marks: ' || s.marks);
END;
/
3. Can a RECORD be based on a table structure?
Yes, you can define a RECORD using %ROWTYPE to automatically match a table’s row structure.
Example:
DECLARE
s employees%ROWTYPE;
BEGIN
s.employee_id := 1001;
s.first_name := 'John';
DBMS_OUTPUT.PUT_LINE('Employee: ' || s.first_name);
END;
/
- %ROWTYPE ensures your RECORD matches the table structure.
- Useful for working with table rows dynamically.
4. How do I access fields in a RECORD?
Use dot notation: record_name.field_name.
Example:
DBMS_OUTPUT.PUT_LINE('ID: ' || s.id);
DBMS_OUTPUT.PUT_LINE('Name: ' || s.name);
5. Can RECORDs store different data types?
Yes, each field in a RECORD can have a different data type, e.g., NUMBER, VARCHAR2, DATE, etc.
Example:
TYPE order_rec IS RECORD (
order_id NUMBER,
customer_name VARCHAR2(50),
order_date DATE
);
6. Can RECORDs be used in loops?
You cannot loop directly over fields, but you can loop through collections of RECORDs.
Example:
DECLARE
TYPE student_tab IS TABLE OF student_rec;
students student_tab := student_tab();
BEGIN
students.EXTEND(2);
students(1) := student_rec(101, 'Alice', 95);
students(2) := student_rec(102, 'Bob', 88);
FOR i IN 1..students.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Name: ' || students(i).name || ', Marks: ' || students(i).marks);
END LOOP;
END;
/
7. Can RECORDs contain other RECORDs?
Yes, RECORDs can be nested inside other RECORDs for complex data structures.
Example:
TYPE address_rec IS RECORD (
street VARCHAR2(50),
city VARCHAR2(50)
);
TYPE student_rec IS RECORD (
id NUMBER,
name VARCHAR2(50),
addr address_rec
);
8. Can RECORDs be stored in database tables?
No, RECORDs exist only in PL/SQL. They cannot be stored directly in a table.
- Use OBJECT types or collections if you want to store composite structures in tables.
9. Advantages of RECORDs
- Combine multiple related fields in a single variable.
- Simplifies PL/SQL programming.
- Works well with %ROWTYPE for table rows.
- Supports modular and readable code.
10. Limitations of RECORDs
- PL/SQL-only; cannot be used directly in SQL.
- Cannot loop over fields individually.
- Nested RECORDs can become complex to manage.