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;