j*****4
发帖数: 283 | 1 REATE OR REPLACE FUNCTION f_test (in_activity_id IN INTEGER)
RETURN VARCHAR2 AS
declare
cursor p_cur is
select coalesce(f.FULL_NAME , g.FULL_NAME) as p_name
from OCER_ACT_PRE e
left join ocer_p f on e.P_ID = f.ID
left join view2 g on f.person_id = g.person_id
where e.activity_id = in_activity_id;
p_rec p_cur%ROWTYPE;
lcl_p_list varchar2;
BEGIN
OPEN pr_cur;
LOOP
FETCH p_cur INTO p_rec;
EXIT WHEN p_cur%NOTFOUND;
lcl_p_list := pr_rec.p_name;
END LOOP;
CLOSE p_cur;
return lcl_p_list;
END; |
B*****g
发帖数: 34098 | 2 干嘛要loop？
【在 j*****4 的大作中提到】
: REATE OR REPLACE FUNCTION f_test (in_activity_id IN INTEGER)
: RETURN VARCHAR2 AS
: declare
: cursor p_cur is
: select coalesce(f.FULL_NAME , g.FULL_NAME) as p_name
: from OCER_ACT_PRE e
: left join ocer_p f on e.P_ID = f.ID
: left join view2 g on f.person_id = g.person_id
: where e.activity_id = in_activity_id;
: p_rec p_cur%ROWTYPE;
|
j*****4
发帖数: 283 | 3 想得到所有的 p_name.
Thanks.
改了一下，怎么也不能 compile.
REATE OR REPLACE FUNCTION f_test (in_activity_id IN INTEGER)
RETURN VARCHAR2 AS
declare
cursor p_cur is
select coalesce(f.FULL_NAME , g.FULL_NAME) as p_name
from OCER_ACT_PRE e
left join ocer_p f on e.P_ID = f.ID
left join view2 g on f.person_id = g.person_id
where e.activity_id = in_activity_id;
p_rec p_cur%ROWTYPE;
lcl_p_list varchar2;
BEGIN
OPEN p_cur;
LOOP
FETCH p_cur INTO p_rec;
EXIT WHEN p_cur%NOTFOUND;
lcl_p_list := p_rec.p_name;
END LOOP;
CLOSE p_cur;
return lcl_p_list;
END; |
B*****g
发帖数: 34098 | 4 你这个就是return最后一个pname
【在 j*****4 的大作中提到】
: 想得到所有的 p_name.
: Thanks.
: 改了一下，怎么也不能 compile.
: REATE OR REPLACE FUNCTION f_test (in_activity_id IN INTEGER)
: RETURN VARCHAR2 AS
: declare
: cursor p_cur is
: select coalesce(f.FULL_NAME , g.FULL_NAME) as p_name
: from OCER_ACT_PRE e
: left join ocer_p f on e.P_ID = f.ID
|
g***l
发帖数: 18555 | 5 CURSOR还LOOP。这是要做啥，你想讲讲要实现个什么吧 |
B*****g
发帖数: 34098 | 6 also "CREATE OR REPLACE"
【在 B*****g 的大作中提到】
: 你这个就是return最后一个pname
|
j*****4
发帖数: 283 | 7 Ok, I changed it. But still can not compile.
总说declare错. Any idea?
CREATE FUNCTION f_test (in_activity_id IN INTEGER)
RETURN VARCHAR2 AS
declare
cursor p_cur is
select coalesce(f.FULL_NAME , g.FULL_NAME) as p_name
from OCER_ACT_PRE e
left join ocer_p f on e.P_ID = f.ID
left join view2 g on f.person_id = g.person_id
where e.activity_id = in_activity_id;
p_rec p_cur%ROWTYPE;
lcl_p_list varchar2;
BEGIN
OPEN p_cur;
LOOP
FETCH p_cur INTO p_rec;
EXIT WHEN p_cur%NOTFOUND;
lcl_p_list := lcl_p_list || ';' || p_rec.p_name;
END LOOP;
CLOSE p_cur;
return lcl_p_list;
END;
【在 B*****g 的大作中提到】
: 你这个就是return最后一个pname
|
B*****g
发帖数: 34098 | 8 remove declare
【在 j*****4 的大作中提到】
: Ok, I changed it. But still can not compile.
: 总说declare错. Any idea?
: CREATE FUNCTION f_test (in_activity_id IN INTEGER)
: RETURN VARCHAR2 AS
: declare
: cursor p_cur is
: select coalesce(f.FULL_NAME , g.FULL_NAME) as p_name
: from OCER_ACT_PRE e
: left join ocer_p f on e.P_ID = f.ID
: left join view2 g on f.person_id = g.person_id
|
j*****4
发帖数: 283 | 9 more error message comming after remove.
Error(5,12): PLS-00341: declaration of cursor 'P_CUR' is incomplete or
malformed
【在 B*****g 的大作中提到】
: remove declare
|
y****9
发帖数: 144 | 10
SQL> CREATE or replace FUNCTION f_test (in_activity_id IN INTEGER)
2 RETURN VARCHAR2
3 AS
4 cursor p_cur is
5 select object_name p_name from user_objects e
6 where e.object_id = in_activity_id;
7 p_rec p_cur%ROWTYPE;
8 lcl_p_list varchar2(4000);
9 BEGIN
10 OPEN p_cur;
11 LOOP
12 FETCH p_cur INTO p_rec;
13 EXIT WHEN p_cur%NOTFOUND;
14 lcl_p_list := lcl_p_list || ';' || p_rec.p_name;
15 END LOOP;
16 CLOSE p_cur;
17 return lcl_p_list;
18 END;
19 /
Function created.
SQL> select f_test(1163615) from dual;
F_TEST(1163615)
----------------------------------------------------------------------------
--------------------------------------------------
-------------------------------------------------
;AUDIT_TRAIL
【在 j*****4 的大作中提到】
: more error message comming after remove.
: Error(5,12): PLS-00341: declaration of cursor 'P_CUR' is incomplete or
: malformed
|
|
|
B*****g
发帖数: 34098 | 11 楼上有人答了。另外你这个根本不用SP, SQL就行
http://www.oracle-base.com/articles/misc/StringAggregationTechn
【在 j*****4 的大作中提到】
: more error message comming after remove.
: Error(5,12): PLS-00341: declaration of cursor 'P_CUR' is incomplete or
: malformed
|
j*****4
发帖数: 283 | 12 谢谢，但在我这里怎么老是说 Error(5,16): PLS-00341: declaration of cursor 'P
_CUR' is incomplete or malformed
【在 y****9 的大作中提到】
:
: SQL> CREATE or replace FUNCTION f_test (in_activity_id IN INTEGER)
: 2 RETURN VARCHAR2
: 3 AS
: 4 cursor p_cur is
: 5 select object_name p_name from user_objects e
: 6 where e.object_id = in_activity_id;
: 7 p_rec p_cur%ROWTYPE;
: 8 lcl_p_list varchar2(4000);
: 9 BEGIN
|
B*****g
发帖数: 34098 | 13 post your new code
'P
【在 j*****4 的大作中提到】
: 谢谢，但在我这里怎么老是说 Error(5,16): PLS-00341: declaration of cursor 'P
: _CUR' is incomplete or malformed
|
j*****4
发帖数: 283 | 14 请指教。 我的版本是11.1， 好多函数用不了
【在 B*****g 的大作中提到】
: 楼上有人答了。另外你这个根本不用SP, SQL就行
: http://www.oracle-base.com/articles/misc/StringAggregationTechn
|
j*****4
发帖数: 283 | 15 CREATE OR REPLACE FUNCTION f_test (in_activity_id IN INTEGER)
RETURN VARCHAR2
AS
cursor preceptor_cur is
select coalesce(f.FULL_NAME , g.FULL_NAME) as preceptor_name
from OCER_ACT_PRE e
left join ocer_preceptor f on e.PRECEPTOR_ID = f.ID
left join view2 g on f.person_id = g.person_id
where e.activity_id = in_activity_id;
preceptor_rec preceptor_cur%ROWTYPE;
lcl_preceptor_list varchar2(4000);
BEGIN
OPEN preceptor_cur;
LOOP
FETCH preceptor_cur INTO preceptor_rec;
EXIT WHEN preceptor_cur%NOTFOUND;
lcl_preceptor_list := lcl_preceptor_list ||'~'|| preceptor_rec.preceptor
_name;
END LOOP;
CLOSE preceptor_cur;
return lcl_preceptor_list;
END;
【在 B*****g 的大作中提到】
: post your new code
:
: 'P
|
j*****4
发帖数: 283 | 16 Found it. It looks like the permission of the view2.
Thanks all of your help.
【在 j*****4 的大作中提到】
: CREATE OR REPLACE FUNCTION f_test (in_activity_id IN INTEGER)
: RETURN VARCHAR2
: AS
: cursor preceptor_cur is
: select coalesce(f.FULL_NAME , g.FULL_NAME) as preceptor_name
: from OCER_ACT_PRE e
: left join ocer_preceptor f on e.PRECEPTOR_ID = f.ID
: left join view2 g on f.person_id = g.person_id
: where e.activity_id = in_activity_id;
: preceptor_rec preceptor_cur%ROWTYPE;
|
B*****g
发帖数: 34098 | 17 下面有9i的solution，当然还有XML的solution，都可以用
【在 j*****4 的大作中提到】
: 请指教。 我的版本是11.1， 好多函数用不了
|
