select a.id as a_id, t.role_name from (select t1.id || ','||t2.id || ',' ||t3.id as id, t1.role_name || ','|| t2.role_name || ',' || t3.role_name as role_name from b t1, b t2, b t3) t, a where t.id = a.role_name;
用游标循环 首先取a表的role_name ,然后where b表.id in (role_name ),再循环组合成“你,我,他”
fetch cur_getid into cur_id; exit when cur_getid%notfound;
v_sql:=cur_id.id||' '||cur_id.rq1;--°ÑÓû§idÓëÈÕÆÚ·ÅÔÚÒ»Æð fetch cur_getrq into cur_rq;
--Ñ»·È¡µÃʱ¼ä if (cur_rq.id=cur_id.id and cur_rq.rq1=cur_id.rq1 ) then select count(*) into l from tt where tt.id=cur_id.id ; for i in 1..l loop v_sql:=v_sql||' '||cur_rq.rq2; fetch cur_getrq into cur_rq; end loop; end if;
DBMS_OUTPUT.put_line(v_sql);
end loop; exception when others then v_err :=sqlerrm; err:=v_err; --DBMS_OUTPUT.put_line(v_err); END prc_sxm1;
比较傻瓜的思路是: 1、写个split的函数通过符号分割字符串,网上代码很多; create or replace function new_split (str in varchar2, delimiter in varchar2).... 2、循环串, select new_split(name,',') into split_dataset from a; j := 1; len := split_dataset.count; while j <= len loop select name into rp_name from b where id=split_dataset(j);
j := j+1; v_sql := v_sql||rp_name; end loop; DBMS_OUTPUT.put_line(v_sql);
首先取a表的role_name ,然后where b表.id in (role_name ),再循环组合成“你,我,他”
看下这篇文章
create or replace procedure prc_sxm1(err out varchar2) is
/************************
Ò»ÕÅ±í£¨¿¼ÇÚÐÅÏ¢±í£©
×ֶΣºÓû§id,ÈÕÆÚ£¬Ê±¼ä
±íÖÐµÄ ¾ßÌåÄÚÈÝΪ
µÚ¶þÌâ:
Ô±í: °à¼¶ ѧºÅ
1 1
1 2
1 3
2 1
2 2
3 1
ת»»ºóµÄ: °à¼¶ ѧºÅ
1 1,2,3
2 1,2
3 1 declare
err varchar2(100);
begin
pro_sxm1(err);
dbms_output.put_line('error is'||err);
end;
select * from prc_sxm2
*************************/
cursor cur_get1 is
select distinct class from tt;
cursor cur_get2 is
select id,rq1 from tt;
v_sql varchar2(200);
v_err varchar2(200);
l integer;
i integer;
--v_sql:='';
cur_id cur_getid%rowtype;
cur_rq cur_getrq%rowtype;
BEGIN
open cur_getid;
open cur_getrq;
loop
fetch cur_getid into cur_id;
exit when cur_getid%notfound;
v_sql:=cur_id.id||' '||cur_id.rq1;--°ÑÓû§idÓëÈÕÆÚ·ÅÔÚÒ»Æð
fetch cur_getrq into cur_rq;
--Ñ»·È¡µÃʱ¼ä
if (cur_rq.id=cur_id.id and cur_rq.rq1=cur_id.rq1 )
then
select count(*) into l from tt where tt.id=cur_id.id ;
for i in 1..l
loop
v_sql:=v_sql||' '||cur_rq.rq2;
fetch cur_getrq into cur_rq;
end loop;
end if;
DBMS_OUTPUT.put_line(v_sql);
end loop;
exception when others
then v_err :=sqlerrm;
err:=v_err;
--DBMS_OUTPUT.put_line(v_err);
END prc_sxm1;
1、写个split的函数通过符号分割字符串,网上代码很多;
create or replace function new_split (str in varchar2, delimiter in varchar2)....
2、循环串,
select new_split(name,',') into split_dataset from a;
j := 1;
len := split_dataset.count;
while j <= len
loop
select name into rp_name from b where id=split_dataset(j);
j := j+1;
v_sql := v_sql||rp_name;
end loop;
DBMS_OUTPUT.put_line(v_sql);