create or replace procedure proc (p_out_1 out number, p_out_2 out varchar2) is begin select type,wm_concat(NAME) into p_out_1, p_out_2 from test where type = 1 group by type; exception when no_data_found then p_out_1 := 0; p_out_2 := ' '; when others then raise; end proc;
create or replace procedure proc(p_type in varchar2,p_result out varchar2) as v_count number; begin select count(1) into v_count from tmp where type=p_type; if v_count=0 then p_result:='0'; else select wm_concat(name) into p_result from tmp where type=p_type; end if; end proc; 调用 declare v varchar2(100); begin proc('1',v); dbms_output.put_line(v); end;
create or replace procedure proc(p_type in varchar2,p_result out varchar2) as v_count number; begin select count(1) into v_count from tmp where type=p_type; if v_count=0 then p_result:='0'; else select substr(max(sys_connect_by_path(name,',')),2) into p_result from ( select name,row_number()over(order by rownum)rn from tmp where type=p_type) connect by prior rn=rn-1 start with rn=1; end if; end proc;
begin
select type,wm_concat(NAME) into p_out_1, p_out_2 from test where type = 1 group by type;
exception
when no_data_found then
p_out_1 := 0;
p_out_2 := ' ';
when others then
raise;
end proc;
as
v_count number;
begin
select count(1) into v_count from tmp
where type=p_type;
if v_count=0 then p_result:='0';
else
select wm_concat(name) into p_result from tmp
where type=p_type;
end if;
end proc;
调用
declare
v varchar2(100);
begin
proc('1',v);
dbms_output.put_line(v);
end;
小明
小二
变成
小明,小二
这样的效果数据库是10以下吗,可以自己写一个函数,调用这个解决http://topic.csdn.net/u/20090616/09/d70860d6-ca63-445e-85d7-dad95256b021.html
#9
as
v_count number;
begin
select count(1) into v_count from tmp
where type=p_type;
if v_count=0 then p_result:='0';
else
select substr(max(sys_connect_by_path(name,',')),2) into p_result from (
select name,row_number()over(order by rownum)rn from tmp
where type=p_type)
connect by prior rn=rn-1
start with rn=1;
end if;
end proc;