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
Arithmetic operators
Here the list of various arithmetic operators with an example
| Operator | Description | Example |
|---|---|---|
| + | Adds two operands | X + Y will give 15 |
| – | Subtracts second operands from the first | X – Y will give 5 |
| * | Multiplies both operands | X * Y will give 50 |
| / | Divides numerator by de-numerator | X / Y will give 2 |
| ** | Exponentiation operator increase one operand to the power of other | X ** Y will give 100000 |
Relational operators
Oracle has various relational operators. Here, the list of various Relational operators with an example:
| Operator | Description | Example |
|---|---|---|
| = | 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 |
Comparison Operators
Here the some list of various Comparison operators with ans an example:
| Operator | Description | Example |
|---|---|---|
| LIKE | The 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. |
| BETWEEN | The 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. |
| IN | The 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 NULL | The 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:
| Operator | Description | Examples |
|---|---|---|
| and | Called the logical AND operator. If both the operands are real, the-comparison N condition becomes true. | ( X and Y) is false. |
| or | Called the logical OR Operator. If any of the two operands will true, then the condition becomes true. | (X or Y) is true. |
| not | Called 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.
| Function | Example | Result | Purpose |
|---|---|---|---|
| ASCII | ASCII(‘A’) | 65 | Returns an ASCII code. |
| CHR(X) | CHR(’65’) | ‘A’ | Returns the character with the ASCII value of x. |
| CONCAT | CONCAT(‘A’,’BC’) | ‘ABC’ | Concatenates the strings x and y and returns the appended string. |
| CONVERT | CONVERT( ‘Ä Ê Í’, ‘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. |
| INSTR | INSTR( ‘This is a playlist’, ‘is’) | 3 | Search for a substring and return the location of the substring in a string |
| LENGTH | LENGTH(‘ABC’) | 3 | Returns the number of characters in x. |
| LOWER | LOWER(‘Abc’) | ‘abc’ | Return a string with all characters converted to lowercase. |
| LPAD | LPAD(‘ABC’,5,’*’) | ‘**ABC’ | Return a string that is left-padded with the specified characters to a certain length. |
| LTRIM | LTRIM(‘ ABC ‘) | ‘ABC ‘ | Remove spaces or other specified characters in a set from the left end of a string. |
| REGEXP_INSTR | REGEXP_INSTR( ‘Y2K problem’,’\d+’) | 2 | Return the position of a pattern in a string. |
| REGEXP_LIKE | REGEXP_LIKE( ‘Year of 2017′,’\d+’ ) | true | Match a string based on a regular expression pattern. |
| REGEXP_SUBSTR | REGEXP_SUBSTR( ‘Number 10’, ‘\d+’ ) | 10 | Extract substrings from a string using a pattern of a regular expression. |
Some more functions of strings in operators in pl/SQL
| REPLACE | REPLACE(‘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. |
| RTRIM | RTRIM(‘ ABC ‘) | ‘ ABC’ | Remove all spaces or specified character in a set from the right end of a string. |
| SOUNDEX | SOUNDEX(‘sea’) | ‘S000’ | Return a phonetic representation of a specified string. |
| SUBSTR | SUBSTR(‘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. |
| TRIM | TRIM(‘ ABC ‘) | ‘ABC’ | Remove the space character or other specified characters either from the start or end of a string. |
| UPPER | UPPER(‘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:)
Nice
Very informative
Great
One another piece of the jewel added
Nice. Thanku. Very informative
These articles are helping a lot..Thanks
Thanks a lot
Good
Superb awesome
Very nice article…
Good content
Informative
Keep it up…
I like this. It’s worth bookmarking.
Informative
Very detailed and great article.
Great work with graphics..! more to be improved.. ! keep up the work!
Hi I read your post. You give very useful content for us.
Thanks
Useful info
Nice info👍
Indeed a complete guide 👍
Useful
Good one 👍
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.
Informative
Very informative.
I adore reading through and I believe this website got some genuinely utilitarian stuff on it!
Spot on with this write-up, I honestly think this website needs much
more attention. I’ll probably be returning to read through more, thanks
for the info!
Your way of teaching is very good. Keep up the good work.
Helpful info. Fortunate me I found your website by accident, and I am stunned why
this coincidence did not took place in advance!
I bookmarked it.
Hello, I enjoy reading all of your post. I like to write a little comment to
support you.