Oracle Level的用法

Oracle树查询level的用法首先创建一张表menu记录菜单的层级情况。表结构如下：menu_idnumber,parent_idnumber,menu_namenvarchar2(20)插入数据：insertintoMENU(MENU_ID,PARENT_ID,MENU_NAME)values(1,null,'AAAA');insertintoMENU(MENU_ID,PARENT_ID,MENU_NAME)values(2,1,'BBBB');insertintoMENU(MENU_ID,PARENT_ID,MENU_NAME)values(3,1,'CCCC');insertintoMENU(MENU_ID,PARENT_ID,MENU_NAME)values(4,1,'DDDD');insertintoMENU(MENU_ID,PARENT_ID,MENU_NAME)values(5,2,'EEEE');insertintoMENU(MENU_ID,PARENT_ID,MENU_NAME)values(6,2,'FFFF');insertintoMENU(MENU_ID,PARENT_ID,MENU_NAME)values(7,2,'GGGG');insertintoMENU(MENU_ID,PARENT_ID,MENU_NAME)values(8,3,'HHHH');commit;查询语句：selectrpad('',(level-1)*3)||menu_namefrommenuconnectbyparent_id=priormenu_idstartwithparent_idisnullconnectby子句定义表中的各个黄是如何相互联系的startwith子句定义数据黄查询的初始起点level表示查询深度===================================oracleLPAD和RPAD收藏declare--Localvariableshereiinteger;leftpadvarchar(11);rightpadvarchar(14);begin--TeststatementshereSELECTLPAD('Hello!',8,'0'),RPAD('Hello!',8,'0')intoleftpad,rightpadFROMDUAL;dbms_output.put_line(leftpad||''||rightpad);end;执行结果后台：00Hello!数8。Hello!00在前面字符串的左边添加后面给定的字符串直到前一个字符串的长度等于中间参在字符串1的右边添加字符串2直到字符串1的长度等于i转：Oracle中Level函数的使用实例.

2010-04-1514:27byTracy.,1734visits,收藏,编辑LevelisapseudocolumnusedwithCONNECTBYanddenotesthenodelevelofthetreestructure.Forexample,giventhefollowingdepartment/sub-departmentlayeringarchitecture,wehaveanAccountingdepartmentwithinaFinancialsdepartmentwithinaSoftwaredepartment,thatis,SoftwareOSFinancialsSpreadsheetsAccountingTheexistenceofavalid\"parent\"departmentcanbeenforcedwithaforeignkeyconstraintonadepartmentnamecolumn.ThisconstraintensuresthatIFadepartmenthasaparent,itisanexistingdepartmentinthesametable.CREATETABLEdept(dept_nameVARCHAR2(20)PRIMARYKEY,parent_nameVARCHAR2(20),CONSTRAINTfk_dept2_parent_nameFOREIGNKEY(parent_name)REFERENCESdept);TheresultofSELECT*FROMDEPTis:DEP_NAME--------SoftwareOSFinancialsSpreadsheetAccountingPARENT_NAME------------NULLSoftwareSoftwareFinancialsFinancialsThefollowingSQLstatementusesLEVELtodenotethelevelnumberofthenodeinthetreestructure.SELECTLEVEL,parent_name,dept_nameFROMdeptCONNECTBYpriordept_name=parent_nameSTARTWITHdept_name='Software'ORDERBYLEVEL;Theresultis:LEVELPARENT_NAMEDEPT_NAME--------------------------------------------------12Software2SoftwareSoftwareOSFinancials3Financials3FinancialsSpreadsheetsAccountingOracle中使用connectby来实现树状查询，其中可以使用level这个伪列来实现分层查询。具体使用如下：一张表menu记录菜单的层级情况。表结构如下：menu_idnumber,parent_idnumber,menu_namenvarchar2(20)首先select*frommenuconnectbymenu_id=parent_idstartwithmenu_id=1;这样可以看到Oracle以树状结构产生结果。某些时候如果我只想要第2层的菜单项如何做呢。select*from(selectlevel,menu_id,parent_id,menu_namefrommenuconncetbymenu_id=parent_idstartwithmenu_id=1)wherelevel=2这样就可以得到层级为第二层的菜单项。这样的SQL写法同样适合rownum伪列，Oracle中实现TOPn查询就是如此写的。