PL/SQL Operators
An operator in PL/SQL is a symbol that tells the compiler to perform a
specific operation on one or more values (operands) and return a result.
In other words:
Operators are the “tools” we use to manipulate data.
Types of Operators in PL/SQL
We’ll group them into six major categories:
Arithmetic Operators
Relational (Comparison) Operators
Logical Operators
Concatenation Operator
Membership & Range Operators
Special Operators
Arithmetic Operators
Used for mathematical calculations.
Operator Description Example
+ Addition a+b
- Subtraction a-b
* Multiplication a*b
/ Division a/b
** Exponentiation (Power) a ** b
Example:
SET SERVEROUTPUT ON;
DECLARE
a NUMBER := 10;
b NUMBER := 3;
BEGIN
DBMS_OUTPUT.PUT_LINE('Addition: ' || (a + b));
DBMS_OUTPUT.PUT_LINE('Subtraction: ' || (a - b));
DBMS_OUTPUT.PUT_LINE('Multiplication: ' || (a * b));
DBMS_OUTPUT.PUT_LINE('Division: ' || (a / b));
DBMS_OUTPUT.PUT_LINE('Power: ' || (a ** b));
END;
/
Relational (Comparison) Operators
Used to compare two values — result is TRUE, FALSE, or NULL.
Operator Description Example
= Equal to a=b
!=, <> Not equal to a <> b
> Greater than a>b
< Less than a<b
>= Greater than or equal to a >= b
<= Less than or equal to a <= b
Example:
DECLARE
x NUMBER := 5;
y NUMBER := 10;
BEGIN
IF x < y THEN
DBMS_OUTPUT.PUT_LINE('x is less than y');
END IF;
IF x <> y THEN
DBMS_OUTPUT.PUT_LINE('x is not equal to y');
END IF;
END;
/
Logical Operators
Used to combine multiple conditions.
Operator Description Example
AND Returns TRUE if both are TRUE (a > 5 AND b < 10)
OR Returns TRUE if any is TRUE (a > 5 OR b < 10)
NOT Reverses the condition NOT(a > 5)
Example:
DECLARE
marks NUMBER := 75;
BEGIN
IF marks >= 60 AND marks <= 100 THEN
DBMS_OUTPUT.PUT_LINE('First Class or Higher');
END IF;
END;
/
Concatenation Operator
|| → Joins two strings together.
Example:
DECLARE
fname VARCHAR2(10) := 'Mahesh';
lname VARCHAR2(10) := 'B';
BEGIN
DBMS_OUTPUT.PUT_LINE('Full Name: ' || fname || ' ' || lname);
END;
/
Membership & Range Operators
Used in SQL/PLSQL conditions.
Operator Description Example
IN TRUE if value is in a list x IN (10,20,30)
NOT IN TRUE if value is not in list x NOT IN (10,20,30)
BETWEEN ...
TRUE if value is in range x BETWEEN 5 AND 15
AND ...
TRUE if value is outside x NOT BETWEEN 5 AND
NOT BETWEEN
range 15
Example:
DECLARE
age NUMBER := 25;
BEGIN
IF age BETWEEN 18 AND 30 THEN
DBMS_OUTPUT.PUT_LINE('Eligible Age');
END IF;
END;
/
Special Operators
Operator Description Example
LIKE Pattern matching name LIKE 'M%'
IS NULL Checks for NULL value salary IS NULL
IS NOT NULL Checks for NOT NULL salary IS NOT NULL
Example:
DECLARE
emp_name VARCHAR2(20) := 'Mahesh';
BEGIN
IF emp_name LIKE 'M%' THEN
DBMS_OUTPUT.PUT_LINE('Name starts with M');
END IF;
END;
/
Example :
DECLARE
name VARCHAR2(20) := 'Mahesh';
marks NUMBER := 85;
BEGIN
IF marks BETWEEN 75 AND 100 AND name LIKE 'M%' THEN
DBMS_OUTPUT.PUT_LINE(name || ' has distinction marks');
END IF;
END;
/
Arithmetic Operators
Q1: Find the sum of two numbers 15 and 25
BEGIN
DBMS_OUTPUT.PUT_LINE(15 + 25); -- Answer: 40
END;
/
Q2: Find the difference between 100 and 35
BEGIN
DBMS_OUTPUT.PUT_LINE(100 - 35); -- Answer: 65
END;
/
-- Q3: Multiply 12 and 8
BEGIN
DBMS_OUTPUT.PUT_LINE(12 * 8); -- Answer: 96
END;
/
-- Q4: Divide 50 by 4
BEGIN
DBMS_OUTPUT.PUT_LINE(50 / 4); -- Answer: 12.5
END;
/
-- Q5: Find 5 raised to the power of 3
BEGIN
DBMS_OUTPUT.PUT_LINE(5 ** 3); -- Answer: 125
END;
/
-- Q6: Calculate total cost: qty=7, price=120
DECLARE
qty NUMBER := 7;
price NUMBER := 120;
BEGIN
DBMS_OUTPUT.PUT_LINE(qty * price); -- Answer: 840
END;
/
-- Q7: Increase salary 50000 by 10%
DECLARE
sal NUMBER := 50000;
BEGIN
DBMS_OUTPUT.PUT_LINE(sal + (sal*0.1)); -- Answer: 55000
END;
/
-- Q8: Area of rectangle (length=8, width=6)
BEGIN
DBMS_OUTPUT.PUT_LINE(8*6); -- Answer: 48
END;
/
-- Q9: Remainder when 20 divided by 3 (MOD)
BEGIN
DBMS_OUTPUT.PUT_LINE(MOD(20,3)); -- Answer: 2
END;
/
-- Q10: Average of 80, 90, 100
BEGIN
DBMS_OUTPUT.PUT_LINE((80+90+100)/3); -- Answer: 90
END;
/
Relational Operators
-- Q1: Check if 10 equals 10
BEGIN
DBMS_OUTPUT.PUT_LINE(10 = 10); -- TRUE
END;
/
-- Q2: Check if 20 not equal to 15
BEGIN
DBMS_OUTPUT.PUT_LINE(20 <> 15); -- TRUE
END;
/
-- Q3: Check if 7 is greater than 3
BEGIN
DBMS_OUTPUT.PUT_LINE(7 > 3); -- TRUE
END;
/
-- Q4: Check if 5 is less than 2
BEGIN
DBMS_OUTPUT.PUT_LINE(5 < 2); -- FALSE
END;
/
-- Q5: Check if 15 >= 15
BEGIN
DBMS_OUTPUT.PUT_LINE(15 >= 15); -- TRUE
END;
/
-- Q6: Compare two variables
DECLARE
a NUMBER := 30;
b NUMBER := 40;
BEGIN
DBMS_OUTPUT.PUT_LINE(a < b); -- TRUE
END;
/
-- Q7: Check if 100 < 50
BEGIN
DBMS_OUTPUT.PUT_LINE(100 < 50); -- FALSE
END;
/
-- Q8: Compare strings ('ABC' = 'abc')
BEGIN
DBMS_OUTPUT.PUT_LINE('ABC' = 'abc'); -- FALSE
END;
/
-- Q9: Check if salary(50000) > bonus(10000)
DECLARE
salary NUMBER := 50000;
bonus NUMBER := 10000;
BEGIN
DBMS_OUTPUT.PUT_LINE(salary > bonus); -- TRUE
END;
/
-- Q10: Check if marks(75) = pass_marks(75)
DECLARE
marks NUMBER := 75;
pass_marks NUMBER := 75;
BEGIN
DBMS_OUTPUT.PUT_LINE(marks = pass_marks); -- TRUE
END;
/
Logical Operators
-- Q1: AND example
BEGIN
DBMS_OUTPUT.PUT_LINE(10 > 5 AND 8 < 12); -- TRUE
END;
/
-- Q2: OR example
BEGIN
DBMS_OUTPUT.PUT_LINE(10 > 15 OR 5 < 10); -- TRUE
END;
/
-- Q3: NOT example
BEGIN
DBMS_OUTPUT.PUT_LINE(NOT(5 > 10)); -- TRUE
END;
/
-- Q4: Combine AND and OR
BEGIN
DBMS_OUTPUT.PUT_LINE((5 > 3 AND 2 < 4) OR 1 = 2); -- TRUE
END;
/
-- Q5: Check eligibility (age=20, marks=70)
DECLARE
age NUMBER := 20;
marks NUMBER := 70;
BEGIN
DBMS_OUTPUT.PUT_LINE(age >= 18 AND marks >= 60); -- TRUE
END;
/
-- Q6: Check fail condition
DECLARE
marks NUMBER := 35;
BEGIN
DBMS_OUTPUT.PUT_LINE(marks < 40 OR marks IS NULL); -- TRUE
END;
/
-- Q7: NOT with equality
BEGIN
DBMS_OUTPUT.PUT_LINE(NOT(100 = 200)); -- TRUE
END;
/
-- Q8: Complex condition
BEGIN
DBMS_OUTPUT.PUT_LINE((10 < 5) AND (3 < 4)); -- FALSE
END;
/
-- Q9: Multi-OR condition
BEGIN
DBMS_OUTPUT.PUT_LINE(5 = 5 OR 5 = 6 OR 5 = 7); -- TRUE
END;
/
-- Q10: Logical with variables
DECLARE
active BOOLEAN := TRUE;
paid BOOLEAN := FALSE;
BEGIN
DBMS_OUTPUT.PUT_LINE(active AND NOT paid); -- TRUE
END;
/
Concatenation Operator
-- Q1: Simple concatenation
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello' || ' World'); -- Hello World
END;
/
-- Q2: Add space between
BEGIN
DBMS_OUTPUT.PUT_LINE('Mahesh' || ' ' || 'Balike'); -- Mahesh Balike
END;
/
-- Q3: Concatenate number
BEGIN
DBMS_OUTPUT.PUT_LINE('Value: ' || 100); -- Value: 100
END;
/
-- Q4: Full name from variables
DECLARE
fname VARCHAR2(10) := 'Ravi';
lname VARCHAR2(10) := 'Kumar';
BEGIN
DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname); -- Ravi Kumar
END;
/
-- Q5: String + calculation
BEGIN
DBMS_OUTPUT.PUT_LINE('Total: ' || (50+25)); -- Total: 75
END;
/
-- Q6: Multi-part
BEGIN
DBMS_OUTPUT.PUT_LINE('PL' || '/' || 'SQL'); -- PL/SQL
END;
/
-- Q7: Names in one line
DECLARE
n1 VARCHAR2(10) := 'A';
n2 VARCHAR2(10) := 'B';
n3 VARCHAR2(10) := 'C';
BEGIN
DBMS_OUTPUT.PUT_LINE(n1 || ', ' || n2 || ', ' || n3); -- A, B, C
END;
/
-- Q8: Prefix & suffix
BEGIN
DBMS_OUTPUT.PUT_LINE('[' || 'DATA' || ']'); -- [DATA]
END;
/
-- Q9: String with date
BEGIN
DBMS_OUTPUT.PUT_LINE('Today: ' || TO_CHAR(SYSDATE, 'DD-
MON-YYYY'));
END;
/
-- Q10: Using concatenation in IF
DECLARE
name VARCHAR2(20) := 'Mahesh';
BEGIN
IF LENGTH(name) > 5 THEN
DBMS_OUTPUT.PUT_LINE(name || ' has long name');
END IF;
END;
/
Membership & Range Operators
-- Q1: IN example
BEGIN
DBMS_OUTPUT.PUT_LINE(10 IN (5,10,15)); -- TRUE
END;
/
-- Q2: NOT IN example
BEGIN
DBMS_OUTPUT.PUT_LINE(20 NOT IN (5,10,15)); -- TRUE
END;
/
-- Q3: BETWEEN example
BEGIN
DBMS_OUTPUT.PUT_LINE(15 BETWEEN 10 AND 20); -- TRUE
END;
/
-- Q4: NOT BETWEEN example
BEGIN
DBMS_OUTPUT.PUT_LINE(25 NOT BETWEEN 10 AND 20); -- TRUE
END;
/
-- Q5: Variable with IN
DECLARE
grade CHAR(1) := 'A';
BEGIN
DBMS_OUTPUT.PUT_LINE(grade IN ('A','B','C')); -- TRUE
END;
/
-- Q6: Variable with BETWEEN
DECLARE
marks NUMBER := 85;
BEGIN
DBMS_OUTPUT.PUT_LINE(marks BETWEEN 80 AND 100); -- TRUE
END;
/
-- Q7: Date with BETWEEN
BEGIN
DBMS_OUTPUT.PUT_LINE(SYSDATE BETWEEN DATE '2025-01-01'
AND DATE '2025-12-31'); -- TRUE
END;
/
-- Q8: NOT IN string
BEGIN
DBMS_OUTPUT.PUT_LINE('X' NOT IN ('A','B','C')); -- TRUE
END;
/
-- Q9: Nested IN
BEGIN
DBMS_OUTPUT.PUT_LINE(3 IN (1,2,3,4,5)); -- TRUE
END;
/
-- Q10: BETWEEN edge case
BEGIN
DBMS_OUTPUT.PUT_LINE(10 BETWEEN 10 AND 20); -- TRUE
END;
/
Special Operators
-- Q1: LIKE example
BEGIN
DBMS_OUTPUT.PUT_LINE('Mahesh' LIKE 'M%'); -- TRUE
END;
/
-- Q2: LIKE with underscore
BEGIN
DBMS_OUTPUT.PUT_LINE('Cat' LIKE 'C_t'); -- TRUE
END;
/
-- Q3: NOT LIKE
BEGIN
DBMS_OUTPUT.PUT_LINE('Dog' NOT LIKE 'C%'); -- TRUE
END;
/
-- Q4: IS NULL example
DECLARE
data VARCHAR2(10);
BEGIN
DBMS_OUTPUT.PUT_LINE(data IS NULL); -- TRUE
END;
/
-- Q5: IS NOT NULL example
DECLARE
value NUMBER := 100;
BEGIN
DBMS_OUTPUT.PUT_LINE(value IS NOT NULL); -- TRUE
END;
/
-- Q6: LIKE with multiple characters
BEGIN
DBMS_OUTPUT.PUT_LINE('Oracle' LIKE '%cle'); -- TRUE
END;
/
-- Q7: Pattern match with single char
BEGIN
DBMS_OUTPUT.PUT_LINE('A1' LIKE 'A_'); -- TRUE
END;
/
-- Q8: Date IS NULL
DECLARE
join_date DATE;
BEGIN
DBMS_OUTPUT.PUT_LINE(join_date IS NULL); -- TRUE
END;
/
-- Q9: NOT LIKE with variable
DECLARE
name VARCHAR2(10) := 'SQL';
BEGIN
DBMS_OUTPUT.PUT_LINE(name NOT LIKE 'P%'); -- TRUE
END;
/
-- Q10: Complex LIKE
BEGIN
DBMS_OUTPUT.PUT_LINE('Mahesh123' LIKE 'M%3'); -- TRUE
END;
/