sql有向图问题期待新解决方案 转自.docVIP

sql有向图问题期待新解决方案 转自 start with.connect by用法简介sql有向图问题期待新解决方案 /* 通过START WITH...CONNECT BY...子句来实现SQL的层次查询. 自从Oracle 9i开始，可以通过SYS_CONNECT_BY_PATH函数实现将父节点到当前行内容以path或者层次元素列表的形式显示出来。 自从Oracle 10g中，还有其他更多关于层次查询的新特性。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。 那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示1， 如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示0。 在Oracle 10g之前的版本中，如果在你的树中出现了环状循环(如一个孩子节点引用一个父亲节点)， Oracle就会报出一个错误提示：ORA-01436：CONNECT BY loop in user data。如果不删掉对父亲的引用就无法执行查询操作。 而在Oracle 10g中，只要指定NOCYCLE就可以进行任意的查询操作。与这个关键字相关的还有一个伪列--CONNECT_BY_ISCYCLE， 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示1，否则就显示0。 The start with.connect by clause can be used to select data that has ahierarchical relationship (usually some sort of parent-child,boss-employee or thing-parts). It is also being used when an sql execution plan is explained. syntax： select.[start with initial-condition]connect by[nocycle]recurse-condition level With level it is possible to show the level in the hierarchical relation of all the data. --oracle 9i sys_connect_by_path With sys_connect_by_path it is possible to show the entire path from the top level down to theactualchild. --oracle 10g connect_by_root connect_by_root is anew operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries. connect_by_is_leaf connect_by_isleaf is anew operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries. connect_by_iscycle connect_by_is_cycle is anew operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries. --start with.connect by.的处理机制 How must astart with.connect by select statement be read and interpreted? If Oracle encounters such an SQL statement,it proceeds as described in the following pseude code. for rec in(select*from some_table)loop if FULLFILLS_START_WITH_CONDITION(rec)then RECURSE(rec,rec.child)； end if； end loop； procedure RECURSE(rec in MATCHES_SELECT_STMT,new_parent IN field_type)is begin APPEND_RESULT_LIST(rec)； for rec_recurse