ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Operators manipulate individual data items called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication
operator is represented by an asterisk (*). Operators, in Oracle are implemented by means of creating in most cases overloaded objects, similar to PL/SQL packages that contain functions only.
The following SQL statement:
SELECT operator_name, number_of_binds
FROM dba_operators
ORDER BY 2;
will show you, for example, that the CONTAINS operator has 24 separate binds (overloads).
Dependencies
ALL_OPERATORS
DBA_OPERATORS
OPERATOR$
CDB_OPERATORS
OBJ$
STANDARD
Arithmetic Operators
Addition
<numeric_value> + <numeric_value>
SELECT 100 + 10 FROM dual;
Subtraction
<numeric_value> - <numeric_value>
SELECT 100 - 10 FROM dual;
Multiplication
<numeric_value> * <numeric_value>
SELECT 100 * 10 FROM dual;
Division
<numeric_value> / <numeric_value>
SELECT 100 / 10 FROM dual;
Power (PL/SQL Only)
'**' (left IN NUMBER, right IN NUMBER) RETURN NUMBER;
'**' (left IN BINARY_DOUBLE, right IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
set serveroutput on
BEGIN
dbms_output.put_line('2 to the 5th is ' || TO_CHAR(2**5));
END;
/
Assignment Operator
Assign
<variable> := <value>
set serveroutput on
DECLARE
x VARCHAR2(1) := 'A';
BEGIN
dbms_output.put_line(x);
The Oracle Database supports the following case-insensitive collations:
BINARY_CI, BINARY_AI, GENERIC_M_CI, GENERIC_M_AI, UCA0700_DUCET_CI, UCA0700_DUCET_AI
SELECT ename
FROM employee
ORDER BY 1 COLLATE GENERIC_M;
SELECT ename
FROM employee
ORDER BY 1 COLLATE BINARY_CI;
Note: Traditional pivoting is done with DECODE and CASE so you may want to look at those demos on the DECODE page
Pivot / Unpivot Demo 1
PIVOT [XML] (<aggregate function> (expression) [AS <alias>]
FOR (<column_list>)
IN <subquery>)
UNPIVOT [<INCLUDE | EXCLUDE> NULLS] (<column_list>) FOR (<column_list>)
IN (<column_list>) [AS (<constant_list>)])
conn oe/oe@pdbdev
CREATE TABLE pivot_table AS
SELECT * FROM (
SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total
FROM orders)
PIVOT (SUM(order_total)
FOR order_mode
IN ('direct' AS Store, 'online' AS Internet));
desc pivot_table
SELECT * FROM pivot_table;
SELECT * FROM pivot_table
UNPIVOT (yearly_total FOR order_mode
IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;
EXPLAIN PLAN FOR
SELECT * FROM pivot_table
UNPIVOT (yearly_total FOR order_mode
IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;
SELECT * FROM pivot_table
UNPIVOT INCLUDE NULLS (yearly_total FOR order_mode
IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;
Pivot / Unpivot Demo 2
conn uwclass/uwclass@pdbdev
SELECT * FROM (
SELECT program_id, customer_id, 1 CNT
FROM airplanes)
PIVOT (SUM(cnt)
FOR customer_id
IN ('AAL' AS AAL, 'DAL' AS DAL, 'ILC' AS ILC, 'NWO' AS NWO, 'SAL' AS SAL, 'SWA' AS SWA, 'USAF' AS USAF));
EXPLAIN PLAN FOR
SELECT * FROM (
SELECT program_id, customer_id, 1 CNT
FROM airplanes)
PIVOT (SUM(cnt)
FOR customer_id
IN ('AAL' AS AAL, 'DAL' AS DAL, 'ILC' AS ILC, 'NWO' AS NWO, 'SAL' AS SAL, 'SWA' AS SWA, 'USAF' AS USAF));
CREATE TABLE pivot_table AS
SELECT * FROM (
SELECT program_id, customer_id, 1 CNT
FROM airplanes)
PIVOT (SUM(cnt)
FOR customer_id
IN ('AAL' AS AAL, 'DAL' AS DAL, 'ILC' AS ILC, 'NWO' AS NWO, 'SAL' AS SAL, 'SWA' AS SWA, 'USAF' AS USAF));
desc pivot_table
SELECT * FROM pivot_table;
SELECT * FROM pivot_table
UNPIVOT (sumx FOR AAL IN (AAL AS 'AAL', DAL AS 'DAL', ILC AS 'ILC', NWO AS 'NWO', SAL AS 'SAL', SWA AS 'SWA', USAF AS 'USAF'))
ORDER BY 2,1;
Unpivot with GROUP BY
conn scott/tiger@pdbdev
SELECT *
FROM (
SELECT ename, job, sal, comm
FROM emp)
UNPIVOT (income_component_value FOR income_component_type
IN (sal, comm))
ORDER BY 1;
SELECT *
FROM emp
WHERE ename = 'ALLEN';
SELECT ename, job, SUM(income_component_value) income
FROM (
SELECT ename, job, sal, comm
FROM emp)
UNPIVOT (income_component_value FOR income_component_type
IN (sal, comm))
GROUP BY ename, job
ORDER BY 1;
EXPLAIN PLAN FOR
SELECT ename, job, SUM(income_component_value) income
FROM (
SELECT ename, job, sal, comm
FROM emp)
UNPIVOT (income_component_value FOR income_component_type
IN (sal, comm))
GROUP BY ename, job
ORDER BY 1;
