Dynamic SQL

Dynamic SQL allows you to construct and execute SQL statements at runtime based on varying conditions or user inputs. Here’s a simple example demonstrating how to use dynamic SQL in PL/SQL.

Example: Using Dynamic SQL to Insert a Record

In this example, we'll create a PL/SQL block that constructs an INSERT statement dynamically and executes it using EXECUTE IMMEDIATE.

Step-by-Step Breakdown

1. Declare Variables: We'll need variables to hold the dynamic SQL string and the values to be inserted.

2. Construct the SQL Statement: Create an SQL INSERT statement as a string.

3. Execute the Statement: Use EXECUTE IMMEDIATE to run the dynamic SQL.

Example Code

DECLARE
v_sql VARCHAR2(200);
v_name VARCHAR2(50) := 'John Doe';
v_salary NUMBER := 50000;
BEGIN
-- Constructing the dynamic SQL INSERT statement
v_sql := 'INSERT INTO employees (name, salary) VALUES (:name, :salary)';

-- Executing the dynamic SQL statement
EXECUTE IMMEDIATE v_sql USING v_name, v_salary;

DBMS_OUTPUT.PUT_LINE('Inserted employee: ' || v_name || ' with salary: ' || v_salary);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;

Explanation

1. Variable Declaration:

v_sql: A string variable to hold the SQL statement.

v_name: A variable for the employee's name.

v_salary: A variable for the employee's salary.

1. Dynamic SQL Construction:

The INSERT statement is built as a string, using placeholders :name and :salary for bind variables.

1. Execution:

The EXECUTE IMMEDIATE statement runs the dynamic SQL. The USING clause binds the variables v_name and v_salary to the placeholders in the SQL string.

1. Output:

A message is printed indicating that the employee has been inserted.

1. Exception Handling:

Any errors during execution will be caught and printed.

Benefits of This Approach

Flexibility: You can change the values of v_name and v_salary without altering the SQL structure.

Security: Using bind variables prevents SQL injection attacks.

Reusability: You can use the same block for different values or conditions by modifying the variable values before execution.

This simple example illustrates how dynamic SQL can be effectively utilized in PL/SQL to insert records into a database table.