In this course, we will study dynamic SQL, its needs, its structure, the difference between static and dynamic SQL and limitations of dynamic SQL.
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.
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  = “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.
|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.|