CREATE OR REPLACE PROCEDURE SHOWTABLE
AS
iCount integer;
sqlstr varchar2(1000);
begin
for x in(select tname from tab where tabtype='TABLE')
loop
sqlstr:= 'Select count(*) into iCount from ' || x.tname;
execute immediate sqlstr; --报错:缺少关键字
if iCount>0 then
DBMS_OUTPUT.put_line(x.tname);
end if;
end loop;
end;
AS
iCount integer;
sqlstr varchar2(1000);
begin
for x in(select tname from tab where tabtype='TABLE')
loop
sqlstr:= 'Select count(*) into iCount from ' || x.tname;
execute immediate sqlstr; --报错:缺少关键字
if iCount>0 then
DBMS_OUTPUT.put_line(x.tname);
end if;
end loop;
end;
照你的方法试了一下,还是老问题,缺少关键字!
AS
iCount integer;
sqlstr varchar2(1000);
begin
for x in(select table_name from all_tables where owner ='sys' )
loop
sqlstr:= 'Select count(*) into iCount from ' || x.table_name;
execute immediate sqlstr using icount;
if iCount>0 then
DBMS_OUTPUT.put_line(x.table_name);
end if;
end loop;
end;
编译成功的
execute immediate sqlstr using icount;
请问sqlstr:= 'Select count(*) into :v from ' || x.table_name; 中的":v"是什么意思?
照你的方法试了一下,提示赋值变量不存在!
改成sqlstr:= 'Select count(*) into :icount from ' || x.table_name;也是不行.
execute immediate sqlstr into icount;
v_table_name varchar2(100) :='tablename';
...
begin
....
sqlstr:= 'Select count(*) from :tablename';
execute immediate sqlstr into v_result using v_table_name;
....
end ;