有三个表
产品表 PRODUCTS(OBJECT_ID integer,CN_PRODUCT_ID varchar2(50))
产品和零件的链接表 link1(OBJECT_ID1 integer,OBJECT_ID2 integer)
OBJECT_ID1是产品的id,OBJECT_ID2是零件的id
零件和零件的链接表 link2(OBJECT_ID1 integer,OBJECT_ID2 integer)
OBJECT_ID1是父零件的id, OBJECT_ID2是子零件的id他们的关系是一个产品下面有一些零件,零件下面又链接有零件
零件和零件的链接link2是递归的现在查询所有的产品下面的零件,返回的是零件的id和,和其所属产品的CN_PRODUCT_ID
我用了中间表temp表,不用temp在动态sql里能不能直接返回数据所需的数据高手帮忙!!DECLARE CURSOR CUR_M_SHOHINHOTEL IS SELECT OBJECT_ID,CN_PRODUCT_ID FROM TN_PRODUCTS;
product CUR_M_SHOHINHOTEL%ROWTYPE;
BEGIN
OPEN CUR_M_SHOHINHOTEL;
loop FETCH CUR_M_SHOHINHOTEL INTO product;
INSERT INTO temp ((
select distinct OBJECT_ID2,product.CN_PRODUCT_ID from ITEMS_TREE start with OBJECT_ID1
in(select OBJECT_ID2 from TN_LINK_00846 where OBJECT_ID1 = product.OBJECT_ID)connect by OBJECT_ID1 = prior OBJECT_ID2
)
union
(
select OBJECT_ID2,product.CN_PRODUCT_ID from TN_LINK_00846 where OBJECT_ID1 = product.OBJECT_ID
)
);
EXIT WHEN CUR_M_SHOHINHOTEL%NOTFOUND;
END LOOP;
CLOSE CUR_M_SHOHINHOTEL;
END;
产品表 PRODUCTS(OBJECT_ID integer,CN_PRODUCT_ID varchar2(50))
产品和零件的链接表 link1(OBJECT_ID1 integer,OBJECT_ID2 integer)
OBJECT_ID1是产品的id,OBJECT_ID2是零件的id
零件和零件的链接表 link2(OBJECT_ID1 integer,OBJECT_ID2 integer)
OBJECT_ID1是父零件的id, OBJECT_ID2是子零件的id他们的关系是一个产品下面有一些零件,零件下面又链接有零件
零件和零件的链接link2是递归的现在查询所有的产品下面的零件,返回的是零件的id和,和其所属产品的CN_PRODUCT_ID
我用了中间表temp表,不用temp在动态sql里能不能直接返回数据所需的数据高手帮忙!!DECLARE CURSOR CUR_M_SHOHINHOTEL IS SELECT OBJECT_ID,CN_PRODUCT_ID FROM TN_PRODUCTS;
product CUR_M_SHOHINHOTEL%ROWTYPE;
BEGIN
OPEN CUR_M_SHOHINHOTEL;
loop FETCH CUR_M_SHOHINHOTEL INTO product;
INSERT INTO temp ((
select distinct OBJECT_ID2,product.CN_PRODUCT_ID from ITEMS_TREE start with OBJECT_ID1
in(select OBJECT_ID2 from TN_LINK_00846 where OBJECT_ID1 = product.OBJECT_ID)connect by OBJECT_ID1 = prior OBJECT_ID2
)
union
(
select OBJECT_ID2,product.CN_PRODUCT_ID from TN_LINK_00846 where OBJECT_ID1 = product.OBJECT_ID
)
);
EXIT WHEN CUR_M_SHOHINHOTEL%NOTFOUND;
END LOOP;
CLOSE CUR_M_SHOHINHOTEL;
END;
解决方案 »
- Oracle如何跨用户访问
- ORACLE 9i 数据库的回滚段使用率不断增加,不知何种问题?
- 建立视图出错,帮忙看下
- 向高手请教oracle 里的对象操作的详细答案
- 在ORACLE中用触发器实现主键功能报错!
- 急呀,高分求助,一个调用存储过程的小问题。。。在线等
- 请教一个很难的SQL语句
- 看看我这个SP写的有问题吗怎么老错气愤
- oracle的表的trigger中怎么得到当前insert 或delete 或update的行的数据。
- 谁能用简单理解的方式 解释 :什么是维度表和事实表?两者的关系是什么?
- 急!高分求如果通过调用基盘的共通去释放ORACLE占用的资源?
- 求肋高手,有关分组查询的问题?
我这里举一例,希望对你有启示CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
select * from tbl_test
--从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid