create procedure pro(p_table in varchar2,p_f1 in varchar2,p_rc out varchar2)
as
str varchar2(50);
begin
str:='select f1||f2||f3 from '||p_table||' where f1='||p_f1;
execute immediate str into p_rc;
end;
/
create or replace function SP_GetData(V_tableName IN STRING,v_condition in string)
return varchar2
IS
TYPE c_curtype IS REF CURSOR;
c_cur c_curtype;
str STRING(2000):='';
v_tname col.tname%TYPE;
v_cname col.cname%TYPE;
result string(1000);
BEGIN
OPEN c_cur FOR SELECT tname,cname FROM col
WHERE tname=''||V_tableName||'';
LOOP
FETCH c_cur INTO v_tname,v_cname;
EXIT WHEN c_cur%NOTFOUND;
str:=''||v_cname||''||''||str;
END LOOP;
CLOSE c_cur;
str:='select '||str||' into '||result||' from '||V_tableName where '||v_condition;
execute immediate str;
return result;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
end SP_GetData;
/
我沒有試,你測一下吧,會出錯的地方就在str:=''||v_cname||''||''||str;