Oracle Built-in Operators

发布时间：2020-12-12 15:06:51 所属栏目：百科来源：网络整理

导读：点击打开链接 Oracle Built-in Operators Version 11.1 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 *

Oracle Built-in OperatorsVersion 11.1Arithmetic OperatorsAddition<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 OperatorAssociation<parameter_name> => <value>exec dbms_stats.gather_schema_stats(USER,CASCADE=>TRUE);Concatenation OperatorConcatenateSELECT <string>|| <string>SELECT 'Daniel '||'Morgan' FROM dual;Date OperatorsAdditionSELECT <date_value> + <numeric_value>SELECT SYSDATE+10 FROM dual;SubtractionSELECT <date_value>-<date_value>SELECT SYSDATE-10 FROM dual;Hierarchical Query OperatorsCONNECT,CONNECT BY,CONNECT BY PRIOR,and CONNECT BY ROOT will be dealt with on a separate page on Hierarchical QueriesMultiset Operators-Combine the results of two nested tables into a single nested tableMultisetCAST(MULTISET(<select statement> AS object_type)See CAST pageMultiset 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 1PIVOT[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 2conn 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 BYconn 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)| ------------------------------------------------------------------------

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

点击打开链接