关于临时表的问题（在线等）

[junior_wu]

我写的存储过程如下，
create or replace procedure COLLECT_CHEM
(CompanyCode in nvarchar2)
as

CURSOR NAME1 IS SELECT * from PPMS_CHEM_PRODCLASS where PARENT_PID='P_HGP' ORDER BY SORT_NO;

current_row PPMS_CHEM_PRODCLASS%ROWTYPE;
current_row1 PPMS_CHEM_PRODSUBCLASS%ROWTYPE;
current_row2 PPMS_CHEM_PRODITEM%ROWTYPE;

BEGIN

for current_row in NAME1 LOOP
DBMS_OUTPUT.PUT_LINE(current_row.cname);
for current_row1 in (select * from PPMS_CHEM_PRODSUBCLASS where PARENT_PID=current_row.cid and sort_no is not null ORDER BY SORT_NO ASC) loop
DBMS_OUTPUT.put_line(current_row1.sname);
for current_row2 in (select * from PPMS_CHEM_PRODITEM where PARENT_ID=current_row1.sid and compcode=CompanyCode and sort_no is not null ORDER BY SORT_NO ASC) loop
DBMS_OUTPUT.put_line(' '||current_row2.itemname);
end loop;
end looP;
END LOOP;
END;
得到的数据如下：
乙烯
合成氨
合成树脂
1、聚乙烯
2、高密度聚乙烯
5300S
5300S粉

3、低密度聚乙烯
18D
18D0
18G
18E
1810D
合成橡胶
1、丁苯橡胶
2、丁腈橡胶
3、顺丁橡胶
4、乙丙橡胶
5、氯磺化聚乙烯
合成纤维
1、丙纶短纤
2、涤纶长丝
3、腈纶短纤
0.88*38普通
1.66*38
高缩1 1.66*38

合成纤维原料和聚合物
1、PTA
2、丙烯腈
3、乙二醇
4、聚酯
化肥
上面的存储过程，用到了游标等信息，通过DBMS_OUTPUT.put_line把数据写出来，我想把这些数据存到一个临时表中，如何实现呢？希望高手能在我存储过程的基础上修改下，我最终想实现在Gridview中把这些数据展示出来。在线等。

[shiyiwan]

create or replace procedure COLLECT_CHEM 
      (CompanyCode in nvarchar2) 
as 

CURSOR NAME1 IS SELECT * from PPMS_CHEM_PRODCLASS where PARENT_PID='P_HGP' ORDER BY SORT_NO; 

current_row PPMS_CHEM_PRODCLASS%ROWTYPE; 
current_row1 PPMS_CHEM_PRODSUBCLASS%ROWTYPE; 
current_row2 PPMS_CHEM_PRODITEM%ROWTYPE; 

BEGIN 

    create table tmp (sname PPMS_CHEM_PRODSUBCLASS.sname%TYPE, itemname PPMS_CHEM_PRODITEM.itemname%TYPE);
    
    for current_row in NAME1 LOOP 
    DBMS_OUTPUT.PUT_LINE(current_row.cname); 
        for current_row1 in (select * from PPMS_CHEM_PRODSUBCLASS where PARENT_PID=current_row.cid and sort_no is not null ORDER BY SORT_NO ASC) loop 
        --DBMS_OUTPUT.put_line(current_row1.sname); 
            for current_row2 in (select * from PPMS_CHEM_PRODITEM where PARENT_ID=current_row1.sid and compcode=CompanyCode and sort_no is not null ORDER BY SORT_NO ASC) loop 
            --DBMS_OUTPUT.put_line('          '||current_row2.itemname); 
            insert into tmp values(current_row1.sname,current_row2.itemname);
            end loop; 
        end looP; 
    END LOOP; 
    
    COMMIT;
END; 

[ptpa]

既然用游标了，干吗还要写入临时表？
或者说写入临时表的目的。

看看
http://hi.baidu.com/wgqweb/blog/item/f6380bbf83de570f19d81fa2.html
如何在ORACLE中返回游标。

[junior_wu]

这个不对。没你想得那么容易呢。[Quote=引用 1 楼 shiyiwan 的回复:]
SQL codecreateorreplaceprocedure COLLECT_CHEM
(CompanyCodein nvarchar2)asCURSOR NAME1ISSELECT*from PPMS_CHEM_PRODCLASSwhere PARENT_PID='P_HGP'ORDERBY SORT_NO;

current_row PPMS_CHEM_PRODCLASS%¡­
[/Quote]

[shiyiwan]

你要什么样的效果？

start with
connect by ?