Some Data Retrieval Terms

You have lots of options in PL/SQL for executing SQL, and all of them occur as some type of cursor inside your PL/SQL program. Before diving into the details of the various approaches, this section will familiarize you with the types and terminology of data retrieval.

Static SQL

A SQL statement is static if its content is determined at compile time.

Dynamic SQL

A SQL statement is dynamic if it is constructed at runtime and then executed, so you don't completely specify the SQL statement in the code you write. You can execute dynamic SQL either through the use of the built-in DBMS_SQL package (available in all versions of Oracle) or with native dynamic SQL (introduced in Oracle8i and described in Chapter 15).

Result set

This is the set of rows identified by Oracle as fulfilling the request for data specified by the SQL statement. The result set is cached in the System Global Area to improve the performance of accessing and modifying the data in that set. Oracle maintains a pointer into the result set, which we will refer to as the current row.

Implicit cursor

PL/SQL declares and manages an implicit cursor every time you execute a SQL DML statement (INSERT, UPDATE, or DELETE) or a SELECT INTO that returns a single row from the database directly into a PL/SQL data structure. This kind of cursor is called "implicit" because Oracle automatically handles many of the cursor-related operations for you, such as allocating a cursor, opening the cursor, fetching records, and even closing the cursor (although this is not an excuse to write code that relies on this behavior).

Explicit cursor

This is a SELECT statement that you declare as a cursor explicitly in your application code. You then also explicitly perform each operation against that cursor (open, fetch, close, etc.). You will generally use explicit cursors when you need to retrieve multiple rows from data sources using static SQL.

Cursor variable

This is a variable you declare that references or points to a cursor object in the database. As a true variable, a cursor variable can change its value (i.e., the cursor or result set it points to) as your program executes. The variable can refer to different cursor objects (queries) at different times. You can also pass a cursor variable as a parameter to a procedure or function. Cursor variables are very useful when passing result set information from a PL/SQL program to another environment, such as Java or Visual Basic.

Cursor attribute

A cursor attribute takes the form %attribute_name and is appended to the name of a cursor or cursor variable. The attribute returns information about the state of the cursor, such as "is the cursor open?" and "how many rows have been retrieved for this cursor?" Cursor attributes work in slightly different ways for implicit and explicit cursors and for dynamic SQL. These variations are explored throughout this chapter.

SELECT FOR UPDATE

This statement is a special variation of the normal SELECT that proactively issues row locks on each row of data retrieved by the query. Use SELECT FOR UPDATE only when you need to "reserve" data you are querying to ensure that no one changes the data while you are processing it.

Bulk processing

In Oracle8i and above, PL/SQL offers the BULK COLLECTION syntax for queries that allows you to fetch multiple rows from the database in a single or "bulk" step.