Path: utzoo!utgpu!water!watmath!clyde!rutgers!ames!ucbcad!ucbvax!hplabs!hpcea!hpfcdc!hpldola!tn
From: tn@hpldola.HP.COM (Ted Neff)
Newsgroups: comp.databases
Subject: Re: Informix query
Message-ID: <11650001@hpldola.HP.COM>
Date: 8 Jan 88 02:10:30 GMT
References: <609@jpusa1.UUCP>
Organization: HP Elec. Design Div. -ColoSpgs
Lines: 61

>> 4)   Difficulty (outside of perform) of doing that most basic of
>>      database applications: that of cycling through the rows, adding,
>>      deleting, modifying as you go.

> I would like to know how to go about this also.  I am using ORACLE
> and would like to do this.
> . . .
> Why Can't relational systems do this?  Or better yet, is there a
> SQL/Relational system out there that does?

I don't know about Oracle, but the SQL standard defines a facility to do
exactly what you describe.  Here is what C. J. Date has to say on page 54
of "A Guide to the SQL Standard" (from Addison-Wesley).

  "A cursor consists essentially of a kind of "pointer" that can be
   used to run through an ordered collection of rows, pointing to each
   of the rows in that collection in turn and thus providing
   addressability to those rows one at a time.  If cursor C is
   pointing to row R, it is said to be "positioned on" row R.  Row R
   can then be updated or deleted via the "positioned" form of the
   UPDATE and DELETE operations (UPDATE/DELETE ... WHERE CURRENT OF C)."

The following excerpt is from Chapter 8, "Sequential Table Processing" in
the "HPSQL C Application Programming Guide" (from Hewlett-Packard),

  "Sequential table processing is the programming technique you use to
   operate on a "multiple-row query result, one row at a time".  The
   query result is referred to as an "active set".  You use a pointer
   called a "cursor" to move through the active set, retrieving a row
   at a time into host variables and optionally updating or deleting
   the row."

Note that the host language in which SQL is embedded (e.g., C, 4GL, or
as in Date's book -- PL/1), provides the procedural constructs (such as
while loops) to control the sequencing through the selected data.  Such
constructs are not specified in the SQL standard.  (If you don't
have such constructs, it doesn't make much sense to have cursors).

The standard SQL cursor operations are:  DECLARE CURSOR, OPEN, FETCH,
UPDATE ... WHERE CURRENT, DELETE ... WHERE CURRENT, and CLOSE.  All of
these operations are present in the two embedded SQL implementations
that I've used -- Informix-4GL and HP Allbase.  Informix-4GL even has
extensions to standard SQL cursors (the most useful being a
scroll-cursor to provide random access to the active set).  A typical
use of the cursor operations would be similar to the following.

    DECLARE c CURSOR FOR select-statement
    OPEN c
    while (condition)
        FETCH c INTO host_language_variables
        if (no more records found)
            break;
        ...
        UPDATE table
            SET table.qty = table.qty + 100
            WHERE CURRENT OF c
        ...
        DELETE FROM table WHERE CURRENT OF c
        ...
    end
    CLOSE c