Oracle PL/SQL Tutorial/PL SQL Operators/Operators
Содержание
- 1 Arithmetic Operators
- 2 Comparison Operators
- 3 Exponentiation
- 4 Logical Operators
- 5 Logical Operators in PL/SQL
- 6 Operators
- 7 Running Anonymous Blocks of Code
- 8 String Operators
- 9 The basic arithmetic operators in action
- 10 The negation and identity operators in action.
- 11 The Relational Operators: =, <>, !=, ~=, <, >, <=, >=
- 12 Use of Comparison Operators with Strings
Arithmetic Operators
Arithmetic operators are used for mathematical computations.
Operator Example Usage
10**5 The exponentiation operator. 10**5 = 100,000.
2*3 The multiplication operator. 2 * 3 = 6. / 6/2 The division operator. 6/2 = 3. + 2+2 The addition operator. 2+2 = 4. - 4-2 The subtraction operator. 4 -2 = 2. - -5 The negation operator. + +5 It complements the negation operator.
The basic arithmetic operators in action.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(4 * 2); --multiplication
3 DBMS_OUTPUT.PUT_LINE(24 / 3); --division
4 DBMS_OUTPUT.PUT_LINE(4 + 4); --addition
5 DBMS_OUTPUT.PUT_LINE(16 - 8); --subtraction
6 END;
7 /
8
8
8
8
PL/SQL procedure successfully completed.
SQL>
Comparison Operators
Comparison operators are used to compare one value or expression to another.
All comparison operators return a boolean result.
Operator Example Usage = IF A = B THEN The equality operator. <> IF A <> B THEN The inequality operator. != IF A != B THEN Another inequality operator, synonymous with <>. ~= IF A ~= B THEN Another inequality operator, synonymous with <>. < IF A < B THEN The less than operator. > IF A > B THEN The greater than operator. <= IF A <= B THEN The less than or equal to operator. >= IF A >= B THEN The greater than or equal to operator. LIKE IF A LIKE B THEN The pattern-matching operator. BETWEEN IF A BETWEEN B AND C THEN Checks to see if a value lies within a specified range of values. IN IF A IN (B,C,D) THEN Checks to see if a value lies within a specified list of values. IS NULL IF A IS NULL THEN Checks to see if a value is null.
Exponentiation
SQL>
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(4 ** 2);
3 END;
4 /
16
PL/SQL procedure successfully completed.
SQL>
SQL>
Logical Operators
PL/SQL has three logical operators: AND, OR, and NOT.
The NOT operator is typically used to negate the result of a comparison expression.
The AND and OR operators are typically used to link together multiple comparisons.
The Syntax for the NOT Operator:
NOT boolean_expression
Logical Operators in PL/SQL
x y x AND y x OR y NOT x True True True True False True False False True False False True False True True False False False False True
Operators
PL/SQL operators are either unary or binary.
Binary operators act on two values.
An example of binary operators is the addition operator, which adds two numbers together.
Unary operators only operate on one value.
The negation operator is unary.
PL/SQL operators can be divided into the following categories:
- Arithmetic operators
- Comparison operators
- Logical operators
- String operators
23. 1. Operators 23. 1. 1. Operators 23. 1. 2. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Thebasicarithmeticoperatorsinaction.htm">The basic arithmetic operators in action</a> 23. 1. 3. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/LogicalOperatorsinPLSQL.htm">Logical Operators in PL/SQL</a> 23. 1. 4. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/RunningAnonymousBlocksofCode.htm">Running Anonymous Blocks of Code</a> 23. 1. 5. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/ArithmeticOperators.htm">Arithmetic Operators</a> 23. 1. 6. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Exponentiation.htm">Exponentiation</a> 23. 1. 7. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Thenegationandidentityoperatorsinaction.htm">The negation and identity operators in action.</a> 23. 1. 8. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/ComparisonOperators.htm">Comparison Operators</a> 23. 1. 9. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/TheRelationalOperators.htm">The Relational Operators: =, <>, !=, ~=, <, >, <=, >=</a> 23. 1. 10. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/LogicalOperators.htm">Logical Operators</a> 23. 1. 11. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/StringOperators.htm">String Operators</a> 23. 1. 12. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/UseofComparisonOperatorswithStrings.htm">Use of Comparison Operators with Strings</a>
Running Anonymous Blocks of Code
SQL>
SQL> set serveroutput on
SQL> declare
2 v_length NUMBER :=5.5;
3 v_width NUMBER :=3.5;
4 v_area NUMBER;
5 begin
6 v_area:=v_length*v_width;
7 DBMS_OUTPUT.put_line("Area:"||v_area);
8 end;
9 /
Area:19.25
PL/SQL procedure successfully completed.
SQL>
String Operators
PL/SQL has two operators specifically designed to operate only on character string data.
These are the LIKE operator and the concatenation (||) operator.
The Syntax for the Concatenation Operator:
string_1 || string_2
The basic arithmetic operators in action
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(4 * 2); --multiplication
3 DBMS_OUTPUT.PUT_LINE(24 / 3); --division
4 DBMS_OUTPUT.PUT_LINE(4 + 4); --addition
5 DBMS_OUTPUT.PUT_LINE(16 - 8); --subtraction
6 END;
7 /
8
8
8
8
PL/SQL procedure successfully completed.
SQL>
SQL>
The negation and identity operators in action.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 x NUMBER;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(-242.24);
5 x := 5;
6 DBMS_OUTPUT.PUT_LINE(-x);
7 x := -5;
8 DBMS_OUTPUT.PUT_LINE(-x);
9 DBMS_OUTPUT.PUT_LINE(+10);
10 DBMS_OUTPUT.PUT_LINE(+x);
11 END;
12 /
-242.24
-5
5
10
-5
PL/SQL procedure successfully completed.
SQL>
The Relational Operators: =, <>, !=, ~=, <, >, <=, >=
String comparisons are case-sensitive.
String comparisons are dependent on the character set being used.
String comparisons are affected by the underlying datatype.
Comparing two values as CHAR strings might yield different results than the same values compared as VARCHAR2 strings.
It"s important to remember that Oracle dates contain a time component
True Expressions False Expressions 5 = 5 5 = 3 "AAAA" = "AAAA" "AAAA " = "AAAA" 5 != 3 5 <> 5 "AAAA " ~= "AAAA" "AAAA" ~= "AAAA" 10 < 200 10.1 < 10.05 "Jeff" < "Jenny" "jeff" < "Jeff" TO_DATE("15-Nov-61" < "15-Nov-97") TO_DATE("1-Jan-97" < "1-Jan-96") 10.1 <= 10.1 10 <= 20 "A" <= "B" "B" <= "A" TO_DATE("1-Jan-97") <= TO_DATE("1-Jan-97) TO_DATE("15-Nov-61") <= TO_DATE("15-Nov-60)
Use of Comparison Operators with Strings
When using character strings in comparison expressions, the result depends on several things:
- Character set
- Datatype
- Case (upper versus lower)
In the typical ASCII environment,
- all lowercase letters are actually greater than all uppercase letters,
- digits are less than all letters, and
- the other characters fall in various places depending on their corresponding ASCII codes.
23. 1. Operators 23. 1. 1. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Operators.htm">Operators</a> 23. 1. 2. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Thebasicarithmeticoperatorsinaction.htm">The basic arithmetic operators in action</a> 23. 1. 3. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/LogicalOperatorsinPLSQL.htm">Logical Operators in PL/SQL</a> 23. 1. 4. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/RunningAnonymousBlocksofCode.htm">Running Anonymous Blocks of Code</a> 23. 1. 5. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/ArithmeticOperators.htm">Arithmetic Operators</a> 23. 1. 6. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Exponentiation.htm">Exponentiation</a> 23. 1. 7. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Thenegationandidentityoperatorsinaction.htm">The negation and identity operators in action.</a> 23. 1. 8. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/ComparisonOperators.htm">Comparison Operators</a> 23. 1. 9. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/TheRelationalOperators.htm">The Relational Operators: =, <>, !=, ~=, <, >, <=, >=</a> 23. 1. 10. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/LogicalOperators.htm">Logical Operators</a> 23. 1. 11. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/StringOperators.htm">String Operators</a> 23. 1. 12. Use of Comparison Operators with Strings