Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
This PL/SQL Tutorial is about PL/SQL Operators.
Operators are special expressions that operate process between one or more values in programming languages.
PL/SQL has operators that allow to process various data.
- Arithmetic operators
- Relational operators
- Comparison operators
- Logical operators
- String operators
Arithmetic Operators in PL/SQL
Arithmetic operators are operators that provide basic numerical operations such as addition, subtraction, multiplication and division on numbers.
1 2 3 4 5 6 7 8 | SET SERVEROUTPUT ON; BEGIN DBMS_OUTPUT.put_line( 10 + 10); DBMS_OUTPUT.put_line( 10 - 7); DBMS_OUTPUT.put_line( 10 * 7); DBMS_OUTPUT.put_line( 10 / 7); DBMS_OUTPUT.put_line( 10 ** 3); -- Exponentiation END; |
Relational Operators in PL/SQL
By comparing the two values, according to the operator result, it returns TRUE or FALSE.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | SET SERVEROUTPUT ON; DECLARE v_number1 number (2) := 10; v_number2 number (2) := 20; BEGIN IF (v_number1 = v_number2) THEN DBMS_OUTPUT.put_line('v_number1 and v_number2 equal.'); ELSE DBMS_OUTPUT.put_line('v_number1 and v_number2 not equal'); END IF; IF (v_number1 < v_number2) THEN DBMS_OUTPUT.put_line('v_number2 bigger.'); ELSE DBMS_OUTPUT.put_line('v_number1 bigger.'); END IF; IF ( v_number1 > v_number2 ) THEN DBMS_OUTPUT.put_line('v_number1 bigger.'); ELSE DBMS_OUTPUT.put_line('v_number2 bigger.'); END IF; v_number1 := 3; v_number2 := 7; IF ( v_number1 <= v_number2 ) THEN DBMS_OUTPUT.put_line('v_number2 equal or bigger.'); END IF; IF ( v_number1 >= v_number2 ) THEN DBMS_OUTPUT.put_line('v_number1 equal or bigger.'); END IF; IF ( v_number1 <> v_number2 ) THEN DBMS_OUTPUT.put_line('v_number1 and v_number2 not equal.'); ELSE DBMS_OUTPUT.put_line('v_number1 and v_number2 equal.'); END IF; END; |
Comparison Operators in PL/SQL
Like Relational Operators, Comparison operators compares two values and returns TRUE or FALSE. You can see comparision operators as below.
- Like Operator in Oracle
- Between Operator in Oracle
- IN Operator in Oracle
- IS NULL Operator in Oracle
LIKE Operator in PL/SQL
Like operator is used to search by the pattern.
1 2 3 4 5 6 7 8 9 10 | SET SERVEROUTPUT ON; DECLARE v_message VARCHAR2(100) := 'Hello I'm Yusuf SEZER'; BEGIN IF v_message LIKE '%Yusuf%' THEN DBMS_OUTPUT.put_line('Value found.'); ELSE DBMS_OUTPUT.put_line('No Value foun.'); END IF; END; |
Between Operator in PL/SQL
The Between Operator is used to query whether the specified value is within the specified range.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SET SERVEROUTPUT ON; DECLARE v_age PLS_INTEGER := 75; BEGIN IF (v_age BETWEEN 0 AND 5) THEN DBMS_OUTPUT.put_line('Baby'); ELSIF (v_age BETWEEN 5 AND 12) THEN DBMS_OUTPUT.put_line('Child'); ELSIF (v_age BETWEEN 12 AND 18) THEN DBMS_OUTPUT.put_line('Young'); ELSE DBMS_OUTPUT.put_line('Other'); END IF; END; |
IN Operator in PL/SQL
The IN Opearator is used to query whether the specified value is one of the specified values.
1 2 3 4 5 6 7 8 9 10 | SET SERVEROUTPUT ON; DECLARE v_message VARCHAR2(55) := 'mine'; BEGIN IF (v_message IN('This', 'girl', 'is', 'mine') ) THEN DBMS_OUTPUT.put_line('found.'); ELSE DBMS_OUTPUT.put_line('not found.'); END IF; END; |
IS NULL Operator in PL/SQL
Used to check if the value is NULL.
1 2 3 4 5 6 7 8 9 10 | SET SERVEROUTPUT ON; DECLARE v_message VARCHAR2(55) := ''; BEGIN IF v_message IS NULL THEN DBMS_OUTPUT.put_line('Mesagge is null.'); ELSE DBMS_OUTPUT.put_line(v_message); END IF; END; |
Logical Operators in PL/SQL
There are 3 logical operator in Oracle PL/SQL. AND, OR,NOT.
AND Operator in PL/SQL: It connects two operands each other(X AND Y). If both operands is true, then the result is true.
OR Operator in PL/SQL: It connects two operands each other(X AND Y). If one of the operands is true, then the result is true.
NOT Operator in PL/SQL: It reverse the result. If the result is true it returns false, and if the result is false, it returns true.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SET SERVEROUTPUT ON; DECLARE v_result1 BOOLEAN := TRUE; v_result2 BOOLEAN := FALSE; BEGIN IF v_result1 AND v_result2 THEN DBMS_OUTPUT.put_line('Both operands are true.'); ELSIF v_result1 OR v_result2 THEN DBMS_OUTPUT.put_line('One of the operands is true.'); END IF; IF NOT v_result2 THEN DBMS_OUTPUT.put_line('v_result2 is FALSE.'); END IF; END; |
String Operator || in PL/SQL
PL / SQL || operator is used to concatenate string expressions.
1 2 3 4 5 6 7 | SET SERVEROUTPUT ON; DECLARE v_name VARCHAR(40) := 'Yusuf'; v_surname VARCHAR(40) := 'SEZER'; BEGIN DBMS_OUTPUT.put_line('Merhaba ben ' || v_name || ' ' || v_surname); END; |
You can find more detailed information about below topics in the below link.
You will find below topics in this article.
- What is PL/SQL
- Oracle PL/SQL Data Types and Variables and Literals
- Oracle PL/SQL Operators
- Oracle PL/SQL Conditional Statements
- Oracle PL/SQL Loops
- Oracle PL/SQL Procedures and Procedure Parameters
- Oracle PL/SQL Functions
- Oracle PL/SQL Cursor
- Oracle PL/SQL Records
- Oracle PL/SQL Exception
- Oracle PL/SQL Trigger
- Oracle PL/SQL Packages
- Oracle PL/SQL Collections
You can find more detailed information about operators at docs.oracle.com
