ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 你可能不知道的PL/SQL用法

你可能不知道的PL/SQL用法

原创 Linux操作系统 作者:SUNBLAZE 时间:2009-09-29 23:53:49 0 删除 编辑

函数
SYS_CONTEXT
返回一些session属性。比如 IP地址,终端和当前用户。

SYS_CONTEXT包含3个参数
SYS_CONTEXT( ,

[, ])
N 命名空间
P 命名空间的属性参数
返回值的长度。默认为256

*********************************************************************************************
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    返回当前模式的ID
CURRENT_SCHEMAID      当前模式的数字类型的ID
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-08-22

  • 博文量
    35
  • 访问量
    79061