Dynamic SQL in DBMS

Here you will learn about dynamic sql in dbms.

What is Dynamic SQL?

When the pattern of database access is known in advance then static SQL is very adequate to serve us. Sometimes, in many applications we may not know the pattern of database access in advance. For example, a report writer must be able to decide at run time that which SQL statements will be needed to access the database. Such a need can’t be fulfilled with static SQL and requires an advanced form of static SQL known as dynamic SQL.

Also Read: Embedded SQL (Static SQL) in DBMS

There are several limitations in static SQL. Although using the host variables (host variables allows us to input values for search condition at run time), we can achieve a little bit dynamicness, for e.g.,

exec sql select tname, sex from teacher where salary > :sal;

Here the salary will be asked on run time. But getting column name or table asked at run time not possible with embedded SQL. For having such a feature we need dynamic SQL.

• In dynamic SQL, the SQL statements are not hard coded in the programming language. The text of the SQL statement is asked at the run time to the user.
• In dynamic SQL, the SQL statements that are to be executed are not known until runtime, so DBMS can’t get prepared for executing the statements in advanced.
• When the program is executed, the DBMS takes the text of SQL statements to execute the statements that are executed in such a manner called statement string. Once DBMS receives the text, it goes through a five steps execution as illustrated below.

Dynamic Statement Execution (Execute Immediate)

The Execute Immediate statement provides the simplest form of dynamic SQL. This statement passes the text of SQL statements to DBMS and asks the DBMS to execute the SQL statements immediately.

For using the statement our program goes through the following steps.

1. The program constructs a SQL statement as a string of text in one of its data areas (called a buffer).
2. The program passes the SQL statements to the DBMS with the EXECUTE IMMEDIATE statement.
3. The DBMS executes the statement and sets the SQL CODE/SQL STATE values to flag the finishing status same like if the statement had been hard coded using static SQL.