Your revised business logic features three tables. In the absence of any indication that the tables contents are related it's better to keep them as separate queries.
Here is my solution (featuring valid Oracle syntax).
CREATE OR REPLACE PROCEDURE SP_NAME (
p_phone_number IN VARCHAR2
, p_REG OUT INTEGER
, p_EMAIL OUT VARCHAR2
, p_VALID OUT INTEGER )
AS
l_usr_cnt pls_integer;
l_email details_users.email%type;
l_sub_cnt pls_integer;
BEGIN
SELECT COUNT (*) into l_usr_cnt
FROM users u
WHERE u.phone_number = p_phone_number;
begin
select du.email into l_email
from details_users du
where du.phone_number = p_phone_number;
exception
when others then
l_email := null;
end;
SELECT COUNT (*) into l_sub_cnt
FROM suscribers s -- probably a typo :-/
WHERE s.phone_number = p_phone_number;
if l_usr_cnt != 0 then
p_reg := 1; -- "true"
else
p_reg := 0; -- "false"
end if;
p_email := l_email;
if l_sub_cnt != 0 then
p_valid := 1;
else
p_valid := 0;
end if;
END;
Note it is permitted to populate an OUT parameter directly from a query, like this:
SELECT COUNT (*)
into p_reg
FROM users u
WHERE u.phone_number = p_phone_number;
However, it is accepted good practice to work with local variables and only assign OUT parameters at the end of the procedure. This is to ensure consistent state is passed to the calling program, (which matters particularly if the called procedure throws an exception).
Also, I prefixed all the parameter names with p_. This is also optional but good practice. Having distinct namespaces is always safer, but it especially matters when the parameter names would otherwise match table column names (i.e. phone_number).
