In a database, we store a hierarchy of objects with:
- A table OBJECT_NODE containing a list of objects, each object is identified by a numeric value “ID”
- And a table OBJECT_TREE containing the parent ship between these objects
Create Table OBJECT_NODE (
ID int,
NAME varchar(255) ) Create Table OBJECT_TREE (
PARENT_ID int,
CHILD_ID int )
For example:
1 ________2________3_______6
| |
| |______7
|
|
|______4________8
| |
| |______9
|
|
|______5 OBJECT_NODE OBJECT_TREE
ID NAME PARENT_ID CHILD_ID
1 N1 1 2
2 N2 2 3
3 N3 2 4
4 N4 2 5
5 N5 3 6
6 N6 3 7
7 N7 4 8
8 N8 4 9
9 N9
For the following 2 questions, please write the procedure for an undefined number of levels in the hierarchy.
1/ Create a SQL Procedure giving all down hierarchy objects of a given node
(in the upper example, all down hierarchy objects of node 2 are 3, 4, 5, 6, 7, 8, 9)
Create Procedure GET_OBJECT_CHILDREN (I_PARENT_ID int)
…
2/ Modify the procedure to give only leaves (objects with no children)
(in the upper example, all leaves objects of node 2 are 5, 6, 7, 8, 9)
Please provide answers to above question based on above hierarchy ..............Plz help me out