Re: Query compare, which one of query is better?

On Oct 20, 10:44 am, "Tomeo" <tke..._at_gmail.com> wrote:
> Hi everybody,
> I have a two versions for the same function:
>
> VERSION A:
> ---------------------------------------------------------------------------­-------------------------------------
> FUNCTION FZ_GET_UNAPPLIED(PIDM IN NUMBER, TERM IN VARCHAR2)
> RETURN NUMBER
> IS
> RN_UNAPPLIED NUMBER (17,2) := 0;
> BEGIN
> BEGIN
> SELECT ABS(SUM(NVL(ACCD.TBRACCD_BALANCE,0)))
> INTO RN_UNAPPLIED
> FROM TBRACCD ACCD,
> TBBDETC DETC
> WHERE ACCD.TBRACCD_DETAIL_CODE = DETC.TBBDETC_DETAIL_CODE
> AND ACCD.TBRACCD_PIDM = PIDM
> AND ACCD.TBRACCD_TERM_CODE = TERM
> AND DETC.TBBDETC_TYPE_IND = 'P';
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> RN_UNAPPLIED := NULL;
> WHEN OTHERS THEN
> RN_UNAPPLIED := NULL;
> END;
> RETURN RN_UNAPPLIED;
> END FZ_GET_UNAPPLIED;
>
> VERSION B:
> ---------------------------------------------------------------------------­-------------------------------------
> FUNCTION FZ_GET_UNAPPLIED(PIDM IN NUMBER, TERM IN VARCHAR2)
> RETURN NUMBER
> IS
> RN_UNAPPLIED NUMBER (17,2) := 0;
> CURSOR C IS
> SELECT ABS(SUM(NVL(ACCD.TBRACCD_BALANCE,0)))
> FROM TBRACCD ACCD,
> TBBDETC DETC
> WHERE ACCD.TBRACCD_DETAIL_CODE = DETC.TBBDETC_DETAIL_CODE
> AND ACCD.TBRACCD_PIDM = PIDM
> AND ACCD.TBRACCD_TERM_CODE = TERM
> AND DETC.TBBDETC_TYPE_IND = 'P';
> BEGIN
> OPEN C;
> FETCH C INTO RN_UNAPPLIED;
> IF C%NOTFOUND THEN
> RN_UNAPPLIED := NULL;
> END IF;
> CLOSE C;
> RETURN RN_UNAPPLIED;
> END FZ_GET_UNAPPLIED;
> ---------------------------------------------------------------------------­--------------
> Which one of version is better? What is better to use cursor (version
> B) or query with exception ? I guess that cursor is better, but I'm new
> in PL/SQL so want to be sure that I'm right.
>
> Thanks a lot,
> Tomas

To determine if one approach is "better" than the other you should consider running actual time tests and reviewing the run statistics to see what level of resources each version uses. For code that will be ran concurrently by many uses the vesion which uses less latches may well be the better choice even if it is slightly slower than the other version in the time tests.

In general your first approach is probably better. I have a problem with your error handler. I do not like your use of a WHEN OTHERS condition in the example. With a select into there are generally only two conditions that the code may need to handle: no_data_found and too_many_rows. Any other error probably indcates a serious condition that needs reporting and investigation.

IMHO -- Mark D Powell -- Received on Fri Oct 20 2006 - 09:59:54 CDT