Re: What is the best approach to process data on row by row basis ?

krislioe_at_gmail.com wrote:
> Hi Gurus,
>
> I need to code stored proc to process sales_orders into Invoices. I
> think that I must do row by row operation, but if possible I don't want
> to use cursor. The algorithm is below :
>
> for all sales_orders with status = "open"
>
> check for credit limit
> if over credit limit -> insert row log_table; process next order
> check for overdue
> if there is overdue invoice -> insert row to log_table; process
> next order
> check all order_items for stock availability
> if there is item that has not enough stock -> insert row to
> log_table; process next order
>
> if all check above are passed:
> create Invoice (header + details)
>
> end_for
>
> What is the best approach to process data on row by row basis like
> above ?

What's the fastest way to fly to West Coast? Don't do it.

What's the best way to process data on a row by row basis? Don't do it.

Oracle is a relational database system and you should process sets of data at a time, not row by row.

It looks to me as if the process you described might be able to be done by 2 SQL statements, perhaps one SQL statement.

That's the best way to do it. Don't commit in a loop, do one commit at the end. Received on Sun Nov 19 2006 - 11:53:39 CST