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:
declareOutput:
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;
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...
