SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
D-80
Dynamic SQL

The PERSNL subvolume contains the EMPLOYEE, DEPT, and JOB tables and the
EMPLIST and MGRLIST views. This commands duplicate all tables and their
indexes, but only the MGRLIST view:
>> DUP $VOL1.PERSNL.*, *.NWPERS.*, VIEWS OFF NO LISTALL;
>> DUP $VOL1.PERSNL.M*, $VOL1.NWPERS.*, VIEWS EXPLICIT
+> NO LISTALL;
If other files or tables on the PERSNL subvolume have names that also begin with
the letter M, you must provide a more specific source fileset list in the second
command: for example, $VOL1.PERSNL.MGRL* or $VOL1.PERSNL.MGRLIST.
Dynamic SQL
Dynamic SQL is a form of embedded SQL that allows you to build, compile, and
execute SQL DCL, DDL, and DML statements during program execution. You can use
dynamic SQL in programs that build SQL statements at execution time or that process
SQL statements entered by users or generated by applications on personal computers.
Two dynamic SQL statements, PREPARE and EXECUTE, can also be used outside
embedded SQL programs to eliminate the need to recompile SQL statements that you
execute multiple times in a single SQLCI session.
The below table summarizes 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:
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