基于对象层次结构创建存储过程 [英] Creating store procedure based on object hierarchy

问题描述

在数据库中，我们存储一个对象层次结构：

- 包含对象列表的表OBJECT_NODE，每个对象由数值ID标识

- 包含这些对象之间的父船的表OBJECT_TREE

 创建 表 OBJECT_NODE（
 ID  int ，
 NAME  varchar （ 255 ））创建 表 OBJECT_TREE（
 PARENT_ID  int ，
 CHILD_ID  int ）

例如：



1 ________2________3_______6

| |

| | ______ 7

|

|

| ______ 4________8

| |

| | ______ 9

|

|

| ______ 5 OBJECT_NODE OBJECT_TREE

ID姓名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 
 

以下2个问题，请在层次结构中编写未定义级别的过程。



1 /创建一个给出给定节点的所有下层对象的SQL过程

（在上面的例子中，节点2的所有下层对象都是3,4,5,6,7,8,9）

 创建 过程 GET_OBJECT_CHILDREN（I_PARENT_ID  int ）

...



2 /修改程序只给叶子（没有对象）志ldren）

（在上面的例子中，节点2的所有叶子对象都是5,6,7,8,9）





请根据以上层次提供上述问题的答案.............. Plz帮我解决

解决方案

< blockquote>我们不做你的作业：这是有原因的。它就是为了让你思考你被告知的事情，并试着理解它。它也在那里，以便您的导师可以识别您身体虚弱的区域，并将更多的注意力集中在补救措施上。



亲自尝试，你可能会发现它不是和你想的一样困难！

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

解决方案

We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.

Try it yourself, you may find it is not as difficult as you think!

这篇关于基于对象层次结构创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！