Advanced Topics

1. Bulk Operations

Bulk operations allow processing multiple rows of data efficiently, reducing context switching between PL/SQL and SQL engines.

1.1 BULK COLLECT

• Purpose: Fetch multiple rows into a collection with a single context switch.
• Syntax:

plsql
Copy code
SELECT columns
BULK COLLECT INTO collection
FROM table_name;

Example: Fetch Employee Data into a Collection

plsql
Copy code
DECLARE
   TYPE emp_rec IS RECORD (
      first_name employees.first_name%TYPE,
      salary employees.salary%TYPE
   );
   TYPE emp_table IS TABLE OF emp_rec;
   emp_data emp_table;
BEGIN
   SELECT first_name, salary
   BULK COLLECT INTO emp_data
   FROM employees
   WHERE department_id = 10;

   FOR i IN emp_data.FIRST..emp_data.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('Name: ' || emp_data(i).first_name || ', Salary: ' || emp_data(i).salary);
   END LOOP;
END;
/

1.2 FORALL

• Purpose: Execute a SQL statement for multiple rows using a collection.
• Syntax:

plsql
Copy code
FORALL index IN collection.FIRST..collection.LAST
   SQL_statement;

Example: Update Employee Salaries

plsql
Copy code
DECLARE
   TYPE emp_id_table IS TABLE OF employees.employee_id%TYPE;
   emp_ids emp_id_table := emp_id_table(101, 102, 103);
BEGIN
   FORALL i IN emp_ids.FIRST..emp_ids.LAST
      UPDATE employees
      SET salary = salary * 1.1
      WHERE employee_id = emp_ids(i);

   DBMS_OUTPUT.PUT_LINE('Salaries updated successfully.');
END;
/

2. Collections

PL/SQL collections allow storing multiple values in a single variable.

2.1 Nested Tables

• Purpose: Unordered collection that can grow dynamically.
• Syntax:

plsql
Copy code
TYPE nested_table_name IS TABLE OF datatype;

Example: Storing Employee Names

plsql
Copy code
DECLARE
   TYPE name_table IS TABLE OF VARCHAR2(50);
   emp_names name_table;
BEGIN
   emp_names := name_table('John', 'Jane', 'Alice');
   FOR i IN emp_names.FIRST..emp_names.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_names(i));
   END LOOP;
END;
/

2.2 VARRAYs (Variable-Size Arrays)

• Purpose: Ordered collection with a predefined maximum size.
• Syntax:

plsql
Copy code
TYPE varray_name IS VARRAY(size) OF datatype;

Example: Storing Department IDs

plsql
Copy code
DECLARE
   TYPE dept_varray IS VARRAY(5) OF NUMBER;
   dept_ids dept_varray := dept_varray(10, 20, 30);
BEGIN
   FOR i IN dept_ids.FIRST..dept_ids.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('Department ID: ' || dept_ids(i));
   END LOOP;
END;
/

2.3 Associative Arrays

• Purpose: Key-value pairs for quick lookups.
• Syntax:

plsql
Copy code
TYPE array_name IS TABLE OF datatype INDEX BY key_datatype;

Example: Employee Salaries

plsql
Copy code
DECLARE
   TYPE salary_table IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
   emp_salaries salary_table;
BEGIN
   emp_salaries('John') := 50000;
   emp_salaries('Jane') := 60000;

   FOR name IN emp_salaries.FIRST..emp_salaries.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('Name: ' || name || ', Salary: ' || emp_salaries(name));
   END LOOP;
END;
/

3. Dynamic SQL

Dynamic SQL enables the construction and execution of SQL statements at runtime.

3.1 EXECUTE IMMEDIATE

• Purpose: Executes a dynamically constructed SQL statement.

Example: Creating a Table Dynamically

plsql
Copy code
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE dynamic_table (id NUMBER, name VARCHAR2(50))';
   DBMS_OUTPUT.PUT_LINE('Table created successfully.');
END;
/

3.2 Using Bind Variables

• Purpose: Improves performance by reducing SQL parsing and enhances security.

Example: Inserting Data Dynamically

plsql
Copy code
DECLARE
   sql_stmt VARCHAR2(200);
   v_id NUMBER := 101;
   v_name VARCHAR2(50) := 'Dynamic User';
BEGIN
   sql_stmt := 'INSERT INTO employees (employee_id, first_name) VALUES (:1, :2)';
   EXECUTE IMMEDIATE sql_stmt USING v_id, v_name;
   DBMS_OUTPUT.PUT_LINE('Data inserted successfully.');
END;
/

4. Performance Tuning

4.1 Writing Efficient Queries

• *Avoid SELECT :
  • Fetch only required columns.
• Use EXISTS Instead of COUNT:
  • Improves performance when checking record existence.

Example:

plsql
Copy code
-- Less efficient
IF (SELECT COUNT(*) FROM employees WHERE employee_id = 101) > 0 THEN
   DBMS_OUTPUT.PUT_LINE('Employee exists.');
END IF;

-- More efficient
IF EXISTS (SELECT 1 FROM employees WHERE employee_id = 101) THEN
   DBMS_OUTPUT.PUT_LINE('Employee exists.');
END IF;

4.2 Indexing and Hints

• Indexing:
  • Speeds up data retrieval by creating indexes on frequently queried columns.

Example: Create an Index

plsql
Copy code
CREATE INDEX idx_emp_salary ON employees(salary);

• Hints:
  • Directs the optimizer to use a specific execution plan.

Example: Using a Hint

plsql
Copy code
SELECT /*+ INDEX(employees idx_emp_salary) */ employee_id, salary
FROM employees
WHERE salary > 50000;

Summary

These advanced topics empower developers to write efficient, flexible, and high-performance PL/SQL code suitable for complex database applications.