Oracle PLS-00455

SQL

A Dynamic Approach to Multirow Queries

By Steven Feuerstein

In the last issue of Oracle Magazine, I introduced an exciting enhancement to the Oracle PL/SQL language in Oracle8i: native dynamic SQL (NDS). NDS is an implementation of dynamic SQL (SQL and PL/SQL statements constructed and executed dynamically, at runtime) that is built directly into the PL/SQL language. Prior to NDS, dynamic SQL was available only with DBMS_SQL—a complicated, relatively slow, and Oracle7-centric package.

In this issue, we'll focus on one of the more complex areas within NDS: multirow queries (a multirow query being, of course, a SELECT statement that identifies more than one row in its result set). Processing is more complicated with such queries than with other dynamic operations, since you must either process each row individually or take advantage of collections to retrieve multiple rows at once.

Fortunately, the learning curve for such queries isn't steep, since the syntax is basically an extension of a previously existing PL/SQL statement: OPEN FOR. We'll start our exploration of multirow NDS queries by looking at the changes in the OPEN FOR syntax, and then build up to an example of a useful generalized utility that demonstrates the power of NDS.

 

An Evolutionary Approach

As with many of the new PL/SQL features in Oracle8i, NDS adds significant new capabilities to PL/SQL with a minimum of new syntax—and therefore, a minimal learning curve. As we saw in Part 1 of this series, the EXECUTE IMMEDIATE statement lets you execute DDL statements, inserts, updates, and deletes—as well as dynamic PL/SQL blocks—all in one line of code. For multiple-row queries, the PL/SQL designers took a look at existing constructs in PL/SQL and realized they could use cursor variables to get the job done.

Rather than introduce new syntax, the PL/SQL designers simply extended the OPEN FOR statement to allow for dynamically constructed SQL strings. In other words, you're no longer limited to using a cursor variable with hard-coded queries, such as:

TYPE name_num_rc IS REF CURSOR;
   cv name_num_rc;
BEGIN
   IF type_in = 'BYDEPT'
   THEN
      OPEN cv FOR
         SELECT ename, deptno FROM emp ORDER BY deptno;


   ELSIF type_in = 'BYSAL'
   THEN
      OPEN cv FOR
         SELECT ename, sal FROM emp ORDER BY sal;

Now, you can now use the same type of structure with variable strings, like so:

   TYPE generic_rc IS REF CURSOR;
   cv generic_rc;
BEGIN
   OPEN cv FOR
      'SELECT '|| column_list ||
        'FROM '|| table_list ||
       'WHERE '|| where_clause ;

And that modified OPEN FOR syntax was the only PL/SQL change needed to allow for NDS support of multirow queries.

 

Electing to Use OPEN FOR

Let's take a closer look at OPEN FOR and multirow queries with a concrete example. With recent elections in the U.S. and other countries, let's consider the challenge faced by Jan Voter. Before deciding who gets her vote, Jan likes to find out everything she can about the candidates— particularly with respect to the campaign contributions they've received.

For each candidate, Jan decides to check out the top 15 contributors who have donated at least US$2,500. Drawing on data from a handy site for U.S. voters (the Center for Responsive Politics, at www.crp.org), Jan has built three different tables to store this information: bush_funds, gore_funds, and nader_funds. Each table has this structure:

CREATE TABLE <name>_funds (
   amount NUMBER,
   source VARCHAR2(200),
   );

She wants to build a single program to display the contributions for a given candidate, so she needs to use dynamic SQL. Let's walk through the steps needed to build this program (the complete procedure is shown in Listing 1).

The first step in a dynamic multirow query is to define the cursor variable that will point to the result set in the System Global Area (see lines 4 through 6 in Listing 1). We declare the cursor variable as an instance of a REF CURSOR type, so we must consider the question of whether to make it strong or weak. A REF CURSOR is strong if it is strongly typed, which means the TYPE statement includes a RETURN clause defining what structure is allowable (number of items and datatypes) for data fetched through a cursor variable based on this TYPE. A strong REF CURSOR looks like this:

