Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL OPERATORS      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: OPERATOR

Definition:
Oracle provides operators to be used with data in order to perform some related action and return a result. An operator can act on a single operand (Unary Operators) or on two operands (Binary Operators). Oracle provides eight kinds of operators:

  1. Arithmetic operators
  2. Concatenation operators
  3. Comparison operators
  4. Logical operators
  5. Hierarchical operators
  6. Set operators
  7. Multiset operators
  8. User-defined operators
All operators function under the hierarchy precedence set by Oracle.

Arithmetic Operators operate on one or more numeric operands and return numeric output.
The available arithmetic operators are: +, -, *, and /.

Arithmetic Operator Example: c := a + b;


Concatenation Operators (||) operate on two string operands to join them into a single string.

Concatenation Operator Example: a || b


Comparison Operators are used to build a comparable condition between two terms, thereby returning a logical result as TRUE, FALSE, or NULL.
The available Comparison operators are: =, <, >, <=, >=, (NOT)IN, (NOT) LIKE, (NOT) BETWEEN, (NOT) EXISTS, and IS (NOT) NULL.

Comparison Operator Example: if (a < b) then
.....


Logical Operators operate on two operands and return a BOOLEAN output.
The available logical operators are: AND, OR, and NOT.

Logical Operator Example: if (a < b) AND (a!=0) then
...


Hierarchical Operators are used only in hierarchical queries.
The available hierarchical operators are: PRIOR and CONNECT_BY_ROOT.

Hierarchical Operator Example:

SELECT ...
START WITH
CONNECT BY PRIOR ....



Set Operators are used to compound multiple queries and return the combined result set.
The available SET operators are: UNION, UNION ALL, MINUS, and INTERSECT.

Set Operator Example:

SELECT query_1
UNION
SELECT query_2



Multiset Operators combine the results of two nested tables into one.
The available Multiset Operators are: MULTISET, MULTISET EXCEPT, MULTISET INTERSECT, and MULTISET UNION.


User Defined Operators are created by the user. They take a set of operands and return a result.
They can be used using CREATE OPERATOR command.

User Defined Operator Syntax:

CREATE OR REPLACE OPERATOR [operator_name]
BINDING (data_type_in) RETURN [data_type_out] USING [function_name];



Related Links:

Related Code Snippets:
  • Create Operator - Similar to built-in operators, a user-defined operator may support arguments of ...
  • Comment an operator - Create a comment for a user-defined operator.
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 191 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?