oracle临时表 create global temporary tablename( marid int, num int ) -- on commit preserve rows; --提交保留数据 退出会话删除数据 -- on commit delete rows; --提交删除数据 事务临时表
CREATE GLOBAL TEMPORARY TABLE TABLENAME ( COL1 VARCHAR2(10), COL2 NUMBER ) ON COMMIT PRESERVE(DELETE) ROWS ; 这种临时表不占用表空间,而且不同的SESSION之间互相看不到对方的数据 在会话结束后表中的数据自动清空,如果选了DELETE ROWS,则在提交的时候即清空数据,PRESERVE则一直到会话结束
CREATE TABLE stack ( marid number, num int );我往临时表里面插入数据insert into stack select MaterialId as MarID,MaterialNum as num from DStockExc where StockexcID=Id and status=0 declare num number; cursor s_cur1 is select MarID,num from stack; begin for v_cur in s_cur1 loop select count(1) into num from DMaterial id=v_cur.MarID; if num>0 then insert into stack select MarID,marnum*v_cur.num from BBOMList where motherid=v_cur.matId and status=0; end if; end loop; end; /
beckhambobo(beckham) 再 insert into stack select MarID,marnum*v_cur.num from BBOMList where motherid=v_cur.matId and status=0; 以后,游标s_cur1 中,没有刚刚插入的值。
不要游标: CREATE TABLE stack ( marid number, num int );我往临时表里面插入数据insert into stack select MaterialId ,MaterialNum from DStockExc where StockexcID=Id and status=0 insert into stack select a.MarID,a.marnum*b.num from BBOMList a,(select MarID,num from stack where MarID in (select id from DMaterial)) b where a.motherid=b.matId and a.status=0 都可以不要临时表
pengdali(大力 V3.0) 不行的,我在select count(1) into num from BBOMList motherid=v_cur.MarID; if num>0 then insert into stack select MarID,marnum*v_cur.num from BBOMList where motherid=v_cur.matId and status=0; -- 我从这里找出来的 MarID ---------还有可能使 select count(1) into num from BBOMList motherid=v_cur.MarID; ----------num>0; esle insert into table1 ;----我在这里要把结果插到另一个表
end if;
按你这样说法,不可能,否则会死循环.
begin insert into stack select MaterialId,marnum*num from BBOMList where motherid=matId and status=0 and exists(select 1 from BBOMList where motherid=matId and status=0) end; /
create global temporary tablename(
marid int,
num int
)
-- on commit preserve rows; --提交保留数据 退出会话删除数据
-- on commit delete rows; --提交删除数据 事务临时表
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE(DELETE) ROWS ;
这种临时表不占用表空间,而且不同的SESSION之间互相看不到对方的数据
在会话结束后表中的数据自动清空,如果选了DELETE ROWS,则在提交的时候即清空数据,PRESERVE则一直到会话结束
marid number,
num int
);我往临时表里面插入数据insert into stack select MaterialId as MarID,MaterialNum as num from DStockExc where StockexcID=Id and status=0
declare
num number;
cursor s_cur1 is
select MarID,num from stack;
begin
for v_cur in s_cur1 loop
select count(1) into num from DMaterial id=v_cur.MarID;
if num>0 then
insert into stack select MarID,marnum*v_cur.num from BBOMList
where motherid=v_cur.matId and status=0;
end if;
end loop;
end;
/
再 insert into stack select MarID,marnum*v_cur.num from BBOMList
where motherid=v_cur.matId and status=0;
以后,游标s_cur1 中,没有刚刚插入的值。
CREATE TABLE stack (
marid number,
num int
);我往临时表里面插入数据insert into stack select MaterialId ,MaterialNum from DStockExc where StockexcID=Id and status=0
insert into stack
select a.MarID,a.marnum*b.num
from BBOMList a,(select MarID,num from stack where MarID in (select id from DMaterial)) b
where a.motherid=b.matId and a.status=0
都可以不要临时表
不行的,我在select count(1) into num from BBOMList motherid=v_cur.MarID;
if num>0 then
insert into stack select MarID,marnum*v_cur.num from BBOMList
where motherid=v_cur.matId and status=0; -- 我从这里找出来的 MarID
---------还有可能使 select count(1) into num from BBOMList motherid=v_cur.MarID;
----------num>0;
esle
insert into table1 ;----我在这里要把结果插到另一个表
end if;
begin
insert into stack select MaterialId,marnum*num from BBOMList
where motherid=matId and status=0 and exists(select 1 from BBOMList where motherid=matId and status=0)
end;
/
我在SQL SERVER里是能够做到的,
我要这样做是因为,这个是关于BOM表的运算,
你这么做,BOM只有一层是可以的,两层就不行了