Static SQL:
Static SQL is the type of SQL in
which we cannot change the form of SQL statements unless we change our program.
The output of our program will be similar each time based upon the input value.
If we use static SQL statements of UPDATE a table, then the table will be
updated every time we enter new data. We
cannot expect other operations from the program unless we change the whole SQL
statements to make them do so.
However, the static SQL can be
made flexible to some extent by using so called host variables.
This model can be useful in
developing programs that are used to do same tasks time and again, like keeping
record of items sold in a store (a billing system).
Dynamic SQL:
Basically, a Dynamic SQL program
generates SQL statements or can take SQL statements as input, in form of
string, in order to provide flexibility to the program. That is why, it can be
considered to be more interactive than Static SQL. In a deeper sense, it is
capable of translating the user input into an executable SQL statement. The
input is capable of changing the operation performed by the program.
However, there are some
statements in SQL that cannot be used dynamically.
This model is useful in programs
in which we might need to use multiple kinds of operations on the same data. It
is also used in such cases where we may need to use many Static SQL statements,
which might prove tedious and lengthy job. Thus, dynamic SQL provides better flexibility
but still due to its complexity, it is slower than Static SQL while execution.
