Dynamic SQL in DBMS

In this course, we will study dynamic SQL, its needs, its structure, the difference between static and dynamic SQL and limitations of dynamic SQL.

What is Dynamic SQL in DBMS?

Dynamic SQL can be stated as the SQL statement that is constructed at the runtime; for example, the application in which the employees or the users enter their own queries.
The static SQL statements do not change at all.

One should use dynamic SQL just in cases if the static SQL does not support the operations one desires to perform, or in cases where we do not know the exact SQL statements that must be executed initially by a PL/SQL procedure.

In the given image below we can dynamically enter the first name, last name, gender, and salary of an employee and search for it.

Dynamic SQL in DBMS

What is the need for dynamic SQL in DBMS

  1. It can be used to create dynamic queries at runtime which offers us great flexibility.
  2. These can be easily updated and edited.
  3. There is no need for rebuilding or pre compilations as we generate access plans during runtime.
  4. It is used in cases where we have non-uniform data.

What is the structure of dynamic SQL

Dynprog

It is an abbreviation for dynamic programming. It is a repository object and is a component of an ABAP program. It contains the screen with its screen elements and the dynprog flow logic.

char * sqlprog = “update account
set balance = balance * 1.15
where acc_number = ?”
EXEC SQL prepare dynprog from: sqlprog;
char account [10] = “A-201”;
EXEC SQL execute dynprog using :account;

In the dynamic SQL, we have a? and it is a place holder for a value that is provided when the SQL program is executed.

What is the difference between dynamic and static SQL

Dynamic SQL Static SQL
The way the database will be accessed is determined at runtime. The way the database will be accessed is predetermined in SQL statements.
It is a little less efficient. It is a little more efficient.
SQL statements compiled at the runtime. SQL statements compiled at the compile time.
Used when the data is non uniformly distributed. Used when the data is uniformly distributed.
Statements like EXECUTE IMMEDIATE, EXECUTE and PREPARE are used. Statements like EXECUTE IMMEDIATE, EXECUTE and PREPARE are not used.
Flexibility is more. Flexibility is less.

What are the limitations of dynamic SQL

  1. We can not use all the queries dynamically.
  2. Security issues are there in dynamic SQL.
  3. The performance of the statements is poor when compared with static SQL.
  4. The optimization of the code can not be done.
  5. It requires more permissions.
  6. Dynamic IP addresses are hard to set up and manage.