Oracle 11g Release 1 (11.1) SQL _ hierarchical query (Overview)

Content

• Demo level Query
• Hierarchical Queries)
• Reference

Demo level Query

The purpose of hierarchical query is to save the recursive code of the program. Writing recursion is always annoying.

Assume thatScottUnder the user, useEMPTable-level query. This table contains an employee and its superiors. The content is as follows:

The highest position is, of course, the chairman.PRESIDENT;

Then the managerMANAGER;

Second, analystsANALYST;

Finally, the salespersonCLEAKAnd sales staffSALESMAN.

Example 1: Describe employees with employee ID 7566 and all their subordinates.

SelectT. empnoAsEmployee ID,

T. enameAsEmployee name,

T. jobAsPosition,

T. mgrAsSuperior ID,

       level

  from emp t

 start with t.empno = 7566

connect by prior t.empno = t.mgr;

Example 2: Show All subordinates numbered 7566.

SelectT. empnoAsEmployee ID,

T. enameAsEmployee name,

T. jobAsPosition,

T. mgrAsSuperior ID

  from emp t

 start with t.mgr = 7566

connect by prior t.empno = t.mgr;

Example 3: The employee number is 7876 and all superiors of the employee.

SelectT. empnoAsEmployee ID,

T. enameAsEmployee name,

T. jobAsPosition,

T. mgrAsSuperior ID

  from emp t

 start with t.empno = 7876

connect by prior t.mgr = t.empno;

From the above demonstration, we can see that hierarchical queries can be either "down" or "up" queries, similar to "recursion ".

• As shown in Example 1, "Find down", then

Start with t. empno = 7566Specify the root node.Connect by prior t. empno = t. mgrSpecifies the relationship between nodes. The superior number is equal to the current employee number.

• As shown in Example 3, "search up", then

Start with t. empno = 7876Specify the root node.Connect by prior t. mgr = t. empnoSpecify the node relationship. The employee ID is equal to the Superior Number of the current employee.

1 2 Next Page