STATIC & DYNAMIC SQL
STATIC SQL VS DYNAMIC
SQL
►Static SQL is SQL statements in an application that do not change at
runtime and, therefore, can be hard-coded into the application.
► Static SQL provides performance advantages over dynamic SQL because
static SQL is pre-processed, which means the statements are parsed,
validated, and optimized only once.
► The full text of static SQL statements is known at compilation,
which provides the following benefits:
❖ Successful compilation verifies that the SQL statements reference
valid database objects.
❖ Successful compilation verifies that the necessary privileges are in place
to access the database objects.
❖ Performance of static SQL is generally better than dynamic SQL.
STATIC SQL VS DYNAMIC
SQL
►Static SQL has limitations that can be overcome with dynamic SQL. You
may not always know the full text of the SQL statements that must be
executed in a PL/SQL procedure. Your program may accept user input that
defines the SQL statements to execute, or your program may need to
complete some processing work to determine the correct course of action.
In such cases, you should use dynamic SQL.
► For example, consider a reporting application that performs standard
queries on tables in a data warehouse environment where the exact
table name is unknown until runtime.
► To efficiently accommodate a large amount of data in the data
warehouse, you create a new table every quarter to store the invoice
information for the quarter. These tables all have the same definition
and are named according to the starting month and year of the quarter,
for
example, INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997,
INV_01_1998,
etc. In such a case, you can use dynamic SQL in your reporting
Dynamic
► SQL
Dynamic SQL is SQL statements that are constructed at
runtime; for example, the application may allow users to
enter their own queries. Thus, the SQL statements cannot be
hard-coded into the application.
► Objective:
► Composing and executing new (not previously compiled) SQL
statements at run-time
► a program accepts SQL statements from the keyboard at run-time
► a point-and-click operation translates to certain SQL query
► Dynamic update is relatively simple; dynamic query can be
complex
► because the type and number of retrieved attributes are
unknown at compile time
DIFFERENCE BETWEEN STATIC AND
DYNAMIC SQL
► Static (embedded) ► Dynamic (interactive)
SQL SQL
► In static SQL how the ► In dynamic SQL, how
database will be database will be
accessed is accessed is determined
predetermined in the at run time.
embedded SQL
statement.
► It is less swift and
► It is more swift efficient.
and efficient.
► Dynamic (interactive)
► Static (embedded) SQL
SQL
► SQL statements are
► SQL statements are
compiled at compile time.
compiled at run time.
► Parsing, validation,
► Parsing,
optimization,
validation,
and
optimization, and generation
generation of application
of application plan are done
plan are done at run
at compile time.
time.
► It is generally used for
► It is generally used for
situations where data is
situations where data
distributed uniformly.
is distributed non-
► Execution IMMEDIATE,EXECUTE uniformly.
and PREPARE statements are
► EXECUTE IMMEDIATE,
not used.
EXECUTE and PREPARE
statements are used.