EXECUTE IMMEDIATE with example| best Explanation

EXECUTE IMMEDIATE in Dynamic PL/SQL allows you to execute a SQL statement dynamically at runtime, meaning you can construct a SQL statement as a string and then execute it. This is particularly useful when the SQL statement varies based on conditions or user input.

Simple Analogy

Think of EXECUTE IMMEDIATE like ordering a custom pizza:

Fixed Menu (Static SQL): You can only choose from the existing menu items (fixed SQL queries) and cannot change the ingredients.

Custom Order (Dynamic SQL): You can specify exactly what you want on your pizza, like extra cheese, specific toppings, or even a different size. This is similar to building a SQL query dynamically based on your needs at that moment.

Simple Example

Imagine you want to retrieve employee details based on a dynamic condition, such as the department name being passed as a variable.

Example Code

DECLARE
v_department_name VARCHAR2(50) := 'Sales'; -- Department name can change based on user input
v_sql VARCHAR2(1000);
v_employee_count NUMBER;
BEGIN
-- Constructing the SQL query dynamically
v_sql := 'SELECT COUNT(*) FROM employees WHERE department = :dept_name';

-- Executing the dynamic SQL statement
EXECUTE IMMEDIATE v_sql INTO v_employee_count USING v_department_name;

-- Displaying the result
DBMS_OUTPUT.PUT_LINE('Number of employees in ' || v_department_name || ': ' || v_employee_count);

END;

Breakdown of the Example

1. Variable Declaration:

v_department_name is set to 'Sales'. This can be changed to any department name.

v_sql holds the dynamic SQL query as a string.

1. Constructing the SQL Query:

The SQL query is constructed with a placeholder :dept_name for the department name. This allows you to safely pass variables without directly concatenating them into the SQL string.

1. Executing the Query:

EXECUTE IMMEDIATE v_sql INTO v_employee_count USING v_department_name;

This line executes the SQL statement stored in v_sql, retrieves the count of employees in the specified department, and stores the result in v_employee_count.

1. Output:

The result is displayed using DBMS_OUTPUT.PUT_LINE.

Summary

EXECUTE IMMEDIATE provides flexibility by allowing you to build and run SQL queries dynamically. This is especially helpful in scenarios where SQL statements need to adapt to varying conditions or parameters at runtime.