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 >
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.

Dynamic SQL Concepts

  • 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 SQL in DBMS

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.
Category: SQL

Leave a Reply

Your email address will not be published. Required fields are marked *