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