Oracle PL/SQL for DBAs

Typical Query Operations

Regardless of the type of query cursor, PL/SQL performs the same operations to execute a SQL statement from within your program. In some cases, PL/SQL takes these steps for you. In others, such as with explicit cursors , you will write the code for these steps yourself.

Parse

The first step in processing a SQL statement is to parse it to make sure it is valid and to determine the execution.

Bind

When you bind, you associate values from your program (host variables) with placeholders inside your SQL statement. With static SQL, the PL/SQL engine itself performs these binds. With dynamic SQL, you must explicitly request a binding of variable values if you wish to use bind variables.

Open

When you open a cursor, the bind variables are used to determine the result set for the SQL statement. The pointer to the active or current row is set to the first row. Sometimes you will not explicitly open a cursor; instead, the PL/SQL engine will perform this operation for you (as with implicit cursors or native dynamic SQL).

Execute

In the execute phase, the statement is run within the SQL engine.

Fetch

If you are performing a query, the FETCH command retrieves the next row from the cursor’s result set. Each time you fetch, PL/SQL moves the pointer forward in the result set. When you are working with explicit cursors, remember that FETCH does nothing (does not raise an error) if there are no more rows to retrieve.

Close

This step closes the cursor and releases all memory ...