How does Oracle execute dynamic SQL statements?

2 years ago
Ava Mitchell
1 minute

In Oracle, the EXECUTE IMMEDIATE statement can be used to execute dynamic SQL statements. This statement allows for dynamically constructing and executing SQL statements at runtime.

Here is an example demonstrating how to execute dynamic SQL statements.

DECLARE
  sql_stmt VARCHAR2(1000);
  emp_name VARCHAR2(100);
BEGIN
  -- 构建动态SQL语句
  sql_stmt := 'SELECT employee_name FROM employees WHERE employee_id = :id';

  -- 执行动态SQL语句
  EXECUTE IMMEDIATE sql_stmt INTO emp_name USING 100;

  -- 输出结果
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
EXCEPTION
  WHEN OTHERS THEN
    -- 处理异常
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

In the example above, a variable sql_stmt is first defined to store the dynamic SQL statement. Next, the EXECUTE IMMEDIATE statement is used to execute the dynamic SQL statement and store the result in the emp_name variable. Finally, the result is output to the console using the DBMS_OUTPUT.PUT_LINE function.

It is important to handle input parameters carefully when using dynamic SQL statements to avoid SQL injection attacks. The USING clause can be used to pass parameter values and ensure their safety.

bannerAds