CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Tim Sanders
Tim Sanders

Reputation: 851

Oracle plsql - Can a query be used in a plsql if statement?

I don't even know if this is possible, but what I'm trying to accomplish is comparing a single distinct value from a table without using SELECT INTO. This is not a requirement. All I'm trying to do is to see if it can be done. Obviously I could just use a SELECT INTO to resolve this, but I thought it would be cool to do in just one line.

Here is an idea of what I'm trying to accomplish:

 var1 := 'some_text';
 if var2 > 0 
   and var1 <> (select distinct col_text from some_table)
 then
  null;

This is not legal oracle plsql. So without adding any other lines of code, how could get this to do a comparison?

One out of many different attempts:

My thought behind this was to create a record type and hopefully convert it to this type all in one line and then it would be evaluated. Just an FYI I thought of this because I have a background in PHP.

 --rec is of %rowtype
 var1 := 'some_text';
 if var2 > 0 
   and var1 <> (rec := (select distinct col_text from some_table)).col_text
 then
  null;

Research:

From what I've read in the oracle documentation it doesn't sound like this is possible, but I'm trying to see if there some type of workaround.

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#CBJCAHGF

I checked out the doc on the assignment statement. Hoping I might find something interesting there.

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/assignment_statement.htm#LNPLS01301

Upvotes: 1

Views: 337

Answers (1)

WW.
WW.

Reputation: 24311

What you are trying to do is not possible. You need to bring the result from the select statement back into a PL/SQL variable in order to conduct an IF statement.

Oracle has different engines for SQL and PL/SQL. They are closely related, but PL/SQL is still a "client" language.

Upvotes: 3

Related Questions