我写的存储过程如下,
create or replace procedure COLLECT_CHEM
(CompanyCode in nvarchar2)
asCURSOR 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中把这些数据展示出来。在线等。
create or replace procedure COLLECT_CHEM
(CompanyCode in nvarchar2)
asCURSOR 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中把这些数据展示出来。在线等。
(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;
或者说写入临时表的目的。 看看
http://hi.baidu.com/wgqweb/blog/item/f6380bbf83de570f19d81fa2.html
如何在ORACLE中返回游标。
connect by ?