The difference between dynamic and static SQL, advantages and disadvantages of dynamic SQL

Static SQL refers to SQL queries that are fully defined at compile time of the program. They do not change during execution, and parameters are passed through bindings.

Dynamic SQL refers to SQL queries that are constructed and executed during runtime of the program, for example, using string concatenations or special APIs (such as EXECUTE IMMEDIATE).

Advantages of dynamic SQL:

• Flexibility: queries with different structures can be formed depending on conditions.
• Allows execution of queries that cannot be predicted in advance.

Disadvantages of dynamic SQL:

• Risk of SQL injection if parameters are not used correctly.
• Loss of optimization benefits, as the query plan may not be cached.
• More difficult to debug and maintain.

Static SQL is safer and more efficient but less flexible.

Example of dynamic SQL in PL/SQL:

DECLARE
  v_sql VARCHAR2(1000);
  v_table_name VARCHAR2(30) := 'EMPLOYEES';
BEGIN
  v_sql := 'SELECT COUNT(*) FROM ' || v_table_name;
  EXECUTE IMMEDIATE v_sql INTO v_count;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;