| 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;
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 |
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));
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 |
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;
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; |
| MINUS |
<expression> MINUS <expression> |
SELECT DISTINCT
table_name
FROM user_tables
MINUS
SELECT DISTINCT table_name
FROM user_indexes; |
| UNION ALL |
<expression> UNION ALL <expression> |
SELECT DISTINCT
table_name
FROM user_tables
UNION ALL
SELECT DISTINCT table_name
FROM user_indexes; |
| UNION |
<expression> UNION <expression> |
SELECT DISTINCT
table_name
FROM user_tables
UNION
SELECT DISTINCT table_name
FROM user_indexes;
|