函数
SYS_CONTEXT
返回一些session属性。比如 IＰ地址，终端和当前用户。
SYS_CONTEXT包含３个参数
SYS_CONTEXT( ,
[, ])
N 命名空间
P 命名空间的属性参数
返回值的长度。默认为２５６
*********************************************************************************************
SQL> conn / as sysdba
Connected.
SQL> select sys_context('USERENV','CURRENT_USERID') from dual;
SYS_CONTEXT('USERENV','CURRENT_USERID')
--------------------------------------------------------------------------------
0
SQL> select sys_context('USERENV','CURRENT_USER') from dual ;
SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
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 ;
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
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
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
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
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
M unknown 11600
M divorced 74
M 37175
55500
26 rows selected.
*********************************************************************************************
可以使用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 ;
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
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
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
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
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
F single 6059
F 18325
M 37175
55500
26 rows selected.
*********************************************************************************************
CUBE
对后边括号的列分别汇总
SQL> SELECT CUST_GENDER gender
,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
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
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
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
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
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
M Married 1810
M Widowed 11
M married 9439
M unknown 11600
M divorced 74
38 rows selected.
*********************************************************************************************
相应也可以转换为
*********************************************************************************************
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 ;
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
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
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
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
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
F single 6059
Mabsent 75
NeverM 1503
Separ. 134
Widowed 136
single 19094
Divorc. 615
married 14253
widow 75
Married 2034
divorced 150
GE MARITAL_STATUS COUNT(*)
-- --------------- ----------
unknown 17428
Mar-AF 3
F 18325
M 37175
55500
38 rows selected.
*********************************************************************************************
但是这种转换也有所不足，随着字段的增多，层数也会增多，对应的代码也会增多。
子查询
子查询非主流用法
关联子查询 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 ;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
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
11 rows selected.
*********************************************************************************************
关联子查询EXISTS
*********************************************************************************************
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 ;
COUNTRY_NAME
----------------------------------------
Australia
Brazil
Canada
Switzerland
China
Germany
India
Italy
Japan
Mexico
Netherlands
COUNTRY_NAME
----------------------------------------
Singapore
United Kingdom
United States of America
14 rows selected.
*********************************************************************************************
标量子查询
*********************************************************************************************
出现在SELECT中的标量子查询
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 ;
LAST_NAME DEPARTMENT_ID HSAL
------------------------- ------------- ----------
Rajs 50 8200
Raphaely 30 11000
Rogers 50 8200
Russell 80 14000
*********************************************************************************************
此种写法是非常低效的。原因就是没有一行记录就要重新执行一下关于HSAL的查询
出现在SELECT WHERE子句中的标量子查询
*********************************************************************************************
SQL> SELECT department_name,
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 ;
DEPARTMENT_NAME MANAGER_ID MGR_NAME
------------------------------ ---------- -------------------------
Administration 200 Whalen
Marketing 201 Hartstein
Purchasing 114 Raphaely
Shipping 121 Fripp
IT 103 Hunold
Executive 100 King
Finance 108 Greenberg
Accounting 205 Higgins
8 rows selected.
*********************************************************************************************
这个查询又可以转换为
*********************************************************************************************
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 ;
DEPARTMENT_NAME MANAGER_ID MGR_NAME
------------------------------ ---------- -------------------------
Marketing 201 Hartstein
IT 103 Hunold
Shipping 121 Fripp
Administration 200 Whalen
Purchasing 114 Raphaely
Executive 100 King
Finance 108 Greenberg
Accounting 205 Higgins
8 rows selected.
*********************************************************************************************
所以第一种用法是不必要的。而且通过执行计划来看 第一种方法也较为低效。
出现在Order中的标量子查询
*********************************************************************************************
SQL> SELECT country_id, city, state_province
FROM locations l
ORDER BY (SELECT country_name
FROM countries c
WHERE l.country_id = c.country_id) 2 3 4 5 ;
CO CITY STATE_PROVINCE
-- ------------------------------ -------------------------
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
CO CITY STATE_PROVINCE
-- ------------------------------ -------------------------
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
CO CITY STATE_PROVINCE
-- ------------------------------ -------------------------
US Seattle Washington
23 rows selected.
*********************************************************************************************
可以转换为
*********************************************************************************************
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 ;
CO CITY STATE_PROVINCE
-- ------------------------------ -------------------------
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
CO CITY STATE_PROVINCE
-- ------------------------------ -------------------------
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
CO CITY STATE_PROVINCE
-- ------------------------------ -------------------------
US South Brunswick New Jersey
23 rows selected.
*********************************************************************************************
DML
INSERT ALL
将select 子句中每一条都进行自上而下的判断。
判断过程如下
是否满足第一条 满足则插入 判断下一个条件
是否满足第二条 满足则插入 判断下一个条件
。。。。
环境准备
*********************************************************************************************
SQL> create table objects as select * from dba_objects ;
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.
*********************************************************************************************
插入数据
*********************************************************************************************
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>
*********************************************************************************************
验证
*********************************************************************************************
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 ;
INDEX_COUNT TABLE_COUNT ALL_COUNT
----------- ----------- ----------
1779 1619 3398
*********************************************************************************************
INSERT FIRST
将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
3398 rows created.
SQL>
*********************************************************************************************
验证
*********************************************************************************************
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 ;
INDEX_COUNT TABLE_COUNT ALL_COUNT
----------- ----------- ----------
1779 1619 0
SQL>
*********************************************************************************************
多表Insert 只须读入一次就可满足对多个不同表的插入。减少了数据的读入，加快了处理的效率。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12474069/viewspace-615880/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12474069/viewspace-615880/
1480
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
