例子:
declare
v_sql varchar2(2000);
cursor aa is select column from table;
begin
for v in aa loop
v_sql :='select * from '||v.column;--获得查询的语句
for v2 in (v_sql) loop --因为上面的语句是动态SQL,所以无论括号里放v_sql或者还是上面的语句都会报错,因为他说找不到表v.column,请麻烦教下,遇到这种情况怎么处理才好 end loop;
end loop;
end;
declare
v_sql varchar2(2000);
cursor aa is select column from table;
begin
for v in aa loop
v_sql :='select * from '||v.column;--获得查询的语句
for v2 in (v_sql) loop --因为上面的语句是动态SQL,所以无论括号里放v_sql或者还是上面的语句都会报错,因为他说找不到表v.column,请麻烦教下,遇到这种情况怎么处理才好 end loop;
end loop;
end;
要先open 然后fetch
declare
v_sql varchar2(2000);
cv_cur sys_refcursor;
v_num number;
begin
v_sql:='select 1 from dual';
open cv_cur for v_sql;
loop
fetch cv_cur into v_num;
exit when cv_cur%notfound;
dbms_output.put_line(v_num);
end loop;
end;
v_sql varchar2(2000);
v_sql2 varchar2(2000);
cv_cur sys_refcursor;
cv_cur2 sys_refcursor;
v_num number;
v_num2 number;
begin
v_sql:='select 1 from dual';
open cv_cur for v_sql;
loop
fetch cv_cur into v_num;
exit when cv_cur%notfound;
v_sql2:='select 2 from dual';
open cv_cur2 for v_sql2;
loop
fetch cv_cur2 into v_num2;
exit when cv_cur2%notfound;
dbms_output.put_line(v_num2);
end loop;
dbms_output.put_line(v_num);
end loop;
end;
declare
v_sql varchar2(2000);
cursor aa is select column from table;
type cur_type is refcursor;
cur1 cur_type;
赋值变量...;
begin
for v in aa loop
v_sql :='select * from '||v.column;--获得查询的语句
open cur1 for v_sql;
loop
fetch cur1 into 变量;
exit when cur1%notfound;.....;
end loop;
close cur1;
end loop;
end;
EXECUTE IMMEDIATE v_sql;
你执行的v_sql 到底是什么内容,自己一定要根据实际情况,在调试器中看清楚