PLSQL Operators

PL SQL Operators

Relational operators compare two expressions or values and return a Boolean result. PL/SQL supports operators like

=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN

Operator	Meaning
=	equal to
<>, !=, ~=, ^=	not equal to
<	less than
>	greater than
<=	less than or equal to
>=	greater than or equal to

Is NULL operator checks if
Example code:

declare
   a number ( 2 ) := 50 ;
   b number ( 2 ) := 15 ;
begin
   if ( a = b ) then
      dbms_output.put_line ( '1-a is equal to b' );
   else
      dbms_output.put_line ( '1-a is not equal to b' );
   end if;
   if ( a < b ) then
      dbms_output.put_line ( '2-a is less than b' );
   else
      dbms_output.put_line ( '2-a is not less than b' );
   end if ;
   if ( a > b ) then
      dbms_output.put_line ( '3-a is greater than b' );
   else
      dbms_output.put_line ( '3-a is not greater than b' );
   end if ;
   -- Lets change value  a and b
   a      := 40 ;
   b      := 90 ;
   if ( a <= b ) then
      dbms_output.put_line ( '4-a is either equal or less than b' );
   end if ;
   if ( b >= a ) then
      dbms_output.put_line ( '5-b is either equal or greater than a' );
   end if ;
   if ( a <> b ) then
      dbms_output.put_line ( '6-a is not equal to b' );
   else
      dbms_output.put_line ( '6-a is equal to b' );
   end if ;
end;

Output:



IS NULL Operator

The IS NULL operator returns the Boolean value TRUE if its operand is null or FALSE if it is not null. test for the state of being null IF variable IS NULL THEN


LIKE Operator

You use the LIKE operator to compare a character, string, or CLOB value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the patterns match or FALSE if they do not match.

The patterns matched by LIKE can include two special-purpose characters underscore _ matches one character and percent sign % matches zero or more characters
example:
select var_name from table_names where var_name LIKE 'JOHN%';

you will get all names starting with JOHN like JOHN, JOHNSON,JOHNATHON etc...

BETWEEN Operator

The BETWEEN operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value."
Example:
If you want to select some record between some input date and system date

Select * from SOME_TABLE where to_char(beginning_date,'yyyymmdd') between to_char(i_first_date,'yyyymmdd') and to_char(sysdate,'yyyymmdd')

IN Operator

The IN operator tests set membership. It means "equal to any member of
 select * from table names where var_name IN ('JOHN','ERIC');
You will get all records containing names of JOHN and ERIC...