五一放假,帮你写一个,但是注意以后亲自动手写比较好。
CREATE OR REPLACE FUNCTION constr (pid CHAR)
RETURN CHAR
AS
cstr VARCHAR2 (100); CURSOR cur_str
IS
SELECT str
FROM tt
WHERE ID = pid; cc cur_str%ROWTYPE;
BEGIN
OPEN cur_str; LOOP
FETCH cur_str
INTO cc; EXIT WHEN cur_str%NOTFOUND;
cstr := cstr || cc.str || ',';
END LOOP; cstr := RTRIM (cstr, ',');
RETURN cstr; CLOSE cur_str;
EXCEPTION
WHEN OTHERS
THEN
RETURN '';
END constr;
/SQL> select * from tt;ID STR
---------- ----------
aa 11
aa 22
aa 33
bb 44
bb 55
cc 66已选择6行。SQL> select id,constr(id) from tt group by id;ID
----------
CONSTR(ID)
--------------------------------------------------
aa
11,22,33bb
44,55cc
66
CREATE OR REPLACE FUNCTION constr (pid CHAR)
RETURN CHAR
AS
cstr VARCHAR2 (100); CURSOR cur_str
IS
SELECT str
FROM tt
WHERE ID = pid; cc cur_str%ROWTYPE;
BEGIN
OPEN cur_str; LOOP
FETCH cur_str
INTO cc; EXIT WHEN cur_str%NOTFOUND;
cstr := cstr || cc.str || ',';
END LOOP; cstr := RTRIM (cstr, ',');
RETURN cstr; CLOSE cur_str;
EXCEPTION
WHEN OTHERS
THEN
RETURN '';
END constr;
/SQL> select * from tt;ID STR
---------- ----------
aa 11
aa 22
aa 33
bb 44
bb 55
cc 66已选择6行。SQL> select id,constr(id) from tt group by id;ID
----------
CONSTR(ID)
--------------------------------------------------
aa
11,22,33bb
44,55cc
66
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=t_c1) LOOP
Col_c2 := Col_c2||cur.c2||',';
END LOOP;
Col_c2 := rtrim(Col_c2,',');
RETURN Col_c2;
END;
/