CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Built-in Operators
Version 11.1
 
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) <numeric_value> ** <numeric_value>
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);

  x := 'B';
  dbms_output.put_line(x);
END;
/
 
Association Operator
Association <parameter_name> => <value>
exec dbms_stats.gather_schema_stats(USER, CASCADE => TRUE);
 
Concatenation Operator
Concatenate SELECT <string> || <string>
SELECT 'Daniel ' || 'Morgan' FROM dual;
 
Date Operators
Addition SELECT <date_value> + <numeric_value>
SELECT SYSDATE + 10 FROM dual;
Subtraction SELECT <date_value> - <date_value>
SELECT SYSDATE - 10 FROM dual;
 
Hierarchical Query Operators
CONNECT, CONNECT BY, CONNECT BY PRIOR, and CONNECT BY ROOT will be dealt with on a separate page on Hierarchical Queries
 
Multiset Operators - Combine the results of two nested tables into a single nested table
Multiset CAST(MULTISET(<select statement> AS object_type)
See CAST page
Multiset Except <nested_table1> MULTISET
EXCEPT <ALL | DISTINCT <nested_table2>
SELECT customer_id, cust_address_ntab1
MULTISET EXCEPT DISTINCT cust_address_ntab2 RESULTS
FROM customer_demo;
Multiset Intersect <nested_table1> MULTISET
INTERSECT <ALL | DISTINCT <nested_table2>
SELECT customer_id, cust_address_ntab1
MULTISET INTERSECT DISTINCT cust_address_ntab2 RESULTS
FROM customer_demo;
Multiset Union <nested_table1> MULTISET
UNION <ALL | DISTINCT <nested_table2>
SELECT customer_id, cust_address_ntab1
MULTISET UNION DISTINCT cust_address_ntab2 RESULTS
FROM customer_demo;
 
Pivot Operators (new 11g)
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

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 TABLE(dbms_xplan.display);

---------------------------------------------------------------------
| Id | Operation          | Name        | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|  0 | SELECT STATEMENT   |             |   12 |   408 |     7  (15)|
|  1 |  SORT ORDER BY     |             |   12 |   408 |     7  (15)|
|* 2 |   VIEW             |             |   12 |   408 |     6   (0)|
|  3 |   UNPIVOT          |             |      |       |            |
|  4 |   TABLE ACCESS FULL| PIVOT_TABLE |    6 |   234 |     3   (0)|
---------------------------------------------------------------------

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

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));

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------
| Id | Operation           | Name      | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|  0 | SELECT STATEMENT    |           |    5 |    45 |   302   (5)|
|  1 |  HASH GROUP BY PIVOT|           |    5 |    45 |   302   (5)|
|  2 |   TABLE ACCESS FULL | AIRPLANES |  250K|  2197K|   290   (2)|
--------------------------------------------------------------------

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

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;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes   | Cost (%CPU)|
------------------------------------------------------------------
|  0 | SELECT STATEMENT     |      |   28 |   728   |    7   (15)|
|  1 |  SORT GROUP BY       |      |   28 |   728   |    7   (15)|
|* 2 |   VIEW               |      |   28 |   728   |    6    (0)|
|  3 |    UNPIVOT           |      |      |         |            |
|  4 |     TABLE ACCESS FULL| EMP  |   14 |   280   |    3    (0)|
------------------------------------------------------------------

SELECT *
FROM emp
WHERE ename = 'ALLEN';
 
Set Operators

INTERSECT
<expression> INTERSECT <expression>
SELECT DISTINCT table_name
FROM user_tables
INTERSECT
SELECT DISTINCT table_name
FROM user_indexes;

EXPLAIN PLAN FOR
SELECT DISTINCT srvr_id
FROM servers
INTERSECT
SELECT DISTINCT srvr_id
FROM serv_inst;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------
| Id |       Operation        |     Name     | Rows | Bytes| Cost(%CPU)|
------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |  608 |    5  (80)|
|  1 |  INTERSECTION          |              |      |      |           |
|  2 |   SORT UNIQUE NOSORT   |              |  141 |  564 |    2  (50)|
|  3 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |  564 |    1   (0)|
|  4 |   SORT UNIQUE          |              |   11 |   44 |    3  (34)|
|  5 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 | 3996 |    2   (0)|
------------------------------------------------------------------------

MINUS
<expression> MINUS <expression>
SELECT DISTINCT table_name
FROM user_tables
MINUS
SELECT DISTINCT table_name
FROM user_indexes;

EXPLAIN PLAN FOR
SELECT DISTINCT srvr_id
FROM servers
MINUS
SELECT DISTINCT srvr_id
FROM serv_inst;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------
| Id |       Operation       |     Name     | Rows | Bytes | Cost(%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |              |  141 |   608 |    5  (80)|
| 1 |  MINUS                 |              |      |       |           |
| 2 |   SORT UNIQUE NOSORT   |              |  141 |   564 |    2  (50)|
| 3 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |    1   (0)|
| 4 |   SORT UNIQUE          |              |   11 |    44 |    3  (34)|
| 5 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |    2   (0)|
------------------------------------------------------------------------

UNION ALL
<expression> UNION ALL <expression>
SELECT DISTINCT table_name
FROM user_tables
UNION ALL
SELECT DISTINCT table_name
FROM user_indexes;

EXPLAIN PLAN FOR
SELECT DISTINCT srvr_id
FROM servers
UNION ALL
SELECT DISTINCT srvr_id
FROM serv_inst;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------
| Id |       Operation       |     Name     | Rows | Bytes | Cost(%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |              |  152 |   608 |    4  (75)|
| 1 |  UNION-ALL             |              |      |       |           |
| 2 |   INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |    1   (0)|
| 3 |   HASH UNIQUE          |              |   11 |    44 |    3  (34)|
| 4 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |    2   (0)|
------------------------------------------------------------------------

UNION
<expression> UNION <expression>
SELECT DISTINCT table_name
FROM user_tables
UNION
SELECT DISTINCT table_name
FROM user_indexes;

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
UNION
SELECT srvr_id
FROM serv_inst;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------
| Id |       Operation       |    Name      | Rows | Bytes | Cost(%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |              |  152 |   608 |    5  (80)|
| 1 |  SORT UNIQUE           |              |  152 |   608 |    5  (80)|
| 2 |   UNION-ALL            |              |      |       |           |
| 3 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |    1   (0)|
| 4 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |    2   (0)|
------------------------------------------------------------------------
 
Other Related Topics
Cast
Delete
Hierarchical Queries
Insert
Regular Expressions
Select
Update
User Defined Operators
Where Clause
Wildcards
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [221 users online]    © 2010 psoug.org