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
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
Here the list of various arithmetic operators with an 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|
Oracle has various relational operators. Here, the list of various Relational operators with an 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|
Here the some list of various Comparison operators with ans an 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.|
Here the list of logical operators with an example:
|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.|
Oracle provides some string functions that help you manipulate character strings more effectively.
|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.|
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
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.