NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
D-79
Summary of Dynamic SQL Statements
Summary of Dynamic SQL Statements
Determining When to Use Dynamic SQL
Dynamic SQL can be less efficient than static SQL because more work is deferred until
run time. If you do not know the whole text of an SQL statement at development time,
but there are only a few alternatives, you might want to program the alternatives into
your application.
If your application requires greater flexibility, dynamic SQL can be useful. For example,
you could use dynamic SQL if your application requires:
•
Flexibility to construct SQL statements at run time: for example, an interactive
interface that is similar to SQLCI, but is designed for an inexperienced user.
•
Restriction of access to data in a table: for example, the program might code an
UPDATE statement for certain columns in a table, but allow the user to enter any
selection criteria (WHERE clause) at run time.
•
Client-server support with deferral of definition of SQL statements until run time:
for example, when the user of an application on a personal computer wants to
manipulate data in a NonStop SQL/MP database on a host system. Such a program
cannot use SQLCI. The user formulates an SQL statement on the personal computer
and the application sends it to a server process on the NonStop system over Multilan
or another communications protocol.
If you plan to execute a dynamic SQL statement only once, you can use EXECUTE
IMMEDIATE to execute the statement, and save any memory that would have stored the
execution plan.
Features of Dynamic SQL
When you write a program that uses dynamic SQL, you use many of the same SQL
statements as you would in static SQL. You can perform most of the same operations
using dynamic SQL statements that you perform with static SQL statements. You can
use DDL, DML, and DCL statements in both modes.
The difference is that all or part of a dynamic SQL statement is obtained at run time
from the user, or generated by your program. You program stores the statement in a
DESCRIBE Returns information about output variables of prepared
statements
DESCRIBE INPUT Returns information about input parameters of prepared
statements
EXECUTE Executes a compiled statement
EXECUTE IMMEDIATE Executes an SQL statement contained in a host variable
PREPARE Compiles a DDL, DML, or DCL statement for later execution
by EXECUTE
RELEASE Deallocates memory for a dynamic SQL statement referred to
through a host variable