This PL\/SQL Tutorial is about PL\/SQL Operators.<\/p>\n
Operators are special expressions that operate process between one or more values \u200b\u200bin programming languages.<\/p>\n
PL\/SQL has operators that allow to process various data.<\/p>\n
\n
Arithmetic operators<\/li>\n
Relational operators<\/li>\n
Comparison operators<\/li>\n
Logical operators<\/li>\n
String operators<\/li>\n<\/ul>\n
Arithmetic Operators in PL\/SQL<\/h3>\n
Arithmetic operators are operators that provide basic numerical operations such as addition, subtraction, multiplication and division on numbers.<\/p>\n
By comparing the two values, according to the operator result, it returns TRUE or FALSE.<\/p>\n
SET SERVEROUTPUT ON;\r\nDECLARE\r\n v_number1 number (2) := 10;\r\n v_number2 number (2) := 20;\r\nBEGIN\r\n\r\n IF (v_number1 = v_number2) THEN\r\n DBMS_OUTPUT.put_line('v_number1 and v_number2 equal.');\r\n ELSE\r\n DBMS_OUTPUT.put_line('v_number1 and v_number2 not equal');\r\n END IF;\r\n \r\n IF (v_number1 < v_number2) THEN\r\n DBMS_OUTPUT.put_line('v_number2 bigger.');\r\n ELSE\r\n DBMS_OUTPUT.put_line('v_number1 bigger.');\r\n END IF;\r\n\r\n IF ( v_number1 > v_number2 ) THEN\r\n DBMS_OUTPUT.put_line('v_number1 bigger.');\r\n ELSE\r\n DBMS_OUTPUT.put_line('v_number2 bigger.');\r\n END IF;\r\n\r\n v_number1 := 3;\r\n v_number2 := 7;\r\n IF ( v_number1 <= v_number2 ) THEN\r\n DBMS_OUTPUT.put_line('v_number2 equal or bigger.');\r\n END IF;\r\n IF ( v_number1 >= v_number2 ) THEN\r\n DBMS_OUTPUT.put_line('v_number1 equal or bigger.');\r\n END IF;\r\n\r\n IF ( v_number1 <> v_number2 ) THEN\r\n DBMS_OUTPUT.put_line('v_number1 and v_number2 not equal.');\r\n ELSE\r\n DBMS_OUTPUT.put_line('v_number1 and v_number2 equal.');\r\n END IF;\r\nEND;<\/pre>\n
Comparison Operators in PL\/SQL<\/h3>\n
Like Relational Operators, Comparison operators compares two values and returns TRUE or FALSE. You can see comparision operators as below.<\/p>\n
\n
Like Operator in Oracle<\/li>\n
Between Operator in Oracle<\/li>\n
IN Operator in Oracle<\/li>\n
IS NULL Operator in Oracle<\/li>\n<\/ul>\n
LIKE Operator in PL\/SQL<\/h4>\n
Like operator is used to search by the pattern.<\/p>\n
SET SERVEROUTPUT ON;\r\nDECLARE\r\n v_message VARCHAR2(100) := 'Hello I'm Yusuf SEZER';\r\nBEGIN\r\n IF v_message LIKE '%Yusuf%' THEN\r\n DBMS_OUTPUT.put_line('Value found.');\r\n ELSE\r\n DBMS_OUTPUT.put_line('No Value foun.');\r\n END IF;\r\nEND;<\/pre>\n
Between Operator in PL\/SQL<\/h4>\n
The Between Operator is used to query whether the specified value is within the specified range.<\/p>\n
SET SERVEROUTPUT ON;\r\nDECLARE\r\n v_age PLS_INTEGER := 75;\r\nBEGIN\r\n IF (v_age BETWEEN 0 AND 5) THEN\r\n DBMS_OUTPUT.put_line('Baby');\r\n ELSIF (v_age BETWEEN 5 AND 12) THEN\r\n DBMS_OUTPUT.put_line('Child');\r\n ELSIF (v_age BETWEEN 12 AND 18) THEN\r\n DBMS_OUTPUT.put_line('Young');\r\n ELSE\r\n DBMS_OUTPUT.put_line('Other');\r\n END IF;\r\nEND;<\/pre>\n
IN Operator in PL\/SQL<\/h4>\n
The IN Opearator is used to query whether the specified value is one of the specified values.<\/p>\n
SET SERVEROUTPUT ON;\r\nDECLARE\r\n v_message VARCHAR2(55) := 'mine';\r\nBEGIN\r\n IF (v_message IN('This', 'girl', 'is', 'mine') ) THEN\r\n DBMS_OUTPUT.put_line('found.');\r\n ELSE\r\n DBMS_OUTPUT.put_line('not found.');\r\n END IF;\r\nEND;<\/pre>\n
IS NULL Operator in PL\/SQL<\/h4>\n
Used to check if the value is NULL.<\/p>\n
SET SERVEROUTPUT ON;\r\nDECLARE\r\n v_message VARCHAR2(55) := '';\r\nBEGIN\r\n IF v_message IS NULL THEN\r\n DBMS_OUTPUT.put_line('Mesagge is null.');\r\n ELSE\r\n DBMS_OUTPUT.put_line(v_message);\r\n END IF;\r\nEND;<\/pre>\n
Logical Operators in PL\/SQL<\/h3>\n
There are 3 logical operator in Oracle PL\/SQL. AND, OR,NOT.<\/p>\n
AND Operator in PL\/SQL: <\/strong>It connects two operands each other(X AND Y). If both operands is true, then the result is true.<\/p>\n
OR Operator in PL\/SQL: <\/strong>It connects two operands each other(X AND Y). If one of the operands is true, then the result is true.<\/p>\n
NOT Operator in PL\/SQL: <\/strong>It reverse the result. If the result is true it returns false, and if the result is false, it returns true.<\/p>\n
SET SERVEROUTPUT ON;\r\nDECLARE\r\n v_result1 BOOLEAN := TRUE;\r\n v_result2 BOOLEAN := FALSE;\r\nBEGIN\r\n IF v_result1 AND v_result2 THEN\r\n DBMS_OUTPUT.put_line('Both operands are true.');\r\n ELSIF v_result1 OR v_result2 THEN\r\n DBMS_OUTPUT.put_line('One of the operands is true.');\r\n END IF;\r\n\r\n IF NOT v_result2 THEN\r\n DBMS_OUTPUT.put_line('v_result2 is FALSE.');\r\n END IF;\r\nEND;<\/pre>\n
String Operator || in PL\/SQL<\/h3>\n
PL \/ SQL || operator is used to concatenate string expressions.<\/p>\n