java2db

PL/SQL Operators



PL/SQL Operators are most essential symbols ,that are used to do Arithmetic , relational, comparison and logical operations. Each operator tells the compiler to do specific operation.

 

PL/SQL Operators  Arithmetic operators

Operator Description
 + (Addition): The sum of two operands.
  (Subtraction):The Subtraction of two operands.
 * (Multiplication):The Multiplication of two operands.
 / (Division):The Division of two operands.
 ** (Exponentiation):left operand to the power of right operand.

Example :

DECLARE
num_one NUMBER;
num_two NUMBER;BEGIN
num_one := 10;
num_two := 5;
dbms_output.put_line(‘Addition of two numbers :’);
dbms_output.put_line(num_one + num_two) ;
dbms_output.put_line(‘Subtraction of two numbers :’);
dbms_output.put_line(num_one num_two) ;
dbms_output.put_line(‘Multiplication of two numbers :’);
dbms_output.put_line(num_one * num_two) ;
dbms_output.put_line(‘Division of two numbers :’);
dbms_output.put_line(num_one / num_two) ;
dbms_output.put_line(‘Exponentiation of two numbers :’);
dbms_output.put_line(num_one ** num_two) ;
END;
OUTPUT :
Addition of two numbers :
15
Subtraction of two numbers :
5
Multiplication of two numbers :
50
Division of two numbers :
2
Exponentiation of two numbers :
100000
PL/SQL procedure successfully completed.

 

 

PL/SQL Operators Relational PL/SQL Operators

Operator Description
 = (Equal to) : If both operand values are equal ,the condition becomes true.
 !=,<>,~=,^= (Not equal to):If both operand values are not equal , the condition becomes true
 > (Greater Than):If the left operand value is higher than right operand value ,the condition becomes true
 >= (Greater Than or Equals to):If the left operand value is higher or equal to the right operand value ,the condition becomes true.
 < (Less Than):If the left operand value is lower than right operand value ,the condition becomes true
 <= (Less Than or Equals to):If the left operand value is lower or equal to the right operand value ,the condition becomes true.

Example :

DECLARE
num_one NUMBER;
num_two NUMBER;
flag Boolean;
BEGIN
num_one := 10;
num_two := 5;
IF (num_one = num_two) THEN
dbms_output.put_line(‘Is equal ? :’ || ‘true’);
ELSE
dbms_output.put_line(‘Is equal ? :’ || ‘false’);
END IF;
IF (num_one != num_two) THEN
dbms_output.put_line(‘Is not equal? :’ || ‘true’);
ELSE
dbms_output.put_line(‘Is not equal? :’ || ‘false’);
END IF;
IF (num_one > num_two) THEN
dbms_output.put_line(‘Is greater than ?:’ || ‘true’);
ELSE
dbms_output.put_line(‘Is greater than ?:’ || ‘false’);
END IF;
IF (num_one >= num_two) THEN
dbms_output.put_line(‘Is greater than or equals to? :’ || ‘true’);
ELSE
dbms_output.put_line(‘Is greater than or equals to? :’ || ‘false’);
END IF;
IF (num_one < num_two) THEN
dbms_output.put_line(‘Is less than ?:’ || ‘true’);
ELSE
dbms_output.put_line(‘Is less than ?:’ || ‘false’);
END IF;
IF (num_one <= num_two) THEN
dbms_output.put_line(‘Is less than or equals to?:’ || ‘true’);
ELSE
dbms_output.put_line(‘Is less than or equals to?:’ || ‘false’);
END IF;
END;
OUTPUT:
Is equal ? :false
Is not equal? :true
Is greater than ?:true
Is greater than or equals to? :true
Is less than ?:false
Is less than or equals to?:falsePL/SQL procedure successfully completed.

 

 

PL/SQL Operators  Comparison PL/SQL Operators

Operator Description
IS NULL Returns true if the operand is NULL else false
LIKE Returns true if the character,String or CLOB matches to the pattern, else return false.
BETWEEN Checks the operand value between the specified range.True if the value is greater than or equals to the lower value and less than or equals to the upper value. if no, it return false.
IN Compare the value to be equal to any value of the set.
NOT IN compare the value not to be equal to any value of the set.

