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:)