是这样的table 中如一列
cghthm_
as
sa
ff
..
转换成 as,sa,ff,..
我有一个转换函数
create or replace function f_get_gzdcghthm(v_fphm IN VARCHAR2)
RETURN CLOB
AS
v_ls_s varchar2(4096);
v_cghth VARCHAR2(30);
begin
v_ls_s := '';
v_cghth := '';
begin
select v_ls_s ¦ ¦ case when cghthm_ = v_cghth then '' else cghthm_
end ¦ ¦ ',',cghthm_ INTO v_ls_s,v_cghth from cw_fksqdmx where fphm_ = v_fphm;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
end;
if SUBSTR(CAST(v_ls_s AS VARCHAR2),1,4000) <> ' ' then
v_ls_s := SUBSTR(v_ls_s,1,LENGTH(v_ls_s) -1);
END IF;
return v_ls_s;
end; 但是提示case 中有错误
cghthm_
as
sa
ff
..
转换成 as,sa,ff,..
我有一个转换函数
create or replace function f_get_gzdcghthm(v_fphm IN VARCHAR2)
RETURN CLOB
AS
v_ls_s varchar2(4096);
v_cghth VARCHAR2(30);
begin
v_ls_s := '';
v_cghth := '';
begin
select v_ls_s ¦ ¦ case when cghthm_ = v_cghth then '' else cghthm_
end ¦ ¦ ',',cghthm_ INTO v_ls_s,v_cghth from cw_fksqdmx where fphm_ = v_fphm;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
end;
if SUBSTR(CAST(v_ls_s AS VARCHAR2),1,4000) <> ' ' then
v_ls_s := SUBSTR(v_ls_s,1,LENGTH(v_ls_s) -1);
END IF;
return v_ls_s;
end; 但是提示case 中有错误
这样拼起来,还能CASE?
end ¦ ¦ ',',cghthm_ INTO v_ls_s,v_cghth from cw_fksqdmx where fphm_ = v_fphm; sql server中有这种用法,即一个变量可以把字段的所有值都加在一起。oracle没有这个功能(或者我还没学到)
create function dbo.f_get_gzdcghtm(@fphm varchar(30))
returns varchar(4096)
as
begin
declare @ls_s varchar(4096)
declare @cghthm varchar(30)
set @ls_s=''
set @cghthm=''
select @ls_s=@ls_s+case when cghthm_=@cghthm then '' else cghthm_ end +',',@cghthm=cghthm_ from cw_fksqdmx where fphm_=@fphm
if @ls_s<>''
set @ls_s=left(@ls_s,len(@ls_s)-1)
return @ls_s
end
create or replace function f_get_gzdcghthm(v_fphm IN VARCHAR2)
RETURN varchar2(4096)
AS
v_ls_s varchar2(4096);
begin
v_ls_s := '';
for x in (select cghthm_, v_cghth from cw_fksqdmx where fphm_ = v_fphm)
begin
v_ls_s := v_ls_s + case when x.cghthm_ = x.v_cghth then '' else x.cghthm_ end;
end; return v_ls_s;
end;