Oracle “CONNECT BY” 使用
?select?lpad('?',3*(level-1))||parentmenuid?a,
lpad('?',3*(level-1))||menuid,
lpad('?',3*(level-1))||menu_name?from?t_pub_menu?where?is_show=1
start??with?menuid=20000
?connect?by?parentmenuid?=?prior?menuid??

Oracle “CONNECT BY” 使用?

??? Oracle “CONNECT BY”是层次查询子句，一般用于树状或者层次结果集的查询。其语法是：

?12[ START WITH?condition ]CONNECT?BY?[ NOCYCLE ] condition

The start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts).

?

说明：?
1. START WITH：告诉系统以哪个节点作为根结点开始查找并构造结果集，该节点即为返回记录中的最高节点。?
2. 当分层查询中存在上下层互为父子节点的情况时，会返回ORA-01436错误。此时，需要在connect by后面加上NOCYCLE关键字。同时，可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。 connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用

?

??? 接下来，用一些示例来说明“CONNECT BY”的用法。

?

[例1]

创建一个部门表，这个表有三个字段，分别对应部门ID，部门名称，以及上级部门ID

?12345678-- Create tablecreate?table?DEP(??DEPID????? number(10) not?null,??DEPNAME??? varchar2(256),??UPPERDEPID number(10));

初始化一些数据

?1234567891011121314151617181920212223SQL> INSERT?INTO?DEP(DEPID, DEPNAME, UPPERDEPID) VALUES?(0, '总经办', null);1 row inserted?SQL> INSERT?INTO?DEP(DEPID, DEPNAME, UPPERDEPID) VALUES?(1, '开发部', 0);1 row inserted?SQL> INSERT?INTO?DEP(DEPID, DEPNAME, UPPERDEPID) VALUES?(2, '测试部', 0);1 row inserted?SQL> INSERT?INTO?DEP(DEPID, DEPNAME, UPPERDEPID) VALUES?(3, 'Sever开发部', 1);1 row inserted?SQL> INSERT?INTO?DEP(DEPID, DEPNAME, UPPERDEPID) VALUES?(4, 'Client开发部', 1);1 row inserted?SQL> INSERT?INTO?DEP(DEPID, DEPNAME, UPPERDEPID) VALUES?(5, 'TA测试部', 2);1 row inserted?SQL> INSERT?INTO?DEP(DEPID, DEPNAME, UPPERDEPID) VALUES?(6, '项目测试部', 2);1 row inserted?SQL> commit;Commit?complete?12345678910111213SQL> SELECT?* FROM?DEP;???????DEPID DEPNAME?????????????????????????????????????????????????????????????????????????? UPPERDEPID----------- -------------------------------------------- -----------??????????0 General Deparment??????????????????????????????????????????????????????????????? ??????????1 Development??????????????????????????????????????????????????????????????????????????????? 0??????????2 QA???????????????????????????????????????????????????????????????????????????????????????? 0??????????3 Server Development???????????????????????????????????????????????????????????????????????? 1??????????4 Client Development???????????????????????????????????????????????????????????????????????? 1??????????5 TA???????????????????????????????????????????????????????????????????????????????????????? 2??????????6 Porject QA???????????????????????????????????????????????????????????????????????????????? 2?7 rows?selected

现在我要根据“CONNECT BY”来实现树状查询结果

?1234567891011121314151617181920SQL> SELECT?RPAD( ' ', 2*(LEVEL-1), '-'?) || DEPNAME "DEPNAME",CONNECT_BY_ROOT DEPNAME "ROOT",CONNECT_BY_ISLEAF "ISLEAF",LEVEL?,SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"FROM?DEPSTART WITH?UPPERDEPID IS?NULLCONNECT?BY?PRIOR?DEPID = UPPERDEPID;?DEPNAME??????????????????????? ROOT??????????????????? ISLEAF????? LEVEL?PATH------------------------------ ------------------- ---------- ---------- --------------------------------------------General Deparment????????????? General Deparment??????????? 0????????? 1 /General Deparment?-Development????????????????? General Deparment??????????? 0????????? 2 /General Deparment/Development?---Server Development???????? General Deparment??????????? 1????????? 3 /General Deparment/Development/Server Development?---Client Development???????? General Deparment??????????? 1????????? 3 /General Deparment/Development/Client Development?-QA?????????????????????????? General Deparment??????????? 0????????? 2 /General Deparment/QA?---TA???????????????????????? General Deparment??????????? 1????????? 3 /General Deparment/QA/TA?---Porject QA???????????????? General Deparment??????????? 1????????? 3 /General Deparment/QA/Porject QA????????????????????????????????????????????????????7 rows?selected

说明：?
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点?
2. CONNECT_BY_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点?
3. LEVEL 伪列表示节点深度?
4. SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔

[例2]

通过CONNECT BY生成序列

?12345678910111213141516SQL> SELECT?ROWNUM FROM?DUAL CONNECT?BY?ROWNUM <= 10;?????ROWNUM----------?????????1?????????2?????????3?????????4?????????5?????????6?????????7?????????8?????????9????????10?10 rows?selected

[例3]

通过CONNECT BY用于十六进度转换为十进制

?123456789101112131415161718192021222324252627CREATE?OR?REPLACE?FUNCTION?f_hex_to_dec(p_str IN?VARCHAR2) RETURN?VARCHAR2 IS????----------------------------------------------????-- 对象名称: f_hex_to_dec????-- 对象描述: 十六进制转换十进制????-- 输入参数: p_str 十六进制字符串????-- 返回结果: 十进制字符串????-- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;????----------------------------------------------????v_return? VARCHAR2(4000);??BEGIN????SELECT?SUM(DATA) INTO?v_return??????FROM?(SELECT?(CASE?upper(substr(p_str, rownum, 1))?????????????????????WHEN?'A'?THEN?'10'?????????????????????WHEN?'B'?THEN?'11'?????????????????????WHEN?'C'?THEN?'12'?????????????????????WHEN?'D'?THEN?'13'?????????????????????WHEN?'E'?THEN?'14'?????????????????????WHEN?'F'?THEN?'15'?????????????????????ELSE?substr(p_str, rownum, 1)???????????????????END) * power(16, length(p_str) - rownum) DATA??????????????FROM?dual????????????CONNECT?BY?rownum <= length(p_str));????RETURN?v_return;??EXCEPTION????WHEN?OTHERS THEN??????RETURN?NULL;??END;

说明：

1. CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历

2. 通过CASE语句，来解析十六进制中的A-F对应的10进制值?