Re: What's wrong with this code!!!

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/10/24
Message-ID: <34511b74.9955525@www.sigov.si>#1/1

On 21 Oct 1997 14:50:41 GMT, corncrowe_at_aol.com (Corncrowe) wrote:

>>We can't really help you if we don't have an error message to >decipher.
>
>I was implying structure and flow rather than a specific error message.
>
>>> delete from temp_aging where ord_id is not null;
>>> commit;
>
>The delete temp_aging and commit statement...there is a cursor at the beginning
> of the transaction. The cursor is populated with a select statement from
> temp_aging, Later in the code fragment a delete statement is encountered.
> What is the purpose of the delete temp_aging at this point in the flow?
>

What is the purpouse of the DELETE statement at this point, you ask? Well, to delete some rows :-) !

The cursor c_orders has not been opened yet, it was only declared. It is obvious the author of this code first deletes a rows from the TEMP_AGING table and then in the next transaction he/she re-populates it with the INSERT statement. And only then the curcsor c_orders is opened and fethed in a loop. So there is nothing wrong with the delete statement.

What realy confuses me in this DELETE is a "WHERE ord_id IS NOT NULL" constraint. This way you leave some records in the TEMP_AGING table which will be process later in a cursor but efectively they will have no inpact on a final update because the constraint in an update will be: "WHERE ord_id = NULL" which will never evaluate to TRUE. Its only a waste of processing time.

What is wrong with the cursor itself is that it has an ORDER BY statement in it without a reason. If you look carefuly what the cursor is used for, you'll see that it efectively causes the final UPDATE to update some records - but *order of updates is totaly irrelevant*. The only explanation for use of ORDER BY I can see is to force the optimizer to use an ord_id index segment (if there is one) instead of the TEMP_AGING table itself. If this is a case then I would rather use a hint - at least the intention will be more obvious to others.

And finaly, as someone else allready mentioned, there is no need to use PL/SQL in this case. You can achive same thing with a straight UPDATE statement without PL/SQL prcessing overhead.

>I guess the question is to find mistakes in structure and point out how this
> stored procedure can be written differently. Think of it as an exercise of
> sorts.
>
>Thanks,
>
>Jon

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Oct 24 1997 - 00:00:00 CDT