OracleLevel的用法.docx

Oracle 树查询 level的用法首先创建一张表menu记录菜单的层级情况。表结构如下：menu_id number,parent_id number,menu_name nvarchar2(20)插入数据：insert into MENU (MENU_ID, PARENT_ID, MENU_NAME)values (1, null, AAAA);insert into MENU (MENU_ID, PARENT_ID, MENU_NAME)values (2, 1, BBBB);insert into MENU (MENU_ID, PARENT_ID, MENU_NAME)values (3, 1, CCCC);insert into MENU (MENU_ID, PARENT_ID, MENU_NAME)values (4, 1, DDDD);insert into MENU (MENU_ID, PARENT_ID, MENU_NAME)values (5, 2, EEEE);insert into MENU (MENU_ID, PARENT_ID, MENU_NAME)values (6, 2, FFFF);insert into MENU (MENU_ID, PARENT_ID, MENU_NAME)values (7, 2, GGGG);insert into MENU (MENU_ID, PARENT_ID, MENU_NAME)values (8, 3, HHHH);commit;查询语句：select rpad( ,(level-1)*3)|menu_name from menuconnect by parent_id = prior menu_idstart with parent_id is nullconnect by 子句定义表中的各个黄是如何相互联系的start with 子句定义数据黄查询的初始起点level 表示查询深度=oracle LPAD 和 RPAD 收藏declare- Local variables herei integer;leftpad varchar(11);rightpad varchar(14);begin- Test statements hereSELECT LPAD(Hello!,8,0),RPAD(Hello!,8,0)into leftpad,rightpad FROM DUAL;dbms_output.put_line(leftpad|rightpad);end;执行结果后台：00Hello!在前面字符串的左边添加后面给定的字符串直到前一个字符串的长度等于中间参数8。Hello!00在字符串1的右边添加字符串2直到字符串1的长度等于i转：Oracle中Level函数的使用实例.2010-04-15 14:27 by Tracy., 1734 visits, 收藏, 编辑 Level is a pseudo column used with CONNECT BY and denotes the node level of the tree structure.For example, given the following department/sub-department layering architecture, we have an Accounting department within a Financials department within a Software department, that is,Software OS Financials Spreadsheets AccountingThe existence of a valid parent department can be enforced with a foreign key constraint on a department name column. This constraint ensures that IF a department has a parent, it is an existing department in the same table.CREATE TABLE dept (dept_name VARCHAR2(20) PRIMARY KEY, parent_name VARCHAR2(20),CONSTRAINT fk_dept2_parent_nameFOREIGN KEY (parent_name) REFERENCES dept);The result of SELECT * FROM DEPT is:DEP_NAME PARENT_NAME- -Software NULLOS SoftwareFinancials SoftwareSpreadsheet FinancialsAccounting FinancialsThe following SQL statement uses LEVEL to denote the level number of the node in the tree structure. SELECT LEVEL, parent_name, dept_name FROM dept CONNECT BY prior dept_name = parent_name START WITH dept_name = Software ORDER BY LEVEL;The result is: LEVEL PARENT_NAME DEPT_NAME- - - 1 Software 2 Software OS 2 Software Financials 3 Financials Spreadsheets 3 Financials AccountingOracle中使用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