Oracle PL/SQL Tutorial - PL/SQL Operators

Concatenation Operator

The concatenation operator || appends one string operand to another.

Each string can be CHAR, VARCHAR2, CLOB.

If either string is a CLOB, the result is a temporary CLOB; otherwise, it is a VARCHAR2 value.

The following code Concatenation Operator.


DECLARE 
  x VARCHAR2(4) := 'Hi'; 
  y VARCHAR2(10) := ' Hey'; 
BEGIN 
  DBMS_OUTPUT.PUT_LINE (x || y); 
END; 
/

The code above generates the following result.

Logical Operators

The following table is the Logical Truth Table for PL/SQL Logical Operators.

Be careful with NULL value.

x	y	x AND y	x OR y	NOT x
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	NULL	NULL	TRUE	FALSE
FALSE	TRUE	FALSE	TRUE	TRUE
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	NULL	FALSE	NULL	TRUE
NULL	TRUE	NULL	TRUE	NULL
NULL	FALSE	FALSE	NULL	NULL
NULL	NULL	NULL	NULL	NULL

Relational Operators

Operator	Meaning
=	equal to
<>, !=, ~=, ^=	not equal to
<	less than
>	greater than
<=	less than or equal to
>=	greater than or equal to

BETWEEN Operator

The BETWEEN operator tests whether a value lies in a specified range.

x BETWEEN a AND b means that x >= a and x <= b.

IN Operator

The IN operator tests set membership.

x IN set means that x is equal to any member of set.

Operator Precedence

The operations within an expression are evaluated in order of precedence.

Operators with equal precedence are applied in no particular order.

We can use parentheses to control the order of evaluation.

The following table shows operator precedence from highest to lowest.

Operator	Operation
**	exponentiation
+, -	identity, negation
*, /	multiplication, division
+, -, \|\|	addition, subtraction, concatenation
=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN	comparison
NOT	logical negation
AND	conjunction
OR	inclusion


DECLARE --   w  w  w .  j a  v  a2 s.  c  o  m
  salary      NUMBER := 60000; 
  commission  NUMBER := 0.10; 
BEGIN 
 
  DBMS_OUTPUT.PUT_LINE(5 + 12 / 4); 
  DBMS_OUTPUT.PUT_LINE(12 / 4 + 5); 
 
  DBMS_OUTPUT.PUT_LINE((8 + 6) / 2); 
 
END; 
/

The code above generates the following result.