多条记录转为一条记录
一般不好实现看样子你这里的权限最多不超过4个,在有限个的情况下还是可以的。select a.EnName,a.ChsName,a.Privs,b.Privs,c.Privs,d.Privs
from tbname a,tbname b,tbname c,tbname d
where a.EnName=b.EnName and a.ChsName=b.Chsname
and a.EnName=c.EnName and a.ChsName=c.Chsname
and a.EnName=d.EnName and a.ChsName=d.Chsname如果权限数量不定,就不好写了
一般不好实现看样子你这里的权限最多不超过4个,在有限个的情况下还是可以的。select a.EnName,a.ChsName,a.Privs,b.Privs,c.Privs,d.Privs
from tbname a,tbname b,tbname c,tbname d
where a.EnName=b.EnName and a.ChsName=b.Chsname
and a.EnName=c.EnName and a.ChsName=c.Chsname
and a.EnName=d.EnName and a.ChsName=d.Chsname如果权限数量不定,就不好写了
p_out varchar2(200):='';
p_temp varchar2(200):='';
cursor c is select a.Privs from 表名 a where a.EnName =p_EnName;
begin
open c;
loop
fetch c into p_temp ;
exit when c%notfound;
p_out:=p_out||','||p_temp;
end loop;
close c;
return p_out;
exception
when others then
return '出错';
end;
--使用
select distinct a.EnName,a.ChsName,test_privs(a.EnName) from 表名 a ;
你那样的写法不行的,你所产生的是笛卡尔集,推荐 dinya2003(OK)的写法,不过需要修正一下小的瑕疵create or replace function test_privs(p_EnName in varchar2) return varchar2 as
p_out varchar2(200):='';
p_temp varchar2(200):='';
cursor c is select a.Privs from 表名 a where a.EnName =p_EnName;
begin
open c;
loop
fetch c into p_temp ;
p_out:=p_out||','||p_temp;
exit when c%notfound;
end loop;
p_out:=substr(p_out,2);
close c;
return p_out;
exception
when others then
return '出错';
end;
Small VARCHAR2 variables are optimized for performance, and larger ones are
optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory tohold the actual value.For a VARCHAR2 variable that is shorter than 2000 bytes,PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.