oracle用法集锦分析和总结.docx

一﹑ORACLE 查询树型关系（connect by prior star）t with connect by prior start经w常it会h被用到一个表中存在递归关系的时候。比如我们经常会将一个比较复杂的目录树存储到一个表中。或者将一些部门存储到一个表中，而这些部门互相有隶属关系。这个时候你就会用到 connect by prior star。t 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-00and bom_ver =1 AND ITEM_NO = 60 and item_no_pa insull CONNECT BY //以下條件為父主鍵=子主鍵 PRIOR artic_code =artic_code and PRIOR bom_verbo=m_ver and PRIOR item_no = item_no_pa; SELECT PART_NO,ITEM_NO,ITEM_NO_PA FROM DV_BOMS WHERE Artic_code = 315066-001-00and bom_ver =1; AND PART_NO=00051; 從那項開始找到底部關系圖select * from dv_boms START WITH artic_code = 315066-001-00and bom_ver =1 and item_no_pa =60 CONNECT BY PRIOR artic_code =artic_code and PRIOR bom_verbo=m_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,P-AR2T)_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_idnumber, 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_na