[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6556
  • Last Modified:

SELECT STATEMENT IN IF CLAUSE? (pl\sql)

hello experts,

is it posible to have a select statement in an if clause. I am writing a function or stored procedure which will search for the account no given and return true\false depending on the result of select statement.

thanks
0
sunny012097
Asked:
sunny012097
1 Solution
 
NicksonKohCommented:
Hi kwieckii,

No, u can have a select in an if. Below is a sample procedure on how i do it.

CREATE OR REPLACE PROCEDURE Sp_Test(av_acctno    VARCHAR2)
IS
   tmp  VARCHAR2(1):= 'N';
BEGIN
   SELECT 'Y' INTO tmp
   FROM abc
   WHERE account_id = av_acctno;
     
   IF tmp = 'Y' THEN
     ...
   ELSE
     ...
   END IF;

   COMMIT;
END;

Cheers
Nickson
0
 
NicksonKohCommented:
sunny,

Sorry sunny for addressing u with the wrong name.

Nickson
0
 
sujith80Commented:
"is it posible to have a select statement in an if clause"
Very much possible.
Follow this example

create or replace function test_func(p_arg number)
return boolean
is
 l_no number := 0;
begin
 if p_arg = 1 then
  select count(*) into l_no
  from a1;
 else
  select count(*) into l_no
  from a2;
 end if;
if l_no > 0 then
 return true;
else
 return false;
end if ;

end;
/

declare
 l_res boolean;
begin
 l_res := test_func(2);
 if l_res = true then
  dbms_output.put_line('true');
 else
  dbms_output.put_line('false');
 end if;
end;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GGuzdziolCommented:
You can do it also without using variables; let's say Your query is <query>. You want to do something only if it returns at least one row:

...
  for x in (select * from (<query> ) where rownum = 1) loop
    ...
  end loop;
...
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
you can even directly call the function in the if/elsif conditions :

begin
 if ( my_func(arguments here....) )  then
  dbms_output.put_line('TRUE');
 else
  dbms_output.put_line('FALSE');
 end if;
end;
/

make sure your function returns true or false for all possible type of returns from the function.

Thanks
0
 
slightwv (䄆 Netminder) Commented:
NicksonKoh,
Sorry but your solution will never hit the 'else' clause because it will receive a NO_DATA_FOUND exception.

sunny,
Can I assume you want the boolean TRUE and FALSE returned?  If so, I don't believe you can do this.  Oracle has a BOOLEAN data type but I think it can only be used inside PL/SQL and can't be returned as a datatype to some calling program.

I've provided a function that returns a Y or N and also a single select statement that will do the same thing.  The drawback to the select statement is that you have to hit the table twice.

testcase:
----------------------
drop table tab1;
create table tab1 (
account number
)
/

insert into tab1 values(1);
commit;


--Do in a single select statement
-- cons against:  have to select it twice
undefine account

select 'Y' from tab1 where account=&&account
union
select 'N' from dual where not exists (select 1 from tab1 where account=&&account)
/

--function based
CREATE OR REPLACE FUNCTION Sp_Test(av_acctno    number) return char
IS
      tmp char(1);
BEGIN
   SELECT 'Y' INTO tmp
   FROM tab1
   WHERE account = av_acctno;
     
   return 'Y';

   exception
         when NO_DATA_FOUND then
               return 'N';

END;
/

show errors


select SP_TEST(1) from dual;
select SP_TEST(2) from dual;

0
 
awking00Commented:
The following assumes that account_no is of varchar2 datatype. If it is something else, modify to accommodate the proper datatype.

create or replace function acct_no_exists(acct_no_in varchar2) return boolean is
v_exists  pls_integer;
begin
select count(*)
into v_exists
from yourtable
where account_no = acct_no_in;
if v_exists > 0 then
return 'TRUE'
else
return 'FALSE'
end if;
end;
/
0
 
NicksonKohCommented:
Hey man, this sux man... what a silly mistake by me :(
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now