ocp中一道简单的递归

112. View the Exhibit and examine the details of the EMPLOYEES table.

Evaluate the following SQL statements:

Statement 1:

SELECT employee_id, last_name, job_id, manager_id

FROM employees

START WITH employee_id = 101

CONNECT BY PRIOR employee_id = manager_id AND manager_id !=

108 ;

Statement 2:

SELECT employee_id, last_name, job_id, manager_id

FROM employees

WHERE manager_id != 108

START WITH employee_id = 101

CONNECT BY PRIOR employee_id = manager_id;

递归查询语句的标准格式为 :

select col_name...

from table_name

where 条件

start with 条件

connect by prior 条件
* where 子句必须出现在 start with 和 connect by 子句之前，否则报错
Statement 1:执行结果


EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID
----------- ------------------------- ---------- ----------
        101 Kochhar                   AD_VP             100
        108 Greenberg                 FI_MGR            101
        200 Whalen                    AD_ASST           101
        203 Mavris                    HR_REP            101
        204 Baer                      PR_REP            101
        205 Higgins                   AC_MGR            101
        206 Gietz                     AC_ACCOUNT        205
Statement 2:执行结果


EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID
----------- ------------------------- ---------- ----------
        101 Kochhar                   AD_VP             100
        108 Greenberg                 FI_MGR            101
        200 Whalen                    AD_ASST           101
        203 Mavris                    HR_REP            101
        204 Baer                      PR_REP            101
        205 Higgins                   AC_MGR            101
        206 Gietz                     AC_ACCOUNT        205
Statement 1和Statement 2执行结果完全一样。
选项：

Which two statements are true regarding the above SQL statements?

(Choose two.)

A. Statement 2 would not execute because the WHERE clause condition

is not allowed in a statement that has the START WITH clause.

递归查询允许使用 where 子句，但是一定要在 START WITH 子句之前使用。

B. The output for statement 1 would display the employee with

MANAGER_ID 108 and all the employees below him or her in the

hierarchy.

第一个语句会显示所有的以 employee_id=101 为根的人，除了

manager_id=108 的人以及他们以后的分支， CONNECT BY 子句中 and 一个条

件可以去掉符合该条件的分支

C. The output of statement 1 would neither display the employee with

MANAGER_ID 108 nor any employee below him or her in the

hierarchy.(right)

CONNECT BY 子句中 and manager_id != 108 去掉符合该条件的分支

D. The output for statement 2 would not display the employee with

MANAGER_ID 108 but it would display all the employees below him or

her in the hierarchy.(right)

where 条件只去除节点，不影响该节点以后的遍历过程在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支。

使用 WHERE 子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它

却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。

使用 CONNECT BY 子句中 and 一个条件来去掉符合该条件的分支

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29674916/viewspace-1793963/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29674916/viewspace-1793963/