DECLARE
   TYPE only_order_data_rc IS REF CURSOR
      RETURN orders%ROWTYPE;

I generally recommend that developers use strong REF CURSORs when using cursor variables with static SQL, because doing so makes the code easier to debug and maintain.

With dynamic SQL, however, you are not allowed to use strong REF CURSORs. At compile time, the query associated with the REF CURSOR is just a string, so the PL/SQL engine cannot validate that the query's SELECT list is consistent with the record structure in the REF CURSOR's RETURN clause. If I try to strongly type a REF CURSOR with dynamic SQL, I will get this compile error:

PLS-00455: cursor 'MONEY_CV' cannot be used in
dynamic SQL OPEN statement

So, I need to take an alternate approach. I define a weak REF CURSOR and then declare a cursor variable based on that structure, as follows:

 4   TYPE refCur IS REF CURSOR;
 5   money_cv refCur;

Once that is done, I declare a record that I can use to retrieve individual rows from any of the three tables (all three tables have the same structure, so I can %ROWTYPE the record against any of the tables; in this case, I used bush_funds):

 6   money_rec bush_funds%ROWTYPE;

Now I am ready to open my dynamic query:

 8      OPEN money_cv FOR
 9         'SELECT amount, source ' ||
10          'FROM ' ||candidate_in ||
            '_funds ' ||
'ORDER BY amount DESC';

I must use dynamic SQL, since I am constructing the name of the table each time I run the program. But dynamic SQL doesn't require any more in the way of special code than what I've just described. I can use the familiar explicit-cursor syntax unchanged for my cursor-variable-based code, as the following lines demonstrate:

13   LOOP
14      FETCH money_cv INTO money_rec;
15      EXIT WHEN money_cv%NOTFOUND;
16
17      DBMS_OUTPUT.put_line (
18         money_rec.source || ': $' ||
19         TO_CHAR (money_rec.amount)
20      );
   END LOOP;

With this program in place, Jan Voter can now request three reports, as shown here (for the full report output, see Listing 2):

SQL> exec show_me_the_money ('gore')
Ernst and Young International: $127125
Citigroup: $98750


SQL> exec show_me_the_money ('bush')
MBNA America Bank: $213400
Vinson and Elkins: $196350
...

SQL> exec show_me_the_money ('nader')
Jack H Olender and Assoc: $4000
Kayline Enterprises: $4000
...

Jan can now decide who gets her vote—and perhaps her campaign contribution.

 

Building Generalized Utilities

We have seen how you can write very application-specific programs by using NDS. One of the more satisfying aspects of dynamic SQL is that you can use it to build utilities that can be applied in many different applications. Now let's construct such a utility—one that also involves a multirow query—with NDS.

How many times have you written a query along these lines:

SELECT some_columns, COUNT(*)
  FROM your_table
 GROUP BY some_columns;

A specific application of this query might be "How many books by category have the word 'Oracle' in them?":

SELECT category, COUNT(*)
  FROM books
 WHERE title LIKE '%ORACLE%'
 GROUP BY category

And then there's the variation involving the HAVING clause, as in:

SELECT some_columns, COUNT(*)
  FROM your_table
 GROUP BY some_columns
HAVING COUNT(*) > N;

In this variation, you don't want to see all the counts; you just want to see those groupings where there are more than N identical values in the specified column(s). This sort of query is often used to identify duplicate identical rows (not a situation you'd think would occur in relational tables, but it does happen).

