Dynamic SQL in PL/SQL

Dynamic SQL in PL/SQL is used in a real-life context, where user input dynamically determines values such as column names and values. The key takeaway is how bind variables are used for values, while string concatenation is required for dynamic column names.

Real-Life Example

Let's say the scenario is:

A report system where users can specify the column by which they want to filter employee data.

The user provides the column name (like salary, hire_date, etc.) and a specific value to filter on.

Dynamic SQL with Bind Variables

DECLARE
v_sql VARCHAR2(1000); -- SQL query string
v_column_name VARCHAR2(30); -- Dynamic column name based on user input
v_value VARCHAR2(100); -- Dynamic value to filter by
BEGIN
-- Assume user input is passed directly as bind variables
v_column_name := :input_column; -- Column name provided by user, e.g., 'salary'
v_value := :input_value; -- Value provided by user, e.g., 50000

-- Construct the dynamic SQL query, dynamically inserting the column name
v_sql := 'SELECT name, ' || v_column_name || ' FROM employees WHERE ' || v_column_name || ' = :value';

-- Execute the dynamic SQL query with the bind variable
EXECUTE IMMEDIATE v_sql USING v_value;

END;

Explanation:

1. Dynamic Column Name:

The column name (v_column_name) is provided by the user and dynamically inserted into the SQL query string.

Note: Column names cannot be bind variables in SQL, so we have to concatenate them directly into the SQL string.

1. Bind Variable for Value:

The bind variable :value is used for dynamic data values, which are passed into the SQL query at runtime. This helps prevent SQL injection by separating the query structure from the data.

The bind variable (v_value) will hold the actual value to filter by, e.g., a salary amount.

1. EXECUTE IMMEDIATE:

EXECUTE IMMEDIATE executes the dynamically built SQL statement.

The USING clause is used to pass the value of the v_value variable into the query.

Example in Real Life:

Assume the following:

The user wants to filter employees based on a dynamic column, say, salary, and the value is 50000.

The :input_column (column name) is provided as 'salary'.

The :input_value (filter value) is provided as 50000.

So, the generated SQL query would look like:

SELECT name, salary
FROM employees
WHERE salary = 50000;

This query will be executed dynamically, filtering employees where the salary column equals 50000.

Points to Consider:

Bind Variables (:value) are used safely to handle user input, ensuring SQL injection protection.

The column name is dynamically inserted using string concatenation (||), as it cannot be used as a bind variable.