Example :

DECLAREname VARCHAR2(20);
salary FLOAT;BEGIN
name := ‘Rose’;
salary := 50000 ;IF(name IS NULL) THEN
dbms_output.put_line(‘Please assign a name ‘);
ELSE
dbms_output.put_line(‘Name is ‘ || name);
END IF;
IF(name LIKE ‘Rose’) THEN
dbms_output.put_line(‘Names are same’);
ELSE
dbms_output.put_line(‘Name are not same’);
END IF;
IF(salary BETWEEN 40000 AND 55000) THEN
dbms_output.put_line(‘Salary range is correct’);
ELSE
dbms_output.put_line(‘Salary range is not correct’);
END IF;
IF(name IN (‘Rose’,’Mark’,’Doll’,’Frank’)) THEN
dbms_output.put_line(‘Name exist in the set’);
ELSE
dbms_output.put_line(‘Name does not exist in the set’);
END IF;
IF(name NOT IN (‘Rose’,’Mark’,’Doll’,’Frank’)) THEN
dbms_output.put_line(‘Name exist in the set’);
ELSE
dbms_output.put_line(‘Name does not exist in the set’);
END IF;
END;
OUTPUT:
Name is Rose
Names are same
Salary range is correct
Name exist in the set
Name does not exist in the set
PL/SQL procedure successfully completed.

 

 

PL/SQL Operators   Logical operators

Operator Description
 AND (Logical AND):If all operands are true the condition become true.
 OR (Logical OR):If at least one operand  true the condition become true.
 NOT (Logical NOT):If operand is true , the NOT operator will make it false.

Example :

DECLARE
num_one NUMBER;
num_two NUMBER;BEGINnum_one := 10;
num_two := 5;IF (num_one >=10 AND num_two < 10) THEN
DBMS_OUTPUT.PUT_LINE(‘Condition is true’);
END IF;
IF (num_one > 10 OR num_two < 10) THEN
DBMS_OUTPUT.PUT_LINE(‘Condition is true’);
END IF;
IF (NOT num_one > 10 AND num_two < 10) THEN
DBMS_OUTPUT.PUT_LINE(‘Condition is true’);
END IF;
END;
OUTPUT:Condition is true
Condition is true
Condition is true
PL/SQL procedure successfully completed.

 

 

PL/SQL Operators  Assignment operator

Operator Description
 := This operator is use to assign a value to a variable or constant.

Example :

DECLARE
name VARCHAR2(20);
salary FLOAT;
BEGIN
name := ‘Rose’;
salary := 50000 ;
DBMS_OUTPUT.PUT_LINE(name);
DBMS_OUTPUT.PUT_LINE(salary );
END;
OUTPUT:Rose
50000
PL/SQL procedure successfully completed.

 

 

PL/SQL Operators   String concatenation operator

Operator Description
|| (concatenation) It will join two or more stings into  a single string (Ex: ‘hello’||’reader’  = helloreader)

Example :

 DBMS_OUTPUT.PUT_LINE(‘Good morning to ‘ || ‘all’);

 

 

PL/SQL Operators   Range  operator

Operator Description
.. It assigns a specific range of values.(Ex: 1..3  means 1,2,3)

Example  :

DECLARE
num_one NUMBER;
BEGIN
num_one := 0;
FOR num_two IN 1..5 LOOP
num_one := num_one + num_two ;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘The total of first five numbers :’|| num_one);
END;
OUTPUT:
The total of first five numbers :15
PL/SQL procedure successfully completed.



Related Posts :
PL/SQL Arrays and nested tables ( Collections )
IF THEN ELSE and CASE in PL/SQL
PL/SQL FOR and WHILE loop controls
PL/SQL Procedures
PL/SQL Triggers
PL/SQL Functions
PL/SQL Variables
Difference between equals method and == operator in java
   [will not be published]



^ <