4.3.1 FETCH into Variables or Records

The FETCH statement in the showcol procedure shown in the previous section fetches into an individual variable. You could also FETCH into a sequence of variables, as shown here:

DECLARE
   TYPE cv_type IS REF CURSOR;
   cv cv_type; 
   mega_bucks company.ceo_compensation%TYPE;
   achieved_by company.layoff_count%TYPE;  
BEGIN
   OPEN cv FOR 
      'SELECT ceo_compensation, layoff_count
        FROM company
       WHERE ' || NVL (whr, '1 = 1');
      
   LOOP
      FETCH cv INTO mega_bucks, achieved_by;

Working with a long list of variables in the FETCH list gets cumbersome and inflexible: you have to declare the variables, keep that set of values synchronized with the FETCH statement, and so on. To ease our troubles, NDS allows us to fetch into a record, as shown here:

DECLARE
   TYPE cv_type IS REF CURSOR;
   cv cv_type; 
   ceo_info company%ROWTYPE;
BEGIN
   OPEN cv FOR 
      'SELECT *
        FROM company
       WHERE ' || NVL (whr, '1 = 1');
      
   LOOP
      FETCH cv INTO ceo_info;

Of course, in many situations you will not want to do a SELECT *; this statement can be very inefficient if your table has hundreds of columns and you only need to work with three of those hundreds. A better approach is to create record TYPEs that correspond to different requirements. The best place to put these structures is in a package specification, so they can be used throughout your application. Here's one such package:

CREATE OR REPLACE PACKAGE company_struc
IS
   TYPE dynsql_curtype IS REF CURSOR;

   TYPE ceo_info_rt IS RECORD (
      mega_bucks company.ceo_compensation%TYPE,
      achieved_by company.layoff_count%TYPE);

END company_struc;

With this package in place, I can rewrite my CEO-related code as follows:

DECLARE
   cur company_struc.dynsql_curtype; 
   rec company_struc.ceo_info_rt; 
BEGIN
   OPEN cv FOR 
      'SELECT ceo_compensation, layoff_count
        FROM company
       WHERE ' || NVL (whr, '1 = 1');
      
   LOOP
      FETCH cv INTO rec;

4.3.2 The USING Clause in OPEN FOR

