Hi
From performance point of view isn't Scenario - II a better option, coz we aren't using LEFT OUTER JOIN.
Scenario - I
Oracle - 9i ==> Join is done using Keywords
OPEN o_r85_profile_cur FOR
SELECT DECODE(pb.profile_exists_flg
, 1
, pb.profile_id
, NULL) l_profile_id
, r85.active_flg
, r85.inactive_dt
, r85.r85_profile_ver
FROM profile_base pb
LEFT OUTER JOIN
r85_profile r85
ON pb.profile_id = r85.profile_id
WHERE pb.entity_id = i_entity_id
AND pb.profile_type = ao_constants.c_r85_profile;
Oracle - 8i ==> Join is done using + operator
OPEN o_r85_profile_cur FOR
SELECT DECODE(pb.profile_exists_flg
, 1
, pb.profile_id
, NULL) l_profile_id
, r85.active_flg
, r85.inactive_dt
, r85.r85_profile_ver
FROM profile_base pb
, r85_profile r85
WHERE pb.entity_id = i_entity_id
AND pb.profile_type = ao_constants.c_r85_profile
AND pb.profile_id = r85.profile_id(+);
Scenario - II
BEGIN
SELECT profile_id
INTO l_profile_id
FROM profile_base
WHERE entity_id = i_entity_id
AND profile_type = ao_constants.c_r85_profile
AND profile_exists_flg = ao_constants.c_profile_exists_yes;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_profile_id := 0;
END;
l_section := 'Open cur o_r85_profile_cur';
OPEN o_r85_profile_cur FOR
SELECT profile_id
, active_flg
, inactive_dt
, r85_profile_ver
FROM r85_profile
WHERE profile_id = l_profile_id;
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment