create table bbbb(a varchar2(1),b varchar2(1)); insert into bbbb values('1','a'); insert into bbbb values('1','b'); ...... --存储过程 create or replace procedure test_bbbb is cursor cur1 is select distinct a from bbbb; v_temp varchar2(4); begin for c in cur1 loop v_temp:=null; for i in (select b from bbbb where a=c.a) loop v_temp:=v_temp||i.b; end loop; dbms_output.put_line(c.a||','||v_temp); end loop; end test_bbbb;得到要求的结果
请问shine333(shine),procedure中的c, i是什么变量?为什么不用定义?
在cursor的for循环中,可以直接用一个变量表示有标的每一行,不用再定义
create function get_filed2(p_field1 in varchar2) return varchar2 as cursor t_sor is select field2 from tabname where field1=p_field1; str varchar2(50); begin for v_sor in t_sor loop str:=str||v_sor.field2; end loop; return str; end; / select field1,get_str(field1) from tabname group by field1
insert into bbbb values('1','a');
insert into bbbb values('1','b');
......
--存储过程
create or replace procedure test_bbbb is
cursor cur1 is
select distinct a from bbbb;
v_temp varchar2(4);
begin
for c in cur1 loop
v_temp:=null;
for i in (select b from bbbb where a=c.a) loop
v_temp:=v_temp||i.b;
end loop;
dbms_output.put_line(c.a||','||v_temp);
end loop;
end test_bbbb;得到要求的结果
return varchar2
as
cursor t_sor is
select field2 from tabname where field1=p_field1;
str varchar2(50);
begin
for v_sor in t_sor loop
str:=str||v_sor.field2;
end loop;
return str;
end;
/
select field1,get_str(field1) from tabname group by field1