| 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);
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 |
<string> || <string> |
| SELECT 'Daniel ' || 'Morgan' FROM dual; |
| |
| Date Operators |
| Addition |
<date_value> + <numeric_value> |
| SELECT SYSDATE + 10 FROM dual; |
| Subtraction |
<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 |
| Note: 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 |
| 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)|
------------------------------------------------------------------------ |
