Thank you very much for your quick respond!
Tomas
On Oct 20, 3:59 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> 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,
> > TomasTo 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 - 10:16:40 CDT
