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