PL/SQL Best way of Checking Table then Pulling the Data given that check Passes-CodePudding

Good Afternoon,

I am using both of these examples when needing to first check if the count of a query is = 1, then if =1, pull the data from that same table in PL/SQL block

Approach 1

DECLARE
v_count number;
v_name varchar2(255);
BEGIN

select count(*) into v_count from THE_USERS where USERID = :IN_ID;

if v_count <> 1 then
raise error;
end if;

select first_name || ' ' || last_name fullname into v_name from THE_USERS where USERID = :IN_ID;

....Other Stuff

END;

Approach 2

DECLARE
v_count number;
v_name varchar2(255);
BEGIN

select count(*), max(first_name || ' ' || last_name) fullname into v_count, v_name from THE_USERS where USERID = :IN_ID;

if v_count <> 1 then

raise error;
end if;
 
....Other Stuff

END;

So I started off with Approach 1, but #2 looks seems to be better just so there is less code, but Im not sure if that would cause a performance hit for the max(). I also rarely expect that error to be thrown compared to the one result.

Let me know if anymore information would be useful.

CodePudding user response：

Don't try to query the table twice, just query the table for the data you want and catch the exceptions if there are too many or too few rows:

DECLARE
  v_name varchar2(255);
BEGIN
  select first_name || ' ' || last_name
  into   v_name
  from   THE_USERS
  where  USERID = :IN_ID;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Handle zero rows
    NULL;
  WHEN TOO_MANY_ROWS THEN
    -- Handle more than 1 row.
    NULL;
END;
/

fiddle