如何转换成Oracle代码？高分求！

[yjh53615728]

DECLARE @t INT
SET @t=1
-->创建临时表#t_BOM存储第一阶的子项物料代码
CREATE TABLE #t_BOM(BMB03 varchar(400),层 INT)
INSERT INTO #t_BOM
SELECT aa.BMB03,@t 
FROM bmb_file aa
WHERE aa.BMB01='14N1003'
ORDER BY aa.BMB03

-->创建临时表#t_BOMChild存储“其下阶有BOM”的子项物料代码
CREATE TABLE #t_BOMChild(BMB03 varchar(400),层 INT)
INSERT INTO #t_BOMChild
SELECT aa.BMB03,aa.层
FROM #t_BOM aa
WHERE EXISTS (SELECT bb.BMB01 FROM bmb_file bb  WHERE aa.BMB03=bb.BMB01)

-->循环向临时表#t_BOM和#t_BOMChild插入数据
WHILE @@ROWCOUNT>0
 BEGIN
   SET @t=@t+1
   INSERT INTO #t_BOM
   SELECT bb.BMB03,@t
   FROM #t_BOMChild aa
   INNER JOIN bmb_file bb ON aa.BMB03=bb.BMB01
   WHERE  aa.层=@t-1

   INSERT INTO #t_BOMChild
   SELECT aa.BMB03,aa.层
   FROM #t_BOM aa
   WHERE aa.层=@t AND EXISTS (SELECT bb.BMB01 FROM bmb_file bb  WHERE aa.BMB03=bb.BMB01)
 END

-->若要去掉下阶有物料的子物料代码则需要下面Delete语句
DELETE FROM #t_BOM  
WHERE EXISTS(SELECT * FROM #t_BOMChild bb WHERE #t_BOM.BMB03=bb.BMB03)

SELECT *
FROM #t_BOM aa

[开着拖拉机泡妞]

你应该把这个帖子移到Oracle版块去。这里懂Oracle的人不是很多

[了解]

进来学习。向你们学习，祝你们成功。

[秋雨飘落]

基础的ORACLE的东西，如果给你转，也要写好几分钟了

[sunny_haizi]

这个可以用powerdesigner

[myloveral]

--新手，不知道对不对

DECLARE t INT;
t:=1;
-->创建临时表t_BOM存储第一阶的子项物料代码
CREATE GLOBAL TEMPORARY TABLE t_BOM(BMB03 varchar(400),层 INT)
INSERT INTO t_BOM
SELECT aa.BMB03,t
FROM bmb_file aa
WHERE aa.BMB01='14N1003'
ORDER BY aa.BMB03;

-->创建临时表t_BOMChild存储“其下阶有BOM”的子项物料代码
CREATE GLOBAL TEMPORARY TABLE t_BOMChild(BMB03 varchar(400),层 INT)
INSERT INTO t_BOMChild
SELECT aa.BMB03,aa.层
FROM t_BOM aa
WHERE EXISTS (SELECT bb.BMB01 FROM bmb_file bb WHERE aa.BMB03=bb.BMB01);

-->循环向临时表t_BOM和t_BOMChild插入数据
WHILE SQL%ROWCOUNT>0 loop
t:=t+1
INSERT INTO t_BOM
SELECT bb.BMB03,@t
FROM t_BOMChild aa
INNER JOIN bmb_file bb ON aa.BMB03=bb.BMB01
WHERE aa.层=t-1;

INSERT INTO t_BOMChild
SELECT aa.BMB03,aa.层
FROM t_BOM aa
WHERE aa.层=t AND EXISTS (SELECT bb.BMB01 FROM bmb_file bb WHERE aa.BMB03=bb.BMB01);

end loop;


-->若要去掉下阶有物料的子物料代码则需要下面Delete语句
DELETE FROM t_BOM
WHERE EXISTS(SELECT * FROM t_BOMChild bb WHERE t_BOM.BMB03=bb.BMB03);

SELECT *
FROM t_BOM aa;