Best practice question: Updating Aggregates

In Oracle 9i:

I have a table with 50 rows marked as "Sent" and 50 rows marked as
"Unsent". I have an outbound that must summarize the 50 "Unsent" rows
(e.g. into 5 lines on a file), then update the 50 "Unsent" rows to
"Sent" as one LUW. The summarization involves joining to other tables,
applying WHERE clauses, and formatting of aggregate results.

Does anyone have strong preferences in this area?

Option A: Write a summary cursor which does the "group by" in the SQL (so the cursor "looks like" the file being produced). Write an independent update that runs after the cursor and updates the rows which were "Unsent" to "Sent".
Pros: Grouping logic and aggregation is done in SQL, not PL/SQL. Easier to maintain, less risk of bugginess, present or future. This is especially per
Cons: Possible data integrity issues - what if the WHERE clause on the cursor is modified, but the WHERE clause of the Update is not?

Option B: Write a detail cursor with a "FOR UPDATE" clause. Do Grouping in PL/SQL. Update each row within the cursor using "WHERE CURRENT OF..." clause.
Pros: No data integrity issues - only rows fetched into the file output can be marked as "Sent".
Cons: Aggregation occurs at 2 levels, plus several aggregate columns are functions applied to aggregate columns... The mantra "don't do in PL/SQL that which can be done in SQL" comes to mind.

Option C: Same as "B", but hide as much of the PL/SQL aggregation as possible in analytic functions.
Pros: Alleviates, slightly, the cons of B. Cons: Makes the already complex cursor even more confusing, particularly to developers not intimately familiar with analytics. I would almost trust many of my co-workers to maintain Option B over Option C.

Option D: SOMETHING I'VE NOT THOUGHT OF (???)

This seems like a fairly common situation.... since I've got the time to pontificate, I thought I'd see which way others have gone on this issue. Thanks in advance! Received on Thu Oct 06 2005 - 09:36:27 CDT