Why do the rows need to be processed in a particular order?
"Ed Stevens" <spamdump_at_nospam.noway.nohow> wrote in message
news:3d877d53.112737467_at_ausnews.austin.ibm.com...
>
>
> Platform: Oracle 8.1.7 on NT4
>
> Developer is involved in a project of converting an application (written
in
> Microfocus Cobol) from DB2/2 on OS/2 to Oracle on NT. The general problem
at
> hand is to identify a selection of rows, sorted ascending by a 'timestamp'
field
> (implemented in our 8.1.7 db as a char(26)), processing each row in turn.
> Processing of each row includes modifying a 'status' column that removes
it from
> the original selection criteria.
>
> In the original application, it is implemented as shown with this pseudo
code:
>
>
> ---- begin pseudo code example -----
>
> Begin loop
>
> SELECT MIN(CTE_DTS)
> INTO :ws-dts
> FROM MY_TABLE
> WHERE APPC_STA = 'P'
> AND CTE_DTS > '0001-01-01-00.00.00.000000'
>
> --- do a bunch of processing based on the row returned above, then change
the
> status of that row ---
>
> UPDATE my_table
> SET appc_sta = 'Z'
> WHERE APPC_STA = 'P'
> AND CTE_DTS = :ws-dts
>
> commit
> End loop
> ---- end pseudo code example -----
>
>
> She was having some difficulty with inconsistent results, which I chalked
up to
> inconsistent test cases. (She's a fairly inexperienced programmer, and
this is
> her first exposure to Oracle). By the time I was able to take a look at
it,
> she had replaced the above with a cursor, thusly;
>
> ---- begin pseudo code example -----
>
> Declare cursor c1 as
> SELECT CTE_DTS
> FROM MY_TABLE
> WHERE APPC_STA = 'P'
> AND CTE_DTS > '0001-01-01-00.00.00.000000'
> ORDER BY CTE_DTS
>
> Loop until no-more-rows
> Fetch cursor c1 into . . . .
> Do a bunch of processing
> Update row returned by the fetch
> commit
> end loop
> ---- end pseudo code example -----
>
> So now I'm wondering how to adviser her on the best approach from an
Oracle
> standpoint. Which general approach would be preferred? My gut feel is
the
> cursor loop.
>
> Begin-loop
> SELECT MIN(timestamp-field)
> WHERE condition-1
>
> UPDATE so that condition-1 is no longer true
> COMMIT
> End-loop
>
> OR . . .
>
> DECLARE CURSOR
> WHERE condition-1
> ORDER BY timestamp-field
>
> Begin-loop
> FETCH CURSOR
> UPDATE so that condition-1 is no longer true
> COMMIT
> End-loop
>
>
> I'm shooting from the hip here, so I may have missed some detail, but if
you
> remember that I'm only presenting pseudo code, the general problem and
question
> should be apparent.
>
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)
Received on Tue Sep 17 2002 - 14:34:37 CDT
