Query compare, which one of query is better?

From: Tomeo <tkepic_at_gmail.com>
Date: 20 Oct 2006 07:44:05 -0700
Message-ID: <1161355445.633291.156330@i42g2000cwa.googlegroups.com>

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 Received on Fri Oct 20 2006 - 09:44:05 CDT