日期:2014-05-17  浏览次数:20897 次

function、cursor 和EXECUTE IMMEDIATE的问题,谢谢同志们,快疯了。
现在想将SQL语句传入函数中,返回cursor  
create   or   replace   function   f_reCursor(sSql   in   varchar2)   return   CURSOR   is
    cur   CURSOR;
begin
    EXECUTE   IMMEDIATE     'cursor   '   ||cur||   '   is   '   ||   sSql;  
或(EXECUTE   IMMEDIATE     'cursor     cur   is   '   ||   sSql;   )也试过
    return(cur);
end   f_reCursor;

提示以下错误::::
FUNCTION   SDE.F_RECURSOR   编译错误

错误:PLS-00201:   必须声明标识符   'CURSOR '
行:1
文本:create   or   replace   function   f_reCursor(sSql   in   varchar2)   return   CURSOR   is

错误:PL/SQL:   Compilation   unit   analysis   terminated
行:0
文本:create   or   replace   function   f_reCursor(sSql   in   varchar2)   return   CURSOR   is


------解决方案--------------------
Create or REPLACE FUNCTION f_reCursor(sSql in varchar2)
RETURN SYS_REFCURSOR
AS
cur SYS_REFCURSOR;
BEGIN
OPEN cur FOR sSql;
RETURN cur;
END f_reCursor;

------解决方案--------------------
f_reCursor 返回的是SYS_REFCURSOR


declare
p_c sys_refcursor;
v_rec t1%rowtype;
begin
p_c=f_reCursor( 'select id,name from t1 where id <100 ');
loop
fetch p_c into v_rec;
EXIT WHEN p_c%NOTFOUND;
dbms_output.put_line(v_rec.id);
end loop;
end;