Oracle PL SQL Tutorial day 4
Oracle PL/SQL is a powerful procedural extension to SQL, widely used for writing complex business logic within the Oracle Database. One of the essential features of PL/SQL is its support for composite data types, which allow developers to group related data items into single logical units. This feature enhances code clarity, reuse, and efficiency.
In this blog post, we’ll explore composite data types in Oracle PL/SQL in depth. We’ll cover:
- What composite data types are
- Types of composite data types
- Syntax and usage
- Real-life examples and best practices
-----------------------------------------------------------
What Are Composite Data Types?
-----------------------------------------------------------
A composite data type is a data type that can hold multiple values of the same or different data types. Unlike scalar data types (such as NUMBER, VARCHAR2, or DATE), composite types can group variables logically—similar to records or arrays in general programming languages.
Oracle PL/SQL supports two main types of composite data types:
1. PL/SQL Records
2. PL/SQL Collections
- - Associative Arrays (Index-By Tables)
- - Nested Tables
- - VARRAYs (Variable-Size Arrays)
-----------------------------------------------------------
1. PL/SQL Records
-----------------------------------------------------------
A record is a group of related data items of different types. Think of it like a row in a table or a struct in C.
Syntax:
---------
TYPE record_type IS RECORD (
field1 datatype,
field2 datatype,
...
);
Example: Using a Record to Hold Employee Data
--------------------------------------------------
DECLARE
TYPE emp_rec_type IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(100),
hire_date DATE
);
emp_rec emp_rec_type;
BEGIN
emp_rec.emp_id := 101;
emp_rec.emp_name := 'John Doe';
emp_rec.hire_date := SYSDATE;
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.emp_id);
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.emp_name);
DBMS_OUTPUT.PUT_LINE('Hire Date: ' || emp_rec.hire_date);
END;
Using %ROWTYPE to Fetch Data:
-------------------------------------
DECLARE
emp_row employees%ROWTYPE;
BEGIN
SELECT * INTO emp_row FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_row.first_name || ' ' || emp_row.last_name);
END;
2. PL/SQL Collections
A collection is a group of elements of the same type, like arrays or lists.
Three types of collections:
| Type | Persistent? | Can Be Sparse? | Bound? |
|------------------|-------------|----------------|---------|
| Associative Array| No | Yes | No |
| Nested Table | Yes | Yes | No |
| VARRAY | Yes | No | Yes |
2.1 Associative Arrays (Index-By Tables)
Syntax:
---------
TYPE assoc_array_type IS TABLE OF element_type INDEX BY PLS_INTEGER;
Example: Simple Lookup Table
DECLARE
TYPE salary_table_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
salaries salary_table_type;
BEGIN
salaries(1001) := 55000;
salaries(1002) := 72000;
DBMS_OUTPUT.PUT_LINE('Salary of Emp 1002: ' || salaries(1002));
END;
Looping Through an Associative Array:
DECLARE
TYPE emp_names_type IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_names emp_names_type;
i PLS_INTEGER;
BEGIN
emp_names(1) := 'Alice';
emp_names(2) := 'Bob';
emp_names(3) := 'Charlie';
i := emp_names.FIRST;
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('Emp ' || i || ': ' || emp_names(i));
i := emp_names.NEXT(i);
END LOOP;
END;
2.2 Nested Tables
Syntax:
---------
TYPE nested_table_type IS TABLE OF element_type;
Example: Nested Table of Numbers
DECLARE
TYPE num_table_type IS TABLE OF NUMBER;
numbers num_table_type := num_table_type(10, 20, 30, 40);
BEGIN
FOR i IN 1 .. numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || numbers(i));
END LOOP;
END;
Example: BULK COLLECT with Nested Table
DECLARE
TYPE emp_id_table IS TABLE OF employees.employee_id%TYPE;
emp_ids emp_id_table;
BEGIN
SELECT employee_id BULK COLLECT INTO emp_ids FROM employees WHERE department_id = 10;
FOR i IN 1 .. emp_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_ids(i));
END LOOP;
END;
2.3 VARRAYs (Variable-Size Arrays)
Syntax:
TYPE varray_type IS VARRAY(n) OF datatype;
Example: VARRAY of Course Names
------------------------------------
DECLARE
TYPE course_list_type IS VARRAY(5) OF VARCHAR2(50);
course_list course_list_type := course_list_type('SQL', 'PL/SQL', 'Java');
BEGIN
FOR i IN 1 .. course_list.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Course ' || i || ': ' || course_list(i));
END LOOP;
END;
-----------------------------------------------------------
Using Composite Types in Procedures
-----------------------------------------------------------
Example: Procedure Using a Record Parameter
----------------------------------------------
DECLARE
TYPE emp_info_type IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(100)
);
PROCEDURE show_emp_info(emp IN emp_info_type) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('ID: ' || emp.emp_id || ', Name: ' || emp.emp_name);
END;
my_emp emp_info_type;
BEGIN
my_emp.emp_id := 200;
my_emp.emp_name := 'Diana';
show_emp_info(my_emp);
END;
-----------------------------------------------------------
Best Practices
-----------------------------------------------------------
- Use %ROWTYPE for table-based records
- Use collections for bulk operations with BULK COLLECT and FORALL
- Prefer associative arrays for temporary in-memory lookups
- Use records to group related data logically
-----------------------------------------------------------
Summary Table
-----------------------------------------------------------
| Composite Type | Example Usage | Allows SQL Ops | Sparse? | Persistent? |
|----------------|------------------------|----------------|---------|-------------|
| Record | Group employee fields | No | N/A | No |
| Assoc. Array | In-memory lookup table | No | Yes | No |
| Nested Table | Bulk operations | Yes | Yes | Yes |
| VARRAY | Fixed-size lists | Yes | No | Yes |
-----------------------------------------------------------
Conclusion
-----------------------------------------------------------
Composite data types in Oracle PL/SQL—records and collections—offer powerful ways to organize, manipulate, and pass around groups of data efficiently.
0 Comments