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 ?
>
> Thank you for your help,
> xtanto

The best approach is to dump the row by row approach (as you will have a context switch for every record) and replace it by insert into log_table
select <some attributes>
from order
where exists
(query determining whether the order is over the credit limit for that customer)

The same approach holds for all the other elements in this assignment.

commit after the last statement.

-- 
Sybrand Bakker
Senior Oracle DBA