Operators in PL/SQL – Oracle PL/SQL Tutorials

In this article, we will discuss operators in PL/SQL of Oracle PL/SQL tutorials. Here we have presented all operators and their examples.

If you missed the previous article of PL/SQL Constants and Literals, then please click here.

Lets start the tour….

What’s inside:
Introduction to Operators
Arithmetic operators
Relational operators
Comparison operators
Logical operators
String operators
FAQs

Introduction to Operators

An operator is a symbol that describes the compiler to perform specific mathematical or logical manipulation

PL/SQL language has various operators, and they are

  • Arithmetic operators
  • Relational operators
  • Comparison operators
  • Logical operators
  • String operators
operators in pl/sql

Arithmetic operators

Here the list of various arithmetic operators with an example

OperatorDescriptionExample
+Adds two operandsX + Y will give 15
Subtracts second operands from the firstX – Y will give 5
*Multiplies both operandsX * Y will give 50
/Divides numerator by de-numeratorX / Y will give 2
**Exponentiation operator increase one operand to the power of otherX ** Y will give 100000

Relational operators

Oracle has various relational operators. Here, the list of various Relational operators with an example:

OperatorDescriptionExample
=Tests if the contents of two operands are equal or not, if yes, then condition becomes true.(X = Y) is not true.
!=<>~=It will check if the values of two operands are equal or not else amounts (values) are not equal, then the condition becomes true.(X != Y) is true.
>It is used to checks if the value of the left operand is greater than the value of right operand, if yes then condition becomes true.(X > Y) is not true.
<It is used to check whether the left operand’s value is less than the value of right operand, if yes then condition becomes true.(A < B) is true.
>=It is used to validate whether the value of the left operand is greater than or equal to the value of right operand, if yes then condition becomes true.(A >= B) is not true.
<=It validates if the value of the left operand is less than or equal to the value of right operand, if yes then condition becomes true.(A <= B) is true
operators in pl/sql

Comparison Operators

Here the some list of various Comparison operators with ans an example:

OperatorDescriptionExample
LIKEThe LIKE operator compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not.If ‘Lucine Tech’ like ‘L% T_h’ returns a Boolean true, whereas, ‘Ritesh Singh’ like ‘R% S_h’ returns a Boolean false.
BETWEENThe BETWEEN operator tests whether a value lies in a specified range. x BETWEEN a AND b means that k >= a and k <= b.If K = 10 then, k between 5 and 20 returns true, k between 5 and 10 returns true, but k between 11 and 20 returns false.
INThe IN operator tests set membership. k IN (set) means that x is equal to any member of set.If k = ‘m’ then, k in (‘a’, ‘b’, ‘c’) returns Boolean false but k in (‘m’, ‘n’, ‘o’) returns Boolean true.
IS NULLThe IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is not NULL. Comparisons involving NULL values always yield NULL.If k = ‘m’, then ‘k is null’ returns Boolean false.

Logical Operators

Here the list of logical operators with an example:

OperatorDescriptionExamples
andCalled the logical AND operator. If both the operands are real, the-comparison N condition becomes true.( X and Y) is false.
orCalled the logical OR Operator. If any of the two operands will true, then the condition becomes true.(X or Y) is true.
notCalled the logical NOT Operator and used to reverse the logical state of its operand. If a condition will true, then Logical NOT operator will make it false.not (X and Y) is true.

Strings

Oracle provides some string functions that help you manipulate character strings more effectively.

FunctionExampleResultPurpose
ASCIIASCII(‘A’)65Returns an ASCII code.
CHR(X)CHR(’65’)‘A’Returns the character with the ASCII value of x.
CONCATCONCAT(‘A’,’BC’)‘ABC’Concatenates the strings x and y and returns the appended string.
CONVERTCONVERT( ‘Ä Ê Í’, ‘US7ASCII’, ‘WE8ISO8859P1’ ) ‘A E I’Convert a character string from one character set to another.
 INITCAP INITCAP(‘hi  there’)‘Hi There’Converts the first character in each word in a specified string to uppercase and the rest to lowercase.
 INSTRINSTR( ‘This is a playlist’, ‘is’)3Search for a substring and return the location of the substring in a string
 LENGTHLENGTH(‘ABC’)3Returns the number of characters in x.