As with the EXECUTE IMMEDIATE statement, you can pass in bind arguments when you open a cursor. You can only provide IN arguments for a query. By using bind arguments you can improve the performance of your SQL also, and also make it easier to write and maintain that code. (See Section 4.4, "Binding Variables later in this chapter for information about this technique.)

Let's revisit the showcol procedure. That procedure accepted a completely generic WHERE clause. Suppose that I have a more specialized requirement: I want to display (or in some other way process) all column information for rows that contain a date column with a value within a certain range. In other words, I want to be able to satisfy this query:

SELECT ename
  FROM emp
 WHERE hiredate BETWEEN x AND y;

as well as this query:

SELECT name
  FROM war_criminal
 WHERE killing_date BETWEEN x AND y;

I also want to make sure that the time component of the date column does not play a role in the WHERE condition.

Here is the header for the procedure:

/* Filename on companion disk: showcol2.sp */
PROCEDURE showcol (
   tab IN VARCHAR2,
   col IN VARCHAR2,
   dtcol IN VARCHAR2,
   dt1 IN DATE,
   dt2 IN DATE := NULL)

The OPEN FOR statement now contains two placeholders and a USING clause to match:

OPEN cv FOR 
      'SELECT ' || col || 
      '  FROM ' || tab ||
      ' WHERE ' || dtcol || 
         ' BETWEEN TRUNC (:startdt) 
               AND TRUNC (:enddt)'
   USING dt1, NVL (dt2, dt1+1);

I have crafted this statement so that if the user does not supply an end date, the WHERE clause returns rows whose date column is the same day as the dt1 provided. The rest of the showcol procedure remains the same, except for some cosmetic changes in the display of the header.

The following call to this new version of showcol asks to see the names of all employees hired in 1982:

BEGIN
   showcol ('emp', 
      'ename', 'hiredate', 
      '01-jan-82', '31-dec-82');
END;
/
----------------------------------------------------------------------
Contents of EMP.ENAME for HIREDATE between 01-JAN-82 and 31-DEC-82
----------------------------------------------------------------------
MILLER

4.3.3 Generic GROUP BY Procedure

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

SELECT some-columns, COUNT(*)
  FROM your-table
 GROUP BY some-columns;

And then there is the variation involving the HAVING clause (you don't want to see all the counts, you just want to see those groupings where there is more than one identical value, and so on). These are very common requirements, but with NDS, you can easily build a program that does all the work for you, for any table, and for any single column (and this is extensible to multiple columns as well).

Here is the header of such a procedure:

/* Filename on companion disk: countby.sp */
PROCEDURE countBy (
   tab IN VARCHAR2,
   col IN VARCHAR2, 
   atleast IN INTEGER := NULL,
   sch IN VARCHAR2 := NULL,
   maxlen IN INTEGER := 30)

tab

The name of the table.

col

The name of the column.

sch

The name of the schema (default of NULL = USER).

atleast

If you supply a non-NULL value for atleast, then the SELECT statement includes a HAVING COUNT(*) greater than that value.

maxlen

Used for formatting of the output.

You can look at the countby.sp file on the companion disk to see the full implementation; here is all the code except that used to do the formatting (header string and so on):

IS
   TYPE cv_type IS REF CURSOR;
   cv cv_type;
   
   SQL_string VARCHAR2(32767) := 
      'SELECT ' || col || ', COUNT(*) 
         FROM ' || NVL (sch, USER) || '.' || tab ||
      ' GROUP BY ' || col;
   
   v_val VARCHAR2(32767);
   v_count INTEGER;
BEGIN
   IF atleast IS NOT NULL
   THEN
      SQL_string := SQL_string || ' HAVING COUNT(*) >= ' || atleast;
   END IF;
 
   OPEN cv FOR SQL_String;
   
   LOOP
      FETCH cv INTO v_val, v_count;
      EXIT WHEN cv%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (RPAD (v_val, maxlen) || ' ' || v_count);
   END LOOP;
   
   CLOSE cv;
END;
/   

As you start to build more and more of these generic utilities, you will find that it doesn't take very much code or effort -- you just have to think through the steps of the SQL string construction carefully.

4.3.4 Generic GROUP BY Package

Displaying information is useful for test purposes, but in many cases you want to work with the queried information further, not simply show it. Let's build on the countby procedure shown in the previous section to provide an implementation in which the results of the dynamic query are stored in an index-by table for subsequent analysis.

Here is the specification of the package:

/* Filename on companion disk: countby.pkg */
CREATE OR REPLACE PACKAGE grp
IS
   TYPE results_rt IS RECORD (
      val VARCHAR2(4000),
      countby INTEGER);
      
   TYPE results_tt IS TABLE OF results_rt 
      INDEX BY BINARY_INTEGER;

   FUNCTION countBy (
      tab IN VARCHAR2,
      col IN VARCHAR2, 
      atleast IN INTEGER := NULL,
      sch IN VARCHAR2 := NULL,
      maxlen IN INTEGER := 30)
      RETURN results_tt;
END grp;
/      

The implementation of the countby function is virtually the same as the procedure. The main difference is that I now have a record structure to fetch into, and an index-by table to fill. You can see both these changes in the loop that fetches the rows:

LOOP
   FETCH cv INTO rec;
   EXIT WHEN cv%NOTFOUND;
   retval(cv%ROWCOUNT) := rec;
END LOOP;

With this package in place, I can very easily build programs that access this analytical information. Here is one example::

/* Filename on companion disk: countby.tst */
DECLARE
   results grp.results_tt;
   indx PLS_INTEGER;
   minrow PLS_INTEGER;
   maxrow PLS_INTEGER;
BEGIN
   results := grp.countby ('employee', 'department_id');
   
   /* Find min and max counts. */
   indx := results.FIRST;
   LOOP
      EXIT WHEN indx IS NULL;

      IF minrow IS NULL OR
         minrow > results(indx).countby
      THEN 
         minrow := indx; 
      END IF;
      
      IF maxrow IS NULL OR
         maxrow < results(indx).countby
      THEN   
         maxrow := indx; 
      END IF;

      /* Perform other processing as well... */

      /* Move to next group count. */
      indx := results.NEXT(indx);
   END LOOP;
END;
/