函数
SYS_CONTEXT
返回一些session属性。比如 IＰ地址，终端和当前用户。
SYS_CONTEXT包含３个参数 [, ********************************************************************************************* SYS_CONTEXT('USERENV','CURRENT_USERID') SYS_CONTEXT('USERENV','CURRENT_USER') ********************************************************************************************* 分组函数 ********************************************************************************************* GE MARITAL_STATUS COUNT(*) GE MARITAL_STATUS COUNT(*) GE MARITAL_STATUS COUNT(*) 26 rows selected. GE MARITAL_STATUS COUNT(*) GE MARITAL_STATUS COUNT(*) GE MARITAL_STATUS COUNT(*) 26 rows selected. SQL> SELECT CUST_GENDER gender ********************************************************************************************* GE MARITAL_STATUS COUNT(*) GE MARITAL_STATUS COUNT(*) GE MARITAL_STATUS COUNT(*) 38 rows selected. ********************************************************************************************* GE MARITAL_STATUS COUNT(*) GE MARITAL_STATUS COUNT(*) GE MARITAL_STATUS COUNT(*) GE MARITAL_STATUS COUNT(*) 38 rows selected. ********************************************************************************************* 但是这种转换也有所不足，随着字段的增多，层数也会增多，对应的代码也会增多。 子查询 ********************************************************************************************* DEPARTMENT_ID LAST_NAME SALARY 11 rows selected. ********************************************************************************************* 关联子查询EXISTS ********************************************************************************************* COUNTRY_NAME COUNTRY_NAME 14 rows selected. ********************************************************************************************* 出现在SELECT中的标量子查询 LAST_NAME DEPARTMENT_ID HSAL ********************************************************************************************* 出现在SELECT WHERE子句中的标量子查询 ********************************************************************************************* SQL> SELECT department_name, DEPARTMENT_NAME MANAGER_ID MGR_NAME 8 rows selected. ********************************************************************************************* ********************************************************************************************* DEPARTMENT_NAME MANAGER_ID MGR_NAME 8 rows selected. ********************************************************************************************* 出现在Order中的标量子查询 SQL> SELECT country_id, city, state_province CO CITY STATE_PROVINCE CO CITY STATE_PROVINCE CO CITY STATE_PROVINCE 23 rows selected. 可以转换为 ********************************************************************************************* CO CITY STATE_PROVINCE CO CITY STATE_PROVINCE CO CITY STATE_PROVINCE 23 rows selected. ********************************************************************************************* DML 环境准备 Table created. SQL> create table object_to_table as select object_name,OBJECT_TYPE from dba_objects where 1=2 ; Table created. SQL> create table object_to_index as select object_name,OBJECT_TYPE from dba_objects where 1=2 ; Table created. SQL> create table object_all as select * from dba_objects where 1=2 ; Table created. ********************************************************************************************* SQL> INDEX_COUNT TABLE_COUNT ALL_COUNT INSERT FIRST 插入数据 3398 rows created. SQL> ********************************************************************************************* INDEX_COUNT TABLE_COUNT ALL_COUNT SQL> *********************************************************************************************
SYS_CONTEXT(
N 命名空间
P 命名空间的属性参数
SQL> conn / as sysdba
Connected.
SQL> select sys_context('USERENV','CURRENT_USERID') from dual;
--------------------------------------------------------------------------------
0
SQL> select sys_context('USERENV','CURRENT_USER') from dual ;
--------------------------------------------------------------------------------
SYS
AUDITED_CURSORID 返回当前用户ID以用来触发审计
AUTHENTICATION_DATA 返回用来验证登陆用户的数据
CURRENT_SCHEMA 返回当前模式的ＩＤ
CURRENT_SCHEMAID 当前模式的数字类型的ＩＤ
CURRENT_USER 当前用户
CURRENT_USERID 当前用户ID
ROLLUP
根据后面括号中列的顺序从后向前逐层汇总。在报表程序中经常使用。
SQL> SELECT CUST_GENDER gender,
NVL(cust_marital_status, 'unknown') marital_status,
COUNT(*)
FROM sh.customers
GROUP BY ROLLUP(cust_gender, NVL(cust_marital_status, 'unknown')) 2 3 4 5 ;
-- --------------- ----------
F widow 38
F Mar-AF 3
F NeverM 682
F Separ. 73
F single 6059
F Divorc. 376
F Mabsent 27
F Married 224
F Widowed 125
F married 4814
F unknown 5828
-- --------------- ----------
F divorced 76
F 18325
M widow 37
M NeverM 821
M Separ. 61
M single 13035
M Divorc. 239
M Mabsent 48
M Married 1810
M Widowed 11
M married 9439
-- --------------- ----------
M unknown 11600
M divorced 74
M 37175
55500
*********************************************************************************************
可以使用group by union all 来进行相等转换
*********************************************************************************************
SQL> select CUST_GENDER gender,
2 NVL(cust_marital_status, 'unknown') marital_status,
COUNT(*)
FROM sh.customers
3 4 5 group by cust_gender, NVL(cust_marital_status, 'unknown')
union all
select CUST_GENDER gender, NULL, count(*)
FROM sh.customers
6 7 8 9 group by CUST_GENDER
union all
10 11 select null, null, COUNT(*) FROM sh.customers ;
-- --------------- ----------
M Married 1810
M NeverM 821
M divorced 74
F widow 38
M Divorc. 239
F Mar-AF 3
F divorced 76
F Married 224
M widow 37
F unknown 5828
F Divorc. 376
-- --------------- ----------
F Mabsent 27
F NeverM 682
M Widowed 11
M married 9439
M unknown 11600
M Mabsent 48
F Separ. 73
M single 13035
M Separ. 61
F Widowed 125
F married 4814
-- --------------- ----------
F single 6059
F 18325
M 37175
55500
*********************************************************************************************
CUBE
对后边括号的列分别汇总
,NVL(cust_marital_status,'unknown') marital_status
,COUNT(*)
FROM sh.customers
GROUP BY CUBE
(cust_gender,NVL(cust_marital_status,'unknown')) 2 3 4 5 6 ;
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
55500
widow 75
Mar-AF 3
NeverM 1503
Separ. 134
single 19094
Divorc. 615
Mabsent 75
Married 2034
Widowed 136
married 14253
-- --------------- ----------
unknown 17428
divorced 150
F 18325
F widow 38
F Mar-AF 3
F NeverM 682
F Separ. 73
F single 6059
F Divorc. 376
F Mabsent 27
F Married 224
-- --------------- ----------
F Widowed 125
F married 4814
F unknown 5828
F divorced 76
M 37175
M widow 37
M NeverM 821
M Separ. 61
M single 13035
M Divorc. 239
M Mabsent 48
-- --------------- ----------
M Married 1810
M Widowed 11
M married 9439
M unknown 11600
M divorced 74
相应也可以转换为
*********************************************************************************************
SQL> SELECT CUST_GENDER gender,
2 NVL(cust_marital_status, 'unknown') marital_status,
COUNT(*)
FROM sh.customers
GROUP BY CUST_GENDER, NVL(cust_marital_status, 'unknown')
3 4 5 6 UNION ALL
SELECT NULL gender,
7 8 NVL(cust_marital_status, 'unknown') marital_status,
COUNT(*)
9 10 FROM sh.customers
GROUP BY NVL(cust_marital_status, 'unknown')
11 12 UNION ALL
SELECT CUST_GENDER gender,
13 14 NULL,
15 COUNT(*)
FROM sh.customers
16 17 GROUP BY CUST_GENDER
UNION ALL
SELECT NULL,
18 19 20 NULL,
COUNT(*)
FROM sh.customers 21 22 ;
-- --------------- ----------
M Married 1810
M NeverM 821
M divorced 74
F widow 38
M Divorc. 239
F Mar-AF 3
F divorced 76
F Married 224
M widow 37
F unknown 5828
F Divorc. 376
-- --------------- ----------
F Mabsent 27
F NeverM 682
M Widowed 11
M married 9439
M unknown 11600
M Mabsent 48
F Separ. 73
M single 13035
M Separ. 61
F Widowed 125
F married 4814
-- --------------- ----------
F single 6059
Mabsent 75
NeverM 1503
Separ. 134
Widowed 136
single 19094
Divorc. 615
married 14253
widow 75
Married 2034
divorced 150
-- --------------- ----------
unknown 17428
Mar-AF 3
F 18325
M 37175
55500
子查询非主流用法
关联子查询 IN
SQL> set line 200 ;
SQL> SELECT department_id, last_name, salary
FROM employees e1
WHERE salary = (SELECT MAX(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id)
ORDER BY 1, 2, 3 2 3 4 5 6 ;
------------- ------------------------- ----------
10 Whalen 4400
20 Hartstein 13000
30 Raphaely 11000
40 Mavris 6500
50 Fripp 8200
60 Hunold 9000
70 Baer 10000
80 Russell 14000
90 King 24000
100 Greenberg 12000
110 Higgins 12000
SQL> select a.country_name
from countries a
where exists
(select 'X' from locations b where a.country_id = b.country_id) 2 3 4 ;
----------------------------------------
Australia
Brazil
Canada
Switzerland
China
Germany
India
Italy
Japan
Mexico
Netherlands
----------------------------------------
Singapore
United Kingdom
United States of America
标量子查询
*********************************************************************************************
SQL> SELECT last_name, department_id,
(SELECT MAX(salary)
FROM employees sq
WHERE sq.department_id = e.department_id) HSAL
FROM employees e
WHERE last_name like 'R%' 2 3 4 5 6 ;
------------------------- ------------- ----------
Rajs 50 8200
Raphaely 30 11000
Rogers 50 8200
Russell 80 14000
此种写法是非常低效的。原因就是没有一行记录就要重新执行一下关于HSAL的查询
2 manager_id,
(SELECT last_name FROM employees e WHERE e.employee_id = d.manager_id) MGR_NAME
3 4 FROM departments d
5 WHERE ((SELECT country_id
6 FROM locations l
WHERE d.location_id = l.location_id) IN
7 8 (SELECT country_id
9 FROM countries c
WHERE c.country_name = 'United States of America'
10 11 OR c.country_name = 'Canada'))
AND d.manager_id IS NOT NULL 12 ;
------------------------------ ---------- -------------------------
Administration 200 Whalen
Marketing 201 Hartstein
Purchasing 114 Raphaely
Shipping 121 Fripp
IT 103 Hunold
Executive 100 King
Finance 108 Greenberg
Accounting 205 Higgins
这个查询又可以转换为
SQL> SELECT department_name,
2 manager_id,
(SELECT last_name FROM employees e WHERE e.employee_id = d.manager_id) MGR_NAME
from departments d, locations l
where d.location_id = l.location_id
and l.country_id in
(SELECT country_id
FROM countries c
WHERE c.country_name in ('United States of America', 'Canada'))
3 4 5 6 7 8 9 10 AND d.manager_id IS NOT NULL ;
------------------------------ ---------- -------------------------
Marketing 201 Hartstein
IT 103 Hunold
Shipping 121 Fripp
Administration 200 Whalen
Purchasing 114 Raphaely
Executive 100 King
Finance 108 Greenberg
Accounting 205 Higgins
所以第一种用法是不必要的。而且通过执行计划来看 第一种方法也较为低效。
*********************************************************************************************
FROM locations l
ORDER BY (SELECT country_name
FROM countries c
WHERE l.country_id = c.country_id) 2 3 4 5 ;
-- ------------------------------ -------------------------
AU Sydney New South Wales
BR Sao Paulo Sao Paulo
CA Toronto Ontario
CA Whitehorse Yukon
CN Beijing
DE Munich Bavaria
IN Bombay Maharashtra
IT Roma
IT Venice
JP Tokyo Tokyo Prefecture
JP Hiroshima
-- ------------------------------ -------------------------
MX Mexico City Distrito Federal,
NL Utrecht Utrecht
SG Singapore
CH Bern BE
CH Geneva Geneve
UK Stretford Manchester
UK London
UK Oxford Oxford
US Southlake Texas
US South San Francisco California
US South Brunswick New Jersey
-- ------------------------------ -------------------------
US Seattle Washington
*********************************************************************************************
SQL> select l.country_id, city, state_province
from locations l ,countries c
WHERE l.country_id = c.country_id
order by c.country_name 2 3 4 ;
-- ------------------------------ -------------------------
AU Sydney New South Wales
BR Sao Paulo Sao Paulo
CA Toronto Ontario
CA Whitehorse Yukon
CN Beijing
DE Munich Bavaria
IN Bombay Maharashtra
IT Roma
IT Venice
JP Tokyo Tokyo Prefecture
JP Hiroshima
-- ------------------------------ -------------------------
MX Mexico City Distrito Federal,
NL Utrecht Utrecht
SG Singapore
CH Geneva Geneve
CH Bern BE
UK London
UK Stretford Manchester
UK Oxford Oxford
US Southlake Texas
US Seattle Washington
US South San Francisco California
-- ------------------------------ -------------------------
US South Brunswick New Jersey
INSERT ALL
将select 子句中每一条都进行自上而下的判断。
判断过程如下
是否满足第一条 满足则插入 判断下一个条件
是否满足第二条 满足则插入 判断下一个条件
。。。。
*********************************************************************************************
SQL> create table objects as select * from dba_objects ;
插入数据
*********************************************************************************************
insert all
when OBJECT_TYPE = 'INDEX' then
into hr.object_to_index (OBJECT_NAME, OBJECT_TYPE) VALUES (OBJECT_NAME, OBJECT_TYPE)
when OBJECT_TYPE = 'TABLE' then
into hr.object_to_TABLE (OBJECT_NAME, OBJECT_TYPE) VALUES (OBJECT_NAME, OBJECT_TYPE)
when OBJECT_TYPE in ('INDEX', 'TABLE')
THEN INTO HR.Object_All
select *
from hr.objects
6796 rows created.
*********************************************************************************************
验证
*********************************************************************************************
SQL> select
(select count(*) from hr.object_to_index) index_count,
(select count(*) from hr.object_to_table) table_count,
(select count(*) from hr.object_all) all_count
from dual 2 3 4 5 ;
----------- ----------- ----------
1779 1619 3398
*********************************************************************************************
将select 子句中每一条都进行自上而下的判断。
判断过程如下
是否满足第一条 满足则插入 不再判断下一个条件
。。。。
*********************************************************************************************
insert first
when OBJECT_TYPE = 'INDEX' then
into hr.object_to_index (OBJECT_NAME, OBJECT_TYPE) VALUES (OBJECT_NAME, OBJECT_TYPE)
when OBJECT_TYPE = 'TABLE' then
into hr.object_to_TABLE (OBJECT_NAME, OBJECT_TYPE) VALUES (OBJECT_NAME, OBJECT_TYPE)
when OBJECT_TYPE in ('INDEX', 'TABLE')
THEN INTO HR.Object_All
select *
from hr.objects
验证
*********************************************************************************************
SQL> select
(select count(*) from hr.object_to_index) index_count,
(select count(*) from hr.object_to_table) table_count,
(select count(*) from hr.object_all) all_count
from dual 2 3 4 5 ;
----------- ----------- ----------
1779 1619 0
多表Insert 只须读入一次就可满足对多个不同表的插入。减少了数据的读入，加快了处理的效率。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12474069/viewspace-615880/ ，如需转载，请注明出处，否则将追究法律责任。
