Dear PL/SQL Guru's,
I am working on the PL/SQL coding best practice for my team. I need your inputs on which of the following is best practice.
1. Embeded SQL Query in Begin ..end block
declare
l_term_id number;
l_name varchar2;
li_name varchar2 := 'IMMEDIATE';
Begin
SELECT term_id,
name
into l_term_id, l_name
FROM ra_terms_tl
WHERE name = li_name ;
Exception
When no_data_found THEN
DBMS_OUTPUT.PUT_LINE('Payment terms not exists');
When WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('More than one payment term exists with same name');
WHEN OTHERS THEN
raise_application_error(-20000,SQLERM);
END;
2. Using cursors
l_term_id number;
l_name varchar2;
li_name varchar2 := 'IMMEDIATE';
cursor c_term(cp_name varchar2) is
SELECT term_id,
name
FROM ra_terms_tl
WHERE name = cp_name;
Begin
open c_term(li_name);
fetch c_term into l_term_id, l_name;
close c_term;
if l_term_id is null then
DBMS_OUTPUT.PUT_LINE('Not able to derive the value of term_id');
end if;
END;
Please advise pros and cons of both the coding approach and your suggestion which approach to be used.
Regards