PROMPT CREATE OR REPLACE FUNCTION getscr
CREATE OR REPLACE function getscr(V_item IN VARCHAR2) return varchar2
is V_string VARCHAR2(1000);
V_count NUMBER;
-- V_item VARCHAR2;
CURSOR c_scr( V_fsc VARCHAR2) IS
[color=red] SELECT imageid FROM gj_pp_image WHERE ppid=V_fsc;[/color]
begin
V_string :='';
V_count :=0;
FOR v_scr IN c_scr(V_item) LOOP
V_count :=V_count+1;
IF V_string is null THEN
V_string :=v_scr.imageid;
else
V_string := V_string||','||v_scr.imageid;
END if ;
END LOOP;
return V_string;
end;
/
作用--把表 gj_pp_image中相同ppid的行的imageid合成一个字段,用逗号隔开,事例:
SELECT a.*,getscr(a.ppid) imageid FROM gj_pp a;
得到的结果:
PPID PPNUM YEAR_QUARTER NAME ISSUEDBY CATEGORY IMAGEID
9 1324654 2013-3Q s-watch sylviaz 1 59,61,60
CREATE OR REPLACE function getscr(V_item IN VARCHAR2) return varchar2
is V_string VARCHAR2(1000);
V_count NUMBER;
-- V_item VARCHAR2;
CURSOR c_scr( V_fsc VARCHAR2) IS
[color=red] SELECT imageid FROM gj_pp_image WHERE ppid=V_fsc;[/color]
begin
V_string :='';
V_count :=0;
FOR v_scr IN c_scr(V_item) LOOP
V_count :=V_count+1;
IF V_string is null THEN
V_string :=v_scr.imageid;
else
V_string := V_string||','||v_scr.imageid;
END if ;
END LOOP;
return V_string;
end;
/
作用--把表 gj_pp_image中相同ppid的行的imageid合成一个字段,用逗号隔开,事例:
SELECT a.*,getscr(a.ppid) imageid FROM gj_pp a;
得到的结果:
PPID PPNUM YEAR_QUARTER NAME ISSUEDBY CATEGORY IMAGEID
9 1324654 2013-3Q s-watch sylviaz 1 59,61,60