Oracle PL/SQL for DBAs

Querying Data

PL/SQL programs query information from the database with the SQL SELECT statement. Because PL/SQL is tightly integrated with the SQL language, you can execute this SELECT statement natively in your PL/SQL block, as shown below:

    DECLARE
       l_employee employee%ROWTYPE;
    BEGIN
       SELECT * INTO l_employee
         FROM employee
        WHERE employee_id = 7500;
    END;

This SELECT INTO is an example of an implicit cursor, and is just one of several ways you can query data from within a PL/SQL block. You have these choices:

Implicit cursors

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.

Explicit cursors

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 .

Cursor variables

Offering an additional level of flexibility, cursor variables (declared from a REF CURSOR type) allow you to pass a pointer to a query’s underlying result set from one program to another. Any program with access to that variable can open, fetch from, or close the cursor.

Cursor expressions

Introduced in Oracle9i Database, the CURSOR expression transforms a SELECT statement into a REF CURSOR result set and can be used in conjunction with table functions (described in Chapter 3) to improve the performance of applications.

Chapter 2 describes cursors in detail.