LOWERLOWER(‘Abc’)‘abc’Return a string with all characters converted to lowercase.
LPADLPAD(‘ABC’,5,’*’)‘**ABC’Return a string that is left-padded with the specified characters to a certain length.
LTRIMLTRIM(‘ ABC ‘)‘ABC  ‘Remove spaces or other specified characters in a set from the left end of a string.
REGEXP_INSTRREGEXP_INSTR( ‘Y2K problem’,’\d+’)2Return the position of a pattern in a string.
REGEXP_LIKEREGEXP_LIKE( ‘Year of 2017′,’\d+’ )trueMatch a string based on a regular expression pattern.
REGEXP_SUBSTRREGEXP_SUBSTR( ‘Number 10’, ‘\d+’ ) 10Extract substrings from a string using a pattern of a regular expression.
Some more functions of strings in operators in pl/SQL
REPLACEREPLACE(‘JACK AND JOND’,’J’,’BL’); ‘BLACK AND BLOND’Replace all occurrences of a substring by another substring in a string.
RPAD RPAD(‘ABC’,5,’*’)‘ABC**’Return a string that is right-padded with the specified characters to a certain length.
RTRIMRTRIM(‘ ABC ‘)‘ ABC’Remove all spaces or specified character in a set from the right end of a string.
SOUNDEXSOUNDEX(‘sea’)‘S000’Return a phonetic representation of a specified string.
SUBSTRSUBSTR(‘Oracle Substring’, 1, 6 )‘Oracle’Extract a substring from a string.
TRANSLATE TRANSLATE(‘12345’, ‘143’, ‘bx’) ‘b2x5’Replace all occurrences of characters by other characters in a string.
TRIMTRIM(‘ ABC ‘)‘ABC’Remove the space character or other specified characters either from the start or end of a string.
UPPERUPPER(‘Abc’)‘ABC’Convert all characters in a specified string to uppercase.

FAQs:

1. What is a PL/SQL block?

Answer: In Oracle PL/SQL, statements are grouped into units called Blocks. The PL/SQL blocks can include the constants, variables, SQL statements, loops, conditional statements, exception handling. These blocks can also build a procedure, a function, or a package.

2. What is a Join?

Answer: Join is a keyword used to query data from multiple tables based on the relationship between the fields of tables. Keys play a significant role in Joins.

3. Which operator has the highest precedence among the −AND, NOT, OR

Answer:NOT

4. What does the colon sign (: ) means in the given statement? : deficit:= balance – loan;

Answer: The colon (: )sign means here that the variable: the deficit is an external variable.

5. Write a single statement that adds the words ‘Hello’ and ‘World’ and assign it in a variable named greeting.

Answer: greet := ‘Hello’ || ‘World’;


This is all about the operators in PL/SQL or PL/SQL operators. In the coming article, we will discuss conditions and loops. So, please stay with us.

Thank you:)

30 thoughts on “Operators in PL/SQL – Oracle PL/SQL Tutorials

  1. I would like to thnkx for the efforts you’ve put in writing this website.
    I’m hoping the same high-grade site post from you in the upcoming as well. Actually, your creative writing skills has encouraged me to get my own blog now. Really the blogging is spreading its wings rapidly and Your write up is a great example of it.

  2. I’m amazed, I have to admit. Rarely do I encounter a blog that’s both equally educative and entertaining, and let
    me tell you, you have hit the nail on the head.
    The issue is something that too few people are speaking intelligently about.
    Now i’m very happy I came across this in my hunt for something
    relating to this.

Leave a Reply

Your email address will not be published. Required fields are marked *