| 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 EXCEPTDISTINCT cust_address_ntab2 RESULTS
FROM customer_demo; |
| Multiset Intersect |
<nested_table1> MULTISET
INTERSECT <ALL | DISTINCT <nested_table2> |
SELECT customer_id,cust_address_ntab1
MULTISET INTERSECTDISTINCT cust_address_ntab2 RESULTS
FROM customer_demo; |
| Multiset Union |
<nested_table1> MULTISET
UNION <ALL | DISTINCT <nested_table2> |
SELECT customer_id,cust_address_ntab1
MULTISET UNIONDISTINCT 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)
FORorder_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',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',order_mode; |
Pivot / Unpivot Demo 2 |
conn uwclass/uwclass
SELECT * FROM (
SELECT program_id,customer_id,1CNT
FROM airplanes)
PIVOT(SUM(cnt)
FORcustomer_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,'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,'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,SUM(income_component_value) income
FROM (
SELECT ename,comm))
GROUP BYename,job
ORDER BY 1;
EXPLAIN PLAN FOR
SELECT 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)| ------------------------------------------------------------------------ |
