我写了一个但是PL/SQL:
declare
num number(10);
tablename char(10);
cursor name is
select tname from tab where tname like 'abc%';
begin
open name;
fetch name into tablename;
loop
exit when name%NOTFOUND;
select count(*) into num from :tablename a, abc_table b where a.number = b.number;
end loop;
end;
我执行的时候为什么总是说我的:tablename写的不对,他不认我的:tablename是一个variable,请问应该怎么用啊?
谢谢。
declare
num number(10);
tablename char(10);
cursor name is
select tname from tab where tname like 'abc%';
begin
open name;
loop
fetch name into tablename;
exit when name%NOTFOUND;
EXECUTE IMMEDIATE 'select count(*) into num from '||tablename ||' a ' || ', abc_table b where a.number = b.number';
end loop;
end;
谢谢楼上的,我试了一下修改后的程序,虽然不报错了,可是那个num的值还是不对,如果把num提出来就对啦,所以正确方法如下:
ideclare
num number(10);
tablename char(10);
cursor name is
select tname from tab where tname like 'abc%';
begin
open name;
loop
fetch name into tablename;
exit when name%NOTFOUND;
EXECUTE IMMEDIATE 'select count(*) from ' || tablename || ' a ' || ', abc_table b where a.number = b.number' into num;
end loop;
end;
这个好像8i以后的功能,oracle 8不支持。