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;
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; /
to jiezhi(浪子) : 真是没想到还有col这样奇妙的系统表 :)但下面这个部分我不是很明白: str:='select '||str||' into '||result||' from '||V_tableName where '||v_condition; execute immediate str; return result;改用 return str;不行吗?
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;
as
str varchar2(50);
begin
str:='select f1||f2||f3 from '||p_table||' where f1='||p_f1;
execute immediate str into p_rc;
end;
/
真是没想到还有col这样奇妙的系统表 :)但下面这个部分我不是很明白: str:='select '||str||' into '||result||' from '||V_tableName
where '||v_condition;
execute immediate str;
return result;改用 return str;不行吗?
我明白啦,你真聪明