表结构
a,序号,b,c,d
01,1,0101,010101,a
01,1,0101,010102,b
01,1,0101,010103,c
01,2,0101,010101,d
01,2,0101,010102,e
01,2,0101,010103,f
01,1,0105,010501,g
01,1,0105,010502,h
01,2,0105,010501,i
01,2,0105,010502,j
02,1,0101,010101,k
02,1,0101,010102,l
02,1,0101,010103,m
02,2,0101,010101,n
02,2,0101,010102,o
02,2,0101,010103,p
02,1,0105,010501,q
02,1,0105,010502,r
02,2,0105,010501,s
02,2,0105,010502,t
………………
字段a,序号,c为索引输出结果以a,b为检索条件,查询a=01,b=0101,输出结果为,010101,010102,010103
----------------------------------
1,a,b,c
2,d,e,f以a,b为检索条件,查询a=01,b=0105,输出结果为,010501,010502
----------------------------
1,g,h
2,i,j以a,b为检索条件,查询a=02,b=0101,输出结果为,010101,010102,010103
----------------------------------
1,k,l,m
2,n,o,p以a,b为检索条件,查询a=02,b=0105,输出结果为,010501,010502
----------------------------
1,q,r
2,s,t请高手指教
a,序号,b,c,d
01,1,0101,010101,a
01,1,0101,010102,b
01,1,0101,010103,c
01,2,0101,010101,d
01,2,0101,010102,e
01,2,0101,010103,f
01,1,0105,010501,g
01,1,0105,010502,h
01,2,0105,010501,i
01,2,0105,010502,j
02,1,0101,010101,k
02,1,0101,010102,l
02,1,0101,010103,m
02,2,0101,010101,n
02,2,0101,010102,o
02,2,0101,010103,p
02,1,0105,010501,q
02,1,0105,010502,r
02,2,0105,010501,s
02,2,0105,010502,t
………………
字段a,序号,c为索引输出结果以a,b为检索条件,查询a=01,b=0101,输出结果为,010101,010102,010103
----------------------------------
1,a,b,c
2,d,e,f以a,b为检索条件,查询a=01,b=0105,输出结果为,010501,010502
----------------------------
1,g,h
2,i,j以a,b为检索条件,查询a=02,b=0101,输出结果为,010101,010102,010103
----------------------------------
1,k,l,m
2,n,o,p以a,b为检索条件,查询a=02,b=0105,输出结果为,010501,010502
----------------------------
1,q,r
2,s,t请高手指教
stf.b%type) is
2 l_line1 varchar2(4000) default '';
3 l_line2 varchar2(4000) default '';
4 begin
5 for y in (select distinct id from testf where a = p_a and b = p_b) loop
6 l_line1 := l_line1||', '||y.id;
7 l_line2 := l_line2||y.id;
8 for yd in (select distinct d from testf where a = p_a and b = p_b and
id = y.id) loop
9 l_line2 := l_line2||','||yd.d ;
10 end loop;
11 l_line2 := l_line2||chr(13);
12 end loop;
13 dbms_output.put_line(l_line1);
14 dbms_output.put_line('----------------------------');
15 dbms_output.put_line(l_line2);
16 end My_test;
17 /过程已创建。
demo@XIAOXIAO> exec My_test('01','0101');, 1, 2
----------------------------
1,a,b,c
2,d,e,f
demo@XIAOXIAO> exec My_test('01','0105');, 1, 2
----------------------------
1,g,h
2,i,j
l_line1 varchar2(4000) default '';
l_line2 varchar2(4000) default '';
begin
if p_a is not null and p_b is not null then
for y in (select distinct id from testf where a = p_a and b = p_b) loop
l_line1 := l_line1||', '||y.id;
l_line2 := l_line2||y.id;
for yd in (select distinct d from testf where a = p_a and b = p_b and id = y.id) loop
l_line2 := l_line2||','||yd.d ;
end loop;
l_line2 := l_line2||chr(13)||chr(10);
end loop;
dbms_output.put_line(l_line1);
dbms_output.put_line('----------------------------');
dbms_output.put_line(l_line2);
else
dbms_output.put_line('条件不足,请完整输入条件!');
end if;
end My_test;修改了一下,这样更完整一点
demo@XIAOXIAO>exec My_test('01','0105');
, 1, 2
----------------------------
1,g,h
2,i,jPL/SQL 过程已成功完成。demo@XIAOXIAO>exec My_test('01','');
条件不足,请完整输入条件!PL/SQL 过程已成功完成。
2 l_line1 varchar2(4000) default '';
3 l_line2 varchar2(4000) default '';
4 begin
5 if p_a is not null and p_b is not null then
6 for y in (select distinct id from testf where a = p_a and b = p_b) loop
7 l_line1 := l_line1||', '||y.id;
8 l_line2 := l_line2||y.id;
9 for yd in (select distinct d from testf where a = p_a and b = p_b and id = y.id) loop
10 l_line2 := l_line2||','||yd.d ;
11 end loop;
12 l_line2 := l_line2||chr(13)||chr(10);
13 end loop;
14 dbms_output.put_line(l_line1);
15 dbms_output.put_line('----------------------------');
16 dbms_output.put_line(l_line2);
17 else
18 dbms_output.put_line('条件不足,请完整输入条件!');
19 end if;
20 end My_test;
21 /警告: 创建的过程带有编译错误。
要用自己的表名替换偶的测试表名的