oracle用法集锦

一﹑ORACLE查询树型关系（connect by prior start with）

connect by prior start with 经常会被用到一个表中存在递归关系的时候。比如我们经常会将一个比较复杂的目录树存储到一个表中。或者将一些部门存储到一个表中，而这些部门互相有隶属关系。这个时候你就会用到connect by prior start with。

典型的使用方法就是：

select * from table connect by prior cur_id=parent_id start with cur_id=???

例如：

a b

1 0

2 1

3 1

4 2

5 3

如果想查找a=2及其下面的所有数据，则：

select * from table connect by prior a=b start with a=2

a b

2 1

4 2

例如1﹕

看成兩個集合START WITH 為條件

select *

from dv_boms

WHERE artic_code = '315066-001-00' AND PART_NO='00051'

START WITH artic_code = '315066-001-00' and bom_ver =1

AND ITEM_NO = 60 and item_no_pa is null

CONNECT BY

//以下條件為父主鍵=子主鍵

PRIOR artic_code = artic_code and PRIOR bom_ver = bom_ver and

PRIOR item_no = item_no_pa;

SELECT PART_NO,ITEM_NO,ITEM_NO_PA

FROM DV_BOMS

WHERE Artic_code = '315066-001-00' and bom_ver =1;

AND PART_NO='00051';

從那項開始找到底部關系圖

select *

from dv_boms

START WITH artic_code = '315066-001-00' and bom_ver =1 and item_no_pa ='60'

CONNECT BY

PRIOR artic_code = artic_code and PRIOR bom_ver = bom_ver AND

PRIOR ITEM_NO=ITEM_NO_PA;

例如2﹕

select distinct artic_code,(case when level = 1 then part_no

when level = 2 then ' '||part_no

when level = 3 then ' '||part_no

end ) part_no

此句也可select artic_code, lpad(' ',4*(level-1))||part_no part_no

,item_no,item_no_pa from dv_boms //where artic_code = '313324-002-00'

connect by prior artic_code = artic_code

and prior bom_ver = bom_ver

and prior item_no = item_no_pa

start with artic_code = '315066-001-00'

and bom_ver =1

and PART_NO='00051'

and item_no = 60 ---从某一节点开始

ORDER BY part_no desc

例3﹕

SELECT lpad(' ',3 * level - 2) ,PART_ID ,level

FROM BOM_DETAIL_NEW

WHERE PRODUCT_ID = :ls_proc_id

START WITH PARENT_PART_ID IS NULL

CONNECT BY PRIOR PART_ID = PARENT_PART_ID;

例3﹕

Oracle中使用connect by 来实现树状查询，其中可以使用level这个伪列来实现分层查询。

具体使用如下：

一张表menu记录菜单的层级情况。表结构如下：

menu_id number,

parent_id number,

menu_name nvarchar2(20)

首先select * from menu connect by menu_id = parent_id start with menu_id = 1;

这样可以看到Oracle以树状结构产生结果。某些时候如果我只想要第2层的菜单项如何做呢。select * from

(select level,menu_id,parent_id,menu_name

f rom menu

conncet by menu_id = parent_id

start with menu_id = 1

)

where level = 2

这样就可以得到层级为第二层的菜单项。这样的SQL写法同样适合rownum伪列，Oracle中实现TOP n查询就是如此写的。

二﹑1.查询语句的使用

使用select语句和子查询(subquery)可以从一个或多个表,视图,实体试图中返回数据.

1.1相关子查询

可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询称为子查询

.where中可以包含一个select语句的子查询

.where中可以包含in,exists语句

.最多可以嵌套16层

.层次过多会影响性能

[例]简单子查询实例

查询是否有的专家既以研究所的名义来申请基金项目,又以大学系为单位申请项目

(按规定只能以一个单位来申请)

SQL> create table univ_subject

2 (

3 name varchar2(12) not null,

4 per_id number not null,

5 dept_name varchar2(20)

6 );

SQL> insert into univ_subject values('gaoqianjing',1001,'信息工程系');

SQL> insert into univ_subject values('wangbing',1002,'物理系');