Database Reference
In-Depth Information
6.
Fetch cursor:
If the SQL statement returns data, this step retrieves it. Especially for queries,
this step is where most of the processing is performed. In the case of queries, the result set
might be partially fetched. In other words, the cursor might be closed before fetching all
the rows.
7.
Close cursor:
The resources associated with the handle and the private SQL area are freed
and consequently made available for other cursors. The shared SQL area in the library
cache isn't removed. It remains there in the hope of being reused in the future.
To better understand this process, it's best to think about each step being executed separately in the order shown by
Figure
2-2
. In practice, though, different optimization techniques are applied to speed up processing. For example, bind
variable peeking requires that the generation of the execution plan is delayed until the value of the bind variables is known.
Depending on the programming environment or techniques you're using, the different steps depicted in
Figure
2-2
may be implicitly or explicitly executed. To make the difference clear, take a look at the following two
PL/SQL blocks that are available in the
lifecycle.sql
script. Both have the same purpose (reading one row from
the
emp
table), but they're coded in a very different way.
The first is a PL/SQL block using the
dbms_sql
package to explicitly code every step shown in Figure
2-2
:
DECLARE
l_ename emp.ename%TYPE := 'SCOTT';
l_empno emp.empno%TYPE;
l_cursor INTEGER;
l_retval INTEGER;
BEGIN
l_cursor := dbms_sql.
open_cursor
;
dbms_sql.
parse
(l_cursor, 'SELECT empno FROM emp WHERE ename = :ename', 1);
dbms_sql.
define_column
(l_cursor, 1, l_empno);
dbms_sql.
bind_variable
(l_cursor, ':ename', l_ename);
l_retval := dbms_sql.
execute
(l_cursor);
IF dbms_sql.
fetch_rows
(l_cursor) > 0
THEN
dbms_sql.
column_value
(l_cursor, 1, l_empno);
dbms_output.put_line(l_empno);
END IF;
dbms_sql.
close_cursor
(l_cursor);
END;
The second is a PL/SQL block taking advantage of an implicit cursor; basically, the PL/SQL block delegates the
control over the cursor to the PL/SQL compiler:
DECLARE
l_ename emp.ename%TYPE := 'SCOTT';
l_empno emp.empno%TYPE;
BEGIN
SELECT empno INTO l_empno
FROM emp
WHERE ename = l_ename;
dbms_output.put_line(l_empno);
END;
Most of the time, what the compiler does is fine. In fact, internally, the compiler generates code that is similar to