These are very common requirements, and with NDS, you can build a single program (we'll call it "countBy") that does all the work for you, for any table. In order to construct the query and display the data, this program will need the following information:

• The name of the table
• The column by which to group
• The number of rows for the HAVING clause ("at least this many"), if desired
• The schema owning the table, if needed (for cases where you run the program against a table not in your own schema)
• The condition for the WHERE clause, if desired

Given those requirements, here is the header for my countBy procedure:

/* file countby.sp */
PROCEDURE countBy (
   tab IN VARCHAR2,
   col IN VARCHAR2,
   atleast IN INTEGER := NULL,
   sch IN VARCHAR2 := NULL,
   whr IN VARCHAR2 := NULL

The examples in Listing 3 show what kind of output the finished program should provide. They show two counts of employees by department (one without an "at least" clause).

Now, let's look at how to construct this handy program. We'll start with the executable section, then look at the main chunks of NDS-related code, and finish up with some nested procedures to format the output nicely (for a listing of the entire procedure, see Listing 4).

The first task is to construct the SQL query string. Since we know little in advance about the table and conditions for each query, the string is composed mostly of concatenations of the parameter values:

BEGIN
   SQL_string :=
      'SELECT ' || col || ', COUNT(*)
         FROM ' || NVL (sch, USER) || '.' ||
               tab ||
      ' WHERE ' || NVL (whr, '1 = 1') ||
      'GROUP BY ' || col;

That's the core query. However, if the user has supplied a non-NULL value for the "at least" parameter, we'll need to append a HAVING clause:

IF atleast IS NOT NULL
   THEN
      SQL_string :=
         SQL_string ||
         ' HAVING COUNT(*) >= ' ||
         atleast;
   END IF;


With all the pieces of the SQL string in place, we can now pass it on to the NDS OPEN FOR statement:

OPEN cv FOR SQL_String;

Then, for each row we fetch, we use DBMS_OUTPUT to display the information on the screen. Note that if we just queried the first row (cv%ROWCOUNT = 1), we take a brief detour to display the header:

   LOOP
      FETCH cv INTO v_val, v_count;
      EXIT WHEN cv%NOTFOUND;

      IF cv%ROWCOUNT = 1
      THEN
         display_header;
      END IF;

      DBMS_OUTPUT.PUT_LINE (
         RPAD (v_val, column_length) || ' ' || v_count);
   END LOOP;

And what, you may ask, are display _header and column_length? These are both local procedures that we define in the declaration section of the countBy procedure. We could have put all of the code for these procedures directly in the countBy execution section, but the result would be harder to read and maintain. Local (or, as they are sometimes called, nested) procedures allow us to keep our executable sections small and easy to follow.

As Listing 5 shows, the display _header procedure is pretty simple. We build border lines with the RPAD function and construct the header string itself much as we did the SQL string: first the core part of the header, and then the extra descriptive information necessary if an "at least" value is provided.

The column_length procedure is a bit more interesting; rather than hard-code information about column lengths in the program, we grab the needed information from the data dictionary:

   FUNCTION column_length RETURN INTEGER
   IS
      retval INTEGER;
   BEGIN
      SELECT data_length INTO retval
        FROM ALL_TAB_COLUMNS
      WHERE OWNER = NVL (UPPER (sch), USER)
        AND TABLE_NAME = UPPER (tab)
        AND COLUMN_NAME = UPPER (col);
      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN NULL;
   END;

This procedure retrieves the maximum length of the values found in the column. By extracting the data length from the data dictionary, I can present the requested data in the most readable manner possible.

 

Exploring Dynamic Potential

As you start building more of these generic utilities, you'll find they don't take much code or effort—you simply need to think through the steps of the SQL-string construction carefully. And the more NDS utilities you build, the more you'll realize how handy they are. Once you incorporate the advanced techniques we'll explore in the third article in this series—including using BULK COLLECT in dynamic SQL, executing dynamic PL/SQL blocks, and working with objects and collections—you'll have what you need to meet any challenge the dynamic way.

Steven Feuerstein ([e-mail]) is a leading expert on the Oracle PL/SQL language. He is the author of five books on PL/SQL—Oracle PL/SQL Programming, Advanced Oracle PL/SQL Programming with Packages, Oracle Built-in Packages, Oracle PL/SQL Programming Guide to Oracle8i Features, and Oracle PL/SQL Developer's Workbook—as well as the PL/SQL CD Bookshelf (all published by O'Reilly & Associates). Steven hosts the PL/SQL Pipeline (www.revealnet.com/plsql-pipeline) and contributes to RevealNet's Active PL/SQL Knowledge Base. He offers training and consulting through PL/Solutions (www.plsolutions.com).