我的表结构如下
CID CNAME CLASS_SORT SID SNAME SUBCLASS_SORT ITEMNAME COMPCODE ITEM_SORT
P_ETH 乙烯 101
P_SAA 合成氨 102
P_SYR 合成树脂 103 P_PEE 1、聚乙烯 10301
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 5300S B 1030201
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 5300S粉 B 1030202
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 5300E B 1030203
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 5300B B 1030204
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 5000B B 1030205
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 1600J B 1030206
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 2200J B 1030207
如何实现下面的表结构呢?在线等答案。
一 乙烯
二 合成氨
三 合成树脂:
1 聚乙烯
2 高密度聚乙烯
5000S
5300E
5300B
5000B
2200J
5000S粉料
这种分等级的展示方式呢?
CID CNAME CLASS_SORT SID SNAME SUBCLASS_SORT ITEMNAME COMPCODE ITEM_SORT
P_ETH 乙烯 101
P_SAA 合成氨 102
P_SYR 合成树脂 103 P_PEE 1、聚乙烯 10301
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 5300S B 1030201
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 5300S粉 B 1030202
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 5300E B 1030203
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 5300B B 1030204
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 5000B B 1030205
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 1600J B 1030206
P_SYR 合成树脂 103 P_HPE 2、高密度聚乙烯 10302 2200J B 1030207
如何实现下面的表结构呢?在线等答案。
一 乙烯
二 合成氨
三 合成树脂:
1 聚乙烯
2 高密度聚乙烯
5000S
5300E
5300B
5000B
2200J
5000S粉料
这种分等级的展示方式呢?
as
TYPE cur_type is REF CURSOR;
cur_ cur_type;
cur_1 cur_type;
cur_2 cur_type;current_row tbl%ROWTYPE;
current_row_1 tbl%ROWTYPE;
current_row_2 tbl%ROWTYPE;i number :=0;
begin
Open cur_ for select CID,CNAME from tbl group by CID,CNAME;
loop
FETCH cur_ into current_row;
EXIT WHEN cur_%NOTFOUND;
i := i+1;
dbms_output.put_line(i||':'||current_row.CNAME);
if exists(select SID from tb1 where CID=current_row.CID)
Open cur_1 for select SID,SNAME from tbl where SID=current_row.CID group by SID,SNAME;
loop
FETCH cur_1 into current_row_1;
EXIT WHEN cur_1%NOTFOUND;
dbms_output.put_line(current_row_1.SNAME);
if exists(select ITEMNAME from tb1 where SID=current_1_row.SID)
Open cur_2 for select ITEMNAME from tbl where SID=current_row_1.SID;
loop
FETCH cur_2 into current_row_2;
EXIT WHEN cur_2%NOTFOUND;
dbms_output.put_line(current_row_2.);
end loop
close cur_2;
end if;
end loop;
close cur_1;
end if;
end loop;
close cur_;
end;
/
if exists(select SID from tb1 where CID=current_row.CID)
改成:select * from tb1 where CID=current_row.CID and (SID <> null or SID <>'')
if exists(select ITEMNAME from tb1 where SID=current_1_row.SID)
改成:if exists(select * from tb1 where SID=current_1_row.SID and (ITEMNAME <> null or ITEMNAME <> ''))create or replace procedure collect_
as
TYPE cur_type is REF CURSOR;
cur_ cur_type;
cur_1 cur_type;
cur_2 cur_type;current_row tbl%ROWTYPE;
current_row_1 tbl%ROWTYPE;
current_row_2 tbl%ROWTYPE;i number :=0;
begin
Open cur_ for select CID,CNAME from tbl group by CID,CNAME;
loop
FETCH cur_ into current_row;
EXIT WHEN cur_%NOTFOUND;
i := i+1;
dbms_output.put_line(i||':'||current_row.CNAME);
if exists(select * from tb1 where CID=current_row.CID and (SID <> null or SID <>''))
Open cur_1 for select SID,SNAME from tbl where SID=current_row.CID group by SID,SNAME;
loop
FETCH cur_1 into current_row_1;
EXIT WHEN cur_1%NOTFOUND;
dbms_output.put_line(current_row_1.SNAME);
if exists(select * from tb1 where SID=current_1_row.SID and (ITEMNAME <> null or ITEMNAME <> ''))
Open cur_2 for select ITEMNAME from tbl where SID=current_row_1.SID;
loop
FETCH cur_2 into current_row_2;
EXIT WHEN cur_2%NOTFOUND;
dbms_output.put_line(current_row_2.);
end loop
close cur_2;
end if;
end loop;
close cur_1;
end if;
end loop;
close cur_;
end;
/
是有点问题:现在已经解决了:
create or replace procedure collect_
as
TYPE cur_type is REF CURSOR;
cur cur_type;
cur_1 cur_type;
cur_2 cur_type;v_cid tbl.CID%TYPE;
v_cname tbl.CNAME%TYPE;
v_sid tbl.SID%TYPE;
v_sname tbl.SNAME%TYPE;
v_itemname tbl.ITEMNAME%TYPE;tmp number :=0;
tmp_1 number :=0;
begin
Open cur for select CID,CNAME from tbl group by CID,CNAME;
loop
FETCH cur into v_cid,v_cname;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line(cur%ROWCOUNT||':'||v_cname);
select count(*) into tmp from tbl where CID=v_cid and (SID <> 'null' or SID <>'');
if (tmp >0) then
Open cur_1 for select SID,SNAME from tbl where cid=v_cid group by SID,SNAME;
loop
FETCH cur_1 into v_sid,v_sname;
EXIT WHEN cur_1%NOTFOUND;
dbms_output.put_line('..'||v_sname);
select count(*) into tmp_1 from tbl where SID=v_sid and (ITEMNAME <> 'null' or ITEMNAME <> '');
if(tmp_1>0) then
Open cur_2 for select ITEMNAME from tbl where SID=v_sid;
loop
FETCH cur_2 into v_itemname;
EXIT WHEN cur_2%NOTFOUND;
dbms_output.put_line('..'||v_itemname);
end loop;
close cur_2;
end if;
end loop;
close cur_1;
end if;
end loop;
close cur;
end;
/