One of the hallmarks of the PL/SQL language is its tight integration with the Oracle database, both for changing data in database tables and for extracting information from those tables. This chapter explores the many features available in PL/SQL to query data from the database and make that data available within PL/SQL programs.
When you execute a SQL statement from PL/SQL, the Oracle database assigns a private work area for that statement and also manages the data specified by the SQL statement in the System Global Area (SGA). The private work area contains information about the SQL statement and the set of data returned or affected by that statement.
PL/SQL provides a number of different ways to name this work area and manipulate the information within it; all of these ways involve defining and working with cursors. They include:
A simple and direct SELECT...INTO retrieves a single row of data into local program variables. It’s the easiest (and often the most efficient) path to your data, but it can often lead to coding the same or similar SELECTs in multiple places in your code.
You can declare the query explicitly in your declaration section (local block or package). In this way, you can open and fetch from the cursor in one or more programs, with a granularity of control not available with implicit cursors.
Offering an additional level of flexibility, cursor variables (declared from a REF CURSOR type) allow ...