具体如下:
第一步:创建table数组类型
TYPE t_person_id IS TABLE OF person.id%TYPE INDEX BY PLS_INTEGER;
第二步:创建函数
FUNCTION fn_get_id(person_id t_person_id,v_id person.id%TYPE)
RETURN varchar2
IS
ret_id varchar2(200) :='';
BEGIN
FOR idx IN person_id.FIRST..person_id.LAST
LOOP
IF(person_id(idx)=v_id) THEN
ret_id := ret_id || TO_CHAR(v_id) || ',';
END IF;
END LOOP;
RETURN ret_id;
END fn_get_id;
第三步,调用函数:
PROCEDURE up_select_person
IS
v varchar2(200);
person_id t_person_id;
BEGIN
SELECT p.id
BULK COLLECT INTO person_id
FROM person p;
v:=fn_get_id(person_id,100); --这样编译OK,也可以成功输出
/* SELECT fn_get_id(person_id,100)--如下语句不能实现,报错因为我要在查询语句中用到。
INTO v
FROM dual;*/
--比如:select id,name,fn_get_id(person_id,id) idstr from person之类的语句用到,但这报错
DBMS_OUTPUT.PUT_LINE(v);
END up_select_person;
报错:
Compilation errors for PACKAGE BODY CXW.PKG_TEST
Error: PLS-00382: expression is of wrong type
Line: 54
Text: SELECT fn_get_id(person_id,100)
Error: PLS-00306: wrong number or types of arguments in call to 'FN_GET_ID'
Line: 54
Text: SELECT fn_get_id(person_id,100)
Error: PL/SQL: ORA-00904: "PKG_TEST"."FN_GET_ID": invalid identifier
Line: 54
Text: SELECT fn_get_id(person_id,100)
Error: PL/SQL: SQL Statement ignored
Line: 54
Text: SELECT fn_get_id(person_id,100)
请问各位高手,是不是oracle不支持,有什么办法解决或替换
我的oracle版是10.2.0.1版本。