Database Reference
In-Depth Information
Table 12-1.
(
continued
)
Application Programming Interface
Bind Variables
Reusing Statements
Client-Side Statement Caching
Native dynamic SQL
(
OPEN
/
FETCH
/
CLOSE
)
✓
Dynamic SQL with the
dbms_sql
package
✓
✓
Precompilers
✓
✓
✓
SQLJ
✓
✓
PL/SQL
PL/SQL offers different methods for executing SQL statements. The two main categories are static SQL and dynamic
SQL. Dynamic SQL can be further divided into three subcategories:
EXECUTE IMMEDIATE
,
OPEN
/
FETCH
/
CLOSE
, and the
dbms_sql
package. The only feature related to parsing that is available for all of them is the possibility of using bind
variables. In fact, the reutilization of statements and client-side statement caching are only partially available. They
are simply not implemented for all categories of SQL statements. The next sections describe the particularities of each
of these four categories.
■
since pL/sQL runs in the database engine, it might seem strange to speak about client-side statement caching.
nevertheless, from the sQL engine's perspective, the pL/sQL engine is a client. in that client, the concept of client-side
statement caching discussed earlier has been implemented.
Note
The PL/SQL blocks provided as examples in this section are excerpts from the
ParsingTest1.sql
,
ParsingTest2.sql
, and
ParsingTest3.sql
scripts implementing test case 1, 2, and 3, respectively.
Static SQL
Static SQL is integrated into the PL/SQL language. As its name indicates, it's static, and therefore, the SQL statement
must be fully known during PL/SQL compilation. For this reason, the utilization of bind variables is unavoidable if
a SQL statement references PL/SQL variables. For example, with static SQL, it's not possible to write a code snippet
reproducing test case 1.
You can write static SQL in two ways. The first is based on implicit cursors, so it gives no possibility of controlling
the life cycle of a cursor. The following PL/SQL block shows an example implementing test case 2:
DECLARE
l_pad VARCHAR2(4000);
BEGIN
FOR i IN 1..10000
LOOP
SELECT pad INTO l_pad
FROM t
WHERE val = i;
END LOOP;
