Databases Reference
In-Depth Information
How it works...
We have created a package with five stored procedures available in it. There are three
stored procedures in the package body that are not exposed to public execution—these
are in the package body and are not listed in the package definition.
The first time we run the
WORKLOAD_NOBIND
procedure, it executes the stored
procedure
TEST_NOBIND
50,000 times, passing in a parameter between 1 and
50,000. The
TEST_NOBIND
procedure will look for a customer in the
SH.CUSTOMERS
table with the same customer ID as the input parameter. Here we use dynamic SQL
statement execution, without using bind variables, but generating a new query for
each parameter passed, concatenating the current value of the parameter to the
query statement itself.
A dynamic SQL statement is built dynamically at runtime and lets
the developer create flexible applications because the full text of
the SQL statement is unknown at compile time and it is defined
only at runtime. A typical example of dynamic SQL statement use
is in the software that lets the user type the query to execute—such
as SQL*Plus or Oracle SQL Developer.
The output screen appears as follows:
You can see that execution of the test case takes more than a minute and 25 seconds.
The second time we execute the
WORKLOAD_BIND
stored procedure, it will launch the stored
procedure
TEST_BIND
, to do the same work as
TEST_NOBIND
, using bind variables to pass
the current parameter (the customer ID) to the query.
