1. What are composite data types in Oracle?
Composite data types are data structures that can store multiple values in a single variable. They allow grouping related data together for easier manipulation in PL/SQL.
Oracle supports these main composite types:
- Records – group different data types under one variable
- Collections – group multiple elements of the same type
- Associative Arrays (Index-by tables)
- Nested Tables
- VARRAYs (Variable-size arrays)
2. What is a RECORD type in Oracle?
A record is a composite type that allows storing a row of data with multiple fields. Each field can have a different data type.
Example:
DECLARE
TYPE emp_rec_type IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.first_name%TYPE,
emp_salary employees.salary%TYPE
);
emp_rec emp_rec_type;
BEGIN
emp_rec.emp_id := 101;
emp_rec.emp_name := 'John';
emp_rec.emp_salary := 5000;
DBMS_OUTPUT.PUT_LINE(emp_rec.emp_name || ' earns ' || emp_rec.emp_salary);
END;
Use case: Combine related fields and pass them around as a single variable.
3. What are collections in Oracle?
Collections are composite types that store multiple elements of the same type. Oracle supports three collection types:
|
Type |
Description |
|
Associative Array |
Key-value pair table (index-by table) |
|
Nested Table |
Unordered set of elements that can be stored in database columns |
|
VARRAY |
Ordered, bounded array of elements; maintains order |
4. What is an associative array (index-by table)?
- Uses unique keys to reference elements
- Can have integer or string keys
- Fully in-memory, used for fast lookups
Example:
DECLARE
TYPE emp_table IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
emp_names emp_table;
BEGIN
emp_names(1) := 'Alice';
emp_names(2) := 'Bob';
DBMS_OUTPUT.PUT_LINE(emp_names(2)); -- Outputs 'Bob'
END;
5. What is a nested table?
- A nested table is unordered and unbounded
- Can be stored in database columns
- Can be manipulated with SQL and PL/SQL
Example:
DECLARE
TYPE emp_nt_type IS TABLE OF employees.first_name%TYPE;
emp_nt emp_nt_type := emp_nt_type();
BEGIN
emp_nt.EXTEND;
emp_nt(1) := 'Alice';
emp_nt.EXTEND;
emp_nt(2) := 'Bob';
DBMS_OUTPUT.PUT_LINE(emp_nt(2)); -- Outputs 'Bob'
END;
6. What is a VARRAY?
- A variable-size array with a maximum size limit
- Maintains order
- Can be stored in database tables
Example:
DECLARE
TYPE emp_varray_type IS VARRAY(5) OF employees.first_name%TYPE;
emp_varray emp_varray_type := emp_varray_type('Alice', 'Bob');
BEGIN
DBMS_OUTPUT.PUT_LINE(emp_varray(1)); -- Outputs 'Alice'
END;
7. How do you access elements in composite types?
- Records: Use record_name.field_name
- Collections: Use collection_name(index)
Example with record and collection:
DECLARE
TYPE emp_rec_type IS RECORD (id NUMBER, name VARCHAR2(20));
TYPE emp_nt_type IS TABLE OF emp_rec_type;
emp_list emp_nt_type := emp_nt_type();
BEGIN
emp_list.EXTEND;
emp_list(1).id := 101;
emp_list(1).name := 'John';
DBMS_OUTPUT.PUT_LINE(emp_list(1).name);
END;
8. Can composite types be used in procedures/functions?
Yes. You can pass records or collections as parameters:
CREATE OR REPLACE PROCEDURE print_emp_names(p_emps IN emp_nt_type) IS
BEGIN
FOR i IN 1..p_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(p_emps(i));
END LOOP;
END;
Great for passing multiple values without creating multiple parameters.
9. Advantages of composite data types
- Reduce code complexity by grouping related data
- Allow row-wise and set-wise operations in PL/SQL
- Support modular and reusable code
- Can interface with SQL (nested tables and VARRAYs)
10. Common mistakes with composite types
- Forgetting to initialize collections before use (EXTEND or constructor)
- Exceeding VARRAY size limits
- Misusing associative array indexes
- Using uninitialized records
11. Best practices
- Use records for row-like structures
- Use associative arrays for in-memory lookups
- Use nested tables for sets that need SQL interaction
- Use VARRAYs for small, fixed-size, ordered lists
- Always initialize collections before adding elements
No comments:
Post a Comment